pg_default_acl

A system catalogue table storing initial privileges to be assigned to newly created objects

pg_default_acl is a system catalogue table storing initial privileges to be assigned to newly created objects, as defined by the ALTER DEFAULT PRIVILEGES command.

pg_default_acl was added in PostgreSQL 9.0 (commit 249724cb).

psql

The contents of pg_default_acl can be queried in psql with the slash command \ddp.

Definition by PostgreSQL version

pg_default_acl (PostgreSQL 13)

              Table "pg_catalog.pg_default_acl"
     Column      |   Type    | Collation | Nullable | Default
-----------------+-----------+-----------+----------+---------
 oid             | oid       |           | not null |
 defaclrole      | oid       |           | not null |
 defaclnamespace | oid       |           | not null |
 defaclobjtype   | "char"    |           | not null |
 defaclacl       | aclitem[] |           | not null |
Indexes:
    "pg_default_acl_oid_index" UNIQUE, btree (oid)
    "pg_default_acl_role_nsp_obj_index" UNIQUE, btree (defaclrole, defaclnamespace, defaclobjtype)
    

Documentation: pg_default_acl

pg_default_acl (PostgreSQL 12)

              Table "pg_catalog.pg_default_acl"
     Column      |   Type    | Collation | Nullable | Default
-----------------+-----------+-----------+----------+---------
 oid             | oid       |           | not null |
 defaclrole      | oid       |           | not null |
 defaclnamespace | oid       |           | not null |
 defaclobjtype   | "char"    |           | not null |
 defaclacl       | aclitem[] |           | not null |
Indexes:
    "pg_default_acl_oid_index" UNIQUE, btree (oid)
    "pg_default_acl_role_nsp_obj_index" UNIQUE, btree (defaclrole, defaclnamespace, defaclobjtype)
    

Documentation: pg_default_acl

pg_default_acl (PostgreSQL 11)

              Table "pg_catalog.pg_default_acl"
     Column      |   Type    | Collation | Nullable | Default
-----------------+-----------+-----------+----------+---------
 defaclrole      | oid       |           | not null |
 defaclnamespace | oid       |           | not null |
 defaclobjtype   | "char"    |           | not null |
 defaclacl       | aclitem[] |           |          |
Indexes:
    "pg_default_acl_oid_index" UNIQUE, btree (oid)
    "pg_default_acl_role_nsp_obj_index" UNIQUE, btree (defaclrole, defaclnamespace, defaclobjtype)
    

Documentation: pg_default_acl

pg_default_acl (PostgreSQL 10)

              Table "pg_catalog.pg_default_acl"
     Column      |   Type    | Collation | Nullable | Default
-----------------+-----------+-----------+----------+---------
 defaclrole      | oid       |           | not null |
 defaclnamespace | oid       |           | not null |
 defaclobjtype   | "char"    |           | not null |
 defaclacl       | aclitem[] |           |          |
Indexes:
    "pg_default_acl_oid_index" UNIQUE, btree (oid)
    "pg_default_acl_role_nsp_obj_index" UNIQUE, btree (defaclrole, defaclnamespace, defaclobjtype)
    

Documentation: pg_default_acl

pg_default_acl (PostgreSQL 9.6)

    Table "pg_catalog.pg_default_acl"
     Column      |   Type    | Modifiers
-----------------+-----------+-----------
 defaclrole      | oid       | not null
 defaclnamespace | oid       | not null
 defaclobjtype   | "char"    | not null
 defaclacl       | aclitem[] |
Indexes:
    "pg_default_acl_oid_index" UNIQUE, btree (oid)
    "pg_default_acl_role_nsp_obj_index" UNIQUE, btree (defaclrole, defaclnamespace, defaclobjtype)
    

Documentation: pg_default_acl

pg_default_acl (PostgreSQL 9.5)

    Table "pg_catalog.pg_default_acl"
     Column      |   Type    | Modifiers
-----------------+-----------+-----------
 defaclrole      | oid       | not null
 defaclnamespace | oid       | not null
 defaclobjtype   | "char"    | not null
 defaclacl       | aclitem[] |
Indexes:
    "pg_default_acl_oid_index" UNIQUE, btree (oid)
    "pg_default_acl_role_nsp_obj_index" UNIQUE, btree (defaclrole, defaclnamespace, defaclobjtype)
    

Documentation: pg_default_acl

pg_default_acl (PostgreSQL 9.4)

    Table "pg_catalog.pg_default_acl"
     Column      |   Type    | Modifiers
-----------------+-----------+-----------
 defaclrole      | oid       | not null
 defaclnamespace | oid       | not null
 defaclobjtype   | "char"    | not null
 defaclacl       | aclitem[] |
Indexes:
    "pg_default_acl_oid_index" UNIQUE, btree (oid)
    "pg_default_acl_role_nsp_obj_index" UNIQUE, btree (defaclrole, defaclnamespace, defaclobjtype)
    

Documentation: pg_default_acl

pg_default_acl (PostgreSQL 9.3)

    Table "pg_catalog.pg_default_acl"
     Column      |   Type    | Modifiers
-----------------+-----------+-----------
 defaclrole      | oid       | not null
 defaclnamespace | oid       | not null
 defaclobjtype   | "char"    | not null
 defaclacl       | aclitem[] |
Indexes:
    "pg_default_acl_oid_index" UNIQUE, btree (oid)
    "pg_default_acl_role_nsp_obj_index" UNIQUE, btree (defaclrole, defaclnamespace, defaclobjtype)
    

Documentation: pg_default_acl

pg_default_acl (PostgreSQL 9.2)

    Table "pg_catalog.pg_default_acl"
     Column      |   Type    | Modifiers
-----------------+-----------+-----------
 defaclrole      | oid       | not null
 defaclnamespace | oid       | not null
 defaclobjtype   | "char"    | not null
 defaclacl       | aclitem[] |
Indexes:
    "pg_default_acl_oid_index" UNIQUE, btree (oid)
    "pg_default_acl_role_nsp_obj_index" UNIQUE, btree (defaclrole, defaclnamespace, defaclobjtype)
    

Documentation: pg_default_acl

pg_default_acl (PostgreSQL 9.1)

    Table "pg_catalog.pg_default_acl"
     Column      |   Type    | Modifiers
-----------------+-----------+-----------
 defaclrole      | oid       | not null
 defaclnamespace | oid       | not null
 defaclobjtype   | "char"    | not null
 defaclacl       | aclitem[] |
Indexes:
    "pg_default_acl_oid_index" UNIQUE, btree (oid)
    "pg_default_acl_role_nsp_obj_index" UNIQUE, btree (defaclrole, defaclnamespace, defaclobjtype)
    

Documentation: pg_default_acl

pg_default_acl (PostgreSQL 9.0)

    Table "pg_catalog.pg_default_acl"
     Column      |   Type    | Modifiers
-----------------+-----------+-----------
 defaclrole      | oid       | not null
 defaclnamespace | oid       | not null
 defaclobjtype   | "char"    | not null
 defaclacl       | aclitem[] |
Indexes:
    "pg_default_acl_oid_index" UNIQUE, btree (oid)
    "pg_default_acl_role_nsp_obj_index" UNIQUE, btree (defaclrole, defaclnamespace, defaclobjtype)
    

Documentation: pg_default_acl

Change history

This table has not changed since it was first added.

Examples

postgres=# CREATE SCHEMA someschema;
CREATE SCHEMA
postgres=# ALTER DEFAULT PRIVILEGES IN SCHEMA someschema GRANT SELECT ON TABLES TO foo; ALTER DEFAULT PRIVILEGES
postgres=# \ddp Default access privileges Owner | Schema | Type | Access privileges ----------+------------+-------+------------------- postgres | someschema | table | foo=r/postgres (1 row)
postgres=# SELECT * FROM pg_default_acl; defaclrole | defaclnamespace | defaclobjtype | defaclacl ------------+-----------------+---------------+------------------ 10 | 16456 | r | {foo=r/postgres} (1 row)
  • PostgreSQL wiki: DefaultACL - feature development discussion

Categories

DDL, System catalogue

See also

ALTER DEFAULT PRIVILEGES