Skip to content

ldap2pg.yml file reference

ldap2pg requires a YAML configuration file usually named ldap2pg.yml and put in working directory. Everything can be configured from the YAML file: Postgres inspect queries, LDAP searches, privileges and synchronization map.


ldap2pg requires a config file where the synchronization map is described.

File Location

ldap2pg searches for configuration file in the following order :

  1. ldap2pg.yml in current working directory.
  2. ~/.config/ldap2pg.yml.
  3. /etc/ldap2pg.yml.

If LDAP2PG_CONFIG or --config is set, ldap2pg skip searching the standard file locations. You can specify - to read configuration from standard input. This is helpful to feed ldap2pg with dynamic configuration.

File Structure

ldap2pg.yml is split in several sections :

  • postgres : setup Postgres connexion and inspection queries.
  • ldap : setup LDAP connexion.
  • privileges : the definition of privileges.
  • sync_map : the list of LDAP searches and associated mapping to roles and grants.

We provide a simple well commented ldap2pg.yml, tested on CI. If you don’t know how to begin, it is a good starting point.


If you have trouble finding the right configuration for your needs, feel free to file an issue to get help.

About YAML

YAML is a super-set of JSON. A JSON document is a valid YAML document. YAML is a very permissive format where indentation is meaningful. See this YAML cheatsheet for some example.

In ldap2pg.yaml file, you will likely use wildcard for glob pattern and curly brace for LDAP attribute injection. Take care of protecting these characters with quotes.

Postgres Parameters

The postgres section defines connection parameters and custom SQL queries for Postgres inspection.

The postgres section contains several *_query parameters. These parameters can be either a string containing an SQL query or a YAML list to return a static list of values, skipping execution of a query on PostgreSQL cluster.


Specifies a PostgreSQL connexion URI.

  dsn: postgres://user@%2Fvar%2Frun%2Fpostgresql:port/


ldap2pg refuses to read a password from a group readable or world readable ldap2pg.yml.


The SQL query to list databases in the cluster. This defaults to all databases connectable, thus including template1. ldap2pg uses this list to reassign objects before dropping a role and managed privileges of databases and other objects in each databases.

  databases_query: "SELECT datname FROM pg_catalog.pg_databases;"
  # OR
  databases_query: [mydb]


Configuring a _query parameter with a YAML list skip querying the cluster for inspection and forces ldap2pg to use a static value.


Name of the role accepting ownership of database of dropped role. Defaults to role used by ldap2pg to synchronize cluster.

Before dropping a role, ldap2pg reassign objects and purge ACL. ldap2pg starts by reassigning database owner by the targetted user. The new owner of the database is the fallback owner. Other objects are reassigned to each database owner.


The SQL query to list the name of managed roles. ldap2pg restricts role deletion and privilege edition to managed roles. Usualy, this query returns children of a dedicated group like ldap_roles. By default, ldap2pg manages all roles found.

public is a special builtin role in Postgres. If managed_roles_query returns public role in the list, ldap2pg will manage privileges on public. By default, ldap2pg manages public privileges.

The following example tells ldap2pg to manage public role, ldap_roles and any members of ldap_roles:

  managed_roles_query: |


    SELECT DISTINCT role.rolname
    FROM pg_roles AS role
    JOIN pg_auth_members AS ms ON ms.member = role.oid
    JOIN pg_roles AS parent
      ON parent.rolname = 'ldap_roles' AND parent.oid = ms.roleid
    ORDER BY 1;


The SQL query to list the names of object owners. ldap2pg execute this query once in the cluster, after ldap2pg has created all roles, before granting and revoking privileges. You need this query only if you manage default privileges with ldap2pg.

  owners_query: |
    SELECT role.rolname
    FROM pg_catalog.pg_roles AS role
    WHERE role.rolsuper IS TRUE;

You can declare per-schema owners with schemas_query. See Managing Privileges for details.


The SQL query returning name and glob pattern to blacklist role from management. ldap2pg won’t touch anything on these roles. Default value is [postgres, pg_*].

  - postgres
  - "pg_*"
  - "rds_*"


Beware that *foo is a YAML reference. You must quote pattern beginning with *.


The SQL query returning all roles, their options and their members. It’s not very useful to customize this. Prefer to configure roles_blacklist_query and managed_roles_query to confine synchronization to a subset of roles.

Role’s options varies from one PostgreSQL version to another. ldap2pg handle this by injecting options columns in {options} substitution.

  roles_query: |
        role.rolname, array_agg(members.rolname) AS members,
        pg_catalog.shobj_description(role.oid, 'pg_authid') as comment
        pg_catalog.pg_roles AS role
    LEFT JOIN pg_catalog.pg_auth_members ON roleid = role.oid
    LEFT JOIN pg_catalog.pg_roles AS members ON members.oid = member
    GROUP BY role.rolname, {options}, comment
    ORDER BY 1;


The SQL query returning the name of schemas in a database. ldap2pg executes this query on each databases returned by databases_query, only if ldap2pg manages privileges. ldap2pg loops on objects in theses schemas when inspecting GRANTs in the cluster.

  schemas_query: |
    SELECT nspname FROM pg_catalog.pg_namespace

schema_query can return a second column as an array of string. This column defines the name of roles owning objects in this schema. See Managing Privileges for details.

LDAP Directory Connection

The LDAP section is fairly simple. The top-level ldap section is meant only to gather LDAP connexion informations.

The ldap section defines libldap parameters.

  uri: ldap://ldap2pg.local:389
  binddn: cn=admin,dc=ldap2pg,dc=local
  # For SASL
  sasl_mech: DIGEST-MD5
  user: saslusername
  password: SECRET

Actually, it’s better to configure ldap connexion through ldaprc and regular libldap environment variables than in YAML. See ldap.conf(5) for details.

The best practice is to configure ldapsearch and then ldap2pg will be happy like any other libldap tool. If not, please open an issue. This allows you to share ldap2pg.yml file between different environments.

PostgreSQL Privileges Section

The privileges top level section is a mapping defining high-level privileges in Postgres cluster, referenced later in Synchronisation map grant rule.

An entry in privileges is either a list of other privileges (also known as group of privileges) or a definition of a custom privilege. A group of privileges can include another group of privileges.

A privilege whose name starts with _ is inactive. An active privilege grant found in the cluster and not granted in the YAML is implicitly revoked. An inactive privilege is still available for inclusion in a privilege group. Every privileges included in an active group is activated, regardless of the _ prefix. This allows ldap2pg to ship well-known privileges ready to be included in your custom group of privileges.

  - __select_on_tables__
  - __connect__
  - custompriv

    type: datacl
    inspect: SELECT ...
    grant: GRANT ...
    revoke: REVOKE ...

Writing a custom privilege is hard. Before writing one, ensure that it’s not already shipped in ldap2pg well-known privileges. Please open an issue if you miss a builtin privilege. Also, please share your custom privilege. This will increase the quality of privilege handling in your installation and in ldap2pg project.


Privilege can be of different kind. The type of privilege influences whether ldap2pg should loops on databases, schemas or owners roles. This changes also the parameters required to define a grant.

    type: datacl

See Defining Custom Privileges for possible values and their meaning.


The SQL query to inspect grants of this privilege in the cluster. The signature of tuples returned by this query varies after privilege type. This query may be executed once for global objects or per database, depending on privilege type.

    inspect: |
      SELECT grantee FROM ...

This is the trickiest query to write when synchronizing privileges. See Privilege documentation for details.


SQL query to grant a privilege to a role. Some parameters are injected in this query using mustache substitution like {role}. Parameters depends on privilege type. For example, a defacl privilege must accept an {owner} parameter.

This option must not be confused with grant rule in synchronisation map.

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

See Privilege documentation for details.


Just like grant ; the SQL query to revoke a privilege from a role. Parameters are substituted with mustache syntax like {role} and depends on privilege type. You must reuse the same parameters as in grant query.

    revoke: REVOKE SELECT ON ALL TABLES IN SCHEMA {schema} FROM {role};

See Privilege documentation for details.

Synchronisation map

The top level sync_map section is a YAML list. This is the only mandatory parameter in ldap2pg.yaml. Each item of sync_map is called a mapping. A mapping is a YAML dict with a description field and any of ldapsearch, role and grant subsection.

- description: "Define DBA roles"
    base: ...
  - name: "{cn}"
    options: LOGIN SUPERUSER

The ldapsearch subsection is optional. You can define roles and grants without querying a directory.


A free string used for logging. This parameter does not accepts mustache parameter injection.


This directive defines LDAP search parameters. Not to be confused with top-level ldap section defining LDAP connexion parameters. It is named after the ldapsearch CLI utility shipped by OpenLDAP project. It’s behaviour should be mostly the same.


This documentation refers LDAP query as search while the word query is reserved for SQL query.

ldapsearch directives allows and requires LDAP attributes injection in role and grant rules using curly braces. See Searching directory for details.


Lists the names of LDAP attributes that LDAP server may not return. This parameters configures a ldap2pg behavior to prevent typographic error in configuration. LDAP protocols allows to query arbiratry attributes, event undefined ones. If configuration has a typo, no error will be returned. By default, ldap2pg consider a missing attributes as a typo, except if it’s listed in allow_missing_attributes.

If member attribute is searched, it will be added to the list. This is the default value.

The following example accepts that a LDAP entry miss sAMAccountName. The entry missing sAMAccountName wont generate a role.

- ldapsearch:
    base: ...
    allow_missing_attributes: [member, sAMAccountName]
  - name: "{sAMAccountName}"
    members: "{}"

base, scope and filter

These parameters have the same meaning, definition and default as searchbase, scope and filter arguments of ldapsearch CLI utility.

- ldapsearch:
    base: ou=people,dc=acme,dc=tld
    scope: sub
    filter: >


Customizes LDAP sub-searches. The joins section is a dictionary with attribute name as key and LDAP search parameters as value. LDAP search parameters are the same as for top LDAP search.

- ldapsearch:
        filter: ...
        scope: ...
  - name: "{member.sAMAccountName}"

The search base of sub-search is the value of the referencing attribute, e.g. each value of member. You can’t customize the base attribute of sub-search. Likewise, ldap2pg infers attributes of sub-searches from role and grant rules.

See Searching directory for details.


Executing a sub-search for each entry of a result set can be very heavy. You may optimize the query by using special LDAP search filter like memberOf. Refer to your LDAP directory administrator and documentation for details.


Sometimes, an attribute references another entry in LDAP rather than specifying a value. This mixed types attributes are hard to handle and must be avoided.

The on_unexpected_dn parameter allows you to tell ldap2pg how to behave it this case. The default is to fail. You can choose to either warn or silently ignore these values.

- ldapsearch:
    on_unexpected_dn: warn  # fail | warn | ignore


Defines a rule to define one or more roles wanted in the target Postgres cluster. This includes name, options, comment and membership. Plural form roles is valid. The value can be either a single role rule or a list of role rules.

- role:
    name: dba
    options: SUPERUSER LOGIN
- roles:
  - name: group0
    options: NOLOGIN
  - name: group1
    options: NOLOGIN


Defines the SQL comment of a role. Default value is Managed by ldap2pg. Accepts LDAP attribute injection.

In case of LDAP attributes injection, you must take care of how many combination will be generated. If the template generates a single comment, ldap2pg will copy the comment for each role generated by the role rule. If the template generates multiple comments, ldap2pg associates name and comment. If there is more or less comments generated than name generated, ldap2pg fails.

The following example defines a static comment shared by all generated roles:

- roles:
    - alice
    - bob
    comment: "Static roles from YAML."

The following example generate a single comment from LDAP entry distinguised name, copied for all generated roles:

- ldapsearch:
    name: "{cn}"
    comment: "Generated from LDAP entry {dn}."

The following example generate a unique comment for each roles generated:

- ldapsearch:
    name: "{}"
    comment: "Generated from LDAP entry {member}."


Name of a child role. A list of names is accepted. The plural form members is valid. Role is granted to children with GRANT ROLE role TO member. member parameter accepts LDAP attributes injection using curly braces. ldap2pg applies roles_blacklist_query on this parameter.

- role:
    name: myrole
    member: mychild


Name of the role wanted in the cluster. The value can be either a single string or a list of strings. Plural form names is valid. You can inject LDAP attributes in name using curly braces. When multiple names are defined, a new role is defined for each name, each with the same attributes such as options and members. comment parameter has a special handling, see above.

- roles:
    name: "my-role-name"

When injecting LDAP attribute in name, each value of the LDAP attribute of each LDAP entry will define a new role. When multiple LDAP attributes are defined in the format, all combination of attributes are generated.

ldap2pg protects role name with double quotes in the target Postgres cluster. Capitalization is preserved, spaces are allowed (even if it’s a really bad idea).

ldap2pg applies roles_blacklist_query on this parameter.


Defines a condition on name as a glob pattern. If a name does not match this pattern, the role is skipped from creation.

- role:
    name: "{cn}"
    name_match: "external_*"


Defines PostgreSQL role options. Maybe an SQL-like string or a YAML dictionary. Valid options are BYPASSRLS, LOGIN, CREATEDB, CREATEROLE, INHERIT, REPLICATION and SUPERUSER. Available options varies following the version of the target PostgreSQL cluster.

- roles:
  - name: my-dba
    options: LOGIN SUPERUSER
  - name: my-group
      LOGIN: no
      INHERIT: yes


Name of a parent role. A list of names is accepted. The plural form parents is valid too. Parent role is granted with GRANT ROLE parent TO role;. parent parameter accepts LDAP attributes injection using curly braces. ldap2pg applies roles_blacklist_query on this parameter.

- role:
    name: myrole
    parent: myparent


Defines a grant of a privilege to a role with corresponding parameters.

- grant:
    privilege: reader
    databases: __all__
    schema: public
    role: myrole


Name of a database, within the list of database names returned by databases_query. May be a list of names. Plural form databases is valid. Default value is special name __all__ which mean all databases as returned by databases_query. Defines the database where the privilege must be granted. Grants found in other databases will be revoked. Accepts LDAP attributes injection using curly braces.

This parameter is valid for all types of privileges.


Name of a privilege, within the privileges defined in privileges YAML section. May be a list of names. Plural form privileges is valid. Required, there is not default value. Accepts LDAP attribute injection using curly braces.


Name of the target role of the grant (granted role). Must be returned in the result of managed_roles_query. May be a list of names. Plural form roles is valid. Accepts LDAP attribute injection using curly braces. ldap2pg applies roles_blacklist_query on this parameter.


Name of a schema, whithin the schemas returned by schemas_query. Special value __all__ means all managed schemas in the databases returned by schemas_query. May be a list of names. Plural form schemas is valid. Accepts LDAP attribute injection using curly braces.

This parameter is ignored for privileges of type globaldefacl and datacl.

Back to top