From: | Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com> |
---|---|
To: | torcher999(at)gmail(dot)com, pgsql-bugs(at)lists(dot)postgresql(dot)org, PG Bug reporting form <noreply(at)postgresql(dot)org> |
Subject: | Re: BUG #15281: Set role does not affect superuser privleges |
Date: | 2018-07-16 21:48:43 |
Message-ID: | cb73d366-5978-7fe3-0d4a-b73d934597d7@2ndquadrant.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2018-07-16 22:12:50 | Re: BUG #15281: Set role does not affect superuser privleges |
Previous Message | Steven Winfield | 2018-07-16 16:58:49 | RE: BUG #15280: Clang 6.0.1 compiler warning - strlcpy |