Display default access privileges for relations, sequences and functions in Postgres
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
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.
If you join pg_default_act
to pg_namespace
you will only list default privileges that are granted using in the schema.