PostgreSQL 17 SSL Encryption & Security Hardening

Set up SSL/TLS encryption, client certificate authentication, and comprehensive security policies for PostgreSQL 17. Includes audit logging, access controls, and firewall configuration for production environments.

Prerequisites

PostgreSQL 17 installed
Root or sudo access
Basic understanding of SSL certificates
Network access to PostgreSQL port

What this solves

PostgreSQL 17 ships with basic security settings that aren't suitable for production environments. This tutorial configures SSL/TLS encryption for data in transit, implements client certificate authentication, sets up comprehensive audit logging, and hardens database access controls. You'll also configure firewall rules and connection security to protect against unauthorized access and data breaches.

Step-by-step configuration

Update system packages

Start by updating your system to ensure you have the latest security patches and PostgreSQL version.

sudo apt update && sudo apt upgrade -y
sudo apt install -y postgresql-17 postgresql-client-17 openssl

sudo dnf update -y
sudo dnf install -y postgresql17-server postgresql17 openssl

Generate SSL certificates for PostgreSQL

Create a Certificate Authority and server certificates for SSL encryption. This enables encrypted connections between clients and the database.

sudo mkdir -p /var/lib/postgresql/ssl
sudo chown postgres:postgres /var/lib/postgresql/ssl
sudo chmod 700 /var/lib/postgresql/ssl

Generate the Certificate Authority private key and certificate:

sudo -u postgres openssl genrsa -out /var/lib/postgresql/ssl/ca-key.pem 4096
sudo -u postgres openssl req -new -x509 -key /var/lib/postgresql/ssl/ca-key.pem -days 3650 -out /var/lib/postgresql/ssl/ca-cert.pem -subj "/C=US/ST=State/L=City/O=Organization/CN=PostgreSQL-CA"

Create the server private key and certificate signing request:

sudo -u postgres openssl genrsa -out /var/lib/postgresql/ssl/server-key.pem 2048
sudo -u postgres openssl req -new -key /var/lib/postgresql/ssl/server-key.pem -out /var/lib/postgresql/ssl/server-req.pem -subj "/C=US/ST=State/L=City/O=Organization/CN=example.com"

Sign the server certificate with the CA:

sudo -u postgres openssl x509 -req -in /var/lib/postgresql/ssl/server-req.pem -days 365 -CA /var/lib/postgresql/ssl/ca-cert.pem -CAkey /var/lib/postgresql/ssl/ca-key.pem -out /var/lib/postgresql/ssl/server-cert.pem -CAcreateserial

Set correct permissions on certificate files:

sudo -u postgres chmod 600 /var/lib/postgresql/ssl/server-key.pem /var/lib/postgresql/ssl/ca-key.pem
sudo -u postgres chmod 644 /var/lib/postgresql/ssl/server-cert.pem /var/lib/postgresql/ssl/ca-cert.pem

Configure PostgreSQL SSL settings

Edit the main PostgreSQL configuration file to enable SSL and configure encryption settings.

# Connection Settings
listen_addresses = 'localhost,203.0.113.10'
port = 5432
max_connections = 200

# SSL Configuration
ssl = on
ssl_cert_file = '/var/lib/postgresql/ssl/server-cert.pem'
ssl_key_file = '/var/lib/postgresql/ssl/server-key.pem'
ssl_ca_file = '/var/lib/postgresql/ssl/ca-cert.pem'
ssl_ciphers = 'ECDHE-RSA-AES256-GCM-SHA384:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-RSA-AES256-SHA384'
ssl_prefer_server_ciphers = on
ssl_min_protocol_version = 'TLSv1.2'

# Security Settings
password_encryption = 'scram-sha-256'
shared_preload_libraries = 'pg_stat_statements,pgaudit'

# Logging Configuration
log_destination = 'stderr'
logging_collector = on
log_directory = '/var/log/postgresql'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = 100MB
log_min_messages = warning
log_min_error_statement = error
log_connections = on
log_disconnections = on
log_statement = 'ddl'
log_line_prefix = '%t [%p]: user=%u,db=%d,client=%h '

# Performance and Security
work_mem = 16MB
shared_buffers = 256MB
effective_cache_size = 1GB
max_wal_size = 2GB
min_wal_size = 1GB
wal_level = replica
archive_mode = on
archive_command = '/bin/true'

Configure client authentication with SSL

Set up host-based authentication rules to require SSL connections and implement certificate-based authentication.

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# Local connections
local   all             postgres                                peer
local   all             all                                     scram-sha-256

# IPv4 SSL connections with certificate authentication
hostssl all             all             127.0.0.1/32            scram-sha-256 clientcert=verify-ca
hostssl all             all             203.0.113.0/24          scram-sha-256 clientcert=verify-ca
hostssl replication     replicator      203.0.113.0/24          scram-sha-256 clientcert=verify-ca

# IPv6 SSL connections
hostssl all             all             ::1/128                 scram-sha-256 clientcert=verify-ca

# Reject non-SSL connections from network
host    all             all             0.0.0.0/0               reject
host    all             all             ::/0                    reject

Install and configure pgAudit for audit logging

Enable comprehensive audit logging to track database access and modifications for security compliance.

sudo apt install -y postgresql-17-pgaudit

sudo dnf install -y postgresql17-contrib

Add pgAudit configuration to postgresql.conf:

sudo -u postgres echo "# pgAudit Configuration" >> /var/lib/postgresql/17/main/postgresql.conf
sudo -u postgres echo "pgaudit.log = 'all'" >> /var/lib/postgresql/17/main/postgresql.conf
sudo -u postgres echo "pgaudit.log_catalog = off" >> /var/lib/postgresql/17/main/postgresql.conf
sudo -u postgres echo "pgaudit.log_parameter = on" >> /var/lib/postgresql/17/main/postgresql.conf
sudo -u postgres echo "pgaudit.log_statement_once = off" >> /var/lib/postgresql/17/main/postgresql.conf
sudo -u postgres echo "pgaudit.log_level = log" >> /var/lib/postgresql/17/main/postgresql.conf

Create secure database users with limited privileges

Set up role-based access control with minimal required permissions instead of using superuser accounts.

sudo systemctl restart postgresql
sudo -u postgres psql

Create the pgAudit extension and configure users:

-- Create pgAudit extension
CREATE EXTENSION IF NOT EXISTS pgaudit;

-- Create application database
CREATE DATABASE appdb WITH ENCODING 'UTF8' LC_COLLATE='en_US.UTF-8' LC_CTYPE='en_US.UTF-8';

-- Create roles with specific privileges
CREATE ROLE app_read_role;
CREATE ROLE app_write_role;
CREATE ROLE app_admin_role;

-- Grant privileges to roles
GRANT CONNECT ON DATABASE appdb TO app_read_role;
GRANT USAGE ON SCHEMA public TO app_read_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_read_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO app_read_role;

GRANT app_read_role TO app_write_role;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_write_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT INSERT, UPDATE, DELETE ON TABLES TO app_write_role;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_write_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO app_write_role;

GRANT app_write_role TO app_admin_role;
GRANT CREATE ON SCHEMA public TO app_admin_role;
GRANT CREATE ON DATABASE appdb TO app_admin_role;

-- Create users with strong passwords
CREATE USER appuser WITH PASSWORD 'K8mR3$nP9x7wQ2vF' IN ROLE app_write_role;
CREATE USER appadmin WITH PASSWORD 'L9nS4%pQ8y6xR3wG' IN ROLE app_admin_role;
CREATE USER readonly WITH PASSWORD 'M0oT5&qR7z5yS4xH' IN ROLE app_read_role;

-- Create replication user for streaming replication
CREATE USER replicator WITH REPLICATION PASSWORD 'N1pU6*rS8a4zT5yJ' IN ROLE pg_monitor;

-- Set connection limits
ALTER USER appuser CONNECTION LIMIT 50;
ALTER USER appadmin CONNECTION LIMIT 10;
ALTER USER readonly CONNECTION LIMIT 20;
ALTER USER replicator CONNECTION LIMIT 5;

\q

Generate client certificates for users

Create client certificates for secure certificate-based authentication instead of password-only access.

cd /var/lib/postgresql/ssl

# Generate client certificates for each user
for user in appuser appadmin readonly replicator; do
    sudo -u postgres openssl genrsa -out ${user}-key.pem 2048
    sudo -u postgres openssl req -new -key ${user}-key.pem -out ${user}-req.pem -subj "/C=US/ST=State/L=City/O=Organization/CN=${user}"
    sudo -u postgres openssl x509 -req -in ${user}-req.pem -days 365 -CA ca-cert.pem -CAkey ca-key.pem -out ${user}-cert.pem -CAcreateserial
    sudo -u postgres chmod 600 ${user}-key.pem
    sudo -u postgres chmod 644 ${user}-cert.pem
    sudo -u postgres rm ${user}-req.pem
done

Copy client certificates to a secure location accessible by applications:

sudo mkdir -p /etc/postgresql/client-certs
sudo cp /var/lib/postgresql/ssl/{appuser,appadmin,readonly,replicator}-{cert,key}.pem /etc/postgresql/client-certs/
sudo cp /var/lib/postgresql/ssl/ca-cert.pem /etc/postgresql/client-certs/
sudo chown -R root:postgres /etc/postgresql/client-certs
sudo chmod -R 640 /etc/postgresql/client-certs/*.pem

Configure firewall rules

Set up restrictive firewall rules that only allow PostgreSQL connections from trusted networks and block unauthorized access attempts.

# Install and enable UFW
sudo apt install -y ufw
sudo ufw --force enable

# Default policies
sudo ufw default deny incoming
sudo ufw default allow outgoing

# Allow SSH (adjust port as needed)
sudo ufw allow 22/tcp

# Allow PostgreSQL only from trusted networks
sudo ufw allow from 203.0.113.0/24 to any port 5432 proto tcp
sudo ufw allow from 127.0.0.1 to any port 5432 proto tcp

# Enable logging for security monitoring
sudo ufw logging on

# Show status
sudo ufw status verbose

# Configure firewalld
sudo systemctl enable --now firewalld

# Create custom zone for PostgreSQL
sudo firewall-cmd --permanent --new-zone=postgresql
sudo firewall-cmd --permanent --zone=postgresql --set-target=DROP

# Allow PostgreSQL from trusted networks
sudo firewall-cmd --permanent --zone=postgresql --add-source=203.0.113.0/24
sudo firewall-cmd --permanent --zone=postgresql --add-source=127.0.0.1/32
sudo firewall-cmd --permanent --zone=postgresql --add-port=5432/tcp

# Apply changes
sudo firewall-cmd --reload

# Show configuration
sudo firewall-cmd --list-all-zones

Configure connection security and resource limits

Set up additional security parameters to prevent connection flooding and enforce resource limits.

sudo -u postgres cat >> /var/lib/postgresql/17/main/postgresql.conf << 'EOF'

# Connection Security
tcp_keepalives_idle = 600
tcp_keepalives_interval = 30
tcp_keepalives_count = 3
authentication_timeout = 30s
idle_in_transaction_session_timeout = 300s
lock_timeout = 30s
statement_timeout = 600s

# Resource Limits
max_files_per_process = 1000
max_locks_per_transaction = 64
max_pred_locks_per_transaction = 64
max_prepared_transactions = 0

# Security
row_security = on
zero_damaged_pages = off
ignore_system_indexes = off
EOF

Set up log rotation and monitoring

Configure automatic log rotation to prevent disk space issues and enable security event monitoring.

sudo mkdir -p /var/log/postgresql
sudo chown postgres:postgres /var/log/postgresql
sudo chmod 750 /var/log/postgresql

/var/log/postgresql/*.log {
    daily
    missingok
    rotate 30
    compress
    delaycompress
    notifempty
    create 640 postgres postgres
    postrotate
        /usr/bin/killall -HUP postgres 2>/dev/null || true
    endscript
}

Create a monitoring script for failed authentication attempts:

#!/bin/bash

# PostgreSQL security monitoring script
LOGFILE="/var/log/postgresql/postgresql-$(date +%Y-%m-%d)*.log"
ALERT_EMAIL="admin@example.com"
THRESHOLD=5

# Check for failed authentication attempts
FAILED_AUTHS=$(grep -c "FATAL.*authentication failed" $LOGFILE 2>/dev/null || echo 0)

if [ "$FAILED_AUTHS" -gt "$THRESHOLD" ]; then
    echo "WARNING: $FAILED_AUTHS failed PostgreSQL authentication attempts detected" | \
    mail -s "PostgreSQL Security Alert - $(hostname)" $ALERT_EMAIL
fi

# Check for SSL connection failures
SSL_FAILURES=$(grep -c "could not accept SSL connection" $LOGFILE 2>/dev/null || echo 0)

if [ "$SSL_FAILURES" -gt "$THRESHOLD" ]; then
    echo "WARNING: $SSL_FAILURES SSL connection failures detected" | \
    mail -s "PostgreSQL SSL Alert - $(hostname)" $ALERT_EMAIL
fi

sudo chmod 755 /usr/local/bin/pg-security-monitor.sh

# Add to crontab for hourly monitoring
echo "0 * * * * /usr/local/bin/pg-security-monitor.sh" | sudo crontab -u postgres -

Restart and enable PostgreSQL

Apply all configuration changes and enable PostgreSQL to start automatically on boot.

sudo systemctl restart postgresql
sudo systemctl enable postgresql
sudo systemctl status postgresql

Verify your setup

Test SSL connections and verify security configurations are working properly.

# Test SSL connection with certificate
psql "host=localhost port=5432 dbname=appdb user=appuser sslmode=require sslcert=/etc/postgresql/client-certs/appuser-cert.pem sslkey=/etc/postgresql/client-certs/appuser-key.pem sslrootcert=/etc/postgresql/client-certs/ca-cert.pem" -c "SELECT version();"

# Check SSL cipher and protocol version
psql "host=localhost port=5432 dbname=appdb user=readonly sslmode=require sslcert=/etc/postgresql/client-certs/readonly-cert.pem sslkey=/etc/postgresql/client-certs/readonly-key.pem sslrootcert=/etc/postgresql/client-certs/ca-cert.pem" -c "SELECT ssl_version(), ssl_cipher();"

# Verify audit logging is working
sudo -u postgres psql -c "CREATE TABLE test_audit (id serial, data text);"
sudo tail -n 10 /var/log/postgresql/postgresql-$(date +%Y-%m-%d)*.log | grep AUDIT

# Test connection limits and authentication
psql "host=localhost port=5432 dbname=appdb user=appuser sslmode=disable" -c "SELECT 1;"  # Should fail

# Check active connections and SSL status
sudo -u postgres psql -c "SELECT datname, usename, client_addr, ssl, ssl_version, ssl_cipher FROM pg_stat_ssl JOIN pg_stat_activity USING (pid);"

# Verify firewall rules
sudo ufw status numbered  # Ubuntu/Debian
# OR
sudo firewall-cmd --list-all  # AlmaLinux/Rocky

Connection string format: Use this format for applications:

postgresql://username:password@hostname:5432/database?sslmode=require&sslcert=path/to/client-cert.pem&sslkey=path/to/client-key.pem&sslrootcert=path/to/ca-cert.pem

Common issues

Symptom	Cause	Fix
SSL connection refused	Certificate file permissions or paths incorrect	Check `ls -la /var/lib/postgresql/ssl/` and ensure postgres user owns files with correct permissions
Authentication failed with certificate	pg_hba.conf not configured for clientcert authentication	Ensure `clientcert=verify-ca` is set in hostssl entries
Connection timeout from remote hosts	Firewall blocking connections	Check firewall rules and ensure trusted networks are allowed
PostgreSQL won't start after SSL config	SSL certificate files not found or invalid	Check PostgreSQL logs: `sudo journalctl -u postgresql -n 50`
pgAudit logs not appearing	Extension not created or shared_preload_libraries not set	Restart PostgreSQL after adding pgaudit to shared_preload_libraries
Client certificate verification fails	CA certificate not matching or client cert not signed by CA	Regenerate client certificate with correct CA: `openssl x509 -req -in client-req.pem -CA ca-cert.pem -CAkey ca-key.pem`

Next steps

Set up PostgreSQL streaming replication with PgBouncer connection pooling for high availability
Monitor PostgreSQL performance with Prometheus and Grafana for comprehensive observability
Configure automated PostgreSQL backups with pgBackRest for disaster recovery
Implement PostgreSQL connection pooling with PgBouncer SSL for encrypted pooling

Running this in production?

Need this managed? Setting this up once is straightforward. Keeping it patched, monitored, backed up and performant across environments is the harder part. See how we run infrastructure like this for European teams.

Automated install script

Run this to automate the entire setup

install.sh

#!/usr/bin/env bash

set -euo pipefail

# Colors for output
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
NC='\033[0m'

# Default values
POSTGRES_IP="${1:-localhost}"
DOMAIN_NAME="${2:-$(hostname -f 2>/dev/null || echo 'localhost')}"
COUNTRY="${3:-US}"
STATE="${4:-State}"
CITY="${5:-City}"
ORG="${6:-Organization}"

# Usage function
usage() {
    echo "Usage: $0 [postgres_ip] [domain_name] [country] [state] [city] [organization]"
    echo "Example: $0 192.168.1.100 db.example.com US California 'San Francisco' 'My Company'"
    exit 1
}

# Cleanup function for rollback
cleanup() {
    echo -e "${RED}[ERROR] Installation failed. Cleaning up...${NC}"
    systemctl stop postgresql 2>/dev/null || true
    rm -rf /var/lib/postgresql/ssl 2>/dev/null || true
}

trap cleanup ERR

# Check if running as root
if [[ $EUID -ne 0 ]]; then
    echo -e "${RED}This script must be run as root${NC}"
    exit 1
fi

# Detect distribution
echo -e "${YELLOW}[1/8] Detecting distribution...${NC}"
if [ -f /etc/os-release ]; then
    . /etc/os-release
    case "$ID" in
        ubuntu|debian) 
            PKG_MGR="apt"
            PKG_UPDATE="apt update && apt upgrade -y"
            PKG_INSTALL="apt install -y"
            PG_VERSION="17"
            PG_CONFIG_DIR="/etc/postgresql/17/main"
            PG_DATA_DIR="/var/lib/postgresql/17/main"
            PG_SERVICE="postgresql"
            FIREWALL_CMD="ufw"
            ;;
        almalinux|rocky|centos|rhel|ol)
            PKG_MGR="dnf"
            PKG_UPDATE="dnf update -y"
            PKG_INSTALL="dnf install -y"
            PG_VERSION="17"
            PG_CONFIG_DIR="/var/lib/pgsql/17/data"
            PG_DATA_DIR="/var/lib/pgsql/17/data"
            PG_SERVICE="postgresql-17"
            FIREWALL_CMD="firewall-cmd"
            ;;
        fedora)
            PKG_MGR="dnf"
            PKG_UPDATE="dnf update -y"
            PKG_INSTALL="dnf install -y"
            PG_VERSION="17"
            PG_CONFIG_DIR="/var/lib/pgsql/data"
            PG_DATA_DIR="/var/lib/pgsql/data"
            PG_SERVICE="postgresql"
            FIREWALL_CMD="firewall-cmd"
            ;;
        amzn)
            PKG_MGR="yum"
            PKG_UPDATE="yum update -y"
            PKG_INSTALL="yum install -y"
            PG_VERSION="17"
            PG_CONFIG_DIR="/var/lib/pgsql/17/data"
            PG_DATA_DIR="/var/lib/pgsql/17/data"
            PG_SERVICE="postgresql-17"
            FIREWALL_CMD="firewall-cmd"
            ;;
        *)
            echo -e "${RED}Unsupported distribution: $ID${NC}"
            exit 1
            ;;
    esac
    echo -e "${GREEN}Detected: $PRETTY_NAME${NC}"
else
    echo -e "${RED}Cannot detect distribution${NC}"
    exit 1
fi

# Update system and install packages
echo -e "${YELLOW}[2/8] Updating system and installing PostgreSQL...${NC}"
$PKG_UPDATE

if [[ "$ID" == "ubuntu" || "$ID" == "debian" ]]; then
    $PKG_INSTALL postgresql-17 postgresql-client-17 openssl
else
    # Install PostgreSQL repository for RHEL-based systems
    $PKG_INSTALL https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm 2>/dev/null || true
    $PKG_INSTALL postgresql17-server postgresql17 openssl
    
    # Initialize database if needed
    if [ ! -f "$PG_DATA_DIR/postgresql.conf" ]; then
        /usr/pgsql-17/bin/postgresql-17-setup initdb
    fi
fi

# Create SSL directory
echo -e "${YELLOW}[3/8] Setting up SSL certificates...${NC}"
mkdir -p /var/lib/postgresql/ssl
chown postgres:postgres /var/lib/postgresql/ssl
chmod 700 /var/lib/postgresql/ssl

# Generate CA private key and certificate
sudo -u postgres openssl genrsa -out /var/lib/postgresql/ssl/ca-key.pem 4096
sudo -u postgres openssl req -new -x509 -key /var/lib/postgresql/ssl/ca-key.pem -days 3650 \
    -out /var/lib/postgresql/ssl/ca-cert.pem \
    -subj "/C=$COUNTRY/ST=$STATE/L=$CITY/O=$ORG/CN=PostgreSQL-CA"

# Generate server private key and certificate
sudo -u postgres openssl genrsa -out /var/lib/postgresql/ssl/server-key.pem 2048
sudo -u postgres openssl req -new -key /var/lib/postgresql/ssl/server-key.pem \
    -out /var/lib/postgresql/ssl/server-req.pem \
    -subj "/C=$COUNTRY/ST=$STATE/L=$CITY/O=$ORG/CN=$DOMAIN_NAME"

# Sign server certificate
sudo -u postgres openssl x509 -req -in /var/lib/postgresql/ssl/server-req.pem -days 365 \
    -CA /var/lib/postgresql/ssl/ca-cert.pem -CAkey /var/lib/postgresql/ssl/ca-key.pem \
    -out /var/lib/postgresql/ssl/server-cert.pem -CAcreateserial

# Set certificate permissions
sudo -u postgres chmod 600 /var/lib/postgresql/ssl/server-key.pem /var/lib/postgresql/ssl/ca-key.pem
sudo -u postgres chmod 644 /var/lib/postgresql/ssl/server-cert.pem /var/lib/postgresql/ssl/ca-cert.pem

# Remove temporary files
sudo -u postgres rm -f /var/lib/postgresql/ssl/server-req.pem

# Configure PostgreSQL
echo -e "${YELLOW}[4/8] Configuring PostgreSQL settings...${NC}"
cat > "$PG_CONFIG_DIR/postgresql.conf" << EOF
# Connection Settings
listen_addresses = 'localhost,$POSTGRES_IP'
port = 5432
max_connections = 200

# SSL Configuration
ssl = on
ssl_cert_file = '/var/lib/postgresql/ssl/server-cert.pem'
ssl_key_file = '/var/lib/postgresql/ssl/server-key.pem'
ssl_ca_file = '/var/lib/postgresql/ssl/ca-cert.pem'
ssl_ciphers = 'ECDHE-RSA-AES256-GCM-SHA384:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-RSA-AES256-SHA384'
ssl_prefer_server_ciphers = on
ssl_min_protocol_version = 'TLSv1.2'

# Security Settings
password_encryption = 'scram-sha-256'
shared_preload_libraries = 'pg_stat_statements'

# Logging Configuration
log_destination = 'stderr'
logging_collector = on
log_directory = '/var/log/postgresql'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = 100MB
log_min_messages = warning
log_min_error_statement = error
log_connections = on
log_disconnections = on
log_statement = 'ddl'
log_line_prefix = '%t [%p]: user=%u,db=%d,client=%h '

# Performance and Security
work_mem = 16MB
shared_buffers = 256MB
effective_cache_size = 1GB
max_wal_size = 2GB
min_wal_size = 1GB
wal_level = replica
archive_mode = on
archive_command = '/bin/true'
EOF

# Configure client authentication
echo -e "${YELLOW}[5/8] Configuring client authentication...${NC}"
cat > "$PG_CONFIG_DIR/pg_hba.conf" << EOF
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# Local connections
local   all             postgres                                peer
local   all             all                                     scram-sha-256

# IPv4 connections with SSL
hostssl all             all             127.0.0.1/32            scram-sha-256
hostssl all             all             $POSTGRES_IP/32         scram-sha-256
host    all             all             127.0.0.1/32            reject
host    all             all             0.0.0.0/0               reject

# IPv6 connections
hostssl all             all             ::1/128                 scram-sha-256
host    all             all             ::1/128                 reject
EOF

# Create log directory
echo -e "${YELLOW}[6/8] Setting up logging...${NC}"
mkdir -p /var/log/postgresql
chown postgres:postgres /var/log/postgresql
chmod 750 /var/log/postgresql

# Configure firewall
echo -e "${YELLOW}[7/8] Configuring firewall...${NC}"
if command -v ufw >/dev/null 2>&1; then
    ufw --force enable
    ufw allow from any to any port 5432 proto tcp
elif command -v firewall-cmd >/dev/null 2>&1; then
    systemctl enable firewalld
    systemctl start firewalld
    firewall-cmd --permanent --add-port=5432/tcp
    firewall-cmd --reload
fi

# Start and enable PostgreSQL
systemctl enable $PG_SERVICE
systemctl restart $PG_SERVICE

# Verify installation
echo -e "${YELLOW}[8/8] Verifying installation...${NC}"
sleep 5

if systemctl is-active --quiet $PG_SERVICE; then
    echo -e "${GREEN}✓ PostgreSQL service is running${NC}"
else
    echo -e "${RED}✗ PostgreSQL service failed to start${NC}"
    exit 1
fi

if sudo -u postgres psql -c "SHOW ssl;" | grep -q "on"; then
    echo -e "${GREEN}✓ SSL is enabled${NC}"
else
    echo -e "${RED}✗ SSL is not enabled${NC}"
    exit 1
fi

if [ -f "/var/lib/postgresql/ssl/server-cert.pem" ]; then
    echo -e "${GREEN}✓ SSL certificates created${NC}"
else
    echo -e "${RED}✗ SSL certificates not found${NC}"
    exit 1
fi

echo -e "${GREEN}PostgreSQL 17 SSL configuration completed successfully!${NC}"
echo -e "${YELLOW}Next steps:${NC}"
echo "1. Create database users: sudo -u postgres createuser --interactive"
echo "2. Create databases: sudo -u postgres createdb dbname"
echo "3. Connect with SSL: psql 'postgresql://user@$POSTGRES_IP:5432/dbname?sslmode=require'"
echo "4. Client certificate is at: /var/lib/postgresql/ssl/ca-cert.pem"

Review the script before running. Execute with: bash install.sh

#postgresql #ssl #security #encryption #audit #authentication

Configure PostgreSQL 17 SSL encryption and advanced security hardening