How to Secure Your PostgreSQL Database: A Step-by-Step Guide

Introduction to PostgreSQL Security
Network Security: Limit Access to Your Database
Authentication & Access Control: Secure User Logins
Authorization & Privilege Management: Least Privilege Principle
Data Encryption: Protect Data at Rest and in Transit
Auditing & Logging: Monitor for Suspicious Activity
Regular Updates & Patching: Stay Ahead of Vulnerabilities
Backup & Disaster Recovery: Prepare for the Worst
Additional Best Practices
Conclusion
References

1. Introduction to PostgreSQL Security

PostgreSQL includes built-in security features, but securing it requires proactive configuration. Key risks to mitigate include:

Unauthorized access (via weak passwords, misconfigured firewalls, or exposed network ports).
Privilege escalation (users gaining more access than intended).
Data leaks (unencrypted data in transit or at rest).
Malicious activity (SQL injection, ransomware, or insider threats).

This guide focuses on defense-in-depth: layering security controls to minimize risk even if one layer fails.

2. Network Security: Limit Access to Your Database

By default, PostgreSQL listens for connections on port 5432 and may accept traffic from any IP if misconfigured. Restricting network access is your first line of defense.

Step 2.1: Restrict Listen Addresses

PostgreSQL binds to 0.0.0.0 (all interfaces) by default, exposing it to the public internet. Limit this to trusted IPs:

Open the PostgreSQL configuration file (postgresql.conf). Paths vary by OS:
- Ubuntu/Debian: /etc/postgresql/<version>/main/postgresql.conf
- RHEL/CentOS: /var/lib/pgsql/<version>/data/postgresql.conf
- macOS (Homebrew): /usr/local/var/postgres/postgresql.conf
Find the listen_addresses directive and set it to trusted IPs (e.g., localhost and your application server):
```
listen_addresses = '127.0.0.1,192.168.1.100'  # Replace with your app server IP  
```
- Use 'localhost' if the database and app run on the same machine.
- Never use '*' or '0.0.0.0' in production (exposes to all networks).

Restart PostgreSQL to apply changes:

sudo systemctl restart postgresql  # Ubuntu/Debian  
# OR  
sudo systemctl restart postgresql-14  # RHEL/CentOS (replace 14 with your version)

Step 2.2: Use Host-Based Authentication (pg_hba.conf)

The pg_hba.conf file (Host-Based Authentication) controls which users, IPs, and authentication methods are allowed to connect.

Open pg_hba.conf (in the same directory as postgresql.conf).

Replace overly permissive rules (e.g., host all all 0.0.0.0/0 md5) with strict entries. Example:

# Allow local connections (via Unix socket) with peer authentication  
local   all             all                                     peer  

# Allow localhost (127.0.0.1) to connect with password  
host    all             all             127.0.0.1/32            scram-sha-256  

# Allow app server (192.168.1.100) to connect with password  
hostssl all             app_user        192.168.1.100/32        scram-sha-256  

# Block all other IPs  
host    all             all             0.0.0.0/0               reject

hostssl: Requires SSL-encrypted connections (see Section 5 for SSL setup).
scram-sha-256: Secure password hashing (preferred over md5).

Reload PostgreSQL to apply changes (no restart needed):
```
sudo systemctl reload postgresql  
```

Step 2.3: Enable Firewall Rules

Use a firewall (e.g., ufw, iptables, or cloud security groups) to block port 5432 except for trusted IPs.

Example with UFW (Ubuntu/Debian):

# Allow SSH (keep this open first!)  
sudo ufw allow 22/tcp  

# Allow PostgreSQL only from app server (192.168.1.100)  
sudo ufw allow from 192.168.1.100 to any port 5432/tcp  

# Deny all other PostgreSQL traffic  
sudo ufw deny 5432/tcp  

# Enable UFW  
sudo ufw enable  

# Verify rules  
sudo ufw status numbered

Step 2.4: Secure Remote Access with VPN

For remote administration (e.g., from your laptop), avoid exposing 5432 directly. Use a VPN (e.g., OpenVPN, WireGuard) or SSH tunneling:

# SSH tunnel: Forward local port 5433 to remote PostgreSQL port 5432  
ssh -L 5433:localhost:5432 [email protected]

Then connect locally via psql -h localhost -p 5433 -U postgres.

3. Authentication & Access Control: Secure User Logins

PostgreSQL uses roles (users/groups) for authentication. Weak authentication (e.g., default passwords, plaintext storage) is a top risk.

Step 3.1: Use SCRAM-SHA-256 for Password Encryption

scram-sha-256 is more secure than md5 (resistant to rainbow table attacks). Enable it:

In postgresql.conf, set:

password_encryption = scram-sha-256  # Default in PostgreSQL 14+

Reload PostgreSQL:
```
sudo systemctl reload postgresql  
```
Update existing passwords to use SCRAM (old md5 hashes still work, but new ones will use SCRAM):
```
ALTER ROLE existing_user WITH PASSWORD 'new_strong_password';  
```

Step 3.2: Disable the Default `postgres` Superuser

The default postgres superuser is a prime target. Either:

Change its password (if you must keep it):

ALTER ROLE postgres WITH PASSWORD 'super_strong_password';

Create a new superuser and drop postgres (advanced):

CREATE ROLE db_admin WITH SUPERUSER LOGIN PASSWORD 'strong_password';  
DROP ROLE postgres;

Step 3.3: Enforce Strong Passwords

Use the pgcrypto extension to validate password strength or integrate with PAM (Pluggable Authentication Modules) for external password policies.

Example with pgcrypto:

-- Enable pgcrypto (for password hashing/validation)  
CREATE EXTENSION IF NOT EXISTS pgcrypto;  

-- Function to check password strength (min 10 chars, mixed case, numbers, symbols)  
CREATE OR REPLACE FUNCTION check_password(password text)  
RETURNS boolean AS $$  
BEGIN  
  RETURN length(password) >= 10  
    AND password ~ '[A-Z]'  
    AND password ~ '[a-z]'  
    AND password ~ '[0-9]'  
    AND password ~ '[!@#$%^&*(),.?":{}|<>]';  
END;  
$$ LANGUAGE plpgsql;  

-- Enforce via a trigger on pg_authid (use cautiously!)  
-- Note: Directly modifying system catalogs is risky; use PAM for production.

4. Authorization & Privilege Management

Even authenticated users should only access data they need. Follow the principle of least privilege.

Step 4.1: Revoke Public Privileges

By default, all users inherit PUBLIC privileges (e.g., on the public schema). Revoke these:

-- Revoke public access to the public schema  
REVOKE ALL ON SCHEMA public FROM public;  

-- Revoke public access to all tables/views in existing schemas  
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM public;  
REVOKE ALL ON ALL SEQUENCES IN SCHEMA public FROM public;  
REVOKE ALL ON ALL FUNCTIONS IN SCHEMA public FROM public;

Step 4.2: Create Roles with Minimal Permissions

Avoid using superusers for daily tasks. Create roles for specific use cases:

Example: Read-Only Role

-- Create a read-only role  
CREATE ROLE read_only_role WITH LOGIN PASSWORD 'read_only_password';  

-- Grant access to the public schema  
GRANT USAGE ON SCHEMA public TO read_only_role;  

-- Grant read access to specific tables  
GRANT SELECT ON TABLE users, orders TO read_only_role;

Example: App User Role

-- Create an app-specific role  
CREATE ROLE app_user WITH LOGIN PASSWORD 'app_password';  

-- Allow usage of the app schema  
GRANT USAGE ON SCHEMA app_schema TO app_user;  

-- Grant CRUD on specific tables  
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE app_schema.customers TO app_user;

Step 4.3: Use Row-Level Security (RLS)

For fine-grained control (e.g., users see only their data), enable Row-Level Security (RLS):

-- Create a table with user-specific data  
CREATE TABLE app_schema.user_data (  
  id SERIAL PRIMARY KEY,  
  user_id TEXT NOT NULL,  -- Matches database username  
  data TEXT NOT NULL  
);  

-- Enable RLS on the table  
ALTER TABLE app_schema.user_data ENABLE ROW LEVEL SECURITY;  

-- Create a policy: Users see only their own data  
CREATE POLICY user_data_isolation ON app_schema.user_data  
  USING (user_id = current_user);  

-- Grant access to app_user  
GRANT SELECT, INSERT, UPDATE ON app_schema.user_data TO app_user;

5. Data Encryption: Protect Data at Rest and in Transit

Step 5.1: Encrypt Data in Transit with SSL

All client-server communication should be encrypted via SSL/TLS.

Step 5.1.1: Generate SSL Certificates
Use OpenSSL to create self-signed certificates (or use trusted CA certificates for production):

# Create a directory for SSL files  
sudo mkdir -p /etc/postgresql/ssl  
sudo chmod 700 /etc/postgresql/ssl  
cd /etc/postgresql/ssl  

# Generate a private key and certificate  
sudo openssl req -new -x509 -nodes -out server.crt -keyout server.key -days 365  
sudo chmod 600 server.key server.crt  
sudo chown postgres:postgres server.key server.crt

Step 5.1.2: Configure PostgreSQL for SSL
Edit postgresql.conf:

ssl = on  
ssl_cert_file = '/etc/postgresql/ssl/server.crt'  
ssl_key_file = '/etc/postgresql/ssl/server.key'  
ssl_ciphers = 'HIGH:!aNULL:!MD5'  # Use strong ciphers  
ssl_prefer_server_ciphers = on

Update pg_hba.conf to require SSL for all remote connections (use hostssl instead of host):

hostssl all             app_user        192.168.1.100/32        scram-sha-256

Reload PostgreSQL:

sudo systemctl reload postgresql

Step 5.2: Encrypt Data at Rest

PostgreSQL doesn’t natively support Transparent Data Encryption (TDE) yet (coming in PostgreSQL 16+ for some cloud providers), but you can use:

OS-Level Encryption: Use LUKS (Linux), BitLocker (Windows), or cloud storage encryption (e.g., AWS EBS, Azure Disk Encryption).
Column-Level Encryption: Use the pgcrypto extension to encrypt sensitive columns (e.g., SSNs, credit cards):

-- Enable pgcrypto  
CREATE EXTENSION IF NOT EXISTS pgcrypto;  

-- Add an encrypted column  
ALTER TABLE users ADD COLUMN ssn_encrypted bytea;  

-- Encrypt data (use a secure key management system in production!)  
UPDATE users SET ssn_encrypted = pgp_sym_encrypt(ssn::text, 'encryption_key');  

-- Decrypt when needed (grant access to the decrypt function selectively)  
GRANT EXECUTE ON FUNCTION pgp_sym_decrypt(bytea, text) TO app_user;

6. Auditing & Logging: Monitor for Suspicious Activity

PostgreSQL logs can reveal unauthorized access or unusual queries. Configure logging to track critical events.

Step 6.1: Enable Detailed Logging

Edit postgresql.conf to log connections, disconnections, and queries:

log_destination = 'stderr'  
logging_collector = on  
log_directory = 'pg_log'  # Path to log files  
log_filename = 'postgresql-%Y-%m-%d.log'  
log_rotation_age = '1d'  
log_rotation_size = 0  

# Log connections/disconnections  
log_connections = on  
log_disconnections = on  

# Log all DDL (CREATE/ALTER/DROP) and data modifications (INSERT/UPDATE/DELETE)  
log_statement = 'ddl'  # Use 'all' for debugging (verbose!)  
log_min_duration_statement = 1000  # Log queries taking >1s (adjust as needed)  

# Log failed authentication attempts  
log_failed_connections = on

Reload PostgreSQL:

sudo systemctl reload postgresql

Step 6.2: Use pgAudit for Advanced Auditing

For granular auditing (e.g., who modified a specific row), use the pgAudit extension:

Install pgAudit (package name varies by OS):

sudo apt install postgresql-14-pgaudit  # Ubuntu/Debian

Edit postgresql.conf to load the extension:

shared_preload_libraries = 'pgaudit'  # Add to existing list  
pgaudit.log = 'write, ddl, function'  # Log writes, DDL, and function calls

Restart PostgreSQL and enable the extension:

sudo systemctl restart postgresql  
CREATE EXTENSION pgaudit;

Step 6.3: Centralize Logs

Forward logs to a centralized system (e.g., ELK Stack, Splunk, or CloudWatch) for long-term storage and alerting. Use tools like rsyslog or fluentd to ship logs.

7. Regular Updates & Patching

PostgreSQL releases security patches for vulnerabilities (e.g., CVE-2023-2454). Stay updated:

Minor Updates: Use your OS package manager to install patches:

sudo apt update && sudo apt upgrade postgresql  # Ubuntu/Debian

Major Updates: Plan upgrades (e.g., 13 → 14) using pg_upgrade to avoid data loss. Test in staging first!
Monitor Vulnerabilities: Subscribe to the PostgreSQL Security Mailing List and check the CVE Database for PostgreSQL-related CVEs.

8. Backup & Disaster Recovery

Even with strong security, data loss can occur (e.g., hardware failure, ransomware). Regular backups are critical.

Step 8.1: Automated Backups with pg_dump

Use pg_dump for logical backups (small to medium databases):

# Create a daily backup script  
cat > /usr/local/bin/backup_postgres.sh << 'EOF'  
#!/bin/bash  
BACKUP_DIR="/var/backups/postgres"  
TIMESTAMP=$(date +%Y%m%d_%H%M%S)  
pg_dump -U postgres -d mydb -F c -f "$BACKUP_DIR/mydb_$TIMESTAMP.dump"  
# Encrypt the backup with GPG (optional but recommended)  
gpg --encrypt --recipient [email protected] "$BACKUP_DIR/mydb_$TIMESTAMP.dump"  
rm "$BACKUP_DIR/mydb_$TIMESTAMP.dump"  
# Delete backups older than 30 days  
find "$BACKUP_DIR" -name "*.dump.gpg" -mtime +30 -delete  
EOF  

# Make executable  
chmod +x /usr/local/bin/backup_postgres.sh  

# Add to crontab (run daily at 2 AM)  
echo "0 2 * * * root /usr/local/bin/backup_postgres.sh" | sudo tee -a /etc/crontab

Step 8.2: Point-in-Time Recovery (PITR)

For large databases or to recover to a specific time, use WAL (Write-Ahead Logging) archiving:

Edit postgresql.conf:

archive_mode = on  
archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f'  # Archive WAL files  
wal_level = replica  # Required for PITR

Create a base backup with pg_basebackup:

pg_basebackup -U postgres -D /var/backups/postgres/base_backup -F t -z

To recover, restore the base backup and replay WAL files up to the desired time.

Step 8.3: Test Backups Regularly

Backups are useless if they can’t be restored. Test monthly:

# Restore a backup to a test database  
pg_restore -U postgres -d test_db /var/backups/postgres/mydb_20240101_020000.dump

9. Additional Best Practices

Disable Unused Extensions: Remove extensions like dblink or postgres_fdw if not needed (they can introduce vulnerabilities).
Secure the File System: Restrict permissions on PostgreSQL data directories (chmod 700 /var/lib/postgresql/<version>/main).
Use a Configuration Management Tool: Tools like Ansible or Puppet ensure security settings (e.g., pg_hba.conf) are enforced across environments.
Password Expiry: Enforce password rotation with ALTER ROLE app_user VALID UNTIL '2024-12-31';.

10. Conclusion

Securing PostgreSQL is an ongoing process, not a one-time task. By following these steps—restricting network access, enforcing strong authentication, encrypting data, auditing logs, and maintaining backups—you’ll significantly reduce your risk of breaches. Regularly review and update your security measures to adapt to new threats.

Table of Contents