PostgreSQL – How to Grant Privileges as Superuser

permissionspostgresql

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 !

Best Answer

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.

Related Solutions

PostgreSQL – How to Grant DROP TABLE/FUNCTION Privileges to a Role

Only the owner (and superusers) can drop objects. Per documentation:

The right to drop an object, or to alter its definition in any way, is not treated as a grantable privilege; it is inherent in the owner, and cannot be granted or revoked. (However, a similar effect can be obtained by granting or revoking membership in the role that owns the object; see below.) The owner implicitly has all grant options for the object, too.

So, make administrator own such objects that users should be able to drop.

ALTER FUNCTION foo() OWNER TO administrator;
ALTER TABLE foo      OWNER TO administrator;

And you remembered to actually grant group membership, right?

GRANT administrator TO _administrator;

PostgreSQL Permissions – When Privileges Are Listed in \l

The backslash commands in psql are shortcuts for a query or queries that look through the system catalogs. The \l command looks at information in pg_catalog.pg_database, specifically, this query:

SELECT d.datname as "Name",
   pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
   pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
   d.datcollate as "Collate",
   d.datctype as "Ctype",
   pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;

You can make psql show what it is using for the backslash commands by passing the -E flag to it when you invoke it on the command line.

If the permissions on a database or other object are the defaults that PostgreSQL creates them with, the *acl column will be NULL. If you change the defaults, as you have, the ACL column will be populated with information related to the GRANT and/or REVOKE statements you have ran.

You can see the permissions/ACLs specifically via either \z or \dp

If you read further here:

http://www.postgresql.org/docs/9.4/static/sql-grant.html

If you scroll down, (or search for the word psql), you can look at the table that shows you how to interpret the ACLs that you see with \l or in an ACL column.

For example:

=Tc/vagrant

means that PUBLIC (the implicit role that contains all roles) has permissions to create temporary tables T and connect c, because the ACL line =xxxxx denotes permissions applied to PUBLIC, while rolname=xxxx applies to that specific role.

This presentation from Dalibo should also help clarify this further: Managing Rights in PostgreSQL

Hope that helps. =)

Best Answer

Related Solutions

PostgreSQL – How to Grant DROP TABLE/FUNCTION Privileges to a Role

PostgreSQL Permissions – When Privileges Are Listed in \l

Related Question