1

We have doubts regarding default privileges for owner of objects. Let me show by example:

We start by creating a brand new role and schema:

psql (9.6.2)
Type "help" for help.

postgres=# CREATE ROLE bro WITH LOGIN;
CREATE ROLE
postgres=# CREATE SCHEMA bro AUTHORIZATION bro;
CREATE SCHEMA
postgres=# \c - bro
You are now connected to database "postgres" as user "bro".
postgres=# SET search_path TO bro, public;
SET

We check the default priveleges and nothing shows up:

postgres=> \ddp
         Default access privileges
 Owner | Schema | Type | Access privileges
-------+--------+------+-------------------
(0 rows)

When creating a table, bro can access it by default:

postgres=> CREATE TABLE t1 (a integer);
CREATE TABLE
postgres=> TABLE t1;
 a
---
(0 rows)

So we REVOKE the default privileges for bro on TABLES.

postgres=> ALTER DEFAULT PRIVILEGES FOR ROLE bro IN SCHEMA bro REVOKE ALL ON TABLES FROM bro;
ALTER DEFAULT PRIVILEGES

There's still nothing in \ddp, but we expect that bro won't be able to access the new tables we create:

postgres=> \ddp
         Default access privileges
 Owner | Schema | Type | Access privileges
-------+--------+------+-------------------
(0 rows)

We create a new table and bro is still able to access it, which seems wrong to us:

postgres=> CREATE TABLE t2 (a integer);
CREATE TABLE
postgres=> \d
       List of relations
 Schema | Name | Type  | Owner
--------+------+-------+-------
 bro    | t1   | table | bro
 bro    | t2   | table | bro
(2 rows)

postgres=> TABLE t2;
 a
---
(0 rows)

Then we try by explicitly revoking privileges from bro for that table and finally is no longer able to access it:

postgres=> REVOKE ALL ON TABLE t2 FROM bro;
REVOKE
postgres=> TABLE t2;
ERROR:  permission denied for relation t2

In the PostgreSQL documentation they state:

As explained under GRANT, the default privileges for any object type normally grant all grantable permissions to the object owner, and may grant some privileges to PUBLIC as well. However, this behavior can be changed by altering the global default privileges with ALTER DEFAULT PRIVILEGES.

Could someone explain if this is the expected behavior?

Thank you!

  • Why does it make sense to revoke all rights from the owner? – dezso Jan 26 '18 at 13:36
  • We may want a role for DDL only and others for DML. This question acutally comes from our pg_dumpall adding ALTER DEFAULT PRIVILEGES statements for the owner of tables, that does not make sense to us. – chamini2 Jan 29 '18 at 13:57
  • Try removing IN SCHEMA bro when you run the ALTER DEFAULT PRIVILEGES. Then run \ddp to see if it changes anything. – da99 Jul 3 '18 at 19:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.