TLS

https://www.postgresql.org/docs/current/ssl-tcp.html
https://www.postgresql.org/docs/current/libpq-ssl.html
https://www.postgresql.org/docs/current/auth-pg-hba-conf.html
https://jdbc.postgresql.org/documentation/ssl/
https://www.psycopg.org/psycopg3/docs/
https://pkg.go.dev/github.com/jackc/pgx/v5
https://node-postgres.com/features/ssl

PostgreSQL TLS protects client <-> server traffic:
    encrypt query / result / password exchange
    verify server certificate with CA
    optionally use client certificate auth

recommended sslmode:
    verify-full:
        encrypt
        verify CA
        verify hostname

avoid in production:
    sslmode=disable
    sslmode=require without CA verification for sensitive systems
    rejectUnauthorized=false

/etc/postgresql/tls/ca.crt
/etc/postgresql/tls/server.crt
/etc/postgresql/tls/server.key

sudo chown postgres:postgres /etc/postgresql/tls/server.key
sudo chmod 0600 /etc/postgresql/tls/server.key
sudo chmod 0644 /etc/postgresql/tls/server.crt /etc/postgresql/tls/ca.crt

hostssl:
    only matches SSL/TLS connections

host:
    can match non-TLS connections

production:
    use hostssl for remote app connections

SELECT pg_reload_conf();

psql "host=db.example.com port=5432 dbname=orders user=order_app sslmode=verify-full sslrootcert=/etc/ssl/company-ca.pem"

SELECT ssl, version, cipher
FROM pg_stat_ssl
WHERE pid = pg_backend_pid();

<dependency>
  <groupId>org.postgresql</groupId>
  <artifactId>postgresql</artifactId>
  <version>42.7.4</version>
</dependency>

import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;

public class PgTlsExample {
  public static void main(String[] args) throws Exception {
    Properties props = new Properties();
    props.setProperty("user", "order_app");
    props.setProperty("password", System.getenv("DB_PASSWORD"));
    props.setProperty("sslmode", "verify-full");
    props.setProperty("sslrootcert", "/etc/ssl/company-ca.pem");

    try (Connection conn = DriverManager.getConnection(
        "jdbc:postgresql://db.example.com:5432/orders", props)) {
      System.out.println(conn.isValid(2));
    }
  }
}

pip install "psycopg[binary]"

import os
import psycopg

conninfo = (
    "host=db.example.com port=5432 dbname=orders user=order_app "
    "sslmode=verify-full sslrootcert=/etc/ssl/company-ca.pem"
)

with psycopg.connect(conninfo, password=os.environ["DB_PASSWORD"]) as conn:
    with conn.cursor() as cur:
        cur.execute("select current_database(), current_user")
        print(cur.fetchone())

go get github.com/jackc/pgx/v5

package main

import (
	"context"
	"fmt"
	"os"

	"github.com/jackc/pgx/v5"
)

func main() {
	url := "postgres://order_app:" + os.Getenv("DB_PASSWORD") +
		"@db.example.com:5432/orders?sslmode=verify-full&sslrootcert=/etc/ssl/company-ca.pem"

	conn, err := pgx.Connect(context.Background(), url)
	if err != nil {
		panic(err)
	}
	defer conn.Close(context.Background())

	var db string
	if err := conn.QueryRow(context.Background(), "select current_database()").Scan(&db); err != nil {
		panic(err)
	}
	fmt.Println(db)
}

npm install pg

import fs from "node:fs";
import { Pool } from "pg";

const pool = new Pool({
  host: "db.example.com",
  port: 5432,
  database: "orders",
  user: "order_app",
  password: process.env.DB_PASSWORD,
  ssl: {
    ca: fs.readFileSync("/etc/ssl/company-ca.pem", "utf8"),
    rejectUnauthorized: true
  }
});

const result = await pool.query("select current_database() as db");
console.log(result.rows[0]);
await pool.end();

server:
    ssl=on
    hostssl used for remote connections
    server.key permission is 0600
    certificate hostname matches connection host
    TLS 1.2+ enforced

client:
    sslmode=verify-full
    CA file mounted read-only
    no rejectUnauthorized=false
    password comes from secret manager

operations:
    cert expiry alert exists
    cert rotation tested
    pg_stat_ssl checked in runbook