PG_DEFAULT_ACL - Amazon Redshift
Table columnsExample
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

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:

  • r–SELECT (read)

  • a–INSERT (append)

  • w–UPDATE (write)

  • d–DELETE

  • x–Grants the privilege to create a foreign key constraint ( REFERENCES).

  • X–EXECUTE

  • *–Indicates that the user receiving the preceding privilege can in turn grant the same privilege to others (WITH GRANT OPTION).

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.