LDAP authentication in ADB

Daria Barysheva

ADB (as well as Greengage DB) supports Lightweight Directory Access Protocol (LDAP) user authentication. When LDAP authentication is used, user accounts and passwords are checked on the selected LDAP server. The steps for configuring LDAP authentication in ADB via ADCM are described below.

NOTE
You can configure LDAP authentication parameters via ADCM in ADB 6 starting with ADB 6.25.1.49. In this version, automatic synchronization of ADB users and groups with LDAP has also been implemented — via the pg-ldap-sync utility, which comes with the ADB bundle. In previous versions of ADB, LDAP authentication was possible, but you needed to manually perform the steps described below in the Synchronize ADB and LDAP section (the pg_hba.conf file upgrade and creation of roles in ADB).

Configure LDAP authentication

To configure LDAP authentication in ADB, follow the steps:

  1. Ensure the target LDAP server is configured and available. ADB supports two LDAP implementations: Microsoft Active Directory (MS AD) and 389 Directory Server as a part of FreeIPA.

  2. Open the configuration parameters of the ADB service in ADCM.

  3. Switch on the UI LDAP authentication toggle.

  4. Fill in the following fields.

    Field Description Default value Example in MS AD

    Type

    A type of the LDAP server. Possible values:

    • MSAD — Microsoft Active Directory

    • FreeIPA — 389 Directory server as a part of FreeIPA

    MSAD

    MSAD

    URI

    A URI for connecting to the LDAP server in the following format: <host>:<port>

     — 

    10.92.12.49:389

    LDAP encryption

    Encryption method to be used for LDAP connections:

    • ldaps — use LDAP over SSL/TLS (LDAPS)

    • starttls — use the Start Transport Layer Security (StartTLS) operation

    • none — do not use encrypted LDAP

    none

     — 

    Users baseDN

    A limitation of the search scope of objects in the LDAP directory that is used in user search requests

     — 

    ou=People,dc=ad,dc=ranger-test

    Groups baseDN

    A limitation of the search scope of objects in the LDAP directory that is used in group search requests

     — 

    ou=Groups,dc=ad,dc=ranger-test

    Users filter

    An LDAP filter that is used to search users. Should be specified according to the RFC 2254

    (&(|(objectClass=person)(objectClass=inetOrgPerson))(cn=*))

    (&(objectClass=person)(objectClass=organizationalPerson)(givenName=*)(sn=*)(sAMAccountName=*))

    Groups filter

    An LDAP filter that is used to search groups. Should be specified according to the RFC 2254

    (&(|(objectClass=group)(objectClass=groupofnames)(objectClass=groupOfUniqueNames))(cn=*))

    (cn=*)

    SA Login

    A user name that is used for service requests to the LDAP server

     — 

    cn=admin,dc=ad,dc=ranger-test

    Password

    A user password that is used for service requests to the LDAP server

     — 

    A password of the admin user

    Lowercase login

    Whether or not to convert user names to lowercase

    false

    false

    Lowercase group

    Whether or not to convert group names to lowercase

    false

    false

    Sync interval

    A cron expression that defines how often synchronization of ADB users and groups with an LDAP server should be performed. The pg-ldap-sync utility, which comes with the ADB bundle, is used for synchronization

    0 * * * *

    0 * * * *
    NOTE

    Various LDAP implementations use different names for object types and identifiers. It is recommended to refine the settings format for a specific LDAP implementation from the LDAP server administrator.

  5. Click Save. Then apply the Reconfigure & Restart action to the ADB service.

    Configure LDAP authentication for ADB via ADCM
    Configure LDAP authentication for ADB via ADCM

Synchronize ADB and LDAP

If LDAP authentication is configured successfully, the following operations are performed in ADB automatically:

  • Upgrade of the pg_hba.conf file. The following entry about LDAP authentication is added to the pg_hba.conf file, which is located in the data directory of the master host (/data1/master/gpseg-1/ in the following example):

    # BEGIN LDAP
host all +ldap_users 0.0.0.0/0 ldap ldapserver=<URI> ldapbasedn="<Users baseDN>" ldapbinddn="<SA Login>" ldapbindpasswd="<Password>" ldapsearchattribute="<attribute>"
# END LDAP

    where:

    • ldap_users — a name of the ADB role, to which all LDAP users will be added.

    • <URI>, <Users baseDN>, <SA Login>, <Password> — values of the eponymous parameters of the ADB service.

    • <attribute> — the LDAP attribute; this value will be used as a name of each role in ADB when synchronizing users. Possible values:

      • sAMAccountName — for MS AD.

      • uid — for FreeIPA.

  • Synchronization of ADB users and groups with LDAP. Information about LDAP users and groups, which meet the selection criteria specified in the configuration step, is added to the ADB database. Synchronization is performed via the pg-ldap-sync utility. To ensure successful synchronization, you can run the following query against the pg_roles table:

    SELECT rolname FROM pg_roles;

    As the SQL command output shows, the following roles are automatically created in ADB after successful LDAP synchronization:

    • ldap_users — a role into which all LDAP users are added (by running the CREATE ROLE …​ LOGIN IN ROLE ldap_users …​ command automatically).

    • ldap_groups — a role into which all LDAP groups are added (by running the CREATE ROLE …​ NOLOGIN IN ROLE ldap_groups …​ command automatically).

    • ffedorov, ppetrov, ssemenov, zeptest — all users received from the LDAP server and added to the ldap_users role.

    • ADCM, ADH, Adccadmins, Analysts, Managers — all groups received from the LDAP server and added to the ldap_groups role. The membership of users in these groups is set automatically by running GRANT.

       rolname
-------------
 gpadmin
 gpmon
 ldap_users
 ldap_groups
 ADCM
 ADH
 Adccadmins
 Analysts
 Managers
 ffedorov
 ppetrov
 ssemenov
 zeptest
(13 rows)
Synchronization details

  • After initial configuration, ADB users are automatically synchronized with an LDAP server according to the schedule specified in the Sync interval parameter. There is no need to run the CREATE ROLE and GRANT commands manually.

  • To synchronize ADB users with an LDAP server on demand (without waiting for the next auto launch), apply the Reconfigure & Restart action to the ADB service.

  • After LDAP authentication is configured, only users registered on the LDAP server can connect to the ADB database. If you delete users on the LDAP side, they cannot further log in to ADB.

  • If a user exists in ADB but not on the LDAP server side, synchronization does not affect this user. However, if a user with the same name exists both in ADB and on the LDAP server, the synchronization behavior depends on whether the ADB user belongs to the LDAP group:

    • If the user belongs to the ldap_users group, on the next synchronization the user data in ADB will be overwritten with the information received from LDAP.

    • If the user does not belong to the ldap_users group, the name conflict will cause synchronization to fail with the role "<role_name>" already exists error. In this case, delete or rename one of the users to resolve the conflict.

  • The pg-ldap-sync utility automates only the following DCL commands: CREATE ROLE <ROLE>, DROP ROLE <ROLE>, GRANT <ROLE> TO <ROLE>, and REVOKE <ROLE> FROM <ROLE>. The commands that define access privileges (e.g. GRANT SELECT, GRANT EXECUTE, GRANT CONNECT, GRANT PRIVILEGES ON <OBJECT> TO <ROLE>) should be run manually in ADB if necessary.

Use LDAP over SSL

There are two methods to establish secure connections between ADB and LDAP servers:

  • LDAPS (LDAP over SSL/TLS) works by establishing a secure TLS tunnel before any LDAP traffic flows. It typically uses a dedicated port (636) to which clients connect directly.

  • With StartTLS, a connection starts as a plaintext LDAP connection, and then the client sends a special StartTLS extended operation to request TLS encryption. It typically uses a regular LDAP port — 389.

To use ADB with LDAP over SSL, configure your LDAP server and select one of these two methods in the LDAP encryption configuration parameter of the ADB service.

In the examples below, val-pam.ru-central1.internal is used as the LDAP server hostname. Make sure to change it to your actual LDAP server hostname when running the commands.

Configure LDAP server to use SSL

The following steps provide an example of setting up SSL encryption for OpenLDAP on Ubuntu 22.04. If your LDAP server already has SSL configured, you can skip the first section — make sure to add the CA certificate to the ADB master host and then enable LDAPS or StartTLS in ADB.

  1. Create a directory for the certificates and navigate into it:

    $ sudo mkdir -p /etc/ldap/ssl && cd /etc/ldap/ssl

  2. Create the self‑signed CA certificate:

    $ sudo openssl req -new -x509 -nodes -out ca.crt -keyout ca.key -days 3650 -subj "/CN=My Certificate Authority"

  3. Generate a server private key and a Certificate Signing Request (CSR). In the Common Name (CN) field, provide the LDAP server address that is accessible from the ADB master host.

    $ sudo openssl req -new -nodes \
  -out server.csr \
  -keyout server.key \
  -subj "/CN=val-pam.ru-central1.internal" \
  -addext "subjectAltName = DNS:val-pam.ru-central1.internal"

  4. Sign the server CSR with the CA to obtain the server certificate:

    $ sudo openssl x509 -req -in server.csr -CA ca.crt -CAkey ca.key -CAcreateserial -out server.crt -days 365

  5. Set permissions for the generated files:

    $ sudo chown -R openldap:openldap /etc/ldap/ssl/
$ sudo chmod 600 /etc/ldap/ssl/server.key
$ sudo chmod 644 /etc/ldap/ssl/server.crt /etc/ldap/ssl/ca.crt

  6. Configure the slapd daemon to use the generated certificates. To do so, create a tls.ldif file with the following content:

    dn: cn=config
changetype: modify
replace: olcTLSCACertificateFile
olcTLSCACertificateFile: /etc/ldap/ssl/ca.crt
-
replace: olcTLSCertificateFile
olcTLSCertificateFile: /etc/ldap/ssl/server.crt
-
replace: olcTLSCertificateKeyFile
olcTLSCertificateKeyFile: /etc/ldap/ssl/server.key

  7. Apply the created configuration:

    $ sudo ldapmodify -Y EXTERNAL -H ldapi:/// -f tls.ldif

    The output should be as follows:

    SASL/EXTERNAL authentication started
SASL username: gidNumber=0+uidNumber=0,cn=peercred,cn=external,cn=auth
SASL SSF: 0
modifying entry "cn=config"

  8. Open the /etc/default/slapd file and add ldaps:/// to the SLAPD_SERVICES parameter, so that the resulting line looks as follows:

    SLAPD_SERVICES="ldap:/// ldaps:/// ldapi:///"

    This setting makes the LDAP server listen on ports 636 (LDAPS) and 389 (unencrypted LDAP). Unencrypted LDAP can be used for StartTLS, which upgrades the connection to TLS after the initial handshake.

  9. Restart slapd:

    $ sudo systemctl restart slapd

  10. Check that the LDAPS server is listening on port 636:

    $ sudo lsof -i :636

    The output should be similar to:

    COMMAND    PID     USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
slapd   114824 openldap   10u  IPv4 184207      0t0  TCP *:ldaps (LISTEN)
slapd   114824 openldap   11u  IPv6 184208      0t0  TCP *:ldaps (LISTEN)

Add the certificate to the master host

For ADB to validate the LDAP server identity, the CA certificate must be present on the master host. If it’s a self-signed certificate (as in the example above), you must also add it to your operating system trust store.

  1. On the master host, create a directory for the CA certificate file:

    $ sudo mkdir -p /etc/ldap/ssl

  2. Copy the CA certificate file from the LDAP server to that directory on the master host:

    $ scp user@val-pam.ru-central1.internal:/etc/ldap/ssl/ca.crt /tmp/ca.crt
$ sudo cp /tmp/ca.crt /etc/ldap/ssl/

  3. Set the permissions for the CA certificate file as follows:

    $ sudo chmod 644 /etc/ldap/ssl/ca.crt

  4. Install the CA certificate in the trust store:

    $ sudo cp /etc/ldap/ssl/ca.crt /usr/local/share/ca-certificates
$ sudo update-ca-certificates

Check secure connections

Before making changes in ADB, you can optionally check that encrypted LDAP connections can be established from the ADB master host to the configured LDAP server.

To check LDAPS, on the ADB master host, run:

$ ldapsearch -H ldaps://val-pam.ru-central1.internal -D cn=admin,dc=ru-central1,dc=internal -W -b "dc=ru-central1,dc=internal" -s base

To check using StartTLS, on the ADB master host, run:

$ ldapsearch -H ldap://val-pam.ru-central1.internal -ZZ -D cn=admin,dc=ru-central1,dc=internal -W -b "dc=ru-central1,dc=internal" -s base

In both cases, the output should contain the Success result.

Example of a successful response
# extended LDIF
#
# LDAPv3
# base <dc=ru-central1,dc=internal> with scope baseObject
# filter: (objectclass=*)
# requesting: ALL
#

# ru-central1.internal
dn: dc=ru-central1,dc=internal
objectClass: top
objectClass: dcObject
objectClass: organization
o: ru-central1.internal
dc: ru-central1

# search result
search: 2
result: 0 Success

# numResponses: 2
# numEntries: 1

Enable LDAPS in ADB

  1. Open the configuration parameters of the ADB service in ADCM.

  2. Switch on the UI LDAP authentication toggle and configure the following LDAP parameters:

    • URI — enter the LDAP server address in the <host>:<port> format. For LDAPS, the port is usually 636. For example, val-pam.ru-central1.internal:636.

    • LDAP encryption — select ldaps.

  3. All other LDAP parameters can be configured as shown for regular LDAP.

  4. Click Save and then run the Reconfigure & Restart action of the ADB service to apply the changes.

Enable LDAPS in ADB
Enable LDAPS in ADB

If the Reconfigure & Restart action completes successfully, the pg_hba.conf file gets a new rule for members of the ldap_users group. This rule includes the LDAP server address with the ldaps scheme, which enables LDAPS. The resulting LDAP configuration section in the pg_hba.conf file should look as follows:

# BEGIN LDAP
host all +ldap_users 0.0.0.0/0 ldap ldapserver=ldaps://val-pam.ru-central1.internal:636 ldapbasedn="ou=People,dc=ru-central1,dc=internal" ldapbinddn="cn=admin,dc=ru-central1,dc=internal" ldapbindpasswd="12345" ldapsearchattribute="uid"
# END LDAP

You can now check LDAP authentication — for users from the ldap_users group, ADB will connect to the LDAP server using LDAPS.

Enable StartTLS in ADB

  1. Open the configuration parameters of the ADB service in ADCM.

  2. Switch on the UI LDAP authentication toggle and configure the LDAP parameters:

    • URI — enter the LDAP server address in the host:port format. A regular LDAP port, 389, is usually used for StartTLS. For example, val-pam.ru-central1.internal:389.

    • LDAP encryption — select starttls.

  3. All other LDAP parameters can be configured as shown for regular LDAP.

  4. Click Save and then run the Reconfigure & Restart action of the ADB service to apply the changes.

Enable StartTLS in ADB
Enable StartTLS in ADB

If the Reconfigure & Restart action completes successfully, the pg_hba.conf file gets a new rule for members of the ldap_users group. This rule includes the ldaptls=1 option, which enables TLS encryption via the StartTLS operation. The resulting LDAP configuration section in the pg_hba.conf file should look as follows:

# BEGIN LDAP
host all +ldap_users 0.0.0.0/0 ldap ldapserver=val-pam.ru-central1.internal:389 ldaptls=1 ldapbasedn="ou=People,dc=ru-central1,dc=internal" ldapbinddn="cn=admin,dc=ru-central1,dc=internal" ldapbindpasswd="12345" ldapsearchattribute="uid"
# END LDAP

You can now check LDAP authentication — for users from the ldap_users group, ADB will connect to the LDAP server using StartTLS.

Check LDAP authentication

To check that LDAP authentication is properly configured in ADB, you can run psql to connect to ADB using the name of any LDAP user as the -U argument (ffedorov in the following example).

$ psql adb -h <Master host name or IP address> -U "ffedorov"

If LDAP authentication is configured successfully, you will be prompted to enter the user password. After entering the correct password (assigned on the LDAP server side), the user can access the database.

Password for user ffedorov:
psql (9.4.26)
Type "help" for help.

adb=>
LDAP authentication errors

  • If a user with the specified name cannot be found among members of the ldap_users role in ADB, the following error occurs when you try to log in to ADB under that user:

    psql: FATAL:  no pg_hba.conf entry for host "10.92.42.236", user "bds", database "adb", SSL off

  • If a user with the specified name is a member of the ldap_users role in ADB, but the user name or password cannot be found in LDAP, the following error occurs when you try to log in to ADB under that user:

    psql: FATAL:  LDAP authentication failed for user "bds"

In both cases, you should wait for automatic synchronization with an LDAP server or run it manually using the Reconfigure & Restart action of the ADB service.

Found a mistake? Seleсt text and press Ctrl+Enter to report it