Bài 20: Security Best Practices

Mục tiêu

Sau bài học này, bạn sẽ:

• Implement SSL/TLS encryption cho PostgreSQL
• Configure secure authentication
• Harden network security
• Enable encryption at rest
• Setup audit logging
• Apply security best practices cho HA cluster

1. SSL/TLS Encryption

1.1. Generate SSL certificates

# Create certificate directory
sudo mkdir -p /etc/postgresql/ssl
cd /etc/postgresql/ssl

# Generate 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.pem

# Set 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.conf

# Require 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.pem

# Copy 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 cert

# Connect with client cert
psql "host=10.0.1.11 dbname=postgres user=app_user \
  sslmode=verify-full \
  sslcert=~/.postgresql/client-cert.pem \
  sslkey=~/.postgresql/client-key.pem \
  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 outgoing

# Allow 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 iptables
# Allow 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 communication
# Install 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_encrypted

# Create 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:
1. Use filesystem encryption (LUKS) ✅
2. Use pgcrypto extension for column-level encryption
3. Wait for future PostgreSQL TDE support
4. 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-pgaudit

# Or 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 FROM
• WRITE: INSERT, UPDATE, DELETE, TRUNCATE, COPY TO
• FUNCTION: Function calls
• ROLE: GRANT, REVOKE, CREATE/DROP ROLE
• DDL: CREATE, ALTER, DROP
• MISC: DISCARD, FETCH, CHECKPOINT, VACUUM, SET
• ALL: Everything

5.2. Log analysis

# Audit logs in PostgreSQL log files
sudo grep "AUDIT:" /var/lib/postgresql/18/data/log/postgresql-*.log

# Example 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

1. Always use SSL/TLS - Encrypt all connections
2. Use SCRAM-SHA-256 - Strong password encryption
3. Implement RBAC - Principle of least privilege
4. Enable audit logging - pgAudit for compliance
5. Encrypt at rest - LUKS for data volumes
6. Strong passwords - 12+ chars, complexity
7. Network segmentation - Firewall rules
8. Regular security audits - Quarterly reviews
9. Keep software updated - Security patches
10. Monitor security events - Alerts and logging

❌ DON'T

1. Don't use 'trust' auth - Always require password
2. Don't expose to internet - Use private networks
3. Don't use default passwords - Change immediately
4. Don't grant superuser - Use specific privileges
5. Don't ignore updates - Security vulnerabilities
6. Don't store passwords plain - Use secrets management
7. Don't skip backups - Ransomware protection
8. 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 protection

Authentication:
☐ 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:

1. Generate SSL certificates
2. Configure PostgreSQL for SSL
3. Update pg_hba.conf to require SSL
4. Test SSL connection
5. Verify with pg_stat_ssl

Lab 2: Implement RBAC

Tasks:

1. Create readonly and readwrite roles
2. Create users with different roles
3. Test access permissions
4. Grant/revoke privileges
5. Document role hierarchy

Lab 3: Enable audit logging

Tasks:

1. Install and configure pgAudit
2. Enable audit logging for DDL and WRITE
3. Perform audited operations
4. Review audit logs
5. Integrate with log aggregation

Lab 4: Security hardening

Tasks:

1. Configure firewall rules
2. Disable unnecessary functions
3. Set resource limits
4. Test security improvements
5. Document security posture

12. Tổng kết

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

Bài 21 sẽ cover Multi-datacenter Setup:

• Cross-DC replication strategies
• Cascading replication
• Disaster recovery planning
• Geographic load balancing
• Network latency considerations