In the example below, we are setting up LDAP with Active Directory (AD), one of the more popular server implementations, using the BigSQL PostgreSQL 96 distribution. Available for Windows and Linux.

Download BigSQL Distribution

Linux:

python -c "$(curl -fsSL http://s3.amazonaws.com/pgcentral/install.py)" 

Windows:

@powershell -NoProfile -ExecutionPolicy unrestricted -Command "iex ((new-object net.webclient).DownloadString('http://s3.amazonaws.com/pgcentral/install.ps1'))"

Find the bigsql directory and install PostgreSQL. Windows users don’t prefix the pgc command with ./ as shown in the following commands:

cd bigsql
./pgc install pg96
./pgc start pg96

Set your environment variables:

cd <directory of installation>/pg96

Linux:

source pg96.env

Windows:

pg96-env.bat

Configure pg_hba.conf

From the PostgreSQL documentaton for pg_hba.conf:

Client authentication is controlled by the pg_hba.conf (HBA stands for host-based authentication.)

The general format of the pg_hba.conf file is a set of records, one per line. A record is made up of a number of fields which are separated by spaces and/or tabs. The fields in the each record specifies a connection type, a client IP address range (if relevant for the connection type), a database name, a user name, and the authentication method to be used for connections matching these parameters.

Modify the LDAP connection record

There are many authentication methods in postgres (md5, trust, peer, etc). In this exercise, you will modify pg_hba.conf to use LDAP as the authentication method.

Navigate to the pg_hba.conf

cd bigsql/data/pg96/

Use your favorite editor to modify the line:

TYPE DATABASE USER CIDR-ADDRESS METHOD
host all all 127.0.0.1/32 trust

When we finish our exercise, the line will look something like this:

TYPE DATABASE USER CIDR-ADDRESS METHOD
host all all 127.0.0.1/32 ldap ldapserver=192.0.2.0 ldapprefix=”” ldapsuffix=”@oscg.com”

Modifying the METHOD field

The METHOD field provides connection credentials for Active Directory. There are 4 parameters you will need to create:

  1. ldap (the authentication method)
  2. ldapserver
  3. ldapprefix
  4. ldapsuffix

ldapserver

You can enter either the Active Directory server name or the ip address. For example:

ldapserver=192.0.2.0 

or

ldapserver=oscg.com

‘ldapprefix’ and ‘ldapsuffix’

Active Directory uses the following structure for login:

  • “DOMAIN_NAME\USERNAME”
  • “USERNAME@DOMAIN_NAME”

The ‘ldapprefix’ and ‘ldapsuffix’ serve as the variables for storing the DOMAIN_NAME value.

So, if the domain name for our active directory instance is OSCG, then we can provide the domain and user login credentials in one of 2 ways:

  • ldapprefix=”OSCG\” (the domain name at login – Note: the backslash is required)
  • ldapsuffix=”@oscg.com” (the domain name of the Active Directory server – Note: the @ is required)

Then, when the user (e.g. Vice Admiral Kathryn Janeway) enters her username (e.g. “kjaneway”), it is prepended or appended to the domain name depending on which method you chose to store the DOMAIN_NAME value:

  • OSCG\kjaneway
  • kjaneway@oscg.com

Note: Use either ldapprefix or ldapsuffix, not both!

In this example, we have modified the ldappreffix parameter:

METHOD
ldap ldapserver=192.0.2.0 ldapprefix=”OSCG\” ldapsuffix=””

and in this example, we have modified the ldapsuffix parameter:

METHOD
ldap ldapserver=192.0.2.0 ldapprefix=”” ldapsuffix=”@oscg.com”

Note: The empty quotes are required for whichever method you are not using – ldapprefix or ldapsuffix

Now, replace the parameters we used in the examples with your own Active Directory credentials:

METHOD
ldap ldapserver=”IP ADDRESS OR DOMAIN NAME OF YOUR ACTIVE DIRECTORY SERVER” ldapprefix=”YOUR DOMAIN_NAME” ldapsuffix=””

or

METHOD
ldap ldapserver=”IP ADDRESS OR DOMAIN NAME OF YOUR ACTIVE DIRECTORY SERVER” ldapprefix=”” ldapsuffix=”@” + “YOUR ACTIVE DIRECTORY SERVER NAME”

There are many more modes and configuration options to choose from when setting up your LDAP authentication. To learn more go here.

Create role for the AD domain user

Login to postgres database as postgres user:

psql -U postgres     

Create role on postgres that exists in active directory:

CREATE ROLE "kjaneway" WITH LOGIN;

Exit psql

\q

Reload the postgres to load new pg_hba.conf

cd bigsql
./pgc reload pg96

Login to postgres with Active Directory/Domain User

psql -h 127.0.0.1 -d postgres -U "kjaneway" 

Now, you should be prompted to enter a password. Enter the password you use for the active directory domain:

Password for user kjaneway: 

And, voila! You should now see the following prompt giving you access to the postgres database using LDAP:

psql (9.6.3)
Type "help" for help.

postgres=>

Final test – disable and enable Active Directory

Login to your Active Directory server and disable the domain account you have been testing against:

Connecting to the postgres database with the domain user and password should now fail:

psql -h 127.0.0.1 -d postgres -U "kjaneway" 
Password for user ldaptest: 
psql: FATAL:  LDAP authentication failed for user "kjaneway"

Now, enable the ‘holly.orr’ account in Active Directory:

Connecting to the postgres database with the domain user and password should now suceed:

psql -h 127.0.0.1 -d postgres -U "kjaneway" 
Password for user kjaneway: 
psql (9.6.3)
Type "help" for help.

openscg=# 

The What and Why of LDAP: A Little Context to Help You Understand

Remember phone books? Those big heavy directories, ordered alphabetically, that you would heave onto your kitchen table and thumb through to find a friend’s phone number and address?

With the advent of the internet and email, a new virtual phonebook was in order. One that allowed you look up an address for someone who’s never sent you email or enabled your organization to keep one centralized up-to-date “phone book” that permissable users had access to.

The solution came in the 1980’s. After 70 years of honing the art of managing phone books, the Telecommunications companies answered the call (get it) by applying the concept of directory services to information technology and computer networking. This resulted in the comprehensive X.500 specification.

In the 1990’s, geeks from the University of Michigan (of course) created a alternative protocal, LDAP, Lightweight Directory Access Protocol. As the name suggests, LDAP was lightweight compared to it’s predeccesor x.500 and quickly became widely adopted

Today, LDAP is a staple of enterprise IT infrastructures for authenticating and storing data about users. It is not limited to contact information and is used to look up encryption certificates, services on a network, and provide “single sign-on” where one password for a user is shared between many services.

Popular commercial LDAP server implementations include:

  • Microsoft Active Directory
  • UnboundID Directory Server
  • Oracle Internet Directory
  • Oracle Unified Directory
  • IBM Security Directory Server
  • NetIQ eDirectory
  • CA Directory

Popular open source LDAP server implementations include:

  • OpenLDAP
  • ForgeRock OpenDJ
  • Apache DS
  • 389 Directory Server

And lately, there have been Cloud LDAP solutions like JumpCloud or OneLogin. But this may come at a risk.

What are you using? Have you tried out Cloud solutions? Hit reply and tell us how you use LDAP with PostgreSQL.