PG_DEFAULT_ACL

Stores information about default access privileges. For more information on default access privileges, see ALTER DEFAULT PRIVILEGES.

PG_DEFAULT_ACL is visible to all users. Superusers can see all rows; regular users can see only their own data. For more information, see Visibility of data in system tables and views.

Table columns

Column name	Data type	Description
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: r – relation (table or view) f – function p – stored procedure
defaclacl	aclitem[]	A string that defines the default privileges for the specified user or user group and object type. If the privileges are granted to a user, the string is in the following form: { username=privilegestring/grantor } username The name of the user to which privileges are granted. If username is omitted, the privileges are granted to PUBLIC. If the privileges are granted to a user group, the string is in the following form: { "group groupname=privilegestring/grantor" } privilegestring A string that specifies which privileges are granted. Valid values are: a – INSERT (append) r – SELECT (read) w – UPDATE (write) d – DELETE R – RULE x – Grants the privilege to create a foreign key constraint ( REFERENCES). t – TRIGGER X – EXECUTE U – USAGE C – CREATE T – CREATE TEMP D – DROP P – TRUNCATE A – ALTER * – Indicates that the user receiving the preceding privilege can in turn grant the same privilege to others (WITH GRANT OPTION). A string holding all privilege code chars, in order by bitmask position, looks like "arwdRxtXUCTDPA". grantor The name of the user that granted the privileges. The following example indicates that the user admin granted all privileges, including WITH GRANT OPTION, to the user dbuser. dbuser=r*a*w*d*x*X*/admin

Example

The following query returns all default privileges defined for the database.

select pg_get_userbyid(d.defacluser) 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;

 user  | schema | object_type |              default_privileges
-------+--------+-------------+-------------------------------------------------------
 admin | tickit | tables      | user1=r/admin + "group group1=a/admin" + user2=w/admin

The result in the preceding example shows that for all new tables created by user admin in the tickit schema, admin grants SELECT privileges to user1, INSERT privileges to group1, and UPDATE privileges to user2.