Well-known Privileges

ldap2pg provides some well-known privileges for recurrent usage. There is no warranty on these privileges. 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 privileges is the inspect queries associated and the boilerplate saved for declaring all GRANT queries.

Using Well-known Privileges

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

privileges:
  ro:
  - __connect__
  - __usage_on_schemas__
  - __select_on_tables__

  rw:
  - ro
  - __insert__
  - __update_on_tables__

  ddl:
  - rw
  - __all_on_schemas__
  - __all_on_tables__

sync_map:
- grant:
    privilege: 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__ gathers __..._on_all_tables__ and __default_..._on_tables__.
• Group starting with __all_on_...__ is equivalent to ALL PRIVILEGES in SQL.
• A privilege specific to one object type does not have _on_<type>__ e.g. __delete_on_tables__ is aliased to __delete__.

This page does not document the SQL standard and the meaning of each SQL privileges. You will find the documentation of SQL privileges in Postgresql GRANT documentation and ALTER DEFAULT PRIVILEGES documentation.

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 privilege. Each privilege name point the the detail of privilege definition.

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

Here we go.

Group __all_on_schemas__

Includes:

• __create_on_schemas__
• __usage_on_schemas__

Group __all_on_sequences__

Includes:

• __default_select_on_sequences__
• __default_update_on_sequences__
• __default_usage_on_sequences__
• __select_on_all_sequences__
• __update_on_all_sequences__
• __usage_on_all_sequences__

Group __all_on_tables__

Includes:

• __default_delete_on_tables__
• __default_insert_on_tables__
• __default_references_on_tables__
• __default_select_on_tables__
• __default_trigger_on_tables__
• __default_truncate_on_tables__
• __default_update_on_tables__
• __delete_on_all_tables__
• __insert_on_all_tables__
• __references_on_all_tables__
• __select_on_all_tables__
• __trigger_on_all_tables__
• __truncate_on_all_tables__
• __update_on_all_tables__

Group __delete_on_tables__

Includes:

• __default_delete_on_tables__
• __delete_on_all_tables__

Alias: __delete__

Group __execute_on_functions__

Includes:

• __default_execute_on_functions__
• __execute_on_all_functions__
• __global_default_execute_on_functions__

Alias: __execute__

Group __insert_on_tables__

Includes:

• __default_insert_on_tables__
• __insert_on_all_tables__

Alias: __insert__

Group __references_on_tables__

Includes:

• __default_references_on_tables__
• __references_on_all_tables__

Alias: __references__

Group __select_on_sequences__

Includes:

• __default_select_on_sequences__
• __select_on_all_sequences__

Group __select_on_tables__

Includes:

• __default_select_on_tables__
• __select_on_all_tables__

Group __trigger_on_tables__

Includes:

• __default_trigger_on_tables__
• __trigger_on_all_tables__

Alias: __trigger__

Group __truncate_on_tables__

Includes:

• __default_truncate_on_tables__
• __truncate_on_all_tables__

Alias: __truncate__

Group __update_on_sequences__

Includes:

• __default_update_on_sequences__
• __update_on_all_sequences__

Group __update_on_tables__

Includes:

• __default_update_on_tables__
• __update_on_all_tables__

Group __usage_on_sequences__

Includes:

• __default_usage_on_sequences__
• __usage_on_all_sequences__

Single Privileges

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

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

Alias: __usage_on_types__

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};