LDAP authentication in ADB

Daria Barysheva

ADB (as well as Greenplum) supports LDAP (Lightweight Directory Access Protocol) 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 been also 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 ADB service configuration page 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

    Users baseDN

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

     — 

    ou=Peoples,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 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, which 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 the 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 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 login 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 next time synchronization runs, the user data in ADB will be overwritten with the information received from LDAP.

  • The pg-ldap-sync extension 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.

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 bds-mdw -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 login 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 login 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 action of the ADB service.

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