PostgreSQL 11 with Active Directory over SSL on CentOS 7

     

Preamble

First of all, I’d like to thank the crew of #postgresql and #centos on freenode for all the help, much appreciated!

The scenario is the Foobar company with Active Directory as the directory service. They use CentOS 7 application servers, among them PostgreSQL. The company’s domain name is foobar.com, and the corresponding AD domain name is ad.foobar.com, as per best practices. Since sAMAccountName is not globally unique, users authenticate with their e-mail addresses instead, e.g. [email protected]. There are a couple domain controllers (DCs), such as dc1.ad.foobar.com and dc2.ad.foobar.com. DC2 also serves as a certificate authority, which is necessary for SSL support.

The user story is AD / LDAP based authentication for corporate users accessing the PostgreSQL instances (e.g. db.foobar.com). Remote authentication must go through SSL. Local users should log into the CentOS servers via AD/LDAP, too. Then they should be able to connect to the PostgreSQL instances without entering their passwords (a.k.a. passwordless login), since they already did that during OS sign in.

Installation

Certificate Authority

One of your DCs needs to have the Active Directory Certificate Authority role installed and a certificate issued. Explaining this is beyond the scope of this document, but there are many guides available online.

Once done, open MMC, add the Certificates snap-in, select Computer account, then Local computer. Open Trusted Root Certification Authorities / Certificates, find the latest certificate for the DC, right click, then All Tasks / Export. Choose Base-64 encoded X.509 (.CER), then save it as ad-ca.cer.

Domain Join

Make sure your CentOS 7 host is joined to the AD domain as explained in Integrating CentOS 7 with Active Directory.

PostgreSQL

Find the current PostgreSQL 11 CentOS 7 RPM in the PostgreSQL Yum Repository. At the time of writing:

yum install https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86_64/pgdg-centos11-11-2.noarch.rpm

Note: this guide uses LDAPS authentication (the method used by Active Directory), which is only supported in PostgreSQL 11 or newer. Older versions won’t work.

Now open /etc/yum.repos.d/CentOS-Base.repo and add the following line to the end of the [base] and [updates] sections:

exclude=postgresql*

Install the PostgreSQL server:

yum install postgresql11-server

Then initialize the database cluster:

/usr/pgsql-11/bin/postgresql-11-setup initdb

Configuration

We use the mail attribute of the AD users for authentication. There are other good candidates, such as userPrincipalName, or maybe sAMAccountName, but I dislike the latter, because it’s not globally unique, and thus usually won’t work with Internet-serving services. To avoid confusion, we decided long ago to require the full e-mail address for authentication, even for Intranet-only services. This is less confusion for the users, too.

Anyhow, the local OS (CentOS) uses sAMAccountName, so chances are, you need some kind of mapping between this and the e-mail address that you generally use for remotely logging in. You can do this in /var/lib/pgsql/11/data/pg_ident.conf:

mailmap /^(.*)$ "\[email protected]"

Now an example /var/lib/pgsql/11/data/pg_hba.conf file:

# auth for the built-in postgres user
local all postgres peer
# auth for AD users logging in via SSH
local all all peer map=mailmap
# IPv4 local connections
host all all 127.0.0.1/32 md5
# IPv6 local connections
host all all ::1/128 md5
# LDAP auth over SSL for remote addresses
hostssl all all 192.168.0.1/24 ldap ldapserver=dc2.ad.foobar.com ldapport=636 ldapscheme=ldaps ldaptls=0 ldapbinddn="CN=ldap,OU=Helpers,OU=Foobar,DC=ad,DC=foobar,DC=com" ldapbindpasswd=*** ldapsearchattribute=mail ldapbasedn="OU=Users,OU=Foobar,DC=ad,DC=foobar,DC=com"

The options should be self-explanatory. For details, please see PostgreSQL 11 Client Authentication.

Now edit /var/lib/pgsql/11/data/postgresql.conf, too:

listen_addresses = '*'
port = 5432
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'

While we’re at it, generate the server key and certificate (make sure to change CN to match your Postgres instance’ hostname):

cd /var/lib/pgsql/11/data
openssl req -new -x509 -days 1095 -nodes -text -out server.crt -keyout server.key -subj "/CN=db.foobar.com"
chmod 0600 server.key server.crt
chown postgres.postgres server.key server.crt

Remember the AD CA certificate you exported? Save that as /etc/pki/ca-trust/source/anchors/ad-ca.cer, then deploy it to the system certificate store:

update-ca-trust extract

Open the firewall port for PostgreSQL:

firewall-cmd --add-service=postgresql --permanent
firewall-cmd --reload

Whew, that should do. Enable and start the PostgreSQL service:

systemctl enable postgresql-11.service
systemctl start postgresql-11.service

Usage

Roles

Since LDAP authentication in PostgreSQL only validates username/password pairs, the role must exist in the database before they can connect:

CREATE ROLE "[email protected]" LOGIN;

Same with CREATEDB and CREATEROLE privileges added:

CREATE ROLE "[email protected]"" CREATEDB CREATEROLE LOGIN;

Or with SUPERUSER, even:

CREATE ROLE "[email protected]" SUPERUSER LOGIN;

To add it to an existing role:

ALTER USER "[email protected]" WITH SUPERUSER;

To revoke it:

ALTER USER "[email protected]" WITH NOSUPERUSER;

Local Login

After the user logs in via SSH using the Active Directory credentials, connecting with psql should be pretty straightforward. No password required! The OS already asked for that 🙂

psql -U "[email protected]" -d dbname

You can make this even easier with a global alias. Add to /etc/skel/.bash_profile:

alias adpsql="psql -U ${USER}@foobar.com"

Please note that this will only apply to new users, existing users will have to add this to ~/.bash_profile manually.

Then connecting to Postgres is as simple as:

adpsql -d dbname

Not too shabby, right?

Remote Login

pgAdmin is the de facto tool for managing PostgreSQL remotely. After installation, create a new server, and specify the required stuff:

  • Host name: db.foobar.com
  • Port: 5432
  • Username: <email address>
  • SSL mode: Require

That’s pretty much it. Enjoy!