PostgreSQL security depends on safe defaults in postgresql.conf, strict rules in pg_hba.conf, and disciplined role management. Updated for PostgreSQL 18 with current security best practices.
In postgresql.conf:
listen_addresses = '127.0.0.1,10.0.20.15' # Specific IPs only
port = 5432
listen_addresses = '*') unless absolutely necessary# Example iptables rules for PostgreSQL
sudo iptables -A INPUT -p tcp -s 10.0.20.0/24 --dport 5432 -j ACCEPT
sudo iptables -A INPUT -p tcp --dport 5432 -j DROP
# Or using ufw
sudo ufw allow from 10.0.20.0/24 to any port 5432
pg_hba.conf)Use explicit network rules and modern authentication:
# PostgreSQL Client Authentication Configuration File
# TYPE DATABASE USER ADDRESS METHOD
# Local admin access (peer authentication for system users)
local all postgres peer
local all all scram-sha-256
# SSL-only connections for applications
hostssl all app_user 10.0.20.0/24 scram-sha-256
hostssl all app_user 10.0.30.0/24 scram-sha-256
# IPv4 local connections (SSL preferred)
hostssl all all 127.0.0.1/32 scram-sha-256
# IPv6 local connections (SSL preferred)
hostssl all all ::1/128 scram-sha-256
# Replication connections (if needed)
hostssl replication repl_user 10.0.20.0/24 scram-sha-256
# Explicitly deny all other connections
host all all 0.0.0.0/0 reject
scram-sha-256 over md5 (available since PostgreSQL 10)hostssl for all remote connections to enforce encryptionhost entriestrust entries in productionreject as a final rule to deny all unmatched connectionspostgresql.conf)# SSL Configuration
ssl = on
ssl_cert_file = '/etc/ssl/certs/postgresql.crt'
ssl_key_file = '/etc/ssl/private/postgresql.key'
ssl_ca_file = '/etc/ssl/certs/ca.crt'
ssl_crl_file = '/etc/ssl/certs/ca.crl' # Certificate Revocation List
# Optional: Require client certificates
# ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL'
# ssl_prefer_server_ciphers = on
# Generate private key
openssl genrsa -out postgresql.key 2048
# Generate certificate signing request
openssl req -new -key postgresql.key -out postgresql.csr
# Generate self-signed certificate
openssl x509 -req -in postgresql.csr -signkey postgresql.key -out postgresql.crt -days 365
# Set proper permissions
chmod 600 postgresql.key
chmod 644 postgresql.crt
sudo chown postgres:postgres postgresql.key postgresql.crt
sudo mv postgresql.key postgresql.crt /etc/ssl/private/
# Connect with SSL enforcement
psql "host=localhost dbname=mydb user=myuser sslmode=require"
# SSL modes: disable, allow, prefer, require, verify-ca, verify-full
-- Create application role with minimal privileges
CREATE ROLE app_user WITH LOGIN PASSWORD 'secure_password';
GRANT CONNECT ON DATABASE app_db TO app_user;
\c app_db
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
GRANT SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA public TO app_user;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO app_user;
-- Revoke public schema creation rights
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
GRANT CREATE ON SCHEMA public TO admin_user; -- Specific admin role only
-- Create role hierarchy
CREATE ROLE db_admin;
CREATE ROLE app_user;
CREATE ROLE readonly_user;
-- Grant privileges to admin role
GRANT ALL PRIVILEGES ON DATABASE app_db 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;
-- Grant admin role to specific users
GRANT db_admin TO alice WITH ADMIN OPTION;
GRANT app_user TO bob;
GRANT readonly_user TO charlie;
-- Grant limited privileges to readonly role
GRANT CONNECT ON DATABASE app_db TO readonly_user;
\c app_db
GRANT USAGE ON SCHEMA public TO readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;
-- Check currently loaded extensions
SELECT * FROM pg_available_extensions;
-- Install extensions securely (only as superuser)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS postgis; -- if needed
-- Audit extension installations
SELECT name, default_version, installed_version FROM pg_available_extensions WHERE installed_version IS NOT NULL;
-- Check superuser accounts
SELECT rolname FROM pg_roles WHERE rolsuper = true;
-- Create non-superuser admin account with specific privileges
CREATE ROLE db_manager CREATEDB CREATEROLE;
GRANT ALL PRIVILEGES ON DATABASE app_db TO db_manager;
-- Enable row-level security on a table
ALTER TABLE employees ENABLE ROW LEVEL SECURITY;
-- Create policy to restrict access
CREATE POLICY employee_access_policy ON employees
FOR ALL TO app_user
USING (department_id = current_setting('app.current_dept')::int);
-- Set user context
SET app.current_dept = '42';
-- Create column with security barrier (PostgreSQL 13+)
-- Note: Security definer functions can be used to control access
CREATE OR REPLACE FUNCTION get_sensitive_data(user_id int)
RETURNS TABLE(ssn text, salary numeric) AS $$
BEGIN
-- Add authorization checks here
IF current_user = 'admin' OR user_id = current_app_user_id() THEN
RETURN QUERY SELECT e.ssn, e.salary FROM employees e WHERE e.id = user_id;
ELSE
RAISE insufficient_privilege;
END IF;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
# Security-focused logging in postgresql.conf
log_connections = on
log_disconnections = on
log_authentication_failures = on # PostgreSQL 14+ feature
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_min_duration_statement = 1000 # Log slow queries (>1s)
log_lock_waits = on # Log lock waits
log_statement = 'none' # Avoid logging sensitive data
log_temp_files = 0 # Log temporary files
log_error_verbosity = default # Set to 'verbose' for more detail if needed
-- Enable pgaudit extension for detailed audit logging
CREATE EXTENSION IF NOT EXISTS pgaudit;
-- Configure audit settings
ALTER SYSTEM SET pgaudit.log = 'write, ddl, role, read';
SELECT pg_reload_conf();
# Logical backup with encryption
pg_dump --format=custom --file=backup.dump --dbname=app_db
pg_dump --format=custom --file=backup.dump --dbname=app_db | gpg --encrypt --recipient admin@example.com > backup.dump.gpg
# Physical backup with encryption
tar -czf - /var/lib/postgresql/18/main | gpg --cipher-algo AES256 --compress-algo 1 --symmetric --output pg_backup.tar.gz.gpg
# Automated backup script with encryption
#!/bin/bash
BACKUP_DIR="/backups/postgres"
DATE=$(date +%Y%m%d_%H%M%S)
pg_dump --format=custom app_db | gpg --cipher-algo AES256 --compress-algo 1 --symmetric --output "${BACKUP_DIR}/app_db_${DATE}.dump.gpg"
# Use environment variables or external secrets management
# Instead of hardcoding passwords in scripts
# Example using external secrets manager
export PGPASSWORD=$(aws secretsmanager get-secret-value --secret-id postgres-password --query SecretString --output text)
# Or use .pgpass file with restricted permissions
chmod 600 ~/.pgpass
cat >> ~/.pgpass << EOF
hostname:port:database:username:password
EOF
# In compose.yaml
services:
postgres:
image: postgres:18
security_opt:
- no-new-privileges:true
read_only: true # If possible with proper volume mounts
tmpfs:
- /tmp
- /var/run/postgresql
user: "999:999" # Run as non-root user
cap_drop:
- ALL
cap_add:
- CHOWN
- SETUID
- SETGID
- DAC_OVERRIDE
#!/bin/bash
# PostgreSQL Security Audit Script
echo "=== PostgreSQL Security Audit ==="
# Check version
echo "PostgreSQL Version:"
sudo -u postgres psql -c "SELECT version();"
# Check superusers
echo "Superuser Accounts:"
sudo -u postgres psql -c "SELECT rolname FROM pg_roles WHERE rolsuper = true;"
# Check authentication methods
echo "Checking pg_hba.conf for insecure configurations..."
if sudo grep -q "all.*all.*trust" /etc/postgresql/18/main/pg_hba.conf; then
echo "WARNING: Found 'trust' authentication in pg_hba.conf"
else
echo "OK: No 'trust' authentication found"
fi
# Check for empty passwords
echo "Checking for roles with empty passwords..."
sudo -u postgres psql -c "SELECT rolname FROM pg_roles WHERE rolpassword = '' OR rolpassword IS NULL;"
# Check listen addresses
echo "Listen Addresses:"
sudo -u postgres psql -c "SHOW listen_addresses;"
echo "=== Audit Complete ==="
listen_addresses to specific IPsscram-sha-256 authentication exclusivelyCREATE on public schema from PUBLIC