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
python -c "$(curl -fsSL http://s3.amazonaws.com/pgcentral/install.py)"
@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
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
Use your favorite editor to modify the line:
When we finish our exercise, the line will look something like this:
|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:
- ldap (the authentication method)
You can enter either the Active Directory server name or the ip address. For example:
‘ldapprefix’ and ‘ldapsuffix’
Active Directory uses the following structure for login:
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:
Note: Use either ldapprefix or ldapsuffix, not both!
In this example, we have modified the ldappreffix parameter:
|ldap ldapserver=192.0.2.0 ldapprefix=”OSCG\” ldapsuffix=””|
and in this example, we have modified the ldapsuffix parameter:
|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:
|ldap ldapserver=”IP ADDRESS OR DOMAIN NAME OF YOUR ACTIVE DIRECTORY SERVER” ldapprefix=”YOUR DOMAIN_NAME” ldapsuffix=””|
|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;
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:
- 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.