How to find Default Access Privileges on PostgreSQL with pg_default_acl

30 June,2020 by Jack Vamvas

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;

 


Author: Jack Vamvas (http://www.dba-ninja.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to find Default Access Privileges on PostgreSQL with pg_default_acl


dba-ninja.com