Skip to content
Advertisement

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

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;

Advertisement

Answer

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.

User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement