1

I have a question about the rights in PG (version 10).

I did 2 tests.

The scenario is the following:

  • create a db with the superuser
  • create a schema owned by a user
  • create a read only role
  • grant privileges to the role
  • grant role to the read only user
  • create a table in the specific schema with the user that owns it

When I grant the privileges as a superuser, the "testuser" can't read the table: test KO.

When I grant the privileges as the owner of the schema, "testuser" can read the table: test OK

Why the superuser can't grant the privileges on a schema owned by another user ?

TEST KO

pg_ctl stop -D .
rm -rf *
pg_ctl init -D .
pg_ctl start -D .
psql -p 5432 -d postgres -c "create database testdb"
psql -p 5432 -d postgres -c "create user testuser"
psql -p 5432 -d postgres -c "create role role_read_testdb"

psql -p 5432 -d testdb -c "create schema authorization testuser"
psql -p 5432 -d testdb -c "GRANT SELECT ON ALL TABLES IN SCHEMA testuser TO role_read_testdb"
psql -p 5432 -d testdb -c "GRANT SELECT ON ALL SEQUENCES IN SCHEMA testuser TO role_read_testdb"
psql -p 5432 -d testdb -c "GRANT USAGE ON SCHEMA testuser TO role_read_testdb"
psql -p 5432 -d testdb -c "ALTER DEFAULT PRIVILEGES IN SCHEMA testuser GRANT SELECT ON TABLES to role_read_testdb"
psql -p 5432 -d testdb -c "ALTER DEFAULT PRIVILEGES IN SCHEMA testuser GRANT SELECT ON SEQUENCES to role_read_testdb"

psql -p 5432 -d postgres -c "create user user_read"
psql -p 5432 -d postgres -c "grant role_read_testdb to user_read"

psql -p 5432 -d testdb -U testuser -c "create table t1(col1 int)"
psql -p 5432 -d testdb -U user_read -c "select count(1) from testuser.t1"
ERROR:  permission denied for relation t1

TEST OK

pg_ctl stop -D .
rm -rf *
pg_ctl init -D .
pg_ctl start -D .
psql -p 5432 -d postgres -c "create database testdb"
psql -p 5432 -d postgres -c "create user testuser"
psql -p 5432 -d postgres -c "create role role_read_testdb"

psql -p 5432 -d testdb -c "create schema authorization testuser"
psql -p 5432 -d testdb -U testuser -c "GRANT SELECT ON ALL TABLES IN SCHEMA testuser TO role_read_testdb"
psql -p 5432 -d testdb -U testuser -c "GRANT SELECT ON ALL SEQUENCES IN SCHEMA testuser TO role_read_testdb"
psql -p 5432 -d testdb -U testuser -c "GRANT USAGE ON SCHEMA testuser TO role_read_testdb"
psql -p 5432 -d testdb -U testuser -c "ALTER DEFAULT PRIVILEGES IN SCHEMA testuser GRANT SELECT ON TABLES to role_read_testdb"
psql -p 5432 -d testdb -U testuser -c "ALTER DEFAULT PRIVILEGES IN SCHEMA testuser GRANT SELECT ON SEQUENCES to role_read_testdb"

psql -p 5432 -d postgres -c "create user user_read"
psql -p 5432 -d postgres -c "grant role_read_testdb to user_read"

psql -p 5432 -d testdb -U testuser -c "create table t1(col1 int)"
psql -p 5432 -d testdb -U user_read -c "select count(1) from testuser.t1"
 count
-------
     0
(1 row)

Thanks for your help !

Improve this question
1
  • Could you please remove the shell command part of the commands? It would make it much easier to read.
    – dezso
    May 24, 2018 at 14:43

1 Answer 1

Reset to default
1

ALTER DEFAULT PRIVILEGES without a FOR ROLE clause alters the default privileges for objects created by only the current role (the role executing the ALTER DEFAULT PRIVILEGES). So in the first case, you altered the superuser's default privileges, which has no effect on testuser's default privileges.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.