Pivotal Greenplum (GPDB)
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.
To get started, find the hostname for your Domain Controller (DC). You can usually get this from the environment variables in a command prompt.
\\MYDC echo %USERDNSDOMAIN%
LAB.LOCAL echo %USERDOMAIN%
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.
- Simple LDAP, no encryption.
host all all 0.0.0.0/0 ldap ldapserver=mydc.lab.local ldapprefix="CORP\" ldapsuffix=""
- Secure LDAP using TLS
host all all 0.0.0.0/0 ldap ldapport="389" ldaptls=1 ldapserver=mydc.lab.local ldapprefix="CORP\" ldapsuffix=""
- 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.
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
You will now need to add database users and set the required permissions for each AD login that needs access to the database.