Re: BUG #15281: Set role does not affect superuser privleges

On 07/16/2018 04:55 PM, PG Bug reporting form wrote:
> The following bug has been logged on the website:
> 
> Bug reference:      15281
> Logged by:          Michal Wozny
> Email address:      torcher999@gmail.com
> PostgreSQL version: 10.4
> Operating system:   Windows Server 2012 R2
> Description:        
> 
> Here are the two scenarios I tested:
> 
> 1) as a session_user: superuser and current_user: non-superuser I can edit
> others' tables
> 2) as a session_user: non-superuser and current_user: superuser I cannot
> edit others' tables
> 
> The documentation contradicts 1) explicitly, 2) implicitly:
> 
> "when a superuser chooses to SET ROLE to a non-superuser role, they lose
> their superuser privileges."
> Source: https://www.postgresql.org/docs/current/static/sql-set-role.html
> 
> 
> Steps to reproduce:
> 
> Preconditions:
> create two non-superusers [user1, user2]
> create one superuser [user0]
> grant user0 to user1
> create a table [u2_tab] with owner user2
> 
> 1)
> disconnect
> connect as user0
> set role user1
> rename u2_tab
> 
> expected: cannot rename u2_tab
> actual: can rename u2_tab
> 
> 2)
> disconnect
> connect as user1
> set role user0
> rename u2_tab
> 
> expected: can rename u2_tab
> actual: cannot rename u2_tab
> 
> SQL steps:
> 
> Preconditions:
> CREATE USER user0 WITH SUPERUSER
> CREATE USER user1
> GRANT user0 to user1
> CREATE USER user2
> CREATE TABLE public.u2_tab
> ALTER TABLE public.u2_tab OWNER TO user2
> 
> 1)
> DISCONNECT
> CONNECT TO db AS user0
> SET ROLE user1
> ALTER TABLE public.u2_tab RENAME TO u2_tab1
> 
> 2)
> DISCONNECT
> CONNECT TO db AS user1
> SET ROLE user0
> ALTER TABLE public.u2_tab1 RENAME TO u2_tab2
> 

It's usually a good idea to provide exact scripts / output so that
people can reproduce the issue easily. For me it behaves like this:

1) non-superuser

test=# drop table u2_tab ;
DROP TABLE
test=# drop role user1;
DROP ROLE
test=# drop role user2;
DROP ROLE
test=# drop role user0;
DROP ROLE
test=# CREATE USER user0 WITH SUPERUSER;
CREATE ROLE
test=# CREATE USER user1;
CREATE ROLE
test=# CREATE USER user2;
CREATE ROLE
test=# CREATE TABLE public.u2_tab();
CREATE TABLE
test=# ALTER TABLE public.u2_tab OWNER TO user2;
ALTER TABLE
test=# \c - user0
You are now connected to database "test" as user "user0".
test=# SET ROLE user1;
SET
test=> ALTER TABLE public.u2_tab RENAME TO u2_tab1;
ERROR:  must be owner of table u2_tab

2) superuser

test=# drop table u2_tab ;
DROP TABLE
test=# drop role user0;
DROP ROLE
test=# drop role user1;
DROP ROLE
test=# drop role user2;
DROP ROLE
test=#
test=#
test=#
test=#
test=# CREATE USER user0 WITH SUPERUSER;
CREATE ROLE
test=# CREATE USER user1;
CREATE ROLE
test=# CREATE USER user2;
CREATE ROLE
test=# CREATE TABLE public.u2_tab();
CREATE TABLE
test=# ALTER TABLE public.u2_tab OWNER TO user2;
ALTER TABLE
test=# \c - user0
You are now connected to database "test" as user "user0".
test=# ALTER TABLE public.u2_tab RENAME TO u2_tab1;
ALTER TABLE

So, correct in both cases. I'm not on Windows, but I don't see why would
it behave differently there.

regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services