Well-known ACL

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 ACLs

Well-known ACL starts and lasts with __. ldap2pg disables ACL 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 ACL 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__ .

ACL groups

Next is an extensive, boring, list of all well known ACL 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 ACLs finally included.

Here we go.

Group __all_on_schemas__

Group __all_on_sequences__

Group __all_on_tables__

Group __delete__

Group __execute__

Group __insert__

Group __references__

Group __select_on_sequences__

Group __select_on_tables__

Group __trigger__

Group __truncate__

Group __update_on_sequences__

Group __update_on_tables__

Group __usage_on_sequences__

ACLs

Next is the list of well-known ACL. 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.

ACL __connect__

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

ACL __create_on_schemas__

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

ACL __default_delete_on_tables__

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

ACL __default_execute_on_functions__

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

ACL __default_insert_on_tables__

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

ACL __default_references_on_tables__

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

ACL __default_select_on_sequences__

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

ACL __default_select_on_tables__

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

ACL __default_trigger_on_tables__

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

ACL __default_truncate_on_tables__

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

ACL __default_update_on_sequences__

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

ACL __default_update_on_tables__

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

ACL __default_usage_on_sequences__

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

ACL __delete_on_all_tables__

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

ACL __execute_on_all_functions__

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

ACL __global_default_execute_on_functions__

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

ACL __insert_on_all_tables__

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

ACL __references_on_all_tables__

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

ACL __select_on_all_sequences__

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

ACL __select_on_all_tables__

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

ACL __temporary__

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

ACL __trigger_on_all_tables__

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

ACL __truncate_on_all_tables__

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

ACL __update_on_all_sequences__

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

ACL __update_on_all_tables__

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

ACL __usage_on_all_sequences__

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

ACL __usage_on_schemas__

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

ACL __usage_on_types__

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