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

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