Re: pgsql: Add new GUC createrole_self_grant.

On Tue, Jan 10, 2023 at 9:40 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Yeah. I concur that a SUSET GUC isn't much fun for a non-superuser
> CREATEROLE holder who might wish to adjust the default behavior they get.
> I also concur that it seems a bit far-fetched that a CREATEROLE holder
> might create a SECURITY DEFINER function that would do something that
> would be affected by this setting. Still, we have no field experience
> with how these mechanisms will actually be used, so I'm worried.

All right. I'm not that worried because I think any problems that crop
up probably won't be that bad, primarily due to the extremely
restricted set of circumstances in which the GUC operates -- but
that's a judgement call, and reasonable people can think differently.

> The scenario I'm worried about could be closed, mostly, if we were willing
> to invent an intermediate GUC privilege level "can be set interactively
> but only by CREATEROLE holders" ("PGC_CRSET"?). But that's an awful lot
> of infrastructure to add for one GUC. Are there any other GUCs where
> that'd be a more useful choice than any we have now?

I don't quite understand what that would do. If a non-CREATEROLE user
sets the GUC, absolutely nothing happens, because the code that is
controlled by the GUC cannot be reached without CREATEROLE privileges.

Of course, if it's possible for a non-CREATEROLE user to set the value
that a CREATEROLE user experiences, that'd be more of a problem --
though still insufficient to create a security vulnerability in and of
itself -- but if user A can change the GUC settings that user B
experiences, why screw around with this when you could just set
search_path?

To answer your question directly, though, I don't know of any other
setting where that would be a useful level. Up until this morning,
CREATEROLE was not usable for any serious purpose because we've been
shipping something that was broken by design for years, so it's
probably fortunate that not much depends on it.

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