Postgres Security

Step 1: Generate a Certificate Authority (CA) and CA Certificate

Generate CA Private Key:

openssl genrsa -out ca.key 2048

Generate CA Certificate:

openssl req -x509 -new -nodes -key ca.key -sha256 -days 3650 -out ca.crt -subj "/C=US/ST=State/L=City/O=My Company/CN=Postgres CA"

Step 2: Generate Server Key, CSR, and Server Certificate

Generate Server Private Key:

openssl genrsa -out server.key 2048

Create a CSR (Certificate Signing Request) for the Server:

openssl req -new -key server.key -out server.csr -config openssl.conf

Sign the Server CSR with CA Certificate to Generate Server Certificate:

openssl x509 -req -in server.csr -CA ca.crt -CAkey ca.key -CAcreateserial -out server.crt -days 365 -sha256 -extensions v3_req -extfile openssl.conf

Step 3: Generate Client Key and Client Certificate (Optional)

Step 2: Generate the Client Key and Certificate

openssl genrsa -out test.key 2048

openssl req -new -key test.key -out test.csr -subj "/C=US/ST=State/L=City/O=My Company/CN=test"

# Sign the CSR with the CA key to create the client certificate

openssl x509 -req -in test.csr -CA ca.crt -CAkey ca.key -CAcreateserial -out test.crt -days 365 -sha256

# Verify the generated certificate

openssl verify -CAfile ca.crt test.crt

On the Database Server (PostgreSQL Host)

Step 1: Copy the CA Certificate to the PostgreSQL Host

scp ca.crt root@db_server:/var/lib/pgsql/15/data/certs/

On clien hosts

# Copy the client key, certificate, and CA certificate to the client host

scp test.key client_user@client_host:/path/to/your/certs/

scp test.crt client_user@client_host:/path/to/your/certs/

scp ca.crt client_user@client_host:/path/to/your/certs/

/var/lib/pgsql/15/data/postgresql.conf

ssl = on

ssl_cert_file = '/var/lib/pgsql/15/data/certs/server.crt'

ssl_key_file = '/var/lib/pgsql/15/data/certs/server.key'

ssl_ca_file = '/var/lib/pgsql/15/data/certs/ca.crt'

/var/lib/pgsql/15/data/pg_hba.conf

hostnossl all all 0.0.0.0/0 reject

hostnossl all all ::/0 reject

#hostssl all all 0.0.0.0/0 cert clientcert=verify-full

#hostssl all all ::/0 cert clientcert=verify-full

#hostssl all all 0.0.0.0/0 scram-sha-256

#hostssl all all ::/0 scram-sha-256

hostssl all all 0.0.0.0/0 scram-sha-256 clientcert=verify-full

hostssl all all ::/0 scram-sha-256 clientcert=verify-full

psql "dbname=test user=test host=192.168.56.91 sslmode=verify-full sslrootcert=ca.crt sslcert=test.crt sslkey=test.key"