Configuring EDB LDAP Sync

EDB LDAP Sync is a collection of tools that synchronize credentials from an LDAP or Active Directory server to EDB Postgres Advanced Server. We recommend that you configure its tools in the following ways.

Find the sample EDB LDAP Sync configuration files at /usr/lib/python3.6/site-packages/ldap_sync/samples/.

Setting Postgres database permissions

You need to modify the permission settings for the database server to allow Postgres to query the LDAP host during Postgres authentication. You can do this by updating pg_hba.conf in the configuration folder of the database server. For EDB Postgres Advanced Server configuration, pg_hba.conf is located in /opt/postgres/data/pg_hba.conf.

pg_hba.conf settings

When configuring pg_hba.conf, the LDAP authentication string must come after the enterprisedb user authentication string. Otherwise, due to the order of evaluation, the enterprisedb user can't log into the system. For example, LDAP server is queried instead of local authentication, LDAP server returns an authentication failure, and the authentication process terminates. For this reason, you need to add host authentication for the enterprisedb user if you want to use host authentication as a part of the ldap2pg setup.

The following authentication methods are available: peer, host with a password string by way of dsn option, and host by way of reading .pgpass. See Configure ldap2pg for more information.

The Postgres database permissions are set according to your database requirements. The following are sample pg_hba.conf stanzas for LDAP authentication queries, where the values in {} are the LDAP server hostname and the basedn LDAP query string:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             enterprisedb                            peer
host    all             enterprisedb    127.0.0.1/32            scram-sha-256
host    all             enterprisedb    ::1/128                 scram-sha-256
host    all             all             0.0.0.0/0               ldap ldapserver="{{ldap host/ip}}" ldapbasedn="{{basedn}}" ldapsearchattribute=uid  ldaptls=0  ldapport=389
# Other authentication strings
.
.
.
# End of the file / add ldap SSL connection here
hostssl all             all             0.0.0.0/0 ldap ldapserver="{{ldap host/ip}}" ldapbasedn="dc={{basedn}}" ldapsearchattribute=uid  ldaptls=0  ldapport=389

The following is a sample pg_hba.conf file for EDB Postgres Advanced Server without any modifications:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             enterprisedb                            peer
local   replication     all                                     peer
local   all             all                                     peer
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256
# node1
hostssl all all 10.10.0.5/32 scram-sha-256
hostssl replication all 10.10.0.5/32 scram-sha-256
# ldap

hostssl all all 0.0.0.0/0 scram-sha-256

The following is a sample pg_hba.conf with the initial configuration and LDAP authentication:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             enterprisedb                            peer
host    all             enterprisedb    127.0.0.1/32            scram-sha-256
host    all             enterprisedb    ::1/128                 scram-sha-256
host    all             all             0.0.0.0/0               ldap ldapserver="ldap" ldapbasedn="dc=planetexpress,dc=com" ldapsearchattribute=uid  ldaptls=0  ldapport=10389
local   replication     all                                     peer
local   all             all                                     peer
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256
# node1
hostssl all all 172.17.0.2/32 scram-sha-256
hostssl replication all 172.17.0.2/32 scram-sha-256
# ldap
hostssl all all 0.0.0.0/0 ldap ldapserver="ldap" ldapbasedn="dc=planetexpress,dc=com" ldapsearchattribute=uid  ldaptls=0  ldapport=10389

After setting pg_hab.conf, reload the configuration to apply the changes with the Postgres shell or the systemctl:

SELECT pg_reload_conf();

systemctl restart postgres

Configure ldap2pg

ldap2pg is a PostgreSQL role and access control list (ACL) management tool. Find the ldap2pg.yml in its default location in the /etc/ folder. For more information on ldap2pg, see the ldpa2pg documentation.

Install the ldap2pg tool with the following command:

yum install python3-ldap2pg

For synchronization to work, you need to set up the following sections in the configuration file:

• ldap

• postgres

• sync_map

Verify LDAP server connection

Before configuring ldap2pg, check that the connection string works. For LDAP servers, you can check if the database server can communicate with the LDAP server by installing the openldap-clients package:

$ yum install -y openldap-clients

Test the connection from the database server to the LDAP server with the ldapsearch command:

$   ldapsearch -H ldap://ldap_server:389 -x -b "<ldap query string>" \
        -D "<admin query string>" -w <admin password> \
        uid=alphanum_user_id

A sample search string looks like this:

$ ldapsearch -H ldap://ldap:10389 -x -b "ou=people,dc=planetexpress,dc=com" \
        -D "cn=admin,dc=planetexpress,dc=com" -w GoodNewsEveryone \
        uid=professor

A successful request returns an LDAP object definition as a plain-text response like the following:

# extended LDIF
#
# LDAPv3
# base <ou=people,dc=planetexpress,dc=com> with scope subtree
# filter: uid=professor
# requesting: ALL
#

# Hubert J. Farnsworth, people, planetexpress.com
dn: cn=Hubert J. Farnsworth,ou=people,dc=planetexpress,dc=com
objectClass: inetOrgPerson
objectClass: organizationalPerson
objectClass: person
objectClass: top
cn: Hubert J. Farnsworth

ldap

The LDAP configuration contains the server port and url. Depending on the type of server and its configuration, you might also need to provide credentials.

OpenLDAP configuration

For servers that allow anonymous connections, the LDAP configuration requires a LDAP server url and port. For servers that need authentication, you must provide password and binddn queries. To do this, binddn must have permission to perform user lookups.

The default port for LDAP is 389:

ldap:
  uri: ldap://ldap:389

Active Directory configuration

The Active Directory setup requires a binddn, password, and uri with permission to read user information.

ldap:
  binddn: cn=ldap2pg,ou=postgresql,dc=edb,dc=com
  password: EDBad32
  uri: ldap://10.100.20.21:389

postgres

The Postgres configuration contains the PostgreSQL database settings, such as authentication and the specifics of schemas and queries, which populate user information.

You can perform database authentication with the dsn string, which supports any authentication methods available in libpq.

Peer authentication

To use peer authentication for EDB Postgres Advanced Server, you need to list only the database name under dbanme. Enter the following string:

dsn: dbname=postgres

To run the sync of ldap2pg as a default peer user for EDB Postgres Advanced Server, set the file permission for /etc/ldap2pg.yml to the local database user. For example:

$ chown enterprisedb:enterprisedb /etc/ldap2pg.yml

Pass a -v parameter to verify that the user sync process for ldap2pg on the database server runs:

$ ldap2pg -v

Using Pgpass

EDB Postgres Advanced Server creates the .pgpass authentication file in /var/lib/edb/ with the default user enterprisedb. If you plan to run synchronization jobs as the enterprisedb user, it's easier to use peer authentication.

This case examines the scenario where EDB Postgres Advanced Server authentication is performed with the enterprisedb user but the sync job for ldap2pg is run as a different user.

Copy .pgpass from the enterprisedb user home directory:

$ sudo cp /var/lib/edb/.pgpass ~/

Modify the dsn variable in the postgres section in /etc/ldap2pg.yaml:

dsn: host=localhost user=enterprisedb dbname=postgres

OpenLDAP configuration

This is a sample configuration for synchronizing the PostgreSQL user accounts with OpenLDAP.

dsn — A Postgres URI string that uses the username:password authentication method.

databases_query — Specifies the Postgres database that contains the user records.

schemas_query — Returns all schemas in pg_catalog to be available to ldap2pg.

roles_blacklist_query — Excludes tables from synchronization operations, in particular anything related to EDB Postgres Advanced Server or internal Postgres tables.

postgres:
  dsn: postgres://enterprisedb:LOCALPASS@localhost:5444/postgres
  databases_query: [postgres]
  schemas_query: "SELECT nspname FROM pg_catalog"
  roles_blacklist_query: [aq_administrator_role, pg_*]

Active Directory configuration

This is a sample configuration for synchronizing the PostgreSQL user accounts with Active Directory.

dsn — A Postgres URI string that uses the username:password authentication method. managed_roles_query — Query to restrict role deletion and privilege edition to specific roles. owners_query — Specifies a global list of owners common to all databases and schemas. schemas_query — Specifies owners for schemas so that ldap2pg can be aware of the schemas in each database.

postgres:
  dsn: postgres://enterprisedb:LOCALPASS@localhost:5444/postgres
  managed_roles_query: |
    SELECT 'public'
    UNION
    SELECT DISTINCT role.rolname
    FROM pg_roles AS role
    LEFT OUTER JOIN pg_auth_members AS ms ON ms.member = role.oid
    LEFT OUTER JOIN pg_roles AS ldap_roles
      ON ldap_roles.rolname = 'ldap_roles' AND ldap_roles.oid = ms.roleid
    WHERE role.rolname IN ('ldap_roles', 'readers', 'writers', 'owners')
        OR ldap_roles.oid IS NOT NULL
    ORDER BY 1;
  owners_query: |
    SELECT DISTINCT role.rolname
    FROM pg_catalog.pg_roles AS role
    JOIN pg_catalog.pg_auth_members AS ms ON ms.member = role.oid
    JOIN pg_catalog.pg_roles AS owners
      ON owners.rolname = 'owners' AND owners.oid = ms.roleid
    ORDER BY 1;
  schemas_query: |
    SELECT nspname FROM pg_catalog.pg_namespace
    WHERE nspname NOT LIKE 'pg_%' AND nspname <> 'information_schema'
    ORDER BY 1;

sync_map

Each sync_map item is called a mapping. A mapping is a YAML dictionary with a description field and any of ldapsearch, role, and grant subsection.

Roles

Define one or more roles that target the PostgreSQL cluster. Specify the roles under selected LDAP accounts that are imported and the permissions they use.

A value can be a single role or a list. This example is a sample definition of roles under sync_map:

roles:
  - names:
    - ldap_roles
    - readers
    options: NOLOGIN
  - name: writers
    # Grant reading to writers
    parent: readers
    options: NOLOGIN
  - name: owners
    # Grant read/write to owners
    parent: writers
    options: NOLOGIN

This example is a simple definition of a single role with LOGIN and SUPERUSER privileges applied to every user authenticated with LDAP:

role:
    name: '{uid}'
    options: LOGIN SUPERUSER

Grant

The grant section defines grant privilege to a role with the corresponding parameters. In other words, this section determines the privileges granted for a role defined in the roles section.

  grant:
  - privilege: ro
    role: readers
    schemas: __all__
  - privilege: rw
    role: writers
    schema: __all__
  - privilege: ddl
    role: owners
    schema: __all__

ldapsearch query and filters

The ldapsearch section specifies the search queries needed to identify the LDAP accounts that you can synchronize with PostgreSQL. For example:

 ldapsearch:
    base: ou=people,dc=planetexpress,dc=com
    binddn: ou=people,dc=planetexpress,dc=com
    filter: "(objectClass=inetOrgPerson)"

Role statement

The role section defines the user accounts and options that can be mapped in the PostgreSQL database. The default name parameter for each user record is cn, which is the account’s full name. The uid name is instead specified, which is a short username.

role:
    name: '{uid}'
    options: LOGIN SUPERUSER

Set a synchronization schedule

To ensure continuous synchronization between your Postgres database and LDAP, we're transitioning to using pg_cron for scheduling tasks instead of pgagent. pg_cron allows for scheduling directly within the Postgres environment, offering a streamlined and efficient approach to task management.

In what follows, we use an example of creating a SQL script to utilize pg_cron for running ldap2pg at defined intervals. These steps assume that you have Postgres with pg_cron installed and configured, the plpython3u procedural language is available in your Postgres instance, and that your Postgres user has sufficient privileges to create functions and schedule cron jobs.

Create the SQL Script

Create a new SQL file named setup_pg_cron.sql. This script will define a function to run ldap2pg and schedule it to execute at regular intervals using pg_cron.

CREATE OR REPLACE FUNCTION run_ldap_sync() RETURNS void LANGUAGE plpython3u as $$
import os
os.system("usr/bin/ldap2pg -N")
$$;

Within the same setup_pg_cron.sql file, add the following line to schedule the run_ldap_sync function. This example schedules the synchronization to run every minute, but you can adjust the cron schedule to fit your needs:

-- run ldap every minute
SELECT cron.schedule('run_ldap_sync_every_minute', '* * * * *', 'SELECT run_ldap_sync();');

Finally, run the setup_pg_cron.sql script through psql to apply the changes. Ensure you're executing this as a user with the necessary permissions.

psql postgres < setup_pg_cron.sql