How to Connect Your App to PostgreSQL Securely

Prerequisites

• PostgreSQL 12+ installed (package locations may vary by distro; this guide assumes Debian/Ubuntu paths)
• Linux shell access with sudo privileges
• openssl command‑line tool (sudo apt install openssl)

Step 1 – Generate SSL Certificates with OpenSSL

OpenSSL is an open‑source cryptographic toolkit that supplies both the libraries most servers use to implement TLS and a versatile CLI utility. You can:

• Create RSA/ECDSA key pairs
• Generate Certificate Signing Requests (CSRs)
• Issue self‑signed certificates for internal testing
• Inspect and convert certificate formats (PEM, DER, PKCS#12)

For local or staging setups we’ll create a self‑signed cert. In production you would submit a CSR to a trusted CA (Let’s Encrypt, DigiCert, etc.).

Choose a Secure Directory for Keys

PostgreSQL runs as the postgres user. We’ll store keys under /etc/ssl/postgres (owned by postgres and not world‑readable). Keeping keys outside your application codebase prevents accidental commits and limits access to the database user only.

# create a dedicated directory for certs & keys
sudo mkdir -p /etc/ssl/postgres
sudo chown postgres:postgres /etc/ssl/postgres
sudo chmod 700 /etc/ssl/postgres

# generate the key + certificate (run as any sudo‑enabled user)
openssl req \
  -newkey rsa:4096      \
  -nodes                \
  -keyout /etc/ssl/postgres/server.key \
  -x509                 \
  -days 365             \
  -out    /etc/ssl/postgres/server.crt

Understanding the Certificate Prompt Fields

During the openssl req process you’ll be asked for:

Prompt	What It Means / Example
Country Name (2 letter code)	ISO‑3166 code: US, DE, IN
State or Province Name	Full state/province: New York
Locality Name	City or town: Brooklyn
Organization Name	Your company/legal entity: Acme Inc
Organizational Unit Name	Department: Engineering (optional)
Common Name (CN)	Critical: must exactly match the hostname clients use (e.g., db.example.com). For IP‑based access use the IP.
Email Address	Contact email for certificate owner: admin@example.com

Permissions matter: limit read access to the private key (server.key):

sudo chmod 600 /etc/ssl/postgres/server.key

Step 2 – Configure PostgreSQL for SSL

The Postgres config directory is distro‑specific. On Debian/Ubuntu it’s /etc/postgresql/15/main/; on RHEL‑based systems it may be /var/lib/pgsql/data/. Always edit files in the active cluster directory so changes persist after upgrades.

Modify postgresql.conf

listen_addresses = '*'
ssl = on
ssl_cert_file = '/etc/ssl/postgres/server.crt'
ssl_key_file  = '/etc/ssl/postgres/server.key'

Edit pg_hba.conf

# TYPE  DATABASE  USER  ADDRESS         METHOD
hostssl all       all   203.0.113.0/24  scram-sha-256

This permits SSL‑only access for the corporate subnet 203.0.113.0/24 and uses modern scram‑sha‑256 passwords. Adjust CIDR as needed.

Step 3 – Restart PostgreSQL

Reloading is sometimes sufficient but a restart guarantees new SSL files are loaded:

sudo systemctl restart postgresql

Step 4 – Connect from Your Application

Use a URI with the strictest sslmode feasible (verify-full in production):

postgresql://dbuser:secret@db.example.com:5432/prod?sslmode=verify-full

Step 5 – Vault Your Secrets

Populate environment variables at deployment time or reference your cloud provider’s secret service. Example Docker docker-compose.yaml snippet:

environment:
  - PGUSER=${PGUSER}
  - PGPASSWORD=${PGPASSWORD}
  - PGHOST=db.example.com
  - PGDATABASE=prod
  - PGSSLMODE=verify-full

Step 6 – Confirm Encryption

From psql:

\conninfo        -- shows SSL protocol & cipher
SELECT ssl_is_used();