notes/AI_generated/PostgreSQL_TLS_01.md

📄 Technical Guide: Setting Up TLS for PostgreSQL

This document consolidates the group’s discussion into a practical, production‑ready reference for configuring and using TLS with PostgreSQL, including server setup, client configuration, password management, and example application code.

---

1. Overview

PostgreSQL supports encrypted connections using TLS (often referred to as SSL in its configuration). Enabling TLS ensures secure client–server communication and can optionally enforce client certificate authentication. This guide provides step‑by‑step instructions for server and client configuration, common pitfalls, and usage examples.

---

2. Server-Side Configuration

Certificates

• Required files:
  • server.key → private key
  • server.crt → server certificate
  • root.crt → CA certificate (recommended)
• Sources: internal PKI, Let’s Encrypt, or self‑signed CA.
• Permissions: server.key must be 0600 or root‑owned with restricted group access.

Placement

• Default paths:
  • $PGDATA/server.key
  • $PGDATA/server.crt
• Override with ssl_cert_file, ssl_key_file, ssl_ca_file in postgresql.conf.

Configuration

Enable TLS in postgresql.conf:

ssl = on
ssl_ciphers = 'HIGH:!aNULL:!MD5'
ssl_prefer_server_ciphers = on
ssl_min_protocol_version = 'TLSv1.2'
ssl_max_protocol_version = 'TLSv1.3'

Access Control

Configure pg_hba.conf:

• Allow TLS but not require:

  host all all 0.0.0.0/0 md5
  

• Require TLS:

  hostssl all all 0.0.0.0/0 md5
  

• Require TLS + client certificate:

  hostssl all all 0.0.0.0/0 cert
  

---

3. Client-Side Configuration

Basic TLS

psql "host=db.example.com sslmode=require"

Verify server certificate

psql "host=db.example.com sslmode=verify-full sslrootcert=/etc/ssl/myca/root.crt"

• sslrootcert is a client-side path to the CA certificate.

Mutual TLS

psql "host=db.example.com sslmode=verify-full \
      sslrootcert=/etc/ssl/myca/root.crt \
      sslcert=/etc/ssl/myca/client.crt \
      sslkey=/etc/ssl/myca/client.key"

Modes comparison

Mode	Encrypts	Validates CA	Validates Hostname	Typical Use
require	Yes	No	No	Basic encryption
verify-ca	Yes	Yes	No	Internal/IP-based
verify-full	Yes	Yes	Yes	Production

---

4. Password Management

.pgpass file (recommended)

Format:

hostname:port:database:username:password

Example:

db.example.com:5432:mydb:myuser:SuperSecretPassword123
localhost:5432:*:postgres:localdevpass
*:5432:*:replicator:replicaPassword

• Location: ~/.pgpass
• Permissions: chmod 600 ~/.pgpass
• Supports wildcards (*).

Environment variable

PGPASSWORD='secret123' psql -U myuser -h localhost -d mydb

Less secure; use only for quick commands.

---

5. Testing & Verification

• Check server TLS status:

  SHOW ssl;
  

• Inspect negotiated protocol & cipher:

  SELECT * FROM pg_stat_ssl;
  

• External test:

  openssl s_client -connect db.example.com:5432 -starttls postgres
  

---

6. Common Pitfalls

Issue	Cause	Fix
FATAL: private key file has group or world access	Wrong permissions	chmod 600 server.key
Client rejects certificate	CN/SAN mismatch	Ensure proper DNS SANs
TLS not enforced	Used host instead of hostssl	Update pg_hba.conf
Backup tools fail	Key readable only by postgres	Store certs outside $PGDATA if group-readable needed

---

7. Application Example (Python)

Minimal psycopg2 script with TLS verify-full and bind variables:

import psycopg2
import psycopg2.extras

def main():
    conn = psycopg2.connect(
        host="db.example.com",
        port=5432,
        dbname="mydb",
        user="myuser",
        password="SuperSecretPassword123",
        sslmode="verify-full",
        sslrootcert="/etc/ssl/myca/root.crt",
        sslcert="/etc/ssl/myca/client.crt",   # optional
        sslkey="/etc/ssl/myca/client.key"     # optional
    )

    with conn:
        with conn.cursor(cursor_factory=psycopg2.extras.DictCursor) as cur:
            cur.execute("SELECT id, name FROM demo WHERE id = %s", (1,))
            print("SELECT:", cur.fetchone())

            cur.execute("INSERT INTO demo (id, name) VALUES (%s, %s)", (2, "Inserted Name"))
            cur.execute("UPDATE demo SET name = %s WHERE id = %s", ("Updated Name", 2))
            cur.execute("DELETE FROM demo WHERE id = %s", (2,))

    conn.close()

if __name__ == "__main__":
    main()

---

Appendix / Future Considerations

• Hardened production templates (PKI layout, Ansible roles, CI/CD verification checklist).
• Alternative drivers: psycopg3, SQLAlchemy, async examples.
• Integration with secret management (Kubernetes secrets, systemd, Ansible vault).
• Directory layout best practices for server vs client PKI.

---

✅ This document now serves as a consolidated technical guide for setting up and using TLS with PostgreSQL, including secure password handling and client application integration.