Granting control of SUSET gucs to non-superusers

Greetings,

On Fri, Apr 30, 2021 at 19:19 Mark Dilger <mark.dilger@enterprisedb.com>
wrote:

PostgreSQL defines a number of GUCs that can only be set by superusers. I
would like to support granting privileges on subsets of these to
non-superuser roles, inspired by Stephen Frost's recent work on
pg_read_all_data and pg_write_all_data roles.

There’s been some effort started in this direction which I was working on
(see the patches about an “admin” role and set of GUCs). I have been
meaning to get back to that but the specific concern I had was about coming
up with how to define the proper set of GUCs.

The specific use case motivating this work is that of a PostgreSQL service

provider. The provider guarantees certain aspects of the service, such as
periodic backups, replication, uptime, availability, etc., while making no
guarantees of other aspects, such as performance associated with the design
of the schema or the queries executed. The provider should be able to
grant to the tenant privileges to set any GUC which cannot be used to
"escape the sandbox" and interfere with the handful of metrics being
guaranteed. Given that the guarantees made by one provider may differ from
those made by another, the exact set of GUCs which the provider allows the
tenant to control may differ.

By my count, there are currently 50 such GUCs, already broken down into 15
config groups. Creating a single new role pg_set_all_gucs seems much too
coarse a control, but creating 50 new groups may be excessive. We could
certainly debate which GUCs could be used to escape the sandbox vs. which
ones could not, but I would prefer a design that allows the provider to
make that determination. The patch I would like to submit would only give
the provider the mechanism for controlling these things, but would not make
the security choices for them.

Do folks think it would make sense to create a role per config group?
Adding an extra 15 default roles seems high to me, but organizing the
feature this way would make the roles easier to document, because there
would be a one-to-one correlation between the roles and the config groups.

New predefined roles are relatively inexpensive. That said, whatever sets
we define need to have some meaning to them- one which is reasonably
future-proofed so that we have some idea what category a new GUC would fit
into.

“Can’t be used to gain superuser” may be a sufficiently clear grouping, as
was more or less contemplated by the “admin” approach. If that doesn’t
work though then we need an understanding of what the limits on these
groups are, so we can competently fit new GUCs into these groups (or invent
new ones if a new GUC truly falls outside all existing but I would expect
that to be a rather rare case..).

We may also wish to keep some GUCs superuser only when they really only
make sense to be used in a developer context...

Thanks,

Stephen

On 04/30/21 19:19, Mark Dilger wrote:

We could certainly debate which GUCs could be used to escape the sandbox
vs. which ones could not, but I would prefer a design that allows the
provider to make that determination.

I find myself wondering how many GUCs flagged SUSET are not flagged that way
because of a determination already made that they could be used to escape.
(Maybe some of the logging ones, only usable to conceal your escape.)

But there might be ways for a provider, scrutinizing each of those
individually, to conclude "this will not allow escape from the sandbox
/I/ have set up, provided the value being set satisfies constraints
x and y" ... a generalization of the LOAD from $libdir/plugins idea.

So that suggests to me some mechanism where a provider could grant
setting foo to role bar using validator baz().

Can SUSET GUCs be set from SECURITY DEFINER functions? Maybe there are
already the pieces to do that, minus some syntax sugar.

Regards,
-Chap

Stephen Frost <sfrost@snowman.net> writes:

On Fri, Apr 30, 2021 at 19:19 Mark Dilger <mark.dilger@enterprisedb.com>
wrote:

PostgreSQL defines a number of GUCs that can only be set by superusers. I
would like to support granting privileges on subsets of these to
non-superuser roles, inspired by Stephen Frost's recent work on
pg_read_all_data and pg_write_all_data roles.

New predefined roles are relatively inexpensive. That said, whatever sets
we define need to have some meaning to them- one which is reasonably
future-proofed so that we have some idea what category a new GUC would fit
into.
“Can’t be used to gain superuser” may be a sufficiently clear grouping, as
was more or less contemplated by the “admin” approach. If that doesn’t
work though then we need an understanding of what the limits on these
groups are, so we can competently fit new GUCs into these groups (or invent
new ones if a new GUC truly falls outside all existing but I would expect
that to be a rather rare case..).
We may also wish to keep some GUCs superuser only when they really only
make sense to be used in a developer context...

Hmm, is there really any point in that? We already have roles
like "pg_write_server_files" and "pg_execute_server_program",
which allow trivial escalation to superuser if one wishes,
but are still useful as being roles you're a bit less likely
to break your database with accidentally than running as full
superuser.

So ISTM that "pg_set_superuser_parameters" could be treated as
being one of those same sorts of roles that you don't give out
to untrusted people, and then we don't have to worry about
exactly which GUCs might be exactly how dangerous to whom.

If we try to define it as being some lesser level of
privilege than that, I'm afraid we will spend lots of
not-very-productive time trying to classify the security
threats from different GUCs ... and they all have *some*
security issue involved, or they wouldn't be restricted in
the first place. Plus, I'm not looking forward to having
to issue CVEs when we realize we misclassified something.

regards, tom lane

On Apr 30, 2021, at 4:28 PM, Stephen Frost <sfrost@snowman.net> wrote:

“Can’t be used to gain superuser” may be a sufficiently clear grouping, as was more or less contemplated by the “admin” approach. If that doesn’t work though then we need an understanding of what the limits on these groups are, so we can competently fit new GUCs into these groups (or invent new ones if a new GUC truly falls outside all existing but I would expect that to be a rather rare case..).

When I first heard that providers want to build sandboxes around PostgreSQL, I thought the idea was a little silly, because providers can just spin up a virtual machine per tenant and give each tenant superuser privileges on their respective VM. Who cares if they mess it up after that?

The problem with that idea turns out to be that the providers want to take responsibility for some of the database maintenance, possibly including backups, replication, etc. I think the set of controls the provider hands over to the tenant will depend very much on the division of responsibility. If the provider is managing replication, then control over session_replication_role and wal_compression is unlikely to be handed to the tenant, but if the tenant is responsible for their own replication scheme, it might be.

Viewing all of this in terms of which controls allow the tenant to escape a hypothetical sandbox seems like the wrong approach. Shouldn't we let service providers decide which controls would allow the tenant to escape the specific sandbox the provider has designed?

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Fri, 30 Apr 2021 at 22:00, Mark Dilger <mark.dilger@enterprisedb.com>
wrote:

Viewing all of this in terms of which controls allow the tenant to escape
a hypothetical sandbox seems like the wrong approach. Shouldn't we let
service providers decide which controls would allow the tenant to escape
the specific sandbox the provider has designed?

I’m not even sure I should be mentioning this possibility, but what if we
made each GUC parameter a grantable privilege? I’m honestly not sure if
this is insane or not. I mean numerically it’s a lot of privileges, but
conceptually it’s relatively simple.

What I like the least about it is actually the idea of giving up entirely
on the notion of grouping privileges into reasonable packages: some of
these privileges would be quite safe to grant in many or even most
circumstances, while others would usually not be reasonable to grant.

On 04/30/21 22:00, Mark Dilger wrote:

Viewing all of this in terms of which controls allow the tenant to escape
a hypothetical sandbox seems like the wrong approach. Shouldn't we let
service providers decide which controls would allow the tenant to escape
the specific sandbox the provider has designed?

I agree that sounds more like the right approach. It seems to me that
in the general case, a provider might conclude that setting foo is
safe in the provider-designed sandbox /if the value being assigned
to it satisfies some provider-determined conditions/.

On 04/30/21 20:02, Chapman Flack wrote:

So that suggests to me some mechanism where a provider could grant
setting foo to role bar using validator baz().

Can SUSET GUCs be set from SECURITY DEFINER functions? Maybe there are
already the pieces to do that, minus some syntax sugar.

The answer seems to be yes: I just created a SECURITY DEFINER function
and used it to change a SUSET-only GUC setting.

So it seems the machinery is already in place with which a provider
could allow a chosen set of SUSET-only GUCs to be set, to values that
satisfy provider-determined conditions, by users in a provider-chosen
role.

Some pretty syntax like GRANT SETTING foo TO ROLE bar WHERE cond;
would simply be sugar on top.

Regards,
-Chap

On May 1, 2021, at 7:07 AM, Chapman Flack <chap@anastigmatix.net> wrote:

On 04/30/21 22:00, Mark Dilger wrote:

Viewing all of this in terms of which controls allow the tenant to escape
a hypothetical sandbox seems like the wrong approach. Shouldn't we let
service providers decide which controls would allow the tenant to escape
the specific sandbox the provider has designed?

I agree that sounds more like the right approach. It seems to me that
in the general case, a provider might conclude that setting foo is
safe in the provider-designed sandbox /if the value being assigned
to it satisfies some provider-determined conditions/.

So it seems the machinery is already in place with which a provider
could allow a chosen set of SUSET-only GUCs to be set, to values that
satisfy provider-determined conditions, by users in a provider-chosen
role.

Some pretty syntax like GRANT SETTING foo TO ROLE bar WHERE cond;
would simply be sugar on top.

I agree with everything you say here. I have some thoughts about usability....

I'd like the experience for the tenant to be as similar as possible to having superuser privileges on their own cluster. The tenant may be migrating an application from a database that they currently manage themselves, and any need to use different syntax from what they have been using is an extra hurdle that could derail the migration.

Extra syntax for use by the service provider seems much easier to justify.

If the service provider can install extra role-aware check_hooks for gucs, and if the include directive for postgresql.conf can specify a role under which a postgresql.conf.tenant file is processed, then the tenant can port their application and their config file and the only things that should break are those things the provider has intentionally prohibited.

Does this sound like a reasonable approach?

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 05/01/21 12:13, Mark Dilger wrote:

Extra syntax for use by the service provider seems much easier to justify.

If the service provider can install extra role-aware check_hooks for gucs,
and if the include directive for postgresql.conf can specify a role under
which a postgresql.conf.tenant file is processed, then the tenant can port
their application and their config file and the only things that should break
are those things the provider has intentionally prohibited.

Maybe version 0 is where the provider just builds a shared object
to go in shared_preload_libraries. The provider has probably already
done a bunch of other stuff more challenging than that.

The GUC system would have to expose a way for the shared object to
chain extra_check_hooks off existing GUCs. An extra_check_hook can check
both the value and the role of the caller.

The configfile syntax for include-with-a-role would be the only other
missing piece.

Version 0.5 is maybe where someone contributes code for such a shared
object that is somewhat general and configured by a yaml file, or
something. (That would probably be easier if an extra_check_hook accepts
the usual void *extra context argument that existing GUC hooks don't.)

Regards,
-Chap

Hi,

On Fri, Apr 30, 2021 at 04:19:22PM -0700, Mark Dilger wrote:

PostgreSQL defines a number of GUCs that can only be set by
superusers. I would like to support granting privileges on subsets of
these to non-superuser roles, inspired by Stephen Frost's recent work
on pg_read_all_data and pg_write_all_data roles.

The specific use case motivating this work is that of a PostgreSQL
service provider. The provider guarantees certain aspects of the
service, such as periodic backups, replication, uptime, availability,
etc., while making no guarantees of other aspects, such as performance
associated with the design of the schema or the queries executed. The
provider should be able to grant to the tenant privileges to set any
GUC which cannot be used to "escape the sandbox" and interfere with
the handful of metrics being guaranteed. Given that the guarantees
made by one provider may differ from those made by another, the exact
set of GUCs which the provider allows the tenant to control may
differ.

By my count, there are currently 50 such GUCs, already broken down
into 15 config groups. Creating a single new role pg_set_all_gucs
seems much too coarse a control, but creating 50 new groups may be
excessive. We could certainly debate which GUCs could be used to
escape the sandbox vs. which ones could not, but I would prefer a
design that allows the provider to make that determination. The patch
I would like to submit would only give the provider the mechanism for
controlling these things, but would not make the security choices for
them.

Do folks think it would make sense to create a role per config group?
Adding an extra 15 default roles seems high to me, but organizing the
feature this way would make the roles easier to document, because
there would be a one-to-one correlation between the roles and the
config groups.

I have a WIP patch that I'm not attaching, but if I get any feedback,
I might be able to adjust the patch before the first version posted.
The basic idea is that it allows things like:

GRANT pg_set_stats_monitoring TO tenant_role;

And then tenant_role could, for example

SET log_parser_stats TO off;

Just saying, I've proposed something very similar, albeit for a narrower
scope (mostly the Reporting and Logging category) here:
/messages/by-id/c2ee39152957af339ae6f3e851aef09930dd2faf.camel@credativ.de

Michael

--
Michael Banck
Projektleiter / Senior Berater
Tel.: +49 2166 9901-171
Fax: +49 2166 9901-100
Email: michael.banck@credativ.de

credativ GmbH, HRB M�nchengladbach 12080
USt-ID-Nummer: DE204566209
Trompeterallee 108, 41189 M�nchengladbach
Gesch�ftsf�hrung: Dr. Michael Meskes, Sascha Heuer

Unser Umgang mit personenbezogenen Daten unterliegt
folgenden Bestimmungen: https://www.credativ.de/datenschutz

On Sat, May 1, 2021 at 12:37 PM Chapman Flack <chap@anastigmatix.net> wrote:

Maybe version 0 is where the provider just builds a shared object
to go in shared_preload_libraries. The provider has probably already
done a bunch of other stuff more challenging than that.

The GUC system would have to expose a way for the shared object to
chain extra_check_hooks off existing GUCs. An extra_check_hook can check
both the value and the role of the caller.

I think there are two parts to this problem. First, the SP needs to be
able to delegate to some users but not others the ability to set
superuser GUCs. Second, the SP needs to be able to control which GUCs
the privileged users get to set and perhaps to what values. A hook of
the type you propose here seems like it might work reasonably well for
that second part, but it's not totally obvious to me how it helps with
the first part.

Instead of going to the extreme of one predefined role per GUC, maybe
we could see if the PGC_SUSET GUCs could be divided into buckets based
on the reason they are so marked? For example, log_parser_stats,
log_planner_stats, log_executor_stats, log_statement_stats,
log_btree_build_stats, trace_locks, trace_userlocks, trace_lwlocks,
log_min_duration_statement, and a bunch of others are probably all
SUSET just on the theory that only the superuser should have the right
to control what ends up in the log. But we could make a predefined
role that represents the right to control what ends up in the log, and
then all of those GUCs could be tied to that role. Is that too
coarse-grained? It might be.

One problem with having a separate predefined role for every PGC_SUSET
GUC is that it's no help for extensions. Both auto_explain and
pg_stat_statements have such GUCs, and there may be out-of-core
extensions that do as well. We should try to come up with a system
that doesn't leave them out in the cold.

--
Robert Haas
EDB: http://www.enterprisedb.com

On 05/03/21 11:22, Robert Haas wrote:

The GUC system would have to expose a way for the shared object to
chain extra_check_hooks off existing GUCs. An extra_check_hook can check
both the value and the role of the caller.

I think there are two parts to this problem. First, the SP needs to be
able to delegate to some users but not others the ability to set
superuser GUCs. Second, the SP needs to be able to control which GUCs
the privileged users get to set and perhaps to what values. A hook of
the type you propose here seems like it might work reasonably well for
that second part, but it's not totally obvious to me how it helps with
the first part.

I guess I was thinking, but forgot to convey to the keyboard, that the
existence of a non-empty extra_check_hooks chain on a SUSET GUC (which
could only have been attached from a shared preload library) would
implicitly change SUSET to mean settable whenever accepted by the hook(s).

Regards,
-Chap

On May 3, 2021, at 8:22 AM, Robert Haas <robertmhaas@gmail.com> wrote:

One problem with having a separate predefined role for every PGC_SUSET
GUC is that it's no help for extensions. Both auto_explain and
pg_stat_statements have such GUCs, and there may be out-of-core
extensions that do as well. We should try to come up with a system
that doesn't leave them out in the cold.

As things stand, all custom variables defined via the DefineCustom{Bool,Int,Real,String,Enum}Variable are placed in the CUSTOM_OPTIONS config_group. We could add a role for controlling any SUSET CUSTOM_OPTIONS GUCs, or we could extend those functions to take a config_group option, or perhaps some of both. I haven't thought too much yet about whether allowing extensions to place a custom GUC into one of the predefined groups would be problematic. Any thoughts on that?

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Mon, May 3, 2021 at 11:45 AM Chapman Flack <chap@anastigmatix.net> wrote:

I guess I was thinking, but forgot to convey to the keyboard, that the
existence of a non-empty extra_check_hooks chain on a SUSET GUC (which
could only have been attached from a shared preload library) would
implicitly change SUSET to mean settable whenever accepted by the hook(s).

Sure, but the hook still needs a way to know which users are entitled
to set the GUC.

--
Robert Haas
EDB: http://www.enterprisedb.com

On Mon, May 3, 2021 at 12:25 PM Mark Dilger
<mark.dilger@enterprisedb.com> wrote:

As things stand, all custom variables defined via the DefineCustom{Bool,Int,Real,String,Enum}Variable are placed in the CUSTOM_OPTIONS config_group. We could add a role for controlling any SUSET CUSTOM_OPTIONS GUCs, or we could extend those functions to take a config_group option, or perhaps some of both. I haven't thought too much yet about whether allowing extensions to place a custom GUC into one of the predefined groups would be problematic. Any thoughts on that?

Well...

One idea would be to get rid of PGC_SUSET altogether and instead have
a set of flags associated with each GUC, like PGF_SERVER_LOG,
PGF_CORRUPT_DATA, PGF_CRASH_SERVER. Then you could associate those
flags with particular predefined roles and grant them out to whoever
you want.

So if a GUC is flagged PGF_SERVER_LOG|PGF_CRASH_SERVER, then the
assumption is that it's security-sensitive because it both lets you
alter the contents of the server log and also lets you crash the
server. If you are granted both pg_server_log and pg_crash_server, you
can set it, otherwise not.

This is just wild brainstorming, but my point is that I don't think
doing it by options groups is particularly good, because it doesn't
really have any relationship to why those things are marked SUSET in
the first place. To take an example involving functions rather than
GUCs, the pageinspect functions are super-user only because you can
crash the server by inspecting malformed data that you supply as an
arbitrarily literal, but AFAIK the functions in pgstattuple have no
similar hazard, and are just super-only because we don't really know
who the superuser wants to authorize, and maybe it's not everybody. So
those cases are really different, even though both are extensions. I
think the same likely holds true for GUCs.

--
Robert Haas
EDB: http://www.enterprisedb.com

On 05/03/21 13:23, Robert Haas wrote:

On Mon, May 3, 2021 at 11:45 AM Chapman Flack <chap@anastigmatix.net> wrote:

I guess I was thinking, but forgot to convey to the keyboard, that the
existence of a non-empty extra_check_hooks chain on a SUSET GUC (which
could only have been attached from a shared preload library) would
implicitly change SUSET to mean settable whenever accepted by the hook(s).

Sure, but the hook still needs a way to know which users are entitled
to set the GUC.

I was contemplating a version 0 with only that minimal support in core
for allowing a shared preload library to set such hooks (and allowing
include-with-a-role in config files), assuming service providers already
do sophisticated building of stuff to construct the environments they
provide, and a C shared object with hooks that enforce their designed
constraints wouldn't be an onerous burden on top of that.

Such providers could then be the laboratories of democracy building
various forms of such things, and if one of those ends up having a
reasonably general configuration mechanism and gets offered as a
contrib module later or for inclusion in core, well, that's version 0.5.

Regards,
-Chap

Greetings,

* Robert Haas (robertmhaas@gmail.com) wrote:

On Mon, May 3, 2021 at 12:25 PM Mark Dilger
<mark.dilger@enterprisedb.com> wrote:

As things stand, all custom variables defined via the DefineCustom{Bool,Int,Real,String,Enum}Variable are placed in the CUSTOM_OPTIONS config_group. We could add a role for controlling any SUSET CUSTOM_OPTIONS GUCs, or we could extend those functions to take a config_group option, or perhaps some of both. I haven't thought too much yet about whether allowing extensions to place a custom GUC into one of the predefined groups would be problematic. Any thoughts on that?

Well...

One idea would be to get rid of PGC_SUSET altogether and instead have
a set of flags associated with each GUC, like PGF_SERVER_LOG,
PGF_CORRUPT_DATA, PGF_CRASH_SERVER. Then you could associate those
flags with particular predefined roles and grant them out to whoever
you want.

So if a GUC is flagged PGF_SERVER_LOG|PGF_CRASH_SERVER, then the
assumption is that it's security-sensitive because it both lets you
alter the contents of the server log and also lets you crash the
server. If you are granted both pg_server_log and pg_crash_server, you
can set it, otherwise not.

This is just wild brainstorming, but my point is that I don't think
doing it by options groups is particularly good, because it doesn't
really have any relationship to why those things are marked SUSET in
the first place. To take an example involving functions rather than
GUCs, the pageinspect functions are super-user only because you can
crash the server by inspecting malformed data that you supply as an
arbitrarily literal, but AFAIK the functions in pgstattuple have no
similar hazard, and are just super-only because we don't really know
who the superuser wants to authorize, and maybe it's not everybody. So
those cases are really different, even though both are extensions. I
think the same likely holds true for GUCs.

In general, I agree that we should be looking at predefined roles as
being similar to the Linux capabilities system- defining certain kinds
of operations which the user who has that role is allowed to do, and
then both in-core and extensions can make decisions based on what
capabilities the user has been GRANT'd.

Hopefully that would limit the amount of cases where a given capability
ends up being overly broad while at the same time allowing extensions to
sensibly be able to use the defined capabilities for their own needs.

As we do in other places, we should make it clear when a certain
capability allows a user with that capability to gain superuser access
as that may not always be clear to a user.

One thing that seems missing from this discussion and is part of what
paused my effort on the 'admin' role proposed towards the end of the
last cycle is that we really need to consider how this all plays with
ALTER SYSTEM and not just SUSET GUCs but also other (eg: POSTMASTER,
SIGHUP) GUCs. That is- imv we should have a sensible solution for
more-or-less all GUCs and which would allow a non-superuser to be able
to set POSTMASTER and SIGHUP GUCs (and perhaps others..) through
ALTER SYSTEM.

Thanks,

Stephen

Stephen Frost <sfrost@snowman.net> writes:

One thing that seems missing from this discussion and is part of what
paused my effort on the 'admin' role proposed towards the end of the
last cycle is that we really need to consider how this all plays with
ALTER SYSTEM and not just SUSET GUCs but also other (eg: POSTMASTER,
SIGHUP) GUCs.

Yeah, I'd meant to bring that up too. The ability to use ALTER
SYSTEM freely is probably a much bigger use-case than messing with
SUSET variables within one's own session.

I'm still of the opinion that slicing and dicing this at the per-GUC
level is a huge waste of effort. Just invent one role that lets
grantees set any GUC, document it as being superuser-equivalent,
and be done.

regards, tom lane

On Mon, May 3, 2021 at 2:41 PM Stephen Frost <sfrost@snowman.net> wrote:

In general, I agree that we should be looking at predefined roles as
being similar to the Linux capabilities system- defining certain kinds
of operations which the user who has that role is allowed to do, and
then both in-core and extensions can make decisions based on what
capabilities the user has been GRANT'd.

Cool.

Hopefully that would limit the amount of cases where a given capability
ends up being overly broad while at the same time allowing extensions to
sensibly be able to use the defined capabilities for their own needs.

Yeah. I think it will be a little tricky to get right, as some of the
cases are a bit subjective, I think.

As we do in other places, we should make it clear when a certain
capability allows a user with that capability to gain superuser access
as that may not always be clear to a user.

+1.

One thing that seems missing from this discussion and is part of what
paused my effort on the 'admin' role proposed towards the end of the
last cycle is that we really need to consider how this all plays with
ALTER SYSTEM and not just SUSET GUCs but also other (eg: POSTMASTER,
SIGHUP) GUCs. That is- imv we should have a sensible solution for
more-or-less all GUCs and which would allow a non-superuser to be able
to set POSTMASTER and SIGHUP GUCs (and perhaps others..) through
ALTER SYSTEM.

I missed the earlier discussion on this topic, but I agree that this
is very important. I think that the discussion of capabilities might
help us get there. For instance, if I'm a service provider, and I give
user "bob" the pg_put_whatever_you_want_in_the_server_log role, and
GUCs are tagged so we know what GUCs that affects, then it seems
natural to me to allow Bob to set those GUCs via ALTER SYSTEM as well
as via ALTER USER or ALTER DATABASE. However, if I don't give him the
pg_frob_shell_commands role, he can't set archive_command.

--
Robert Haas
EDB: http://www.enterprisedb.com

On Mon, May 3, 2021 at 2:48 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

I'm still of the opinion that slicing and dicing this at the per-GUC
level is a huge waste of effort. Just invent one role that lets
grantees set any GUC, document it as being superuser-equivalent,
and be done.

If you want to grant someone full superuser rights, you can do that
already. The trick is what to do when you want someone to be able to
administer the cluster in a meaningful way without giving them full
superuser rights.

I agree that in some cases it's fine to have predefined roles that are
known to permit easy escalation to superuser privileges, like
pg_execute_server_program. It doesn't provide any real security, but
like you said, it can help prevent mistakes. However, there is a real
use cases for a privileged user who cannot be permitted to escalate to
superuser or to the OS account, but still needs to be able to do some
administration of the cluster. The scenario Mark laid out in his
original post is very common. In fact, it may already be the dominant
model for PostgreSQL deployment, and if it isn't now, it will be in 5
years. Letting each individual company that's providing a hosted
PostgreSQL solution hack up its own solution to that problem, all of
which are subtly incompatible with each other and with upstream, is
not good for users or the project.

--
Robert Haas
EDB: http://www.enterprisedb.com