This is a basic code sequence to set the Default Access Privileges on a PostgreSQL database. This example defines future privileges for future objects for this user. But what if you want to view the stored information about default access privileges ? There is a view called pg_default_acl
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT ALL PRIVILEGES ON TABLES TO myuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT ALL PRIVILEGES ON SEQUENCES TO myuser;
If you want to view the default access information stored you can use the PG_DEFAULT_ACL view. The default record set i.e SELECT * FROM PG_DEFAULT_ACL will return a recordset , but it can be difficult to read - without some further digging .
mydb=#SELECT * FROM PG_DEFAULT_ACL
defaclrole | defaclnamespace | defaclobjtype | defaclacl
------------+-----------------+---------------+--------------------------
10 | 25457 | r | {myuser=arwdDxt/postgres}
10 | 25457 | S | {myser=rwU/postgres}
The column names are fairly self explanatory , but here's a quick summary from the postgresql documentation
| defacluser | integer | ID of the user to which the listed privileges are applied. |
| defaclnamespace | oid | The object ID of the schema where default privileges are applied. The default value is 0 if no schema is specified. |
| defaclobjtype | character |
The type of object to which Default Privileges are applied. Valid values are as follows: |
| defaclacl | aclitem[] |
A string that defines the default privileges for the specified user or user group and object type. |
To make this more readable , use a query such as :
select pg_get_userbyid(d.defaclrole) as user, n.nspname as schema, case d.defaclobjtype when 'r' then 'tables' when 'f' then 'functions' end as object_type,array_to_string(d.defaclacl, ' + ') as default_privileges from pg_catalog.pg_default_acl d left join pg_catalog.pg_namespace n on n.oid = d.defaclnamespace;
