Connecting Securely with TLS

# psycopg 3, with the pre-built binary package that bundles libpq (and
# therefore OpenSSL).
psycopg[binary]>=3.2,<4

{
  "name": "tls-connect",
  "private": true,
  "type": "module",
  "scripts": {
    "build": "tsc",
    "start": "node dist/main.js"
  },
  "dependencies": {
    "pg": "^8.23.1"
  },
  "devDependencies": {
    "@types/node": "^22.0.0",
    "@types/pg": "^8.23.1",
    "typescript": "^5.9.0"
  }
}

<dependencies>
  <!-- The PostgreSQL JDBC driver (pgJDBC). -->
  <dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.7.14</version>
  </dependency>
</dependencies>

<ItemGroup>
  <!-- Npgsql, the ADO.NET data provider for PostgreSQL. -->
  <PackageReference Include="Npgsql" Version="10.0.3" />
</ItemGroup>

// pgx is a pure Go driver and toolkit for PostgreSQL.
require github.com/jackc/pgx/v5 v5.12.0

require (
    github.com/jackc/pgpassfile v1.0.0 // indirect
    github.com/jackc/pgservicefile v0.0.0-20240606120523-5a60cdf6a761 // indirect
    golang.org/x/text v0.41.0 // indirect
)

[dependencies]
# The synchronous client; tokio-postgres is the asynchronous equivalent.
postgres = "0.19"
# TLS support for rust-postgres, using OpenSSL through the openssl crate.
postgres-openssl = "0.5"
openssl = "0.10"

source "https://rubygems.org"

# The pg gem wraps libpq, so libpq and its headers must be installed.
gem "pg", "~> 1.6"

# Build with libpq, using pg_config to find the headers and library.
CFLAGS += -Wall -Wextra -I$(shell pg_config --includedir)
LDFLAGS += -L$(shell pg_config --libdir)
LDLIBS += -lpq

main: main.c

import os
import sys

import psycopg

import { readFileSync } from "node:fs";

import pg from "pg";

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

using Npgsql;

import (
    "context"
    "errors"
    "fmt"
    "net"
    "net/url"
    "os"

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

use std::env;
use std::error::Error;
use std::process::ExitCode;

use openssl::ssl::{SslConnector, SslMethod};
use openssl::x509::store::X509StoreBuilder;
use openssl::x509::X509;
use postgres::config::SslMode;
use postgres::{Client, Config};
use postgres_openssl::MakeTlsConnector;

require "pg"

#include <stdbool.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>

#include <libpq-fe.h>

def connection_settings() -> dict:
    """Reads the connection settings from the standard libpq environment
    variables, with defaults suitable for a local development server.

    The or operator treats an empty variable as unset, as libpq does, and
    a password of None is simply not sent. PGSSLROOTCERT has no default,
    because verification is meaningless without the certificate of the
    authority that signed the server certificate.
    """
    root_cert = os.environ.get("PGSSLROOTCERT")
    if not root_cert:
        raise RuntimeError("PGSSLROOTCERT must name the root certificate")

    return {
        "host": os.environ.get("PGHOST") or "localhost",
        "port": os.environ.get("PGPORT") or "5432",
        "dbname": os.environ.get("PGDATABASE") or "postgres",
        "user": os.environ.get("PGUSER") or "postgres",
        "password": os.environ.get("PGPASSWORD") or None,
        # verify-full checks that the certificate chains to the root
        # certificate and that it matches the host name. Weaker modes,
        # such as require, encrypt the connection without proving who is
        # on the other end.
        "sslmode": "verify-full",
        "sslrootcert": root_cert,
    }

// Read the connection settings from the standard libpq environment
// variables, with defaults suitable for a local development server.
// The || operator, unlike ??, also treats an empty variable as unset, as
// libpq does.
function connectionConfig(): pg.ClientConfig {
  // PGSSLROOTCERT has no default, because verification is meaningless
  // without the certificate of the authority that signed the server
  // certificate.
  const rootCert = process.env.PGSSLROOTCERT;
  if (!rootCert) {
    throw new Error("PGSSLROOTCERT must name the root certificate");
  }

  return {
    host: process.env.PGHOST || "localhost",
    port: Number(process.env.PGPORT || "5432"),
    database: process.env.PGDATABASE || "postgres",
    user: process.env.PGUSER || "postgres",
    password: process.env.PGPASSWORD || undefined,
    // Supplying the root certificate as ca makes Node trust only that
    // authority, and rejectUnauthorized makes Node reject a certificate
    // that does not chain to it or does not match the host name. Together
    // they are the equivalent of sslmode=verify-full.
    ssl: { ca: readFileSync(rootCert), rejectUnauthorized: true },
  };
}

/** Returns the value of an environment variable, or a fallback. */
static String envOr(String name, String fallback) {
    String value = System.getenv(name);
    return (value != null && !value.isEmpty()) ? value : fallback;
}

/**
 * Returns the JDBC URL that identifies the server and database, built
 * from the standard libpq environment variables.
 */
static String connectionUrl() {
    return "jdbc:postgresql://"
            + envOr("PGHOST", "localhost") + ":"
            + envOr("PGPORT", "5432") + "/"
            + envOr("PGDATABASE", "postgres");
}

/**
 * Returns the user name, password, and TLS settings as connection
 * properties; the password is omitted if PGPASSWORD is unset or
 * empty. PGSSLROOTCERT has no default, because verification is
 * meaningless without the certificate of the authority that signed
 * the server certificate.
 */
static Properties connectionProperties() {
    String rootCert = envOr("PGSSLROOTCERT", null);
    if (rootCert == null) {
        throw new IllegalStateException(
                "PGSSLROOTCERT must name the root certificate");
    }

    Properties props = new Properties();
    props.setProperty("user", envOr("PGUSER", "postgres"));
    String password = envOr("PGPASSWORD", null);
    if (password != null) {
        props.setProperty("password", password);
    }
    // verify-full checks that the certificate chains to the root
    // certificate and that it matches the host name. Weaker modes,
    // such as require, encrypt the connection without proving who is
    // on the other end.
    props.setProperty("sslmode", "verify-full");
    props.setProperty("sslrootcert", rootCert);
    return props;
}

// Returns the value of an environment variable, or null if the variable
// is unset or empty; libpq also treats an empty variable as unset.
static string? EnvValue(string name) =>
    Environment.GetEnvironmentVariable(name) is { Length: > 0 } value
        ? value
        : null;

// Returns the value of an environment variable, or a fallback if unset.
static string EnvOr(string name, string fallback) =>
    EnvValue(name) ?? fallback;

// Builds a connection string from the standard libpq environment
// variables, with defaults suitable for a local development server. The
// builder takes care of quoting any special characters. PGSSLROOTCERT has
// no default, because verification is meaningless without the certificate
// of the authority that signed the server certificate.
static string ConnectionString() =>
    new NpgsqlConnectionStringBuilder
    {
        Host = EnvOr("PGHOST", "localhost"),
        Port = int.Parse(EnvOr("PGPORT", "5432")),
        Database = EnvOr("PGDATABASE", "postgres"),
        Username = EnvOr("PGUSER", "postgres"),
        Password = EnvValue("PGPASSWORD"),
        // VerifyFull checks that the certificate chains to the root
        // certificate and that it matches the host name. Weaker modes,
        // such as Require, encrypt the connection without proving who is
        // on the other end.
        SslMode = SslMode.VerifyFull,
        RootCertificate = EnvValue("PGSSLROOTCERT")
            ?? throw new InvalidOperationException(
                "PGSSLROOTCERT must name the root certificate"),
    }.ConnectionString;

// envOr returns the value of an environment variable, or a fallback if
// the variable is unset or empty.
func envOr(name, fallback string) string {
    if value := os.Getenv(name); value != "" {
        return value
    }
    return fallback
}

// connString builds a postgres:// URL from the standard libpq environment
// variables. net/url takes care of escaping any special characters in
// the user name, password, or database name. PGSSLROOTCERT has no
// default, because verification is meaningless without the certificate
// of the authority that signed the server certificate.
func connString() (string, error) {
    rootCert := os.Getenv("PGSSLROOTCERT")
    if rootCert == "" {
        return "", errors.New("PGSSLROOTCERT must name the root certificate")
    }

    u := url.URL{
        Scheme: "postgres",
        Host:   net.JoinHostPort(envOr("PGHOST", "localhost"), envOr("PGPORT", "5432")),
        Path:   "/" + envOr("PGDATABASE", "postgres"),
        // verify-full checks that the certificate chains to the root
        // certificate and that it matches the host name. Weaker modes,
        // such as require, encrypt the connection without proving who
        // is on the other end.
        RawQuery: url.Values{
            "sslmode":     {"verify-full"},
            "sslrootcert": {rootCert},
        }.Encode(),
    }
    if password := os.Getenv("PGPASSWORD"); password != "" {
        u.User = url.UserPassword(envOr("PGUSER", "postgres"), password)
    } else {
        u.User = url.User(envOr("PGUSER", "postgres"))
    }
    return u.String(), nil
}

/// Returns the value of an environment variable, if it is set and not
/// empty; libpq also treats an empty variable as unset.
fn env_value(name: &str) -> Option<String> {
    env::var(name).ok().filter(|value| !value.is_empty())
}

/// Returns the value of an environment variable, or a fallback if the
/// variable is unset or empty.
fn env_or(name: &str, fallback: &str) -> String {
    env_value(name).unwrap_or_else(|| fallback.to_string())
}

/// Builds the connection configuration from the standard libpq
/// environment variables, with defaults suitable for a local server.
/// The default SSL mode of the crate is Prefer, which falls back to a
/// plain connection if the server declines TLS, so Require is set
/// explicitly. Require makes the connection fail instead; the TLS
/// connector built below then verifies the certificate.
fn config() -> Config {
    let mut config = Config::new();
    config
        .ssl_mode(SslMode::Require)
        .host(&env_or("PGHOST", "localhost"))
        .port(env_or("PGPORT", "5432").parse().expect("PGPORT must be a number"))
        .dbname(&env_or("PGDATABASE", "postgres"))
        .user(&env_or("PGUSER", "postgres"));
    if let Some(password) = env_value("PGPASSWORD") {
        config.password(&password);
    }
    config
}

/// Returns the path of the root certificate file. PGSSLROOTCERT has no
/// default, because verification is meaningless without the certificate
/// of the authority that signed the server certificate.
fn root_cert_path() -> Result<String, Box<dyn Error>> {
    env_value("PGSSLROOTCERT").ok_or_else(|| "PGSSLROOTCERT must name the root certificate".into())
}

# Returns the value of an environment variable, or a fallback if the
# variable is unset or empty; libpq also treats an empty variable as unset.
def env_or(name, fallback)
  value = ENV[name]
  value.nil? || value.empty? ? fallback : value
end

# Returns the connection settings from the standard libpq environment
# variables, with defaults suitable for a local development server.
# compact removes the password if PGPASSWORD is not set.
def connection_params
  # PGSSLROOTCERT has no default, because verification is meaningless
  # without the certificate of the authority that signed the server
  # certificate.
  root_cert = env_or("PGSSLROOTCERT", nil)
  raise ArgumentError, "PGSSLROOTCERT must name the root certificate" unless root_cert

  {
    host: env_or("PGHOST", "localhost"),
    port: env_or("PGPORT", "5432"),
    dbname: env_or("PGDATABASE", "postgres"),
    user: env_or("PGUSER", "postgres"),
    password: env_or("PGPASSWORD", nil),
    # verify-full checks that the certificate chains to the root
    # certificate and that it matches the host name. Weaker modes, such
    # as require, encrypt the connection without proving who is on the
    # other end.
    sslmode: "verify-full",
    sslrootcert: root_cert
  }.compact
end

/* Return the value of an environment variable, or a fallback if unset. */
static const char *
env_or(const char *name, const char *fallback)
{
    const char *value = getenv(name);

    return (value != NULL && value[0] != '\0') ? value : fallback;
}

/*
 * Connection parameters are passed to libpq as two parallel,
 * NULL-terminated arrays of keywords and values. A NULL value is ignored,
 * so the password is only sent if PGPASSWORD is set.
 */
static const char *const keywords[] = {
    "host", "port", "dbname", "user", "password",
    "sslmode", "sslrootcert", NULL
};

/*
 * Fill in the value for each keyword from the environment, and return
 * false if PGSSLROOTCERT is missing. It has no default, because
 * verification is meaningless without the certificate of the authority
 * that signed the server certificate.
 */
static bool
read_settings(const char *values[])
{
    values[0] = env_or("PGHOST", "localhost");
    values[1] = env_or("PGPORT", "5432");
    values[2] = env_or("PGDATABASE", "postgres");
    values[3] = env_or("PGUSER", "postgres");
    values[4] = env_or("PGPASSWORD", NULL);

    /*
     * verify-full checks that the certificate chains to the root
     * certificate and that it matches the host name. Weaker modes, such
     * as require, encrypt the connection without proving who is on the
     * other end.
     */
    values[5] = "verify-full";
    values[6] = env_or("PGSSLROOTCERT", NULL);
    values[7] = NULL;
    return values[6] != NULL;
}

def connect() -> psycopg.Connection:
    """Opens a verified TLS connection with the settings from the
    environment. The connection fails if verification fails."""
    return psycopg.connect(**connection_settings())

// Opens a verified TLS connection with the settings from the
// environment. The connection fails if verification fails.
async function connect(): Promise<pg.Client> {
  const client = new pg.Client(connectionConfig());
  try {
    await client.connect();
  } catch (err) {
    // Release the client before passing the failure to the caller.
    await client.end();
    throw err;
  }
  return client;
}

/**
 * Opens a verified TLS connection with the settings from the
 * environment. The connection fails if verification fails.
 */
static Connection connect() throws SQLException {
    return DriverManager.getConnection(
            connectionUrl(), connectionProperties());
}

// Creates a data source with the settings from the environment. A data
// source manages a pool of physical connections, and each call to
// OpenConnectionAsync borrows one from the pool; the connection fails if
// verification fails.
static NpgsqlDataSource CreateDataSource() =>
    NpgsqlDataSource.Create(ConnectionString());

// connect opens a verified TLS connection with the settings from the
// environment. The connection fails if verification fails.
func connect(ctx context.Context) (*pgx.Conn, error) {
    dsn, err := connString()
    if err != nil {
        return nil, err
    }
    conn, err := pgx.Connect(ctx, dsn)
    if err != nil {
        return nil, fmt.Errorf("connection failed: %w", err)
    }
    return conn, nil
}

/// Builds a TLS connector that trusts only the given root certificate.
/// SslConnector::builder starts with the system root certificates, so the
/// certificate store is replaced with one that holds nothing but the
/// certificates in the file; otherwise any publicly trusted certificate
/// for the host name would also be accepted. The connector verifies the
/// certificate chain and the host name by default, which together are the
/// equivalent of sslmode=verify-full; the host name checked is the one in
/// the Config.
fn tls_connector(root_cert: &str) -> Result<MakeTlsConnector, Box<dyn Error>> {
    let mut store = X509StoreBuilder::new()?;
    for cert in X509::stack_from_pem(&std::fs::read(root_cert)?)? {
        store.add_cert(cert)?;
    }

    let mut builder = SslConnector::builder(SslMethod::tls())?;
    builder.set_cert_store(store.build());
    Ok(MakeTlsConnector::new(builder.build()))
}

/// Opens a verified TLS connection with the settings from the
/// environment. The connection fails if verification fails.
fn connect() -> Result<Client, Box<dyn Error>> {
    let tls = tls_connector(&root_cert_path()?)?;
    Ok(config().connect(tls)?)
}

# Opens a verified TLS connection with the settings from the environment.
# The connection fails if verification fails.
def connect
  PG.connect(connection_params)
end

/*
 * Open a verified TLS connection with the settings from the environment,
 * or return NULL if PGSSLROOTCERT is missing. libpq otherwise always
 * returns a connection object, which the caller must check with
 * PQstatus() and release with PQfinish(), even if the connection attempt
 * failed; the attempt fails if verification fails.
 */
static PGconn *
connect_to_server(void)
{
    const char *values[sizeof(keywords) / sizeof(keywords[0])];

    if (!read_settings(values))
        return NULL;
    return PQconnectdbParams(keywords, values, 0);
}

def connection_is_encrypted(conn: psycopg.Connection) -> bool:
    """Asks the server whether this connection uses TLS."""
    # pg_stat_ssl has one row for each connection; pg_backend_pid()
    # identifies the one that belongs to this session. The version column
    # holds the protocol version, such as TLSv1.3.
    row = conn.execute(
        "SELECT ssl, version FROM pg_catalog.pg_stat_ssl "
        "WHERE pid = pg_backend_pid()"
    ).fetchone()
    return row[0]

// Asks the server whether this connection uses TLS.
async function connectionIsEncrypted(client: pg.Client): Promise<boolean> {
  // pg_stat_ssl has one row for each connection; pg_backend_pid()
  // identifies the one that belongs to this session. The version column
  // holds the protocol version, such as TLSv1.3.
  const result = await client.query<{ ssl: boolean; version: string }>(
    "SELECT ssl, version FROM pg_catalog.pg_stat_ssl " +
      "WHERE pid = pg_backend_pid()",
  );
  return result.rows[0].ssl;
}

/** Asks the server whether this connection uses TLS. */
static boolean connectionIsEncrypted(Connection conn)
        throws SQLException {
    // pg_stat_ssl has one row for each connection; pg_backend_pid()
    // identifies the one that belongs to this session. The version
    // column holds the protocol version, such as TLSv1.3.
    String sql = "SELECT ssl, version FROM pg_catalog.pg_stat_ssl "
            + "WHERE pid = pg_backend_pid()";

    // try-with-resources closes the statement and the result set when
    // each block exits, whether or not an error occurred.
    try (Statement stmt = conn.createStatement();
         ResultSet rs = stmt.executeQuery(sql)) {
        rs.next();
        return rs.getBoolean(1);
    }
}

// Asks the server whether this connection uses TLS.
static async Task<bool> ConnectionIsEncryptedAsync(NpgsqlConnection conn)
{
    // pg_stat_ssl has one row for each connection; pg_backend_pid()
    // identifies the one that belongs to this session. The version column
    // holds the protocol version, such as TLSv1.3.
    await using var cmd = new NpgsqlCommand(
        "SELECT ssl, version FROM pg_catalog.pg_stat_ssl " +
        "WHERE pid = pg_backend_pid()", conn);
    await using var reader = await cmd.ExecuteReaderAsync();

    // A boolean maps to bool.
    await reader.ReadAsync();
    return reader.GetBoolean(0);
}

// connectionIsEncrypted asks the server whether this connection uses TLS.
func connectionIsEncrypted(ctx context.Context, conn *pgx.Conn) (bool, error) {
    // pg_stat_ssl has one row for each connection; pg_backend_pid()
    // identifies the one that belongs to this session. The version column
    // holds the protocol version, such as TLSv1.3, and is NULL without
    // TLS, so it scans into a pointer.
    var ssl bool
    var version *string
    err := conn.QueryRow(ctx,
        "SELECT ssl, version FROM pg_catalog.pg_stat_ssl "+
            "WHERE pid = pg_backend_pid()",
    ).Scan(&ssl, &version)
    if err != nil {
        return false, fmt.Errorf("query failed: %w", err)
    }
    return ssl, nil
}

/// Asks the server whether this connection uses TLS.
fn connection_is_encrypted(client: &mut Client) -> Result<bool, postgres::Error> {
    // pg_stat_ssl has one row for each connection; pg_backend_pid()
    // identifies the one that belongs to this session. The version column
    // holds the protocol version, such as TLSv1.3, and is NULL without
    // TLS, so it maps to an Option.
    let row = client.query_one(
        "SELECT ssl, version FROM pg_catalog.pg_stat_ssl \
         WHERE pid = pg_backend_pid()",
        &[],
    )?;
    let _version: Option<String> = row.get(1);

    // ssl is a boolean, which maps to bool.
    Ok(row.get(0))
}

# Asks the server whether this connection uses TLS.
def connection_encrypted?(conn)
  # pg_stat_ssl has one row for each connection; pg_backend_pid()
  # identifies the one that belongs to this session. The version column
  # holds the protocol version, such as TLSv1.3.
  result = conn.exec(
    "SELECT ssl, version FROM pg_catalog.pg_stat_ssl " \
    "WHERE pid = pg_backend_pid()"
  )

  # Results arrive as strings; PostgreSQL writes a boolean as t or f.
  result.getvalue(0, 0) == "t"
end

/*
 * Store in *encrypted whether the server reports this connection as using
 * TLS, and return true on success. On failure, PQerrorMessage() describes
 * the error.
 */
static bool
connection_is_encrypted(PGconn *conn, bool *encrypted)
{
    /*
     * pg_stat_ssl has one row for each connection; pg_backend_pid()
     * identifies the one that belongs to this session. The version column
     * holds the protocol version, such as TLSv1.3.
     */
    PGresult *res = PQexec(conn,
                           "SELECT ssl, version FROM pg_catalog.pg_stat_ssl "
                           "WHERE pid = pg_backend_pid()");
    bool ok = PQresultStatus(res) == PGRES_TUPLES_OK && PQntuples(res) == 1;

    /* Results arrive as text; PostgreSQL writes a boolean as t or f. */
    if (ok)
        *encrypted = strcmp(PQgetvalue(res, 0, 0), "t") == 0;

    /* PQclear() frees the result, and accepts NULL. */
    PQclear(res);
    return ok;
}

def main() -> int:
    try:
        # The with block closes the connection when it exits, whether or
        # not an error occurred.
        with connect() as conn:
            encrypted = connection_is_encrypted(conn)
            print(f"Connection is encrypted: {str(encrypted).lower()}")
            # Reaching this line means that verify-full succeeded; a
            # certificate or host name mismatch raises an error instead.
            print("Server certificate verified: true")
    # psycopg.Error is the base class for every error the driver raises,
    # including connection and certificate verification failures.
    except (psycopg.Error, RuntimeError) as e:
        print(f"Database error: {e}", file=sys.stderr)
        return 1

    return 0


if __name__ == "__main__":
    sys.exit(main())

// Returns a readable description of an error. When a host name resolves
// to several addresses, Node reports a failure to reach all of them as an
// AggregateError, whose own message is empty.
function describe(err: unknown): string {
  if (err instanceof AggregateError) {
    return err.errors.map(describe).join("; ");
  }
  return err instanceof Error ? err.message : String(err);
}

async function main(): Promise<number> {
  let client: pg.Client | undefined;
  try {
    client = await connect();
    const encrypted = await connectionIsEncrypted(client);
    console.log(`Connection is encrypted: ${encrypted}`);
    // Reaching this line means that verification succeeded; a
    // certificate or host name mismatch rejects the promise instead.
    console.log("Server certificate verified: true");
    return 0;
  } catch (err) {
    // Connection, certificate, and server errors all arrive here as
    // rejected promises.
    console.error(`Database error: ${describe(err)}`);
    return 1;
  } finally {
    // The finally block runs whether or not an error occurred; client is
    // undefined if the connection attempt failed.
    await client?.end();
  }
}

process.exitCode = await main();

public static void main(String[] args) {
    // try-with-resources closes the connection when the block exits,
    // whether or not an error occurred.
    try (Connection conn = connect()) {
        boolean encrypted = connectionIsEncrypted(conn);
        System.out.println("Connection is encrypted: " + encrypted);
        // Reaching this line means that verify-full succeeded; a
        // certificate or host name mismatch throws instead.
        System.out.println("Server certificate verified: true");
    // SQLException covers connection, certificate, and server errors;
    // IllegalStateException reports a missing PGSSLROOTCERT.
    } catch (SQLException | IllegalStateException e) {
        System.err.println("Database error: " + e.getMessage());
        System.exit(1);
    }
}

try
{
    // await using disposes of the data source, and returns the connection
    // to the pool, when the block exits, whether or not an error occurred.
    await using var dataSource = CreateDataSource();
    await using var conn = await dataSource.OpenConnectionAsync();

    var encrypted = await ConnectionIsEncryptedAsync(conn);
    Console.WriteLine($"Connection is encrypted: {encrypted.ToString().ToLowerInvariant()}");
    // Reaching this line means that VerifyFull succeeded; a certificate
    // or host name mismatch throws instead.
    Console.WriteLine("Server certificate verified: true");
}
// NpgsqlException covers connection failures; PostgresException, which
// derives from it, carries errors reported by the server.
// InvalidOperationException reports a missing PGSSLROOTCERT.
catch (Exception e) when (e is NpgsqlException or InvalidOperationException)
{
    Console.Error.WriteLine($"Database error: {e.Message}");
    return 1;
}

return 0;

// run connects, runs the query, and prints the result, returning any
// error to the caller.
func run(ctx context.Context) error {
    conn, err := connect(ctx)
    if err != nil {
        return err
    }
    // The deferred call closes the connection when run() returns, whether
    // or not an error occurred.
    defer conn.Close(ctx)

    encrypted, err := connectionIsEncrypted(ctx, conn)
    if err != nil {
        return err
    }

    fmt.Printf("Connection is encrypted: %t\n", encrypted)
    // Reaching this line means that verify-full succeeded; a certificate
    // or host name mismatch makes connect() return an error instead.
    fmt.Println("Server certificate verified: true")
    return nil
}

// main reports any error returned by run() and exits with a non-zero
// status. Keeping the work in run() means that deferred cleanup always
// happens before the program exits.
func main() {
    if err := run(context.Background()); err != nil {
        fmt.Fprintf(os.Stderr, "Database error: %v\n", err)
        os.Exit(1)
    }
}

/// Connects, runs the query, and prints the result. The ? operator
/// returns any error to the caller.
fn run() -> Result<(), Box<dyn Error>> {
    let mut client = connect()?;
    let encrypted = connection_is_encrypted(&mut client)?;
    println!("Connection is encrypted: {encrypted}");
    // Reaching this line means that verification succeeded; a certificate
    // or host name mismatch makes connect() return an error instead.
    println!("Server certificate verified: true");

    // Dropping the client also closes the connection, including when ?
    // returns early, but close() lets us see any error that occurs while
    // doing so.
    client.close()?;
    Ok(())
}

/// Reports any error returned by run() and exits with a failure status.
/// The top-level error is often generic ("error connecting to server"),
/// so the chain of underlying causes is printed too.
fn main() -> ExitCode {
    match run() {
        Ok(()) => ExitCode::SUCCESS,
        Err(e) => {
            eprintln!("Database error: {e}");
            let mut cause = e.source();
            while let Some(c) = cause {
                eprintln!("  caused by: {c}");
                cause = c.source();
            }
            ExitCode::FAILURE
        }
    }
}

begin
  conn = connect
  puts "Connection is encrypted: #{connection_encrypted?(conn)}"
  # Reaching this line means that verify-full succeeded; a certificate or
  # host name mismatch raises PG::ConnectionBad instead.
  puts "Server certificate verified: true"
# PG::Error is the base class for every error the gem raises, including
# connection failures and errors reported by the server.
rescue PG::Error, ArgumentError => e
  warn "Database error: #{e.message}"
  exit 1
# The ensure block runs whether or not an error occurred. conn is nil if
# the connection attempt failed, hence the safe navigation operator.
ensure
  conn&.close
end

int
main(void)
{
    int status = EXIT_FAILURE;
    bool encrypted;
    PGconn *conn = connect_to_server();

    if (conn == NULL)
    {
        fprintf(stderr, "Database error: PGSSLROOTCERT must name the "
                        "root certificate\n");
        return status;
    }

    /* The || operator skips the query if the connection failed. */
    if (PQstatus(conn) != CONNECTION_OK ||
        !connection_is_encrypted(conn, &encrypted))
    {
        fprintf(stderr, "Database error: %s", PQerrorMessage(conn));
    }
    else
    {
        printf("Connection is encrypted: %s\n", encrypted ? "true" : "false");
        /*
         * Reaching this line means that verify-full succeeded; a
         * certificate or host name mismatch fails the connection instead.
         */
        printf("Server certificate verified: true\n");
        status = EXIT_SUCCESS;
    }

    /* Close the connection whether or not an error occurred. */
    PQfinish(conn);
    return status;
}

python3 -m venv .venv
.venv/bin/pip install -r requirements.txt
.venv/bin/python main.py

npm install
npm run build
npm start

mvn compile exec:java

dotnet run

go run .

cargo run

bundle install
bundle exec ruby main.rb

make
./main

"""
Ellie's Postgres Cookbook
Connecting to PostgreSQL over TLS with full verification, using psycopg 3.

Copyright (c) 2026, pgEdge, Inc.
This code is released under the PostgreSQL License.
"""

import os
import sys

import psycopg


def connection_settings() -> dict:
    """Reads the connection settings from the standard libpq environment
    variables, with defaults suitable for a local development server.

    The or operator treats an empty variable as unset, as libpq does, and
    a password of None is simply not sent. PGSSLROOTCERT has no default,
    because verification is meaningless without the certificate of the
    authority that signed the server certificate.
    """
    root_cert = os.environ.get("PGSSLROOTCERT")
    if not root_cert:
        raise RuntimeError("PGSSLROOTCERT must name the root certificate")

    return {
        "host": os.environ.get("PGHOST") or "localhost",
        "port": os.environ.get("PGPORT") or "5432",
        "dbname": os.environ.get("PGDATABASE") or "postgres",
        "user": os.environ.get("PGUSER") or "postgres",
        "password": os.environ.get("PGPASSWORD") or None,
        # verify-full checks that the certificate chains to the root
        # certificate and that it matches the host name. Weaker modes,
        # such as require, encrypt the connection without proving who is
        # on the other end.
        "sslmode": "verify-full",
        "sslrootcert": root_cert,
    }


def connect() -> psycopg.Connection:
    """Opens a verified TLS connection with the settings from the
    environment. The connection fails if verification fails."""
    return psycopg.connect(**connection_settings())


def connection_is_encrypted(conn: psycopg.Connection) -> bool:
    """Asks the server whether this connection uses TLS."""
    # pg_stat_ssl has one row for each connection; pg_backend_pid()
    # identifies the one that belongs to this session. The version column
    # holds the protocol version, such as TLSv1.3.
    row = conn.execute(
        "SELECT ssl, version FROM pg_catalog.pg_stat_ssl "
        "WHERE pid = pg_backend_pid()"
    ).fetchone()
    return row[0]


def main() -> int:
    try:
        # The with block closes the connection when it exits, whether or
        # not an error occurred.
        with connect() as conn:
            encrypted = connection_is_encrypted(conn)
            print(f"Connection is encrypted: {str(encrypted).lower()}")
            # Reaching this line means that verify-full succeeded; a
            # certificate or host name mismatch raises an error instead.
            print("Server certificate verified: true")
    # psycopg.Error is the base class for every error the driver raises,
    # including connection and certificate verification failures.
    except (psycopg.Error, RuntimeError) as e:
        print(f"Database error: {e}", file=sys.stderr)
        return 1

    return 0


if __name__ == "__main__":
    sys.exit(main())

/*
 * Ellie's Postgres Cookbook
 * Connecting to PostgreSQL over TLS with full verification, using
 * node-postgres.
 *
 * Copyright (c) 2026, pgEdge, Inc.
 * This code is released under the PostgreSQL License.
 */

import { readFileSync } from "node:fs";

import pg from "pg";

// Read the connection settings from the standard libpq environment
// variables, with defaults suitable for a local development server.
// The || operator, unlike ??, also treats an empty variable as unset, as
// libpq does.
function connectionConfig(): pg.ClientConfig {
  // PGSSLROOTCERT has no default, because verification is meaningless
  // without the certificate of the authority that signed the server
  // certificate.
  const rootCert = process.env.PGSSLROOTCERT;
  if (!rootCert) {
    throw new Error("PGSSLROOTCERT must name the root certificate");
  }

  return {
    host: process.env.PGHOST || "localhost",
    port: Number(process.env.PGPORT || "5432"),
    database: process.env.PGDATABASE || "postgres",
    user: process.env.PGUSER || "postgres",
    password: process.env.PGPASSWORD || undefined,
    // Supplying the root certificate as ca makes Node trust only that
    // authority, and rejectUnauthorized makes Node reject a certificate
    // that does not chain to it or does not match the host name. Together
    // they are the equivalent of sslmode=verify-full.
    ssl: { ca: readFileSync(rootCert), rejectUnauthorized: true },
  };
}

// Opens a verified TLS connection with the settings from the
// environment. The connection fails if verification fails.
async function connect(): Promise<pg.Client> {
  const client = new pg.Client(connectionConfig());
  try {
    await client.connect();
  } catch (err) {
    // Release the client before passing the failure to the caller.
    await client.end();
    throw err;
  }
  return client;
}

// Asks the server whether this connection uses TLS.
async function connectionIsEncrypted(client: pg.Client): Promise<boolean> {
  // pg_stat_ssl has one row for each connection; pg_backend_pid()
  // identifies the one that belongs to this session. The version column
  // holds the protocol version, such as TLSv1.3.
  const result = await client.query<{ ssl: boolean; version: string }>(
    "SELECT ssl, version FROM pg_catalog.pg_stat_ssl " +
      "WHERE pid = pg_backend_pid()",
  );
  return result.rows[0].ssl;
}

// Returns a readable description of an error. When a host name resolves
// to several addresses, Node reports a failure to reach all of them as an
// AggregateError, whose own message is empty.
function describe(err: unknown): string {
  if (err instanceof AggregateError) {
    return err.errors.map(describe).join("; ");
  }
  return err instanceof Error ? err.message : String(err);
}

async function main(): Promise<number> {
  let client: pg.Client | undefined;
  try {
    client = await connect();
    const encrypted = await connectionIsEncrypted(client);
    console.log(`Connection is encrypted: ${encrypted}`);
    // Reaching this line means that verification succeeded; a
    // certificate or host name mismatch rejects the promise instead.
    console.log("Server certificate verified: true");
    return 0;
  } catch (err) {
    // Connection, certificate, and server errors all arrive here as
    // rejected promises.
    console.error(`Database error: ${describe(err)}`);
    return 1;
  } finally {
    // The finally block runs whether or not an error occurred; client is
    // undefined if the connection attempt failed.
    await client?.end();
  }
}

process.exitCode = await main();

/*
 * Ellie's Postgres Cookbook
 * Connecting to PostgreSQL over TLS with full verification, using JDBC and
 * the PostgreSQL JDBC driver (pgJDBC).
 *
 * Copyright (c) 2026, pgEdge, Inc.
 * This code is released under the PostgreSQL License.
 */

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class Main {

    /** Returns the value of an environment variable, or a fallback. */
    static String envOr(String name, String fallback) {
        String value = System.getenv(name);
        return (value != null && !value.isEmpty()) ? value : fallback;
    }

    /**
     * Returns the JDBC URL that identifies the server and database, built
     * from the standard libpq environment variables.
     */
    static String connectionUrl() {
        return "jdbc:postgresql://"
                + envOr("PGHOST", "localhost") + ":"
                + envOr("PGPORT", "5432") + "/"
                + envOr("PGDATABASE", "postgres");
    }

    /**
     * Returns the user name, password, and TLS settings as connection
     * properties; the password is omitted if PGPASSWORD is unset or
     * empty. PGSSLROOTCERT has no default, because verification is
     * meaningless without the certificate of the authority that signed
     * the server certificate.
     */
    static Properties connectionProperties() {
        String rootCert = envOr("PGSSLROOTCERT", null);
        if (rootCert == null) {
            throw new IllegalStateException(
                    "PGSSLROOTCERT must name the root certificate");
        }

        Properties props = new Properties();
        props.setProperty("user", envOr("PGUSER", "postgres"));
        String password = envOr("PGPASSWORD", null);
        if (password != null) {
            props.setProperty("password", password);
        }
        // verify-full checks that the certificate chains to the root
        // certificate and that it matches the host name. Weaker modes,
        // such as require, encrypt the connection without proving who is
        // on the other end.
        props.setProperty("sslmode", "verify-full");
        props.setProperty("sslrootcert", rootCert);
        return props;
    }

    /**
     * Opens a verified TLS connection with the settings from the
     * environment. The connection fails if verification fails.
     */
    static Connection connect() throws SQLException {
        return DriverManager.getConnection(
                connectionUrl(), connectionProperties());
    }

    /** Asks the server whether this connection uses TLS. */
    static boolean connectionIsEncrypted(Connection conn)
            throws SQLException {
        // pg_stat_ssl has one row for each connection; pg_backend_pid()
        // identifies the one that belongs to this session. The version
        // column holds the protocol version, such as TLSv1.3.
        String sql = "SELECT ssl, version FROM pg_catalog.pg_stat_ssl "
                + "WHERE pid = pg_backend_pid()";

        // try-with-resources closes the statement and the result set when
        // each block exits, whether or not an error occurred.
        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            rs.next();
            return rs.getBoolean(1);
        }
    }

    public static void main(String[] args) {
        // try-with-resources closes the connection when the block exits,
        // whether or not an error occurred.
        try (Connection conn = connect()) {
            boolean encrypted = connectionIsEncrypted(conn);
            System.out.println("Connection is encrypted: " + encrypted);
            // Reaching this line means that verify-full succeeded; a
            // certificate or host name mismatch throws instead.
            System.out.println("Server certificate verified: true");
        // SQLException covers connection, certificate, and server errors;
        // IllegalStateException reports a missing PGSSLROOTCERT.
        } catch (SQLException | IllegalStateException e) {
            System.err.println("Database error: " + e.getMessage());
            System.exit(1);
        }
    }
}

/*
 * Ellie's Postgres Cookbook
 * Connecting to PostgreSQL over TLS with full verification, using Npgsql.
 *
 * Copyright (c) 2026, pgEdge, Inc.
 * This code is released under the PostgreSQL License.
 */

using Npgsql;

// Returns the value of an environment variable, or null if the variable
// is unset or empty; libpq also treats an empty variable as unset.
static string? EnvValue(string name) =>
    Environment.GetEnvironmentVariable(name) is { Length: > 0 } value
        ? value
        : null;

// Returns the value of an environment variable, or a fallback if unset.
static string EnvOr(string name, string fallback) =>
    EnvValue(name) ?? fallback;

// Builds a connection string from the standard libpq environment
// variables, with defaults suitable for a local development server. The
// builder takes care of quoting any special characters. PGSSLROOTCERT has
// no default, because verification is meaningless without the certificate
// of the authority that signed the server certificate.
static string ConnectionString() =>
    new NpgsqlConnectionStringBuilder
    {
        Host = EnvOr("PGHOST", "localhost"),
        Port = int.Parse(EnvOr("PGPORT", "5432")),
        Database = EnvOr("PGDATABASE", "postgres"),
        Username = EnvOr("PGUSER", "postgres"),
        Password = EnvValue("PGPASSWORD"),
        // VerifyFull checks that the certificate chains to the root
        // certificate and that it matches the host name. Weaker modes,
        // such as Require, encrypt the connection without proving who is
        // on the other end.
        SslMode = SslMode.VerifyFull,
        RootCertificate = EnvValue("PGSSLROOTCERT")
            ?? throw new InvalidOperationException(
                "PGSSLROOTCERT must name the root certificate"),
    }.ConnectionString;

// Creates a data source with the settings from the environment. A data
// source manages a pool of physical connections, and each call to
// OpenConnectionAsync borrows one from the pool; the connection fails if
// verification fails.
static NpgsqlDataSource CreateDataSource() =>
    NpgsqlDataSource.Create(ConnectionString());

// Asks the server whether this connection uses TLS.
static async Task<bool> ConnectionIsEncryptedAsync(NpgsqlConnection conn)
{
    // pg_stat_ssl has one row for each connection; pg_backend_pid()
    // identifies the one that belongs to this session. The version column
    // holds the protocol version, such as TLSv1.3.
    await using var cmd = new NpgsqlCommand(
        "SELECT ssl, version FROM pg_catalog.pg_stat_ssl " +
        "WHERE pid = pg_backend_pid()", conn);
    await using var reader = await cmd.ExecuteReaderAsync();

    // A boolean maps to bool.
    await reader.ReadAsync();
    return reader.GetBoolean(0);
}

try
{
    // await using disposes of the data source, and returns the connection
    // to the pool, when the block exits, whether or not an error occurred.
    await using var dataSource = CreateDataSource();
    await using var conn = await dataSource.OpenConnectionAsync();

    var encrypted = await ConnectionIsEncryptedAsync(conn);
    Console.WriteLine($"Connection is encrypted: {encrypted.ToString().ToLowerInvariant()}");
    // Reaching this line means that VerifyFull succeeded; a certificate
    // or host name mismatch throws instead.
    Console.WriteLine("Server certificate verified: true");
}
// NpgsqlException covers connection failures; PostgresException, which
// derives from it, carries errors reported by the server.
// InvalidOperationException reports a missing PGSSLROOTCERT.
catch (Exception e) when (e is NpgsqlException or InvalidOperationException)
{
    Console.Error.WriteLine($"Database error: {e.Message}");
    return 1;
}

return 0;

// Ellie's Postgres Cookbook
// Connecting to PostgreSQL over TLS with full verification, using pgx.
//
// Copyright (c) 2026, pgEdge, Inc.
// This code is released under the PostgreSQL License.

package main

import (
    "context"
    "errors"
    "fmt"
    "net"
    "net/url"
    "os"

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


// envOr returns the value of an environment variable, or a fallback if
// the variable is unset or empty.
func envOr(name, fallback string) string {
    if value := os.Getenv(name); value != "" {
        return value
    }
    return fallback
}

// connString builds a postgres:// URL from the standard libpq environment
// variables. net/url takes care of escaping any special characters in
// the user name, password, or database name. PGSSLROOTCERT has no
// default, because verification is meaningless without the certificate
// of the authority that signed the server certificate.
func connString() (string, error) {
    rootCert := os.Getenv("PGSSLROOTCERT")
    if rootCert == "" {
        return "", errors.New("PGSSLROOTCERT must name the root certificate")
    }

    u := url.URL{
        Scheme: "postgres",
        Host:   net.JoinHostPort(envOr("PGHOST", "localhost"), envOr("PGPORT", "5432")),
        Path:   "/" + envOr("PGDATABASE", "postgres"),
        // verify-full checks that the certificate chains to the root
        // certificate and that it matches the host name. Weaker modes,
        // such as require, encrypt the connection without proving who
        // is on the other end.
        RawQuery: url.Values{
            "sslmode":     {"verify-full"},
            "sslrootcert": {rootCert},
        }.Encode(),
    }
    if password := os.Getenv("PGPASSWORD"); password != "" {
        u.User = url.UserPassword(envOr("PGUSER", "postgres"), password)
    } else {
        u.User = url.User(envOr("PGUSER", "postgres"))
    }
    return u.String(), nil
}


// connect opens a verified TLS connection with the settings from the
// environment. The connection fails if verification fails.
func connect(ctx context.Context) (*pgx.Conn, error) {
    dsn, err := connString()
    if err != nil {
        return nil, err
    }
    conn, err := pgx.Connect(ctx, dsn)
    if err != nil {
        return nil, fmt.Errorf("connection failed: %w", err)
    }
    return conn, nil
}


// connectionIsEncrypted asks the server whether this connection uses TLS.
func connectionIsEncrypted(ctx context.Context, conn *pgx.Conn) (bool, error) {
    // pg_stat_ssl has one row for each connection; pg_backend_pid()
    // identifies the one that belongs to this session. The version column
    // holds the protocol version, such as TLSv1.3, and is NULL without
    // TLS, so it scans into a pointer.
    var ssl bool
    var version *string
    err := conn.QueryRow(ctx,
        "SELECT ssl, version FROM pg_catalog.pg_stat_ssl "+
            "WHERE pid = pg_backend_pid()",
    ).Scan(&ssl, &version)
    if err != nil {
        return false, fmt.Errorf("query failed: %w", err)
    }
    return ssl, nil
}


// run connects, runs the query, and prints the result, returning any
// error to the caller.
func run(ctx context.Context) error {
    conn, err := connect(ctx)
    if err != nil {
        return err
    }
    // The deferred call closes the connection when run() returns, whether
    // or not an error occurred.
    defer conn.Close(ctx)

    encrypted, err := connectionIsEncrypted(ctx, conn)
    if err != nil {
        return err
    }

    fmt.Printf("Connection is encrypted: %t\n", encrypted)
    // Reaching this line means that verify-full succeeded; a certificate
    // or host name mismatch makes connect() return an error instead.
    fmt.Println("Server certificate verified: true")
    return nil
}

// main reports any error returned by run() and exits with a non-zero
// status. Keeping the work in run() means that deferred cleanup always
// happens before the program exits.
func main() {
    if err := run(context.Background()); err != nil {
        fmt.Fprintf(os.Stderr, "Database error: %v\n", err)
        os.Exit(1)
    }
}

// Ellie's Postgres Cookbook
// Connecting to PostgreSQL over TLS with full verification, using the
// postgres and postgres-openssl crates.
//
// Copyright (c) 2026, pgEdge, Inc.
// This code is released under the PostgreSQL License.

use std::env;
use std::error::Error;
use std::process::ExitCode;

use openssl::ssl::{SslConnector, SslMethod};
use openssl::x509::store::X509StoreBuilder;
use openssl::x509::X509;
use postgres::config::SslMode;
use postgres::{Client, Config};
use postgres_openssl::MakeTlsConnector;

/// Returns the value of an environment variable, if it is set and not
/// empty; libpq also treats an empty variable as unset.
fn env_value(name: &str) -> Option<String> {
    env::var(name).ok().filter(|value| !value.is_empty())
}

/// Returns the value of an environment variable, or a fallback if the
/// variable is unset or empty.
fn env_or(name: &str, fallback: &str) -> String {
    env_value(name).unwrap_or_else(|| fallback.to_string())
}

/// Builds the connection configuration from the standard libpq
/// environment variables, with defaults suitable for a local server.
/// The default SSL mode of the crate is Prefer, which falls back to a
/// plain connection if the server declines TLS, so Require is set
/// explicitly. Require makes the connection fail instead; the TLS
/// connector built below then verifies the certificate.
fn config() -> Config {
    let mut config = Config::new();
    config
        .ssl_mode(SslMode::Require)
        .host(&env_or("PGHOST", "localhost"))
        .port(env_or("PGPORT", "5432").parse().expect("PGPORT must be a number"))
        .dbname(&env_or("PGDATABASE", "postgres"))
        .user(&env_or("PGUSER", "postgres"));
    if let Some(password) = env_value("PGPASSWORD") {
        config.password(&password);
    }
    config
}

/// Returns the path of the root certificate file. PGSSLROOTCERT has no
/// default, because verification is meaningless without the certificate
/// of the authority that signed the server certificate.
fn root_cert_path() -> Result<String, Box<dyn Error>> {
    env_value("PGSSLROOTCERT").ok_or_else(|| "PGSSLROOTCERT must name the root certificate".into())
}

/// Builds a TLS connector that trusts only the given root certificate.
/// SslConnector::builder starts with the system root certificates, so the
/// certificate store is replaced with one that holds nothing but the
/// certificates in the file; otherwise any publicly trusted certificate
/// for the host name would also be accepted. The connector verifies the
/// certificate chain and the host name by default, which together are the
/// equivalent of sslmode=verify-full; the host name checked is the one in
/// the Config.
fn tls_connector(root_cert: &str) -> Result<MakeTlsConnector, Box<dyn Error>> {
    let mut store = X509StoreBuilder::new()?;
    for cert in X509::stack_from_pem(&std::fs::read(root_cert)?)? {
        store.add_cert(cert)?;
    }

    let mut builder = SslConnector::builder(SslMethod::tls())?;
    builder.set_cert_store(store.build());
    Ok(MakeTlsConnector::new(builder.build()))
}

/// Opens a verified TLS connection with the settings from the
/// environment. The connection fails if verification fails.
fn connect() -> Result<Client, Box<dyn Error>> {
    let tls = tls_connector(&root_cert_path()?)?;
    Ok(config().connect(tls)?)
}

/// Asks the server whether this connection uses TLS.
fn connection_is_encrypted(client: &mut Client) -> Result<bool, postgres::Error> {
    // pg_stat_ssl has one row for each connection; pg_backend_pid()
    // identifies the one that belongs to this session. The version column
    // holds the protocol version, such as TLSv1.3, and is NULL without
    // TLS, so it maps to an Option.
    let row = client.query_one(
        "SELECT ssl, version FROM pg_catalog.pg_stat_ssl \
         WHERE pid = pg_backend_pid()",
        &[],
    )?;
    let _version: Option<String> = row.get(1);

    // ssl is a boolean, which maps to bool.
    Ok(row.get(0))
}

/// Connects, runs the query, and prints the result. The ? operator
/// returns any error to the caller.
fn run() -> Result<(), Box<dyn Error>> {
    let mut client = connect()?;
    let encrypted = connection_is_encrypted(&mut client)?;
    println!("Connection is encrypted: {encrypted}");
    // Reaching this line means that verification succeeded; a certificate
    // or host name mismatch makes connect() return an error instead.
    println!("Server certificate verified: true");

    // Dropping the client also closes the connection, including when ?
    // returns early, but close() lets us see any error that occurs while
    // doing so.
    client.close()?;
    Ok(())
}

/// Reports any error returned by run() and exits with a failure status.
/// The top-level error is often generic ("error connecting to server"),
/// so the chain of underlying causes is printed too.
fn main() -> ExitCode {
    match run() {
        Ok(()) => ExitCode::SUCCESS,
        Err(e) => {
            eprintln!("Database error: {e}");
            let mut cause = e.source();
            while let Some(c) = cause {
                eprintln!("  caused by: {c}");
                cause = c.source();
            }
            ExitCode::FAILURE
        }
    }
}