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