Re: ALTER ROLE/DATABASE RESET ALL versus security

Tom Lane wrote:
> It looks to me like the code in AlterSetting() will allow an ordinary
> user to blow away all settings for himself.  Even those that are for
> SUSET variables and were presumably set for him by a superuser.  Isn't
> this a security hole?  I would expect that an unprivileged user should
> not be able to change such settings, not even to the extent of
> reverting to the installation-wide default.

Yes, it is, but this is not a new hole.  This works just fine in 8.4
too:

alvherre=# create role foo;
CREATE ROLE
alvherre=# alter role foo set lc_messages = 'C';
ALTER ROLE
alvherre=# set session AUTHORIZATION foo;
SET
alvherre=> show lc_messages ;lc_messages 
-------------es_CL.UTF-8
(1 fila)

alvherre=> alter role foo reset all;
ALTER ROLE
alvherre=> reset session AUTHORIZATION ;
RESET
alvherre=# set session AUTHORIZATION foo;
SET
alvherre=> show lc_messages ;lc_messages 
-------------es_CL.UTF-8
(1 fila)

alvherre=> alter role foo set lc_messages to 'C';
ERROR:  se ha denegado el permiso para cambiar la opción «lc_messages»


So any user is able to reset settings that were set for him by the
superuser.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support