Обсуждение: BUG #15281: Set role does not affect superuser privleges
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
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
Tomas Vondra <tomas.vondra@2ndquadrant.com> writes:
> On 07/16/2018 04:55 PM, PG Bug reporting form wrote:
>> 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
> 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:
> ...
> So, correct in both cases. I'm not on Windows, but I don't see why would
> it behave differently there.
Tomas' test left out the "GRANT user0 to user1" bit, but I can't
reproduce the behavior as described either.
regards, tom lane
On 07/17/2018 12:12 AM, Tom Lane wrote: > Tomas Vondra <tomas.vondra@2ndquadrant.com> writes: >> On 07/16/2018 04:55 PM, PG Bug reporting form wrote: >>> 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 > >> 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: >> ... >> So, correct in both cases. I'm not on Windows, but I don't see why would >> it behave differently there. > > Tomas' test left out the "GRANT user0 to user1" bit, but I can't > reproduce the behavior as described either. > Ah, right. Sorry for not mentioning that. I've actually tried both with and without that GRANT (no effect on behavior), but I've assumed it's there only to allow the `SET user0` which I've replaced by connecting directly as user0. So I haven't included it into the response. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services