Skip to content

Builtins Privileges

ldap2pg provides some builtin ACL and predefined privilege profiles 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.

Using Predefined Privilege Profiles

A privilege profile is a list of reference to a privilege type in an ACL. In ldap2pg, an ACL is a set of query to inspect, grant and revoke privilege on a class of objects. The inspect query expands aclitem PostgreSQL type to list all grants from system catalog. Privilege profile can include another profile.

Builtin privilege profile starts and ends with __. ldap2pg disables privilege profile starting with _. Thus you have to include builtin privileges profile in another profile to enable them. If two profiles reference the same privilege, ldap2pg will inspect it once.

privileges:
  ro:
  - __connect__
  - __usage_on_schemas__
  - __select_on_tables__

  rw:
  - ro
  - __insert__
  - __update_on_tables__

  ddl:
  - rw
  - __all_on_schemas__
  - __all_on_tables__

rules:
- grant:
    privilege: ddl
    database: mydb
    role: admins

Builtin profile’s name follows the following loose convention:

  • ..._on_all_tables__ references ALL TABLES IN SCHEMA ACL. Likewise for sequences and functions.
  • __default_...__ references both global and schema-wide default privileges.
  • __..._on_tables__ groups __..._on_all_tables__ and __default_..._on_tables__.
  • Group starting with __all_on_...__ is equivalent to ALL PRIVILEGES in SQL. However, each privilege will be granted individually.
  • A privilege specific to one object type does not have _on_<type> suffix. 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.

ACL Reference

Here is the list of builtin ACL.

For effective privileges:

  • DATABASE: privilege on database like CONNECT, CREATE, etc.
  • SCHEMA: manage USAGE and CREATE on schema.
  • LANGUAGE: manage USAGE on procedural languages.
  • ALL FUNCTIONS IN SCHEMA: manage EXECUTE on all functions per schema.
  • ALL SEQUENCES IN SCHEMA: like above but for sequences.
  • ALL TABLES IN SCHEMA: like above but for tables and views.
  • GLOBAL DEFAULT: manage default privileges on database.
  • SCHEMA DEFAULT: manage default privileges per schema.

ALL ... IN SCHEMA ACL inspects whether a privilege is granted to only a subset of objects. This is a partial grant. A partial grant is either revoked if unwanted or regranted if expected.

You can reference these ACL using privileges:on parameter in YAML. Like this:

privileges:
  myprofile:
  - type: SELECT
    on: ALL TABLES IN SCHEMA

Default privileges references a privilege type and a class of objects. ldap2pg inspect default privileges for the following object classes:

  • SEQUENCES
  • FUNCTIONS
  • TABLES

You must reference object class in privilege profile using object parameter in YAML.

You cannot (yet) configure custom ACL.

Profiles Reference

Profile __all_on_functions__

Profile __all_on_routines__

Profile __all_on_schemas__

Profile __all_on_sequences__

Profile __all_on_tables__

Profile __delete_on_tables__

Profile __execute_on_functions__

Profile __execute_on_routines__

Profile __insert_on_tables__

Profile __references_on_tables__

Profile __select_on_sequences__

Profile __select_on_tables__

Profile __trigger_on_tables__

Profile __truncate_on_tables__

Profile __update_on_sequences__

Profile __update_on_tables__

Profile __usage_on_sequences__

Privileges Reference

Here is the list of predefined privileges:

Name Manages
__connect__ CONNECT ON DATABASE
__create_on_schemas__ CREATE ON SCHEMA
__delete_on_all_tables__ DELETE ON ALL TABLES IN SCHEMA
__execute_on_all_functions__ EXECUTE ON ALL FUNCTIONS IN SCHEMA
__execute_on_all_routines__ EXECUTE ON ALL ROUTINES IN SCHEMA
__insert_on_all_tables__ INSERT ON ALL TABLES IN SCHEMA
__references_on_all_tables__ REFERENCES ON ALL TABLES IN SCHEMA
__select_on_all_sequences__ SELECT ON ALL SEQUENCES IN SCHEMA
__select_on_all_tables__ SELECT ON ALL TABLES IN SCHEMA
__temporary__ TEMPORARY ON DATABASE
__trigger_on_all_tables__ TRIGGER ON ALL TABLES IN SCHEMA
__truncate_on_all_tables__ TRUNCATE ON ALL TABLES IN SCHEMA
__update_on_all_sequences__ UPDATE ON ALL SEQUENCES IN SCHEMA
__update_on_all_tables__ UPDATE ON ALL TABLES IN SCHEMA
__usage_on_all_sequences__ USAGE ON ALL SEQUENCES IN SCHEMA
__usage_on_schemas__ USAGE ON SCHEMA

Default Privileges Reference

Here is the list of predefined default privileges. Default privilege profile references both global and schema defaults.

Name Manages
__default_delete_on_tables__ DELETE ON TABLES
__default_execute_on_functions__ EXECUTE ON FUNCTIONS
__default_execute_on_routines__ EXECUTE ON ROUTINES
__default_insert_on_tables__ INSERT ON TABLES
__default_references_on_tables__ REFERENCES ON TABLES
__default_select_on_sequences__ SELECT ON SEQUENCES
__default_select_on_tables__ SELECT ON TABLES
__default_trigger_on_tables__ TRIGGER ON TABLES
__default_truncate_on_tables__ TRUNCATE ON TABLES
__default_update_on_sequences__ UPDATE ON SEQUENCES
__default_update_on_tables__ UPDATE ON TABLES
__default_usage_on_sequences__ USAGE ON SEQUENCES