Aim_
After this lesson, you will:
- Implement SSL/TLS encryption for PostgreSQL_
- Configure secure authentication
- Harden network security
- Enable encryption at rest_
- Setup logging audit
- Apply security best practices for HA cluster
1. SSL/TLS Encryption
1.1. Generate SSL certificates
# Create certificate directory sudo mkdir -p /etc/postgresql/ssl cd /etc/postgresql/sslGenerate CA private key
sudo openssl genrsa -out ca-key.pem 4096
Generate CA certificate
sudo openssl req -new -x509 -days 3650 -key ca-key.pem -out ca-cert.pem
-subj "/CN=PostgreSQL-CA/O=MyOrg/C=US"Generate server private key
sudo openssl genrsa -out server-key.pem 4096
Generate server certificate signing request
sudo openssl req -new -key server-key.pem -out server-req.pem
-subj "/CN=postgres.example.com/O=MyOrg/C=US"Sign server certificate
sudo openssl x509 -req -in server-req.pem -days 365
-CA ca-cert.pem -CAkey ca-key.pem -CAcreateserial
-out server-cert.pemSet permissions
sudo chmod 600 server-key.pem sudo chown postgres:postgres /etc/postgresql/ssl/*
1.2. Configure PostgreSQL for SSL_
-- Enable SSL ALTER SYSTEM SET ssl = on; ALTER SYSTEM SET ssl_cert_file = '/etc/postgresql/ssl/server-cert.pem'; ALTER SYSTEM SET ssl_key_file = '/etc/postgresql/ssl/server-key.pem'; ALTER SYSTEM SET ssl_ca_file = '/etc/postgresql/ssl/ca-cert.pem';-- SSL ciphers (strong only) ALTER SYSTEM SET ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL'; ALTER SYSTEM SET ssl_prefer_server_ciphers = on; ALTER SYSTEM SET ssl_min_protocol_version = 'TLSv1.2';
-- Restart required
sudo systemctl restart patroni
1.3. Configure pg_hba.conf for SSL
# /var/lib/postgresql/18/data/pg_hba.confRequire SSL for all connections
hostssl all all 0.0.0.0/0 scram-sha-256
Or mixed (SSL preferred but not required)
hostssl all all 0.0.0.0/0 scram-sha-256 host all all 0.0.0.0/0 scram-sha-256
Reload
sudo -u postgres psql -c "SELECT pg_reload_conf();"
1.4. Test SSL connection
# Connect with SSL psql "host=10.0.1.11 port=5432 dbname=postgres user=postgres sslmode=require"Verify SSL
psql -h 10.0.1.11 -U postgres -c "SELECT ssl_is_used();"
ssl_is_used
------------
t ← true = SSL enabled ✅
Check SSL cipher
psql -h 10.0.1.11 -U postgres -c " SELECT pid, usename, ssl, client_addr, backend_type FROM pg_stat_ssl JOIN pg_stat_activity USING (pid); "
1.5. Client certificate authentication
# Generate client certificate sudo openssl genrsa -out client-key.pem 4096 sudo openssl req -new -key client-key.pem -out client-req.pem
-subj "/CN=app_user/O=MyOrg/C=US" sudo openssl x509 -req -in client-req.pem -days 365
-CA ca-cert.pem -CAkey ca-key.pem -CAcreateserial
-out client-cert.pemCopy to client machine
scp client-*.pem app_user@app-server:~/.postgresql/
# pg_hba.conf - require client cert hostssl all all 0.0.0.0/0 certConnect with client cert
psql "host=10.0.1.11 dbname=postgres user=app_user
sslmode=verify-full
sslcert=/.postgresql/client-cert.pem/.postgresql/client-key.pem
sslkey=
sslrootcert=~/.postgresql/ca-cert.pem"
2. Authentication
2.1. SCRAM-SHA-256 (recommended)
-- Set password encryption method ALTER SYSTEM SET password_encryption = 'scram-sha-256';-- Create user with SCRAM CREATE USER app_user WITH PASSWORD 'strong_password';
-- Update existing user password \password existing_user -- Will use SCRAM
# pg_hba.conf
host all all 0.0.0.0/0 scram-sha-256
2.2. Disable weak authentication
# DON'T USE:host all all 0.0.0.0/0 trust ❌ No password!
host all all 0.0.0.0/0 md5 ❌ Weak encryption
USE:
hostssl all all 0.0.0.0/0 scram-sha-256 ✅
hostssl all all 0.0.0.0/0 cert ✅
2.3. Password policies
-- Install passwordcheck extension CREATE EXTENSION IF NOT EXISTS passwordcheck;-- Configure in postgresql.conf ALTER SYSTEM SET passwordcheck.min_length = 12; ALTER SYSTEM SET passwordcheck.require_special = on; ALTER SYSTEM SET passwordcheck.require_digit = on; ALTER SYSTEM SET passwordcheck.require_upper = on;
-- Test - weak password will be rejected CREATE USER weak_user WITH PASSWORD '12345'; -- ERROR CREATE USER strong_user WITH PASSWORD 'MyStr0ng!Pass123'; -- OK
2.4. Role-based access control
-- Create roles CREATE ROLE readonly; CREATE ROLE readwrite; CREATE ROLE admin;-- Grant privileges GRANT CONNECT ON DATABASE myapp TO readonly; GRANT USAGE ON SCHEMA public TO readonly; GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
GRANT CONNECT ON DATABASE myapp TO readwrite; GRANT USAGE ON SCHEMA public TO readwrite; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO readwrite;
-- Create users with roles CREATE USER app_readonly WITH PASSWORD 'pass123'; GRANT readonly TO app_readonly;
CREATE USER app_readwrite WITH PASSWORD 'pass456'; GRANT readwrite TO app_readwrite;
-- Default privileges for future tables ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite;
3. Network Security
3.1. Firewall configuration
# Using ufw sudo ufw default deny incoming sudo ufw default allow outgoingAllow SSH
sudo ufw allow 22/tcp
Allow PostgreSQL only from specific IPs
sudo ufw allow from 10.0.1.0/24 to any port 5432 proto tcp
Allow Patroni REST API (internal only)
sudo ufw allow from 10.0.1.0/24 to any port 8008 proto tcp
Allow etcd (internal only)
sudo ufw allow from 10.0.1.0/24 to any port 2379 proto tcp sudo ufw allow from 10.0.1.0/24 to any port 2380 proto tcp
Enable firewall
sudo ufw enable sudo ufw status
# Using iptablesAllow PostgreSQL from specific subnet
sudo iptables -A INPUT -p tcp -s 10.0.1.0/24 --dport 5432 -j ACCEPT sudo iptables -A INPUT -p tcp --dport 5432 -j DROP
Allow Patroni REST API (internal)
sudo iptables -A INPUT -p tcp -s 10.0.1.0/24 --dport 8008 -j ACCEPT sudo iptables -A INPUT -p tcp --dport 8008 -j DROP
Save rules
sudo iptables-save > /etc/iptables/rules.v4
3.2. Network segmentation
Architecture:
Internet
↓
[Load Balancer / Application Layer]
↓ (Private Network 10.0.1.0/24)
[PostgreSQL HA Cluster]
node1: 10.0.1.11
node2: 10.0.1.12
node3: 10.0.1.13
Security Rules:
- PostgreSQL port 5432: Only accessible from app servers
- Patroni port 8008: Internal cluster only
- etcd ports 2379/2380: Internal cluster only
SSH port 22: Bastion host only
3.3. VPN/Private network
# Use WireGuard for inter-node communicationInstall WireGuard
sudo apt-get install -y wireguard
Generate keys
wg genkey | tee privatekey | wg pubkey > publickey
Configure /etc/wireguard/wg0.conf
[Interface] PrivateKey = <private_key> Address = 10.100.0.1/24 ListenPort = 51820
[Peer] PublicKey = <peer_public_key> AllowedIPs = 10.100.0.2/32 Endpoint = 203.0.113.2:51820
Start WireGuard
sudo wg-quick up wg0 sudo systemctl enable wg-quick@wg0
4. Encryption at Rest
4.1. File system encryption (LUKS)
# Encrypt data volume sudo cryptsetup luksFormat /dev/sdb sudo cryptsetup luksOpen /dev/sdb pgdata_encryptedCreate filesystem
sudo mkfs.ext4 /dev/mapper/pgdata_encrypted
Mount
sudo mkdir -p /var/lib/postgresql/encrypted sudo mount /dev/mapper/pgdata_encrypted /var/lib/postgresql/encrypted
Auto-mount on boot (/etc/crypttab)
pgdata_encrypted /dev/sdb none luks
/etc/fstab
/dev/mapper/pgdata_encrypted /var/lib/postgresql/encrypted ext4 defaults 0 2
4.2. Transparent Data Encryption (TDE)
Note: PostgreSQL 18 doesn't have built-in TDE Options:
- Use filesystem encryption (LUKS) ✅
- Use pgcrypto extension for column-level encryption
- Wait for future PostgreSQL TDE support
Use commercial solutions (EDB, Cybertec)
4.3. Column-level encryption
-- Install pgcrypto CREATE EXTENSION IF NOT EXISTS pgcrypto;-- Encrypt data CREATE TABLE sensitive_data ( id SERIAL PRIMARY KEY, name TEXT, ssn BYTEA -- Encrypted );
-- Insert encrypted data INSERT INTO sensitive_data (name, ssn) VALUES ('John Doe', pgp_sym_encrypt('123-45-6789', 'encryption_key'));
-- Decrypt data SELECT id, name, pgp_sym_decrypt(ssn, 'encryption_key') AS ssn FROM sensitive_data;
-- Better: Store encryption key in vault (HashiCorp Vault)
5. Audit Logging
5.1. pgAudit extension_
# Install pgAudit sudo apt-get install -y postgresql-18-pgauditOr compile from source
cd /tmp git clone https://github.com/pgaudit/pgaudit.git cd pgaudit make install USE_PGXS=1
-- Enable pgAudit ALTER SYSTEM SET shared_preload_libraries = 'pgaudit';
-- Restart required
sudo systemctl restart patroni
-- Create extension CREATE EXTENSION IF NOT EXISTS pgaudit;-- Configure audit logging ALTER SYSTEM SET pgaudit.log = 'write, ddl, role'; ALTER SYSTEM SET pgaudit.log_catalog = off; ALTER SYSTEM SET pgaudit.log_client = on; ALTER SYSTEM SET pgaudit.log_level = 'log'; ALTER SYSTEM SET pgaudit.log_parameter = on; ALTER SYSTEM SET pgaudit.log_relation = on; ALTER SYSTEM SET pgaudit.log_statement_once = off;
-- Reload SELECT pg_reload_conf();
Audit classes:
READ: SELECT, COPY FROMWRITE: INSERT, UPDATE, DELETE, TRUNCATE, COPY TOFUNCTION: Function callsROLE: GRANT, REVOKE, CREATE/DROP ROLEDDL: CREATE, ALTER, DROPMISC: DISCARD, FETCH, CHECKPOINT, VACUUM, SETALL: Everything
5.2. Log analysis
# Audit logs in PostgreSQL log files sudo grep "AUDIT:" /var/lib/postgresql/18/data/log/postgresql-*.logExample audit log entry:
2024-11-25 10:00:00 UTC [12345]: [1-1] user=admin,db=myapp AUDIT: SESSION,1,1,WRITE,DELETE,TABLE,public.users,"DELETE FROM users WHERE id = 123",<not logged>
5.3. Centralized logging
# Use filebeat to ship logs to ELK stack/etc/filebeat/filebeat.yml
filebeat.inputs:
- type: log enabled: true paths:
- /var/lib/postgresql/18/data/log/*.log fields: type: postgresql cluster: postgres-ha node: node1
output.elasticsearch: hosts: ["elasticsearch:9200"] index: "postgresql-%{+yyyy.MM.dd}"
6. Security Hardening
6.1. Disable unnecessary features_
-- Disable file system access functions (for non-superusers) REVOKE EXECUTE ON FUNCTION pg_read_file(text) FROM PUBLIC; REVOKE EXECUTE ON FUNCTION pg_read_binary_file(text) FROM PUBLIC; REVOKE EXECUTE ON FUNCTION pg_ls_dir(text) FROM PUBLIC;
-- Disable COPY TO/FROM file -- (Use COPY TO/FROM PROGRAM or stdin/stdout instead)
6.2. Limit superuser access
-- Create admin role with specific privileges instead of superuser CREATE ROLE db_admin WITH LOGIN PASSWORD 'strong_password'; GRANT ALL PRIVILEGES ON DATABASE myapp TO db_admin; GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO db_admin; GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO db_admin;
-- Don't use 'postgres' superuser for applications!
6.3. Resource limits
-- Connection limits per user ALTER USER app_user CONNECTION LIMIT 50;-- Statement timeout per user ALTER USER app_user SET statement_timeout = '30s';
-- Lock timeout ALTER USER app_user SET lock_timeout = '5s';
-- Idle in transaction timeout ALTER USER app_user SET idle_in_transaction_session_timeout = '5min';
6.4. Hide PostgreSQL version
-- Change server_version_num (cosmetic security)
-- Note: Doesn't actually hide from determined attackers
ALTER SYSTEM SET application_name = 'myapp';
6.5. Secure replication
# In patroni.yml postgresql: authentication: replication: username: replicator password: !vault | # Use Ansible Vault $ANSIBLE_VAULT;1.1;AES256 ...encrypted...superuser: username: postgres password: !vault | $ANSIBLE_VAULT;1.1;AES256 ...encrypted...
7. Compliance and Standards
7.1. PCI DSS compliance
Requirements for PostgreSQL:
✅ Encryption in transit (SSL/TLS)
✅ Encryption at rest (LUKS)
✅ Strong authentication (SCRAM-SHA-256)
✅ Access control (RBAC)
✅ Audit logging (pgAudit)
✅ Regular updates and patches
✅ Network segmentation
✅ Principle of least privilege
7.2. GDPR compliance_
-- Data anonymization CREATE TABLE users_anonymized AS SELECT id, md5(name) AS name, md5(email) AS email, age, country FROM users;
-- Right to be forgotten DELETE FROM users WHERE id = 12345; DELETE FROM user_logs WHERE user_id = 12345; DELETE FROM user_sessions WHERE user_id = 12345;
7.3. HIPAA compliance
Requirements:
✅ PHI encryption at rest and in transit
✅ Access controls and authentication
✅ Audit logs (who accessed what, when)
✅ Automatic logoff (idle_in_transaction_session_timeout)
✅ Unique user identification
✅ Emergency access procedures
✅ Data backup and recovery
8. Security Monitoring
8.1. Monitor failed login attempts
-- Query for failed authentication SELECT datname, usename, client_addr, COUNT() as failed_attempts FROM pg_stat_database_conflicts GROUP BY datname, usename, client_addr HAVING COUNT() > 10;
-- Or from logs -- sudo grep "authentication failed" /var/lib/postgresql/18/data/log/*.log
8.2. Monitor privilege escalation
-- Track role changes SELECT * FROM pg_stat_activity WHERE query ILIKE '%GRANT%' OR query ILIKE '%REVOKE%';
-- Audit log will capture these with pgAudit
8.3. Alert on security events
# Prometheus alert rules
groups:
name: security rules:-
alert: FailedAuthentication expr: increase(pg_stat_database_conflicts_total[5m]) > 10 labels: severity: warning annotations: summary: "Multiple failed authentication attempts"
alert: UnencryptedConnection expr: pg_stat_ssl_count{ssl="false"} > 0 labels: severity: warning annotations: summary: "Unencrypted connection detected"
-
9. Best Practices Summary
✅ DO
- Always use SSL/TLS - Encrypt all connections
- Use SCRAM-SHA-256 - Strong password encryption
- Implement RBAC - Principle of least privilege
- Enable audit logging - pgAudit for compliance_
- Encrypt at rest - LUKS for data volumes
- Strong passwords - 12+ chars, complexity
- Network segmentation - Firewall rules
- Regular security audits - Quarterly reviews
- Keep software updated - Security patches
- Monitor security events - Alerts and logging
❌ DON'T_
- Don't use 'trust' auth_ - Always require password_
- Don't expose to internet - Use private networks
- Don't use default passwords_ - Change immediately_
- Don't superuser - Use specific privileges
- Don't ignore updates - Security vulnerabilities
- Don't store passwords plain - Use secrets management
- Don't skip backups - Ransomware protection
- Don't forget about etcd security - It has cluster secrets_
10. Security Checklist
Network Layer: ☐ Firewall configured (only necessary ports) ☐ Private network for inter-node communication ☐ VPN for remote access ☐ DDoS protectionAuthentication: ☐ SSL/TLS enabled ☐ SCRAM-SHA-256 password encryption ☐ Client certificate authentication (optional) ☐ Strong password policy ☐ No default passwords ☐ Separate users for different applications
Authorization: ☐ RBAC implemented ☐ Least privilege principle ☐ Superuser access limited ☐ Resource limits set
Encryption: ☐ SSL/TLS for connections ☐ File system encryption (LUKS) ☐ Column-level encryption for sensitive data ☐ Backup encryption
Auditing: ☐ pgAudit enabled ☐ Audit logs centralized ☐ Log retention policy ☐ Regular log reviews
Monitoring: ☐ Failed login attempts tracked ☐ Security alerts configured ☐ Anomaly detection ☐ Regular vulnerability scans
Maintenance: ☐ Regular security updates ☐ Patch management process ☐ Quarterly security audits ☐ Incident response plan ☐ Disaster recovery tested
11. Lab Exercises
Lab 1: Setup SSL/TLS
Tasks:
- Generate SSL certificates
- Configure PostgreSQL for SSL
- Update pg_hba.conf to require SSL
- Test SSL connection
- Verify with pg_stat_ssl
Lab 2: Implement RBAC
Tasks:
- Create readonly and readwrite roles
- Create users with different roles_
- Test access permissions
- Grant/revoke privileges_
- Document role hierarchy_
Lab 3: Enable audit logging
Tasks:
- Install and configure pgAudit
- Enable audit logging for DDL and WRITE
- Perform audited operations
- Review audit logs_
- Integrate with log aggregation_
Lab 4: Security hardening
Tasks:
- Configure firewall rules_
- Disable unnecessary functions
- Set resource limits
- Test security improvements
- Document security posture
12. Summary_
Security Layers_
1. Network: Firewall, VPN, segmentation
2. Authentication: SCRAM-SHA-256, SSL certificates
3. Authorization: RBAC, least privilege
4. Encryption: SSL/TLS, LUKS, pgcrypto
5. Auditing: pgAudit, centralized logging
6. Monitoring: Alerts, anomaly detection
Critical Security Settings_
-- SSL ssl = on-- Authentication password_encryption = 'scram-sha-256'
-- Audit shared_preload_libraries = 'pgaudit' pgaudit.log = 'write, ddl, role'
-- Limits statement_timeout = '30s' idle_in_transaction_session_timeout = '5min'
Next Steps
Lesson 21 will cover _Multi-datacenter Setup:
- Cross-DC replication strategies
- Cascading replication
- Disaster recovery planning
- Geographic load balancing
- Network latency considerations