Skip site navigation (1) Skip section navigation (2)


From: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Bruce Momjian <bruce(at)momjian(dot)us>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: ALTER ROLE/DATABASE RESET ALL versus security
Date: 2010-03-25 15:11:38
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
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

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                      
The PostgreSQL Company - Command Prompt, Inc.

In response to

pgsql-hackers by date

Next:From: Tom LaneDate: 2010-03-25 16:45:40
Subject: Re: last_statrequest is in the future
Previous:From: Fujii MasaoDate: 2010-03-25 14:33:24
Subject: Re: walreceiver is uninterruptible on win32

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group