Re: BUG #15281: Set role does not affect superuser privleges - Mailing list pgsql-bugs
From | Tomas Vondra |
---|---|
Subject | Re: BUG #15281: Set role does not affect superuser privleges |
Date | |
Msg-id | cb73d366-5978-7fe3-0d4a-b73d934597d7@2ndquadrant.com Whole thread Raw |
In response to | BUG #15281: Set role does not affect superuser privleges (PG Bug reporting form <noreply@postgresql.org>) |
Responses |
Re: BUG #15281: Set role does not affect superuser privleges
|
List | pgsql-bugs |
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
pgsql-bugs by date: