There are many cases where external connection poolers like pgBouncer become unavoidable despite the costs and complexities associated with them. PgBouncer is one of the most popular external connection poolers for PostgreSQL. It is thin and lightweight, so it doesn’t have built-in authentication features like LDAP, which is essential for many enterprises. Luckily, pgBouncer has support for PAM. So, a setup of PAM-LDAP could be the right solution when pgBouncer is used. When it comes to LDAP / AD, the authentication happens outside PostgreSQL. So, generally, DBAs don’t have much control over it other than doing some final configuration. This blog post is about the final configuration required, and setting up an LDAP server is not covered.
Test setup
For this write-up, I am using a simple OpenLDAP – slapd setup, which comes along with most Linux Distros, including Ubuntu. How to setup an OpenLDAP server is beyond the scope of this blog. The LDAP / Active Directory for your organization could be different. The configuration of PostgreSQL and pgBouncer remains independent of the LDAP server used. In case you are interested in setting up SAMBA as your LDAP server, please refer to the blog post Testing LDAP Authentication and Authorization on Percona Operator for MongoDB.
In OpenLDAP-slapd, I have a LDAP user account jobin with the following details.
1 2 3 4 5 6 7 8 | ###Create a new user cat << EOF > jobin.ldif dn: uid=jobin,ou=People,dc=percona,dc=com uid: jobin cn: jobin sn: augustine objectClass: inetOrgPerson EOF |
This account is added to the directory using ldapadd utility.
1 | ldapadd -x -W -D "cn=admin,dc=percona,dc=com" -f jobin.ldif |
The password for the account is set as jobin123” using ldappasswd:
1 | ldappasswd -s jobin123 -W -D "cn=admin,dc=percona,dc=com" -x "uid=jobin,ou=People,dc=percona,dc=com" |
A user with the same name should exist in the database because LDAP-PAM is only for authentication, because Authorization and Access control require real database users.
1 2 | postgres=# create user jobin with password 'job'; CREATE ROLE |
Please note that the password on the database side is kept different from the LDAP password to make sure that authentication is really happening with the LDAP password, not the password of the user account in the database.
PostgreSQL can handle LDAP authentication directly without PAM. Here is the sample pg_hba.conf entry:
1 | host all jobin 0.0.0.0/0 ldap ldapserver=localhost ldapbasedn="OU=People,DC=percona,DC=com" ldapsearchattribute=uid |
We shall verify whether the LDAP authentication is working fine for PostgreSQL.
Example with wrong LDAP password:
1 2 3 4 | $ psql -h 127.0.0.1 -U jobin -d postgres -W Password: psql: error: connection to server at "127.0.0.1", port 5432 failed: FATAL: LDAP authentication failed for user "jobin" connection to server at "127.0.0.1", port 5432 failed: FATAL: LDAP authentication failed for user "jobin" |
Example with correct LDAP password:
1 2 3 4 5 6 7 | $ psql -h 127.0.0.1 -U jobin -d postgres -W Password: psql (15.6 (Ubuntu 15.6-1.pgdg22.04+1)) SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off) Type "help" for help. postgres=> |
Installing and configuring PAM
We can check for available PAM modules in the system. We are interested in pam_ldap:
1 2 | $ ls /lib/*/security | grep ldap pam_ldap.so |
If the module is not already present, we may have to install it:
1 2 3 4 5 6 7 8 9 10 11 12 13 | $ sudo apt install libpam-ldap Reading package lists... Done Building dependency tree... Done Reading state information... Done The following additional packages will be installed: ldap-auth-client ldap-auth-config libnss-ldap Suggested packages: nscd The following NEW packages will be installed: ldap-auth-client ldap-auth-config libnss-ldap libpam-ldap 0 upgraded, 4 newly installed, 0 to remove and 0 not upgraded. Need to get 114 kB of archives. After this operation, 378 kB of additional disk space will be used. |
This will automatically start the configuration.
1 2 3 | Setting up ldap-auth-client (0.5.4) ... Setting up ldap-auth-config (0.5.4) ... Setting up libpam-ldap:amd64 (186-4ubuntu2) ... |
Here are the sample configuration entries:
1 2 3 | $ cat /etc/ldap.conf | grep percona base dc=percona,dc=com rootbinddn cn=admin,dc=percona,dc=com |
Generally, the “common-auth” configuration would be sufficient:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | cat /etc/pam.d/common-auth # # /etc/pam.d/common-auth - authentication settings common to all services # # This file is included from other service-specific PAM config files, # and should contain a list of the authentication modules that define # the central authentication scheme for use on the system # (e.g., /etc/shadow, LDAP, Kerberos, etc.). The default is to use the # traditional Unix authentication mechanisms. # # As of pam 1.0.1-6, this file is managed by pam-auth-update by default. # To take advantage of this, it is recommended that you configure any # local modules either before or after the default block, and use # pam-auth-update to manage selection of other modules. See # pam-auth-update(8) for details. # here are the per-package modules (the "Primary" block) auth [success=2 default=ignore] pam_unix.so nullok auth [success=1 default=ignore] pam_ldap.so use_first_pass # here's the fallback if no module succeeds auth requisite pam_deny.so # prime the stack with a positive return value if there isn't one already; # this avoids us returning an error just because nothing sets a success code # since the modules above will each just jump around auth required pam_permit.so # and here are more per-package modules (the "Additional" block) auth optional pam_cap.so # end of pam-auth-update config |
However, many users prefer to have a dedicated configuration for pgBouncer, and pgBouncer, by default, gives the service name “pgbouncer.” If you prefer that route, you may create a configuration file /etc/pam.d/pgbouncer:
1 2 | $ cat /etc/pam.d/pgbouncer auth required pam_ldap.so |
Testing PAM authentication
It is a good idea to test whether the PAM authentication is working before proceeding to configure the pgBouncer for PAM. Here, I am using the tool pamtester, which is available for most Linux Distros.
1 | sudo apt install pamtester |
Verify that the PAM authentication happens with the correct password and fails otherwise.
1 2 3 4 5 6 7 | $ pamtester login jobin authenticate Password: pamtester: successfully authenticated $ pamtester login jobin authenticate Password: pamtester: Authentication failure |
pgBouncer configuration
As a first step, please make sure that the pgBouncer is compiled with pam support.
1 2 3 4 5 6 7 8 9 10 11 12 | $ ldd /usr/sbin/pgbouncer linux-vdso.so.1 (0x00007ffc5c8c1000) libcares.so.2 => /lib/x86_64-linux-gnu/libcares.so.2 (0x00007829493f6000) libevent-2.1.so.7 => /lib/x86_64-linux-gnu/libevent-2.1.so.7 (0x00007829493a2000) libssl.so.3 => /lib/x86_64-linux-gnu/libssl.so.3 (0x00007829492fe000) libcrypto.so.3 => /lib/x86_64-linux-gnu/libcrypto.so.3 (0x0000782948e00000) libsystemd.so.0 => /lib/x86_64-linux-gnu/libsystemd.so.0 (0x0000782948d39000) libpam.so.0 => /lib/x86_64-linux-gnu/libpam.so.0 (0x00007829492ec000) libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x0000782948a00000) liblzma.so.5 => /lib/x86_64-linux-gnu/liblzma.so.5 (0x00007829492bf000) libzstd.so.1 => /lib/x86_64-linux-gnu/libzstd.so.1 (0x0000782948c6a000) … |
As we can see, the pgBouncer is linking to libpam.so.0. Those who compile pgBouncer from the source often miss this, and it is one of the most frequent causes of trouble.
Now we can proceed to configure the pgBouncer. For this demo, I have a very simple pgBouncer configuration as follows:
1 2 3 4 5 6 7 8 9 10 | [databases] * = host=127.0.0.1 port=5432 [pgbouncer] listen_port = 6432 listen_addr = * auth_type = pam logfile = /tmp/pgbouncer.log pidfile = /tmp/pgbouncer.pid admin_users = postgres |
Now, once we start the pgBouncer service, we should be able to authenticate using pam-ldap:
1 2 3 4 5 6 | postgres@u2:~$ psql -h 127.0.0.1 -U jobin -p 6432 -d postgres -W Password: psql (15.6 (Ubuntu 15.6-1.pgdg22.04+1)) Type "help" for help. postgres=> |
An authentication failure will produce errors as follows in pgBouncer logs.
1 2 3 4 | 2024-04-25 03:50:39.747 UTC [185] LOG C-0x5fed7a61cc00: postgres/jobin@127.0.0.1:35840 login attempt: db=postgres user=jobin tls=no 2024-04-25 03:50:39.752 UTC [185] WARNING pam_authenticate() failed: Authentication failure 2024-04-25 03:50:40.072 UTC [185] LOG C-0x5fed7a61cc00: postgres/jobin@127.0.0.1:35840 closing because: PAM authentication failed (age=0s) 2024-04-25 03:50:40.072 UTC [185] WARNING C-0x5fed7a61cc00: postgres/jobin@127.0.0.1:35840 pooler error: PAM authentication failed |
Note:
- This blog post demonstrates minimal functionality. It does not recommend configurations for production systems.
- This blog post doesn’t recommend external connection poolers everywhere. External connection poolers like pgBouncer need to be considered only in those cases where the application doesn’t have a good connection pooler or wherever it is unavoidable. The use should be judiciously controlled.
Our PostgreSQL Performance Tuning eBook condenses years of database expertise into a practical guide for optimizing your PostgreSQL databases. Inside, you’ll discover our most effective PostgreSQL performance strategies derived from real-world experience.
Download now: Elevate your PostgreSQL Performance
