Re: Allow placeholders in ALTER ROLE w/o superuser

On Sat, Nov 19, 2022 at 12:33 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Alexander Korotkov <aekorotkov(at)gmail(dot)com> writes:
> > This makes sense. But do we really need to store the OID of the role?
> > validate_option_array_item() already checks if the placeholder option
> > passes validation for PGC_SUSET. So, we can just save a flag
> > indicating that this check was not successful. If so, then the value
> > stored can be only used for PGC_USERSET. Do you think this would be
> > correct?
>
> Meh ... doesn't seem like much of an improvement. You still need
> to store something that's not there now.

Yes, but it wouldn't be needed to track dependencies of pg_role
mentions in pg_db_role_setting. That seems to be a significant
simplification.

> This also seems to require
> some shaky assumptions about decisions having been made when storing
> still being valid later on. Given the possibility of granting or
> revoking permissions for SET, I think we don't really want it to act
> that way.

Yes, it might be shaky. Consider user sets parameter
pg_db_role_setting, and that appears to be capable only for
PGC_USERSET. Next this user gets the SET permissions. Then this
parameter needs to be set again in order for the new permission to
take effect.

But consider the other side. How should we handle stored OID of a
role? Should the privilege checking be moved from "set time" to "run
time"? Therefore, revoking SET permission from role may affect
existing parameters in pg_db_role_setting. It feels like revoke of
SET permission also aborts changes previously made with that
permission. This is not how we normally do, and that seems confusing.

I think if we implement the flag and make it user-visible, e.g.
implement something like "ALTER ROLE ... SET ... USERSET;", then it
might be the lesser confusing option.

Thoughts?

------
Regards,
Alexander Korotkov