I recently got a few support cases from customers seeking to connect Postgres with LDAP (usually with some form of SSL/TLS encryption, to ensure security). I spent a bit of time trying to create a consistently reproducible environment where LDAP could be used to authenticate PostgreSQL connections, and wanted to write it down somewhere. The trickiest part was to get LDAP + encryption working, and I think I’ve got a somwhat-reliable way to stand up an environment for testing.
There are a couple of ways that LDAP implements SSL/TLS encryption, which we won’t get into here, but because Postgres doesn’t support LDAPS as of v. 10 (but it seems like it will be supported in v. 11), we will focus on LDAP + StartTLS.
Setting up LDAP
Setting up LDAP seems intimidating, as there’s a whole suite of commands and options to explore. I mean, there are jobs dedicated to this sector of IT Management, not to mention the plethora of different architectures (Active Directory, Kerberos, GSSAPI, PAM, etc.) Thankfully, Osixia has made it easy by providing a docker container. Now, it’s a simple as
Out of the box, LDAP works. All you need to do is create an LDAP user, create a counterpart in Postgres with
CREATE ROLE, and configure
HUP the server, sign in with
psql and all is good:
Setting up LDAP + StartTLS
It takes a little extra work to make the Docker container behave in a way that Postgres can talk to it with
StartTLS. The first step is create your own Certificate Authority, then an SSL certificate and sign it. Working with SSL/TLS is also intimidating (with all the ciphers, acronyms, versions, and such), and I won’t go into that here, but I was surprised to find that it wasn’t terribly hard to get the 3 things that I needed. After that, you need to create your LDAP Docker container by including the
--env LDAP_TLS_VERIFY_CLIENT=try flag in the
docker run statement, as mentioned in Issue #105. Finally, you’ll need to copy your CA cert, SSL cert, and SSL key into
/container/service/slapd/assets/. Once those are all in place (you may need to do a
docker restart ldap-service), verify that
LDAP + StartTLS is working properly by doing a simple
ldapsearch from the client side (i.e., wherever you’re running Postgres):
HUP the server, and you should be able to log in with
LDAP + StartTLS authentication:
You can verify that Postgres is indeed using
StartTLS by inspecting the LDAP server’s logs:
How to Keep LDAP and PostgreSQL in Sync
Now that your connection is set up, you want to make sure that users and roles in LDAP and Postgres match up. This does not happen between LDAP and Postgres by default, but luckily there is a program available, pg_ldap_sync, that allows Postgres to synchronize memberships and roles from LDAP. To install pg_ldap_sync, you must first install Ruby—this can be done with a Windows installer, or on Debian/Ubuntu with the command apt-get install ruby libpq-dev.Once you have Ruby installed, you can now install the program and its dependencies:
gem install pg-ldap-sync git clone https://github.com/larskanis/pg-ldap-sync.git cd pg-ldap-sync gem install bundler bundle install bundle exec rake install
Next, create a config.yaml file (based on one of the sample files provided) to provide search parameters for groups and users in LDAP. Next, test the config file:
pg_ldap_sync -c config.yaml -vv -t Now run the file in modify mode: pg_ldap_sync -c my_config.yaml -vv
When running, pg_ldap_sync issues CREATE ROLE, DROP ROLE, GRANT, and REVOKE commands in Postgres to keep users and roles from LDAP in sync. It can also be secured by SSL/TLS.
Getting PostgreSQL working with LDAP and with SSL/TLS can be intimidating, but it doesn’t have to be. With a bit of poking around on Google, and finding the right resources, what seemed to be a herculian task actually became quite doable. One important lesson I learned through these support cases, and in setting up this environment, was that it’s very important to verify from the client side with
ldapwhoami with the
-Z flag to make sure LDAP with encryption was properly set up. Some people tested only on the LDAP/server side, not on the Postgres side, and lost many hours trying to wrangle with
pg_hba.conf and ultimately blaming Postgres for being buggy in its implementation of LDAP authentication, when in reality it was LDAP that was misconfigured.
Richard is a Senior Support Engineer at EnterpriseDB and supports the entire suite of EnterpriseDB's products. Prior to joining EnterpriseDB, Richard worked as a database engineer and web developer, functioning primarily in operations with a focus on scalability, performance, and recoverability. He has a broad range of knowledge in a number of technologies, and most recently has been involved in developing tools for rapid-deployment of EDB Postgres Advanced Server in Docker containers. Richard is an EnterpriseDB Certified PostgreSQL Professional.