Re: ALTER ROLE/DATABASE RESET ALL versus security

Tom Lane wrote:
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> > I have come up with the attached patch. I haven't tested it fully yet,
> > and I need to backport it. The gist of it is: we can't simply remove
> > the pg_db_role_setting tuple, we need to ask GUC to reset the settings
> > array, for which it checks superuser-ness on each setting.
>
> I think you still want to have a code path whereby the tuple will be
> deleted once the array is empty. Failing to check that is inefficient
> and also exposes clients such as pg_dump to corner case bugs.

Yeah, that's there too -- it behaves the same way as ALTER / RESET for a
particular setting.

I just applied it all the way back to 7.4. It was a bit of a pain to
backport it, because every version seemed to have this or that little
incompatibility.

I attempted a regression test, but it's also painful because there's no
nice way to clean up after a newly created user (i.e. drop it): after
the last \c - regress_user_guc, there's no way to go back to the
original user. And we can't use SET SESSION AUTHORIZATION because it
doesn't cause the settings for the role to be loaded. (I think that's a
bug too). Suggestions on how to enable this are welcome.

-- Test user-specific settings
create role regress_user_guc login;
alter role regress_user_guc set work_mem to '128MB';
alter role regress_user_guc set lc_messages to 'C';
\c - regress_user_guc
select name, setting, source
from pg_settings
where name in ('work_mem', 'lc_messages')
order by name;
alter role regress_user_guc reset all;
\c - regress_user_guc
-- can't display actual value here because it may be installation-dependant
select name, setting, source
from pg_settings
where name in ('work_mem', 'lc_messages')
order by name;

(I think I should also use a superuser setting other than lc_messages).

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.