Skip to main content

PostgreSQL PAM Authentication

Guide for configuring PAM (Pluggable Authentication Modules) authentication in PostgreSQL, including integration with LDAP for centralized user management.

What is PAM Authentication?​

PAM allows PostgreSQL to use the system's authentication mechanisms instead of managing passwords internally. This enables:

  • Using system users for database access
  • Integration with LDAP/Active Directory
  • Centralized authentication management
  • Support for various authentication methods

Basic PAM Setup​

Step 1: Configure pg_hba.conf​

Edit PostgreSQL's host-based authentication file:

sudo nvim /var/lib/pgsql/data/pg_hba.conf

Add a PAM authentication entry:

# TYPE  DATABASE  USER  ADDRESS      METHOD          OPTIONS
host    all       all   0.0.0.0/0    pam             pamservice=postgresql
Connection Types
  • local: Unix socket connections
  • host: TCP/IP connections (with or without SSL)
  • hostssl: SSL-encrypted TCP/IP only
  • hostnossl: Non-SSL TCP/IP only

Step 2: Adjust Shadow File Permissions​

PostgreSQL needs to read the shadow file for authentication:

sudo chmod 444 /etc/shadow
Security Consideration

This makes the shadow file readable by all users. While the passwords are still hashed, consider security implications in your environment. Alternative: add postgres user to shadow group.

Alternative (more secure):

sudo usermod -aG shadow postgres
sudo chmod 640 /etc/shadow

Step 3: Create PAM Service File​

Create or edit the PAM configuration for PostgreSQL:

sudo nvim /etc/pam.d/postgresql

Basic configuration:

auth        required    pam_unix.so debug
account     required    pam_permit.so

Explanation:

  • auth required pam_unix.so: Use Unix system authentication
  • debug: Enable detailed logging (remove in production)
  • account required pam_permit.so: Allow all authenticated users

Step 4: Restart PostgreSQL​

Apply the changes:

sudo systemctl restart postgresql

Step 5: Test Authentication​

Try connecting with a system user:

psql -h localhost -U systemuser -d postgres

The system will prompt for the user's system password.

PAM with LDAP Integration​

Prerequisites​

Install PAM LDAP module:

Arch Linux:

yay -S pam_ldap

Debian/Ubuntu:

sudo apt install libpam-ldap

RHEL/AlmaLinux:

sudo yum install pam_ldap

This installs:

  • PAM module: /usr/lib/security/pam_ldap.so
  • Config file: /etc/pam_ldap.conf

Configure PAM LDAP​

Edit the LDAP configuration:

sudo nvim /etc/pam_ldap.conf

Basic configuration:

# LDAP server host
host localhost

# Base DN for user searches
base ou=users,dc=example,dc=org

# LDAP port
port 389

# Optional: Use LDAPS (secure)
# ssl start_tls
# port 636

Advanced options:

# Bind credentials (if required)
binddn cn=readonly,dc=example,dc=org
bindpw secretpassword

# Search scope
scope sub

# Filter for user authentication
pam_filter objectClass=posixAccount

# Attribute mappings
pam_login_attribute uid
pam_member_attribute memberUid

Configure PAM Service for LDAP​

Edit or create the PAM service file:

sudo nvim /etc/pam.d/postgresql

For LDAP authentication:

auth        required    pam_ldap.so
account     required    pam_ldap.so

Or combine Unix and LDAP (fallback):

auth        sufficient  pam_ldap.so
auth        required    pam_unix.so
account     sufficient  pam_ldap.so
account     required    pam_permit.so
sufficient vs required
  • sufficient: If successful, no further modules are checked
  • required: Must succeed, but continues checking other modules
  • requisite: Must succeed, stops immediately if fails

PgBouncer PAM Configuration​

If using PgBouncer as a connection pooler:

Configure PgBouncer​

Edit pgbouncer.ini:

[databases]
mydb = host=localhost port=5432 dbname=mydb

[pgbouncer]
auth_type = pam
auth_file = /etc/pgbouncer/userlist.txt

Create PAM Service for PgBouncer​

sudo nvim /etc/pam.d/pgbouncer

Content:

auth        required    pam_ldap.so
account     required    pam_ldap.so

Adjust Permissions​

sudo chmod 444 /etc/shadow
# Or add pgbouncer user to shadow group
sudo usermod -aG shadow pgbouncer

Testing and Troubleshooting​

Test LDAP Connection​

# Test LDAP search
ldapsearch -x -H ldap://localhost:389 -b "ou=users,dc=example,dc=org"

# Test specific user
ldapsearch -x -H ldap://localhost:389 -b "ou=users,dc=example,dc=org" "(uid=username)"

Check PAM Authentication​

Test PAM directly:

pamtester postgresql username authenticate

View Logs​

# PostgreSQL logs
sudo tail -f /var/log/postgresql/postgresql-*.log

# System auth logs
sudo tail -f /var/log/auth.log        # Debian/Ubuntu
sudo tail -f /var/log/secure          # RHEL/CentOS

# PAM debug output
journalctl -u postgresql -f

Common Issues​

Issue: Permission denied

Solution: Check shadow file permissions and postgres user groups

Issue: LDAP connection failed

Solution: 
- Verify LDAP server is running: systemctl status slapd
- Test connectivity: telnet localhost 389
- Check firewall: sudo firewall-cmd --list-all

Issue: User not found in LDAP

Solution:
- Verify base DN in pam_ldap.conf
- Check LDAP user's posixAccount objectClass
- Test ldapsearch with same parameters

Security Best Practices​

  1. Use SSL/TLS for LDAP
ssl start_tls
port 636
tls_cacertdir /etc/ssl/certs
  1. Limit Database Access
# Only allow specific users
host    mydb    ldapuser1,ldapuser2    0.0.0.0/0    pam
  1. Use Read-Only LDAP Bind Account
binddn cn=readonly,dc=example,dc=org
  1. Enable Logging
auth    required    pam_unix.so debug
  1. Restrict Shadow File
sudo usermod -aG shadow postgres
sudo chmod 640 /etc/shadow

Complete Example Configuration​

pg_hba.conf​

# TYPE  DATABASE  USER           ADDRESS      METHOD    OPTIONS
local   all       postgres                    peer
host    all       all            127.0.0.1/32 pam       pamservice=postgresql
host    all       all            0.0.0.0/0    pam       pamservice=postgresql

/etc/pam.d/postgresql​

auth        sufficient  pam_ldap.so
auth        required    pam_unix.so
account     sufficient  pam_ldap.so
account     required    pam_permit.so

/etc/pam_ldap.conf​

host ldap.example.org
base ou=users,dc=example,dc=org
port 389
ssl start_tls
pam_login_attribute uid
pam_filter objectClass=posixAccount

Additional Resources​