33

After altering the default privileges on a Postgres database object, how can you view them?

For instance, if you grant all privileges to role_name for all tables created in the schema schema_name:

ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name GRANT ALL ON TABLES TO role_name;
Clint Pachl
  • 10,848
  • 6
  • 41
  • 42

3 Answers3

51

Using the psql(1) interactive terminal

There is another way, at least in recent Postgres versions.
Use the \ddp command

               Default access privileges
     Owner      | Schema |   Type   | Access privileges 
----------------+--------+----------+-------------------
 role_x         |        | function | =X/role_x
 role_x         |        | sequence | 
 role_x         |        | table    | 
 role_x         |        | type     | =U/role_x

Read more about it under the Notes section here:
http://www.postgresql.org/docs/current/static/sql-alterdefaultprivileges.html

Yvo
  • 18,681
  • 11
  • 71
  • 90
  • 1
    Sidenote: default privileges are sometimes difficult to remove. In my case I was trying to drop a role, so I first transfered the ownership with REASSIGN OWNED and then dropped things like the default privileges with DROP OWNED. – Yvo Feb 15 '15 at 14:58
  • 1
    @Madeo you may not have created any access privileges. I just ran `\ddp` on a new database created with Postgresql 12.5 and it returns *0 rows*. – Clint Pachl Jan 14 '21 at 09:45
  • @Madeo you should only get rows with this if you actually executed "ALTER DEFAULT PRIVILEGES" on an object – cowbert Apr 28 '21 at 21:44
33

Using a SQL query

SELECT 
  nspname,         -- schema name
  defaclobjtype,   -- object type
  defaclacl        -- default access privileges
FROM pg_default_acl a JOIN pg_namespace b ON a.defaclnamespace=b.oid;

Where the value of defaclobjtype is r = relation (table, view), S = sequence, f = function.

These access privileges are only for newly created objects within the schema namespace.

Clint Pachl
  • 10,848
  • 6
  • 41
  • 42
  • 1
    If you get zero results with the query above, try it without the join on pg_namespace. That's the query I used until I found out about \ddp. – Yvo Feb 15 '15 at 14:51
  • 5
    @Zyphrax Indeed, if you run psql with `-E`, you can see that `\ddp` uses a LEFT JOIN there. – RazerM Jul 18 '17 at 07:33
  • Worked for me! Event found a bad/incorrectly assigned grant to a user. – steven87vt Apr 21 '19 at 12:29
  • 1
    and the "result" appears in col defaclacl, in a form like "{=arwdDxt/}" , which says the role will be granted permissions arwdDxt for objects created by owner. – gwideman Feb 08 '20 at 00:58
2

If you join pg_default_acl to pg_namespace you will only list default privileges that are granted using in the schema.

cowbert
  • 3,212
  • 2
  • 25
  • 34
Cheryl Grant
  • 25
  • 1