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;
This is only a preview. Your comment has not yet been posted.
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.
Posted by: |