Re: Allow placeholders in ALTER ROLE w/o superuser

At Tue, 19 Jul 2022 09:53:39 -0700, Nathan Bossart <nathandbossart@gmail.com> wrote in 
> On Tue, Jul 19, 2022 at 12:55:14AM -0500, Steve Chavez wrote:
> > Taking your options into consideration, for me the correct behaviour should
> > be:
> > 
> > - The ALTER ROLE placeholder should always be stored with a PGC_USERSET
> > GucContext. It's a placeholder anyway, so it should be the less restrictive
> > one. If the user wants to define it as PGC_SUSET or other this should be
> > done through a custom extension.
> > - When an extension claims the placeholder, we should check the
> > DefineCustomXXXVariable GucContext with PGC_USERSET. If there's a match,
> > then the value gets applied, otherwise WARN or ERR.
> >   The role GUCs get applied at login time right? So at this point we can
> > WARN or ERR about the defined role GUCs.
> > 
> > What do you think?
> 
> Hm.  I would expect ALTER ROLE to store the PGC_SUSET context when executed
> by a superuser or a role with privileges via pg_parameter_acl.  Storing all
> placeholder GUC settings as PGC_USERSET would make things more restrictive
> than they are today.  For example, it would no longer be possible to apply
> any ALTER ROLE settings from superusers for placeholders that later become
> custom GUCS.

Currently placehoders are always created PGC_USERSET, thus
non-superuser can set it.  But if loaded module defines the custom
variable as PGC_SUSET, the value set by the user is refused then the
value from ALTER-ROLE-SET or otherwise the default value from
DefineCustom*Variable is used. If the module defines it as
PGC_USERSET, the last value is accepted.

If a placehoders were created PGC_SUSET, non-superusers cannot set it
on-session. But that behavior is not needed since loadable modules
reject PGC_USERSET values as above.


Returning to the topic, that operation can be allowed in PG15, having
being granted by superuser using the GRANT SET ON PARMETER command.

=# GRANT SET ON PARAMETER my.username TO r1;

r1=> ALTER ROLE r1 SET my.username = 'hoge_user_x';
<success>
r1=> \c
r1=> => show my.username;
 my.username 
-------------
 hoge_user_x
(1 row)

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center