Pivotal Knowledge Base

Follow

LDAP authentication for GPDB and Hawq

Environment

Product Version

Pivotal Greenplum (GPDB)

Pivotal Hawq

4.3.x,

Hawq 1.3.0.1

OS RHEL 6.x
Others  

Purpose

Greenplum Database supports LDAP, LDAP + TLS and LDAP/SSL authentication methods. This is an example of how to authenticate database users to Microsoft Active Directory.

Procedure

To get started, find the hostname for your Domain Controller (DC). You can usually get this from the environment variables in a command prompt.

echo %LOGONSERVER%
\\MYDC echo %USERDNSDOMAIN%
LAB.LOCAL echo %USERDOMAIN%
CORP

From these codes, you should be able to make the fully qualified name of a domain controller. In this example, it would be "mydc.lab.local". If you are not on the same network as your database, you may need to ask for the name of a DC that is 'closer' to the database.

You can test LDAP connectivity from the Greenplum master server using the ldapsearch utility from the openldap-clients package. You will also need Active Directory login credentials.

The following will connect as the user CORP\mylogin and will prompt for it's password. It will display the Common Name and the SAM Account Name commonly used to login to AD.

ldapsearch -x -h mydc.lab.local -D 'CORP\mylogin' -W -b 'DC=CORP,DC=LAB,DC=LOCAL' 'samAccountName=mylogin' cn sAMAccountName

For secure connections to the DC, you will need to install the domains public certificate. You can retrieve this a member pc/server in the AD domain.  Using the mmc console, add the Certificates snap-in to manage "My user account".  

Then under Trusted Root Certificate Authorities, locate your domains root certificate. Copy this certificate to disk as Base-64 encode x.509 and make note where you saved it.  You will need to copy this file onto the Greenplum Master server.

 

On your Greenplum master, you should have OpenLDAP installed, and in /etc/openldap you should create a directory cacerts if there is not one there already. Copy the domains public certificate to /etc/openldap/cacerts.

In /etc/openldap/ldap.conf, you need

TLS_CACERTDIR /etc/openldap/cacerts
TLS_REQCERT allow

You can then test the secure connection to port 389 + TLS

ldapsearch -x -h mydc.lab.local -p 389 -Z -D 'CORP\mylogin' -W -b 'DC=CORP,DC=LAB,DC=LOCAL' 'samAccountName=mylogin' cn sAMAccountName

and also PORT 636 with SSL

ldapsearch -H 'ldaps://mydc.lab.local' -D 'corp\oconnb27' -W -b 'DC=CORP,DC=LAB,DC=COM''samAccountName=mylogin' cn sAMAccountName 

Configure pg_hba.conf to allow ldap authentication.

  1. Simple LDAP, no encryption.
    host all all 0.0.0.0/0 ldap ldapserver=mydc.lab.local ldapprefix="CORP\" ldapsuffix=""
  2. Secure LDAP using TLS
    host all all 0.0.0.0/0 ldap ldapport="389" ldaptls=1 ldapserver=mydc.lab.local ldapprefix="CORP\" ldapsuffix=""
  3. Secure LDAP with SSL
    host all all 0.0.0.0/0 ldap ldapserver="ldaps://mydc.lab.local" ldapprefix="CORP\" ldapsuffix=""

For the secure ldap methods, configure gpadmin's environment variable LDAPCONF.

export LDAPCONF=/etc/openldap/ldap.conf 

Setting this for the first time will require a restart of the database as it is needed to be set before the database is started. Further changes to pg_hba.conf do not require this.

To stop and restart the database run the following:

gpstop -M fast
gpstart 

To only have the database read the pg_hba.conf

gpstop -u

You will now need to add database users and set the required permissions for each AD login that needs access to the database.

Additional Information

Comments

  • Avatar
    Kyle Dunn

    Great article - gave resolution to a multi-day battle with secure LDAP.

    One thing to note: the ```TLS_REQCERT allow``` option can be omitted if the root CA cert is installed in ```TLS_CACERTDIR``` (and ldap.conf is configured accordingly)
    and that cert is trusted by running this command: ```openssl x509 -hash -noout -in caCertFileInBase64.pem ; ln -s caCertFileInBase64.pem caCertFileInBase64.pem.0 ``` in the ```TLS_CACERTDIR``` directory as root

  • Avatar
    Brian OConnell

    An update on the openldap configuration:

    1) The CA cert in /etc/openldap/cacerts need to be readable by gpadmin

    chmod 444 /etc/openldap/cacerts/myCA.cer

    2) If you only specify TLS_CACERTDIR in ldap.conf, you can only have one CA cert in /etc/openldap/cacerts. The presence of any other file will cause the ldap authentication in psql to fail. The solution is to specify the specific CA cert.

    LDAPTLS_CACERT /etc/openldap/cacerts/myCA.cer

    you can specify it in ldap.conf, or you can also set it as an environment variable.

Powered by Zendesk