Display default access privileges for relationships, sequences, and functions in Postgres

Question

After changing the default privileges for a Postgres database object, how can you view them?

For example, if you grant all role_name privileges to all tables created in the schema_name :

 ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name GRANT ALL ON TABLES TO role_name; 

Answer 1

Using 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 defaclobjtype r = relation (table, view), S = sequence, f = function .

These access rights are only for newly created objects in the schema namespace.

Answer 2

Using psql interactive terminal (1)

There is another way, at least in recent versions of Postgres.
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 this in the "Notes" section:
http://www.postgresql.org/docs/9.0/static/sql-alterdefaultprivileges.html

Answer 3

If you attach pg_default_act to pg_namespace , you will see only the default privileges that are granted in the scheme.