Re: ALTER USER SET log_* not allowed...

>> doh! So much for that idea. There's no real way to have some
>> useconfig variables run by the super user and some run by the session
>> user. My workaround is to have the program call a SECURITY DEFINER
>> function, but I'd be nice to be able to remove that hack.
>
> Yeah, the whole concept of USERLIMIT GUC variables is fairly dubious,
> because of the fact that we have to be able to set these values at
> times
> when we don't necessarily know whether the user is a superuser or not.
[snip]
> It strikes me that a more useful definition would be to say that you
> must be superuser, period, to install an ALTER USER/DATABASE value for
> any USERLIMIT variable; and then we could treat these values as
> privileged for USERLIMIT purposes. This would lose the ability for
> non-superusers to set allowable values for themselves this way, but
> I think the case we'd gain is more useful.
>
> Comments?

Oh! Please! I thought about suggesting that but didn't think it'd
pass your litmus test and figured a pg_shadow.useconfig and an
pg_shadow.admconfig would be received better, but, absolutely! The
reason I hesitated to suggest such a change was SET search_path = foo;,
which a user should be able to set on their own. Sure it'd be easy to
have it a one-off exception, but then it'd be a one-off exception and
having an 'ALTER USER usr ADMIN SET guc = val' would skirt that issue
completely. That's the only concern that I have.

How about this, leave the existing system in place, but since useconfig
is just a TEXT[], if an admin sets the value (ALTER USER usr ADMIN
SET), prefix the guc name with an 'A:'. As things currently stand,
useconfig looks like, '{search_path=dbmail,log_statements=none}', but
after, it'd look like: '{search_path=dbmail,A:log_statements=none}'.
Then log_statements gets set with admin privs, where as search_path is
set with user privs. Pros:

*) Preserves backwards compatibility with existing databases and the
GUC security infrastructure (no need to bump catalog version)
*) Allows GUC variables to be set with differing permission levels and
still be set by the user
*) At ALTER USER time, a permission message can be returned that tells
the user a GUC has already been set by the admin, go bug them to change
this value

Cons:

*) Places a special value on the prefix of GUC variable names.
*) Requires adding a new keyword in the ALTER USER syntax.
*) Feels a tad like a "miscellaneous" column that is on the verge of
being abused.

Then again, isn't it on the horizon to have GUC reworked? Maybe this
would be an acceptable addition. *shrug* Just an idea. -sc

--
Sean Chittenden