ldap2pg

ldap2pg is a tool that automates the creation, update, and removal of PostgreSQL roles and users based on an enterprise directory (LDAP).

Key capabilities include:

• Role Lifecycle Management: Creates, alters, and drops PostgreSQL roles derived from LDAP searches.
• Static Role Creation: Creates roles defined directly in YAML to complement LDAP entries.
• Privilege Management: Grants or revokes privileges either statically via YAML or dynamically from LDAP entries.
• Group Management: Manages role parents (aliases for groups).
• Safety & Observability: Supports dry runs and check modes, logs LDAP searches as ldapsearch(1) commands, and logs every SQL statement executed.

It is compatible with PostgreSQL versions 9.5 through 18 and works with directory services such as Samba DC, OpenLDAP, FreeIPA, Oracle Internet Directory, and Microsoft Active Directory.

ldap2pg is a tool designed to automate the management of PostgreSQL roles and privileges. It can:

• Synchronize from LDAP: Create, alter, and drop PostgreSQL roles based on LDAP searches.
• Synchronize from YAML: Create static roles defined directly in your configuration file.
• Manage Privileges: Grant or revoke privileges statically or based on LDAP entries.
• Manage Role Hierarchy: Handle role parents (groups).

It supports PostgreSQL versions from 9.5 up to 17. It is compatible with various directory services including Samba Directory, OpenLDAP, FreeIPA, Oracle Internet Directory, and Microsoft Active Directory.

gokrb5 provides both client and server-side Kerberos capabilities:

Server Side

• SPNEGO Authentication: HTTP handler wrapper for Kerberos authentication.
• Microsoft AD PAC: HTTP handler wrapper that decodes Microsoft Active Directory PAC authorization data and exposes it in the HTTP request context.

Client Side

• SPNEGO Client: Authenticate to SPNEGO Kerberos-protected web services.
• Password Management: Ability to change the client's password.

General Utilities

• Custom Integration: Kerberos libraries for building custom implementations.
• File Parsing: Support for parsing Keytab files, krb5.conf files, and client credentials cache files (e.g., /tmp/krb5cc_$(id -u $(whoami))).

Builtin profiles follow specific naming conventions to group privileges:

• ..._on_all_<type>__: References ALL <TYPE> IN SCHEMA ACLs (e.g., __all_on_tables__).
• __default_...__: References both global and schema-wide default privileges.
• __..._on_<type>__: A group that includes both __..._on_all_<type>__ and __default_..._on_<type>__.
• __all_on_...__: Equivalent to ALL PRIVILEGES in SQL, but implemented by granting each privilege individually.
• Aliasing: Some object-specific privileges omit the _on_<type> suffix. For example, __delete_on_tables__ is aliased to __delete__.

ldap2pg manages the parent-child relationships (membership) of roles. While the roles_blacklist_query is applied to parents, ldap2pg will still grant membership to 'unmanaged' parents.

This behavior allows you to manually create a group in PostgreSQL and then use ldap2pg to manage only the members of that group.

You can inject LDAP attribute values into various parameters using curly braces {}. ldap2pg expands these placeholders to the corresponding values for each entry found in the LDAP search.

• Single attributes: Use {attribute_name} (e.g., {cn}).
• Multiple attributes: If a parameter contains multiple attributes (e.g., {attr1}_{attr2}), ldap2pg expands to all possible combinations of those attributes for each entry.
• Pseudo attribute: The {dn} attribute is always available and represents the Distinguished Name of the original LDAP entry.

Example: Given an entry with cn: dimitri and company: external, the format {company}_{cn} results in external_dimitri.

# Example of attribute injection in YAML
- ldapsearch: ...
  role: "{company}_{cn}"

The rules section is the only mandatory parameter in the ldap2pg.yaml configuration file. It is a YAML list where each item (a mapping) defines how to manage PostgreSQL roles or grants. Each mapping can optionally include a description for logging and an ldapsearch section to fetch data from a directory.

To define roles without an LDAP directory, omit the ldapsearch section. To define roles based on LDAP entries, include ldapsearch and use curly braces {} for attribute injection in role or grant subsections.

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

If an LDAP attribute contains a Distinguished Name (DN), you can access specific components of that DN using dot notation: <attribute>.<rdn>.

Accessing RDNs

Use the format {attribute.rdn}. Note that if an RDN has multiple values, only the first value is returned.

Supported RDNs: cn, l, st, o, ou, c, street, dc, and uid.

Example: If member is cn=toto,cn=Users,dc=bridoulou,dc=fr:

• {member.cn} returns toto.
• {member.dc} returns ldap (the first component).

Triggering Sub-searches

If you use an RDN that is not in the supported list above, ldap2pg will trigger a sub-search. The format {attribute.unknown_attr} uses the value of attribute as the LDAP search base, narrowed by the unknown_attr attribute.

You can manage roles without superuser privileges by using a PostgreSQL role with the CREATEROLE option.

Important Security Note:

• PostgreSQL < 15: CREATEROLE is roughly equivalent to a superuser because the user can grant themselves almost any privilege.
• PostgreSQL 16+: It is safe to run ldap2pg with an unprivileged CREATEROLE user.

Limitations of non-superuser mode:

• You cannot manage certain role options like SUPERUSER, BYPASSRLS, or REPLICATION.
• You cannot detect if a user has been granted these privileges spuriously.
• Ensure the creator role has sufficient permissions to revoke grants from managed users.