Re: Allow placeholders in ALTER ROLE w/o superuser

At Tue, 19 Jul 2022 09:53:39 -0700, Nathan Bossart <nathandbossart(at)gmail(dot)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