Features #

Requirements #

ldap2pg is released as a single binary with no dependencies.

At run time, ldap2pg requires an unprivileged role with CREATEDB and CREATEROLE options or a superuser access.

ldap2pg does not require to run on the same host as the synchronized Postgres Pro Enterprise cluster.

With 2MiB of RAM and one vCPU, ldap2pg can synchronize several thousands of roles in seconds, depending on Postgres Pro Enterprise instance and LDAP directory response time.

File Location #

ldap2pg searches for configuration file in the following order:

If LDAP2PG_CONFIG or --config is set, ldap2pg skips 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:

If you don't know how to begin, a simple tested and well commented ldap2pg.yml is a good starting point.

rules:
  - role: {cn}  # It's an invalid YAML dict.
  - role: "{cn}"  # It's a string with LDAP attribute injection

postgres Section #

The postgres section defines custom SQL queries for Postgres Pro 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 Postgres Pro cluster.

ldap Section #

The ldap section customizes LDAP client behaviour. Configure connection using ldap.conf and LDAP* environment variables.

privileges Section #

The privileges top-level section defines Postgres Pro privileges. It is a mapping defining privilege profiles, referenced later in synchronisation map's grant rule. A privilege profile is a list of either references to a privilege type in a Postgres Pro ACL or other profiles. A privilege profile may include another profile, recursively. See Managing Privileges for details.

privileges:
  reading:
  - on: GLOBAL DEFAULT
    type: SELECT
    object: TABLES
  writing:
  - reading
  - on: GLOBAL
    type: SELECT
    object: TABLES

A privilege profile whose name starts with _ is inactive unless included in an active profile.

rules Section #

The top-level rules section defines synchronisation rules. The section contains is a YAML list. This is the only mandatory parameter in ldap2pg.yaml. Each item of rules is called a mapping. A mapping is a YAML dictionary with any of role or grant subsection. A mapping can optionnaly have a description field and a ldapsearch section.

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

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

acls Section #

Defines Postgres Pro ACLs. An ACL is set of queries to list GRANTs in the cluster and to manage them by granting or revoking item in the list. ACL is scoped to instance or database. References ACL in privileges profiles. Writing a custom ACL is tricky, ensure you understand both PostgreSQL and ldap2pg before.

What Databases to Synchronize? #

databases_query returns the flat list of databases to manage. The databases_query must return the default database as defined in PGDATABASE. When dropping roles, ldap2pg loops the databases list to reassign objects and clean GRANTs of the role to be dropped. This databases list also narrows the scope of GRANTs inspection. ldap2pg will revoke GRANTs only on these databases. See the section called “postgres Section” for details.

postgres:
  databases_query: |
    SELECT datname
    FROM pg_catalog.pg_database
    WHERE datallowconn IS TRUE;

Synchronize a Subset of Roles #

By default, ldap2pg manages all roles from Postgres Pro, it has powers on, minus the default blacklist. If you want ldap2pg to synchronsize only a subset of roles, you need to customize inspection query in postgres:managed_roles_query. The following query excludes superusers from synchronization.

postgres:
  managed_roles_query: |
    SELECT 'public'
    UNION
    SELECT rolname
    FROM pg_catalog.pg_roles
    WHERE rolsuper IS FALSE
    ORDER BY 1;

ldap2pg will only drop, revoke, grant on roles returned by this query.

A common case for this query is to return only members of a group like ldap_roles. This way, ldap2pg is scoped to a subset of roles in the cluster.

The public role does not exist in the system catalog. Thus if you want ldap2pg to manage public privileges, you must include explicitly public in the set of managed roles. This is the default. Of course, even if public is managed, ldap2pg won't drop or alter it if it's not in the directory.

A safety net to completely ignore some roles is roles_blacklist_query.

postgres:
  roles_blacklist_query: [postgres, pg_*]  # This is the default.

Inspecting Schemas #

For schema-wide privileges, ldap2pg needs to know managed schemas for each database. This is the purpose of schemas_query.

Configuring Owners Default Privileges #

To configure default privileges, use the default keyword when referencing a privilege:

privileges:
  reading:
  - default: global
    type: SELECT
    on: TABLES

Then grant it using grant rule:

rules:
- grant:
  - privilege: reading
    role: readers
    schema: public
    owner: ownerrole

You can use __auto__ as owner. For each schema, ldap2pg will configure every managed role having CREATE privilege on schema.

rules:
- grant:
  - privilege: reading
    role: readers
    schema: public
    owner __auto__

ldap2pg configures default privileges last, after all effective privileges. Thus CREATE on schema is granted before ldap2pg inspects creators on schemas.

Static Queries #

You can replace all queries with a static list in YAML. This list will be used as if returned by Postgres Pro. That's very handy to freeze a value like databases or schemas.

postgres:
  databases_query: [postgres]
  schemas_query: [public]

Running Unprivileged #

ldap2pg is designed to run unprivileged. Synchronization user needs CREATEROLE option to manage other unprivileged roles. CREATEDB options allow synchronization user to manage database owners.

ldap2pg user must have createrole_self_grant set to inherit,set to properly handle groups.

CREATE ROLE ldap2pg LOGIN CREATEDB CREATEROLE;
ALTER ROLE ldap2pg SET createrole_self_grant TO 'inherit,set;

Running unprivileged before PostgreSQL 16 is actually flawed. You'd better just run ldap2pg with superuser privileges, you won't feel falsly secured.

Ignoring Roles #

ldap2pg totally ignores roles matching one of the global pattern defined in roles_blacklist_query:

    postgres:
      # This is the default value.
      roles_blacklist_query: [postgres, pg_*]
    

The role blacklist is also applied to grants. ldap2pg will never apply grant or revoke on a role matching one of the blacklist patterns.

ldap2pg blacklists its running user.

Membership #

ldap2pg manages parents of roles. ldap2pg applies roles_blacklist_query to parents. However, ldap2pg grants unmanaged parents. This way, you can create a group manually and manage its members using ldap2pg.

Configuring Directory Access #

ldap2pg reads directory configuration from ldaprc file and LDAP* environment variables. Known LDAP options are:

See ldap.conf(5) man page for the meaning and format of each options.

Injecting LDAP Attributes #

Several parameters accept LDAP attribute injection using curly braces. To do this, wrap attribute name with curly braces like {cn} or {sAMAccountName}. ldap2pg expands to each value of the attribute for each entries of the search.

If the parameter has multiple LDAP attributes, ldap2pg expands to all combinations of attributes for each entries.

Given the following LDAP entries:

dn: uid=dimitri,cn=Users,dc=bridoulou,dc=fr
objectClass: inetOrgPerson
uid: dimitri
sn: Dimitri
cn: dimitri
mail: dimitri@bridoulou.fr
company: external
dn: cn=domitille,cn=Users,dc=bridoulou,dc=fr
objectClass: inetOrgPerson
objectClass: organizationalPerson
objectClass: person
objectClass: top
cn: domitille
sn: Domitille
company: acme
company: external

The format {company}_{cn} with the above LDAP entries generates the following strings:

The pseudo attribute dn is always available and references the distinguished name (DN) of the original LDAP entry.

- ldapsearch: ...
  role: "{cn.lower()}"

Basics #

The base design of ldap2pg is as follows. Instead of revoke-everything-regrant design, ldap2pg uses inspect-modify design. The process is the same as for roles synchronization, including the three following steps:

ldap2pg represents privileges with these core objects:

ldap2pg.yml holds profiles and grant rule. ldap2pg synchronizes ACL one at a time, database by database. ldap2pg synchronizes default privileges last.

By default, ldap2pg does not manage any privileges. To enable privilege management, you must define at least one active profile in privileges section. The simplest way is to reuse built-in privilege profiles shipped with ldap2pg in an active custom profile.

Defining a Privilege Profile #

A privilege profile is a list of references to either a privilege type on an ACL or another profile. ldap2pg ships several predefined ACL like DATABASE, LANGUAGE, etc. A privilege type is USAGE, CONNECT and so on, as described in Section 5.8. See privileges YAML section documentation for details on privilege profile format.

ldap2pg loads referenced ACL by inspecting Postgres Pro cluster with carefully crafted queries. ldap2pg inspects only ACL referenced in at least one profile. Inspected grants are supposed to revocation unless explicitly wanted by a grant rule.

Extended Intance Inspection #

When managing privileges, ldap2pg has deeper inspection of Postgres Pro instance. ldap2pg inspects schemas after roles synchronization and before synchronizing privileges. ldap2pg inspects objects owner after privileges synchronization and before synchronizing default privileges. An object owner is a role having CREATE privilege on the schema.

Granting Privilege Profile #

Inspecting millions of privileges may consume a lot of resources on Postgres Pro instance. Revoking privileges is known to be slow in Postgres Pro. The best practice is to grant privileges to a group role and let user inherit privileges. With ldap2pg, you can define static groups in YAML and inherit them when creating roles from directory.

Use grant rule to grant a privilege profile to one or more roles. When granting privileges, you must define the grantee. You may scope the grant to one or more databases, one or more schemas. If the privilege profile includes default privileges, you may define the owners on which to configure default privileges.

By default, a grant applies to all managed databases as returned by databases_query, to all schemas of each database as returned by schemas_query.

Example #

The following example defines three privileges profile. The rules defines three groups and grant the corresponding privilege profile:

privileges:
  reading:
  - __connect__
  - __usage_on_schemas__
  - __select_on_tables__
  writing:
  - reading  # include reading privileges
  - __insert_on_tables__
  - __update_on_tables__
  owning:
  - writing
  - __create_on_schemas__
  - __truncate_on_tables__
rules:
- role:
  - names:
    - readers
    - writers
    - owners
    options: NOLOGIN
- grant:
  - privilege: reading
    role: readers
  - privilege: writing
    role: writers
  - privilege: owning
    role: owners

Another way of including reading profile in writing is to writers group to inherit readers group.

Managing Public Privileges #

Postgres Pro has a pseudo-role called public. It's a wildcard role meaning every users. All roles in Postgres Pro implicitly inherits from this public role. Granting a privilege to public role grants to every roles now and in the future.

Postgres Pro also has the public schema. The public schema is a real schema available in all databases.

Postgres Pro has some built-in privileges for public role. Especially for the public schema. For example, public has CONNECT on all databases by default. This means that you only rely on pg_hba.conf to configure access to databases, which requires administrative access to the cluster and a pg_reload_conf() call.

By default, ldap2pg includes public role in managed roles. Predefined ACL knows how to inspect built-in privileges granted to public. If you want to preserve public role, rewrite managed_roles_query to not include public.

Managing Default Privileges #

If you grant SELECT privileges on all tables in a schema to a role, this won't apply to new tables created afterward. Instead of reexecuting ldap2pg after the creation of every objects, Postgres Pro provides a way to define default privileges for future objects.

Postgres Pro attaches default privileges to the creator role. When the role creates an object, Postgres Pro applies the corresponding default privileges to the new object. e.g., ALTER DEFAULT PRIVILEGES FOR ROLE bob GRANT SELECT ON TABLES TO alice; ensures every new table bob creates will be selectable by alice:

If ldap2pg creates and drops creator roles, you want ldap2pg to properly configure default privileges on these roles. If you wonder whether to manage privileges with ldap2pg, you should at least manage default privileges along creator.

ldap2pg inspects the creators from Postgres Pro, per schemas, not LDAP directory. A creator is a role with LOGIN option and CREATE privilege on a schema. You can manually set the target owner of a grant to any managed roles.

ldap2pg does not configure privileges on __all__ schemas. You are supposed to use GLOBAL DEFAULT ACL instead. If you want to grant/revoke default privilege per schema, you must reference SCHEMA DEFAULT ACL.

The following example configures default privileges for alice to allow bob to SELECT on future tables created by alice.

privileges:
  reading:
  - type: SELECT
    on: SCHEMA DEFAULT
    object: TABLES
  owning:
  - type: CREATE
    on: SCHEMAS

rules:
- roles:
    names:
    - alice
    - bob
    options: LOGIN
- grant:
    privilege: owning
    role: alice
- grant:
    privilege: reading
    role: bob

Postgres Pro has hard-wired global default privileges. If a role does not have global default privileges configured, Postgres Pro assumes some defaults. By default, Postgres Pro just grants privileges on owner. You can see them once you modify the default privileges. Postgres Pro will copy the hard-wired values along with your granted privileges.

If you don't explicitly re-grant these privileges in ldap2pg.yml, ldap2pg will revoke these hard-wired privileges. Actually, an owner of table don't need to be granted SELECT on its own tables. Thus, the hard-wired defaults are useless. You can let ldap2pg purge these useless defaults.

Using Predefined Privilege Profiles #

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

Built-in privilege profile starts and ends with __. ldap2pg disables privilege profile starting with _. Thus you have to include built-in 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__

  ddl:
  - rw
  - __all_on_schemas__

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

Built-in profile's name follows the loose convention below:

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

ACL Reference #

Here is the list of built-in ACLs.

For effective privileges:

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 re-granted if expected.

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

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

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

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

You cannot configure custom ACL (yet).

Profiles Reference #

Privileges Reference #

Here is the list of predefined privileges:

Default Privileges Reference #

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

Configure pg_hba.conf with LDAP #

ldap2pg does NOT configure Postgres Pro for you. You should carefully read Section 20.10 for this point. Having Postgres Pro properly configured before writing ldap2pg.yaml is a good start. Here is the steps to setup Postgres Pro with LDAP in the best order:

Once you have LDAP authentication configured in Postgres Pro cluster, you can move to automate role creation from the LDAP directory using ldap2pg:

Search LDAP Directory #

The first step is to search your LDAP server with ldapsearch(1), the command-line interface tool from OpenLDAP. Like this:

$ ldapsearch -H ldaps://ldap.ldap2pg.docker -U testsasl -W
Enter LDAP Password:
SASL/DIGEST-MD5 authentication started
SASL username: testsasl
SASL SSF: 128
SASL data security layer installed.
# extended LDIF
#
# LDAPv3
...
# search result
search: 4
result: 0 Success

# numResponses: 16
# numEntries: 15
$

Now save the settings in ldaprc:

LDAPURI ldaps://ldap.ldap2pg.docker
LDAPSASL_AUTHCID testsasl

And in environment: LDAPPASSWORD=secret

Next, update your ldapsearch to properly match role entries in LDAP server:

$ ldapsearch -H ldaps://ldap.ldap2pg.docker -U testsasl -W -b cn=dba,ou=groups,dc=ldap,dc=ldap2pg,dc=docker '' member
...
# dba, groups, ldap.ldap2pg.docker
dn: cn=dba,ou=groups,dc=ldap,dc=ldap2pg,dc=docker
member: cn=Alan,ou=people,dc=ldap,dc=ldap2pg,dc=docker
member: cn=albert,ou=people,dc=ldap,dc=ldap2pg,dc=docker
member: cn=ALICE,ou=people,dc=ldap,dc=ldap2pg,dc=docker

# search result
search: 4
result: 0 Success

...
$

Now translate the query in ldap2pg.yaml and associate a role mapping to produce roles from each values of each entries returned by the LDAP search:

- ldapsearch:
    base: cn=dba,ou=groups,dc=ldap,dc=ldap2pg,dc=docker
  role:
    name: '{member.cn}'
    options: LOGIN SUPERUSER

Test it:

$ ldap2pg
...
Querying LDAP cn=dba,ou=groups,dc=ldap,dc=ldap2pg,dc=docker...
Would create alan.
Would create albert.
Would update options of alice.
...
Comparison complete.
$

Read further on how to control role creation from LDAP entry in Configuration. Once you're satisfied with the comparison output, go real with --real.

Using LDAP High-Availability #

ldap2pg supports LDAP high availability out of the box just like any OpenLDAP client. Use a space-separated list of URIs to tell all servers.

$ LDAPURI="ldaps://ldap1 ldaps://ldap2" ldap2pg

See ldap.conf(5) for further details.

Running as Non-Superuser #

Since Postgres Pro> provides a CREATEROLE role option, you can manage roles without superuser privileges. Security-wise, it's a good idea to manage roles without superuser privileges.

ldap2pg supports this case. However, you must be careful about the limitations. Let's call the non-superuser role creating other roles creator.

Removing All Roles #

If ever you want to clean all roles in a Postgres Pro cluster, ldap2pg could be helpful. You must explicitly define empty rules.

$ echo '{version: 6, rules: []}' | ldap2pg --config -
...
Empty synchronization map. All roles will be dropped!
...

In this example, default blacklist applies. ldap2pg never drops its connection role.

ldap2pg as Docker Container #

We assume that you are already familiar with Docker and willing to save the setup time.

To run the container simply use the command:

$ docker run --rm dalibo/ldap2pg --help

The Docker image of ldap2pg uses the same configuration options as explained in the Command-Line Interface and ldap2pg.yml sections. You can mount the ldap2pg.yml configuration file.

$ docker run --rm -v ${PWD}/ldap2pg.yml:/workspace/ldap2pg.yml dalibo/ldap2pg

You can also export some environmnent variables with the -e option:

$ docker run --rm -v ${PWD}/ldap2pg.yml:/workspace/ldap2pg.yml -e PGDSN=postgres://postgres@localhost:5432/ -e LDAPURI=ldaps://localhost -e LDAPBINDDN=cn=you,dc=entreprise,dc=fr -e LDAPPASSWORD=pasglop dalibo/ldap2pg

Make sure your container can resolve the hostname you are pointing to. If you use some internal name resolution, be sure to add the -dns= option to your command pointing to your internal DNS server. You can also find more information here.

Use Case #

Say you have a custom enum type myenum in your database.

CREATE TYPE public.myenum AS ENUM ('toto', 'titi', 'tata');

We want to manage privileges on this object, eventually other types, with a custom ACL.

Naming #

Name your ACL after the keyword in GRANT or REVOKE statement. From GRANT USAGE ON TYPE mytype TO myrole, name your ACL TYPE.

acls:
  TYPE:
    ...

Scope #

Postgres Pro defines user types per schema. Since we'll hardcode the schema, we will scope our ACL to database.

acls:
  TYPE:
    scope: database

Grant and Revoke #

Writing GRANT and REVOKE statements is the easiest part. See grant for details on query format. We'll use the object field of grant to store the name of the type. Public schema is hardcoded, as explained above.

acls:
  TYPE:
    scope: database
    grant: GRANT <privilege> ON TYPE public.<object> TO <grantee>;
    revoke: REVOKE <privilege> ON TYPE public.<object> FROM <grantee>;

Inspect #

The inspect query is the most difficult part. You need to master aclitem, aclexplode and acldefault Postgres Pro system built-ins. The signature of the inspect query depends on the scope of the ACL.

For instance scope:

For database scope:

partial tells ldap2pg to re-grant ALL ... IN SCHEMA privileges. Since our ACL is handling one object at a time, partial will always be false.

ldap2pg sends a single parameter to inspect query: the effective list of privilege types managed by the configuration. This list is an array of text. ldap2pg expects query to filter other privileges out of the list.

For TYPE ACL, we will inspect privileges on pg_type system catalog.

acls:
  TYPE:
    scope: database
    grant: GRANT <privilege> ON <acl> public.<object> TO <grantee>;
    revoke: REVOKE <privilege> ON <acl> public.<object> FROM <grantee>;
    inspect: |
      WITH grants AS (
        SELECT typname,
               (aclexplode(COALESCE(typacl, acldefault('T', typowner)))).privilege_type AS priv,
               (aclexplode(COALESCE(typacl, acldefault('T', typowner)))).grantee::regrole::text AS grantee
          FROM pg_catalog.pg_type
        WHERE typnamespace::regnamespace = 'public'::regnamespace
          AND typtype <> 'b'  -- exclude base type.
      )
      SELECT grants.priv AS "privilege",
            grants.typname AS "object",
            CASE grants.grantee WHEN '-' THEN 'public' ELSE grants.grantee END AS grantee,
            FALSE AS partial
        FROM grants
      WHERE "priv" = ANY ($1)
      ORDER BY 2, 3, 1
      ;

As you see, it's not an easy query. This query works on Postgres Pro 17.

Usage #

You can now use your custom ACL in a profile. Reference object in profile, not in grant rule.

privileges:
  custom:
  - type: USAGE
    on: TYPE
    object: myenum

rules:
- roles:
    names:
    - alice
  grant:
    privilege: custom
    role: alice

You must reference all types manually in the profile. Executing ldap2pg should produce changes in your database:

$ ldap2pg
...
16:52:02 CHANGE Would Revoke privileges.                         grant="USAGE ON TYPE myenum TO public" database=db0
16:52:02 CHANGE Would Grant privileges.                          grant="USAGE ON TYPE myenum TO alice" database=db0
16:52:02 INFO   Comparison complete.                             searches=0 roles=1 queries=5 grants=1
16:52:02 INFO   Use --real option to apply changes.
16:52:02 INFO   Done.                                            elapsed=44.345229ms mempeak=1.6MiB ldap=0s inspect=28.992071ms sync=0s

That's it.

Debugging #

If you encounter problem, isolate the issue. Reduce the configuration to your custom ACL. Synchronize a single role, grant to it. Avoid LDAP searches, use only static rules. Synchronize a single database. Enable debug messages with --verbose option.

ldap2pg works database per database then ACL per ACL. The messages for each ACL are as follow:

First line about your ACL has acl=TYPE record attribute.

    17:13:35 DEBUG  Inspecting grants.                               acl=TYPE scope=database database=db0
    

Then you have messages for inspection: query and arguments. For each grant returned, a Found grant. message appears.

17:13:35 DEBUG  Executing SQL query:
WITH grants AS (
...
WHERE "priv" = ANY ($1)
ORDER BY 2, 3, 1
;
 arg=[USAGE]
17:13:35 DEBUG  Found grant in Postgres instance.                grant="USAGE ON TYPE myenum TO public" database=db0

Then, ldap2pg expands grants generated by rule. For each grant generated, a Wants grant. message is printed.

17:13:35 DEBUG  Wants grant.                                     grant="USAGE ON TYPE myenum TO alice" database=db0

Finally, ldap2pg prints changes it would apply.

17:13:35 CHANGE Would Revoke privileges.                         grant="USAGE ON TYPE myenum TO public" database=db0
17:13:35 DEBUG  Would Execute SQL query:
REVOKE USAGE ON TYPE public."myenum" FROM "public";
17:13:35 CHANGE Would Grant privileges.                          grant="USAGE ON TYPE myenum TO alice" database=db0
17:13:35 DEBUG  Would Execute SQL query:
GRANT USAGE ON TYPE public."myenum" TO "alice";

At the end, ldap2pg prints a conclusion message, even if no changes are required.

17:13:35 DEBUG  Privileges synchronized.                         acl=TYPE database=db0