Well-known Privileges

ldap2pg provides some well-known ACLs for recurrent usage. There is no warranty of on these ACLs. You have to check privileges configuration on your databases just like you should do with your own code.

The true added-value of well-known ACLs is the inspect queries associated and the boilerplate saved for declaring all GRANT queries.

Using Well-known Privileges

Well-known ACL starts and lasts with __. ldap2pg disables privilege starting with _. Thus you have to include well-known ACLs in a group to enable them. If two groups reference the same ACL, it will be deduplicated, don’t worry.

acls:
  ro:
  - __connect__
  - __usage_on_schemas__
  - __select_on_tables__

  rw:
  - ro
  - __insert__
  - __update_on_tables__

  ddl:
  - rw
  - __all_on_schema__
  - __all_on_tables__

sync_map:
- grant:
    acl: ddl
    database: mydb
    schema: __all__
    role: admins

Well-known privilege name follows the following loose convention:

  • _on_all_tables__ is equivalent to GRANT ... ON ALL TABLES IN SCHEMA ....
  • __default_... is equivalent to ALTER DEFAULT PRIVILEGES ... IN SCHEMA ....
  • __..._on_tables__ groups __..._on_all_tables__ and __default_..._on_tables__.
  • Group starting with __all_on_ is equivalent to ALL PRIVILEGES in SQL.
  • A privilege specific to one type does not have _on_type__ e.g. __delete_on_tables__ is shorten to __delete__ .

Privilege Groups

Next is an extensive, boring, list of all well known privilege groups in master. Each group is documented by its name and the list of included ACL. Each ACL name point the the detail of ACL definition.

Actually, a group like __all_on_tables__ is implemented as group of groups. But for the sake of simplicity, the documentation lists the resolved list of concrete privileges finally included.

Here we go.

Group __all_on_schemas__

Group __all_on_sequences__

Group __all_on_tables__

Group __delete__

Group __delete_on_tables__

Group __execute__

Group __execute_on_functions__

Group __insert__

Group __insert_on_tables__

Group __references__

Group __references_on_tables__

Group __select_on_sequences__

Group __select_on_tables__

Group __trigger__

Group __trigger_on_tables__

Group __truncate__

Group __truncate_on_tables__

Group __update_on_sequences__

Group __update_on_tables__

Group __usage_on_sequences__

Single Privileges

Next is the list of well-known privileg. Each is associated with a REVOKE query and an inspect query implementing full inspection of grantees, including built-in grants to PUBLIC.

For the actual meaning of each SQL privileges, refer to official Postgres documentation of GRANT statement.

Privilege __connect__

GRANT CONNECT ON DATABASE {database} TO {role};

Privilege __create_on_schemas__

GRANT CREATE ON SCHEMA {schema} TO {role};

Privilege __default_delete_on_tables__

ALTER DEFAULT PRIVILEGES FOR ROLE {owner} IN SCHEMA {schema}
GRANT DELETE ON TABLES TO {role};

Privilege __default_execute_on_functions__

ALTER DEFAULT PRIVILEGES FOR ROLE {owner} IN SCHEMA {schema}
GRANT EXECUTE ON FUNCTIONS TO {role};

Privilege __default_insert_on_tables__

ALTER DEFAULT PRIVILEGES FOR ROLE {owner} IN SCHEMA {schema}
GRANT INSERT ON TABLES TO {role};

Privilege __default_references_on_tables__

ALTER DEFAULT PRIVILEGES FOR ROLE {owner} IN SCHEMA {schema}
GRANT REFERENCES ON TABLES TO {role};

Privilege __default_select_on_sequences__

ALTER DEFAULT PRIVILEGES FOR ROLE {owner} IN SCHEMA {schema}
GRANT SELECT ON SEQUENCES TO {role};

Privilege __default_select_on_tables__

ALTER DEFAULT PRIVILEGES FOR ROLE {owner} IN SCHEMA {schema}
GRANT SELECT ON TABLES TO {role};

Privilege __default_trigger_on_tables__

ALTER DEFAULT PRIVILEGES FOR ROLE {owner} IN SCHEMA {schema}
GRANT TRIGGER ON TABLES TO {role};

Privilege __default_truncate_on_tables__

ALTER DEFAULT PRIVILEGES FOR ROLE {owner} IN SCHEMA {schema}
GRANT TRUNCATE ON TABLES TO {role};

Privilege __default_update_on_sequences__

ALTER DEFAULT PRIVILEGES FOR ROLE {owner} IN SCHEMA {schema}
GRANT UPDATE ON SEQUENCES TO {role};

Privilege __default_update_on_tables__

ALTER DEFAULT PRIVILEGES FOR ROLE {owner} IN SCHEMA {schema}
GRANT UPDATE ON TABLES TO {role};

Privilege __default_usage_on_sequences__

ALTER DEFAULT PRIVILEGES FOR ROLE {owner} IN SCHEMA {schema}
GRANT USAGE ON SEQUENCES TO {role};

Privilege __delete_on_all_tables__

GRANT DELETE ON ALL TABLES IN SCHEMA {schema} TO {role}

Privilege __execute_on_all_functions__

GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA {schema} TO {role}

Privilege __global_default_execute_on_functions__

ALTER DEFAULT PRIVILEGES FOR ROLE {owner} GRANT EXECUTE ON FUNCTIONS TO {role};

Privilege __insert_on_all_tables__

GRANT INSERT ON ALL TABLES IN SCHEMA {schema} TO {role}

Privilege __references_on_all_tables__

GRANT REFERENCES ON ALL TABLES IN SCHEMA {schema} TO {role}

Privilege __select_on_all_sequences__

GRANT SELECT ON ALL SEQUENCES IN SCHEMA {schema} TO {role}

Privilege __select_on_all_tables__

GRANT SELECT ON ALL TABLES IN SCHEMA {schema} TO {role}

Privilege __temporary__

GRANT TEMPORARY ON DATABASE {database} TO {role};

Privilege __trigger_on_all_tables__

GRANT TRIGGER ON ALL TABLES IN SCHEMA {schema} TO {role}

Privilege __truncate_on_all_tables__

GRANT TRUNCATE ON ALL TABLES IN SCHEMA {schema} TO {role}

Privilege __update_on_all_sequences__

GRANT UPDATE ON ALL SEQUENCES IN SCHEMA {schema} TO {role}

Privilege __update_on_all_tables__

GRANT UPDATE ON ALL TABLES IN SCHEMA {schema} TO {role}

Privilege __usage_on_all_sequences__

GRANT USAGE ON ALL SEQUENCES IN SCHEMA {schema} TO {role}

Privilege __usage_on_schemas__

GRANT USAGE ON SCHEMA {schema} TO {role};

Privilege __usage_on_types__

GRANT USAGE ON SCHEMA {schema} TO {role};