> ## Documentation Index
> Fetch the complete documentation index at: https://docs.minimus.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Postgres TLS Tutorial

> A guide to setting up Postgres and testing that it accepts TLS connections, enforces authentication, and allows secure read/write operations from a test client

The following guide will help you deploy the Minimus Postgres image with self-signed, locally issued certificates to help you get started. Run the code to try it for yourself.

<Info>
  For production purposes, we recommend using publicly trusted certificates issued by a Certificate Authority (CA).
</Info>

## Components

* **Postgres image built by Minimus**: Postgres container configured with the secure configuration for client authentication.
* **psql** installed
* Dynamic certificate generation via OpenSSL:
  * **certgen.sh script**: Shell script that generates a custom CA, server, and client certificates using OpenSSL.
  * **minidebug image**: A Minimus dev toolkit that provides a shell, OpenSSL, and other utilities used to generate the certificates.

## What this guide demonstrates

* TLS handshake validation
* Server/client certificate trust
* Basic auth and Postgres operations
* Image compatibility

## Directory Structure

```bash theme={null}
.
├── certgen.sh             # Certificate generation script
├── create-certs.yml       # Compose file to run certgen container
├── entrypoint.sh          # Custom PostgreSQL entrypoint to set permissions
├── pg_hba.conf            # Custom pg_hba config to require SSL and client certs
└── docker-compose.yml     # Compose file to run PostgresDB
```

## Deploy Postgres with TLS certificates

### Prerequisite: Authenticate to the Minimus Registry

Run the docker login command to authenticate to the Minimus registry:

```shellscript theme={null}
echo "{token}" | docker login reg.mini.dev -u minimus --password-stdin
```

### Step 1: Generate TLS certificates

<Steps>
  <Step title="Save script that generates TLS certificates">
    Save the following script to a file named `certgen.sh`. The script is used to generate the TLS certificates and store them in a `certs` folder on the host.

    ```bash certgen.sh expandable theme={null}
    #!/bin/sh
    # Company: Minimus
    # Author: Alexander Haytovich

    set -e
    cd /certs

    echo "[INFO] Creating OpenSSL config for server..."
    cat > openssl.cnf <<EOF
    [req]
    distinguished_name = req_distinguished_name
    req_extensions = v3_server
    prompt = no

    [req_distinguished_name]
    CN = postgres

    [v3_server]
    keyUsage = keyEncipherment, dataEncipherment
    extendedKeyUsage = serverAuth
    subjectAltName = @alt_names

    [alt_names]
    DNS.1 = postgres
    DNS.2 = localhost
    IP.1 = 127.0.0.1
    IP.2 = 192.168.30.0
    IP.3 = 192.168.30.2
    IP.4 = 192.168.30.3



    [v3_ca]
    subjectKeyIdentifier = hash
    authorityKeyIdentifier = keyid:always,issuer
    basicConstraints = critical, CA:true
    keyUsage = critical, keyCertSign, cRLSign
    EOF

    echo "[INFO] Creating OpenSSL config for client..."
    cat > client_openssl.cnf <<EOF
    [req]
    distinguished_name = req_distinguished_name
    req_extensions = v3_client
    prompt = no

    [req_distinguished_name]
    CN = testuser

    [v3_client]
    keyUsage = critical, digitalSignature, keyEncipherment
    extendedKeyUsage = clientAuth
    EOF

    echo "[INFO] Generating CA certificate..."
    openssl genrsa -out ca-key.pem 2048
    openssl req -x509 -new -nodes -key ca-key.pem \
      -sha256 -days 365 -out ca.pem \
      -subj "/CN=Test CA" \
      -extensions v3_ca -config openssl.cnf

    echo "[INFO] Generating server key and CSR..."
    openssl genrsa -out server-key.pem 2048
    openssl req -new -key server-key.pem -out server.csr -config openssl.cnf -extensions v3_server

    echo "[INFO] Signing server certificate..."
    openssl x509 -req -in server.csr -CA ca.pem -CAkey ca-key.pem -CAcreateserial \
      -out server-cert.pem -days 365 -sha256 \
      -extfile openssl.cnf -extensions v3_server

    echo "[INFO] Generating client certificate..."
    openssl genrsa -out client-key.pem 2048
    openssl req -new -key client-key.pem -out client.csr \
      -config client_openssl.cnf -extensions v3_client
    openssl x509 -req -in client.csr -CA ca.pem -CAkey ca-key.pem -CAcreateserial \
      -out client-cert.pem -days 365 -sha256 \
      -extfile client_openssl.cnf -extensions v3_client

    # Ensure private key is root-owned and protected (for PostgreSQL)
    echo "[INFO] Fixing permissions for client-key.pem..."

    #python client not running as root, changing client permissions to user 1000 that is running the python client
    chown 1000:1000 /certs/client-key.pem
    chmod 600 /certs/client-key.pem


    echo "[SUCCESS] Certificates created:"
    ```
  </Step>

  <Step title="Save Docker Compose configuration">
    Save the following YAML file to run with Docker Compose. It uses the [**Minimus minidebug image**](https://images.minimus.io/gallery/images/minidebug/quick-start?__hstc=180987128.11065ee83c8bdcec1851176c12d849d3.1762436227738.1762436227738.1762436227738.1&__hssc=180987128.1.1762436227739&__hsfp=2666866004) to generate the certificates with the `certgen.sh` shell script. Minidebug is a Minimus dev toolkit that provides a shell, OpenSSL, and other utilities. The certificates will be persisted in the `certs` volume on the host.

    ```yaml create-certs.yml theme={null}
    services:
      create_certs:
        image: reg.mini.dev/minidebug:latest
        container_name: create_certs
        volumes:
        - ./certs:/certs
        - ./certgen.sh:/certgen.sh:ro
        entrypoint:
        - /bin/sh
        - /certgen.sh
        network_mode: none
    ```
  </Step>

  <Step title="Generate certificates">
    Run the following to generate the certificates:

    ```shellscript theme={null}
    docker compose -f create-certs.yml up
    ```
  </Step>
</Steps>

Congrats! You have just generated the following self-signed certificates:

* Self-signed CA certificate (`ca.pem`)
* Server certificates (`server-cert.pem`, `server-key.pem`) with SANs: `postgres`, `localhost`, `127.0.0.1`, and `192.168.30.3`
* Client certificates for `testuser`(`client.pem`, `client-key.pem`)

Client private key permissions are set to `0600` and owned by UID `1000`. Certificate permissions are adjusted to support non-root containers. In the next steps, you will mount these certificates into the Postgres container.

### Step 2: Deploy Postgres server

<Steps>
  <Step title="Save custom entrypoint">
    ```bash entrypoint.sh theme={null}
    #!/bin/bash
    set -e
    # Fix ownership so postgres can use the private key
    echo "[INFO] Fixing ownership and permissions of /certs..."
    chown postgres:postgres /certs/server-key.pem
    echo "[INFO] Fixing file permissions..."
    # Restrict private key access
    chmod 600 /certs/server-key.pem

    echo "[INFO] Launching PostgreSQL..."
    exec docker-entrypoint.sh "$@"
    ```

    Make sure the entrypoint script is executable on your host. If necessary, give it execute permissions:

    <CodeGroup>
      ```bash Give file execute permissions theme={null}
      chmod +x ./entrypoint.sh
      ```

      ```bash Verify execute permissions theme={null}
      ls -l ./entrypoint.sh
      ```
    </CodeGroup>
  </Step>

  <Step title="Save custom HBA">
    PostgreSQL’s default access rules are defined in the file `pg_hba.conf`.  Save the following configuration to mount it and customize the Host-Based Authentication rules.

    ```bash pg_hba.conf theme={null}
    # Allow local socket connections without SSL (for internal psql commands)
    local   all             all                                     trust

    # Allow readonly_user to connect using password over SSL
    hostssl all readonly_user 0.0.0.0/0 scram-sha-256

    # Allow remote SSL connections with client cert validation
    hostssl all             all             0.0.0.0/0               cert clientcert=verify-full
    ```
  </Step>

  <Step title="Save Docker Compose script">
    Save the following Docker Compose script to a file named `docker-compose.yml`. This script sets up the Postgres service with a healthcheck, mounts a volume with the certificates, the custom entrypoint and the custom HBA config, and maps port 5432.

    ```yaml docker-compose.yml expandable theme={null}
    services:
      postgres:
        image: reg.mini.dev/postgres
        container_name: pg_ssl
        environment:
          POSTGRES_USER: testuser
          POSTGRES_PASSWORD: testpass
          POSTGRES_DB: testdb
        volumes:
        - pgdata:/var/lib/postgresql/data
        - ./certs:/certs
        - ./entrypoint.sh:/entrypoint.sh:ro
        - ./pg_hba.conf:/etc/postgresql/pg_hba.conf
        ports:
        - 5432:5432
        healthcheck:
          test:
          - CMD
          - pg_isready
          - -U
          - testuser
          - -d
          - testdb
          interval: 5s
          retries: 10
        entrypoint:
        - /entrypoint.sh
        command: "postgres\n  -c ssl=on\n  -c ssl_cert_file=/certs/server-cert.pem\n \
          \ -c ssl_key_file=/certs/server-key.pem\n  -c ssl_ca_file=/certs/ca.pem\n  -c\
          \ hba_file=/etc/postgresql/pg_hba.conf\n"
    volumes:
      pgdata: null
    ```
  </Step>

  <Step title="Run Postgres">
    Start the Postgres container:

    ```shellscript theme={null}
    docker compose -f docker-compose.yml up
    ```
  </Step>
</Steps>

### Step 3: Test your Postgres server

You can use psql to connect over TLS and run tests. For example, here are a few commands you can try out:

1. Connect to the db:

   <CodeGroup>
     ```bash Connect to DB theme={null}
     psql "host=127.0.0.1 \
     port=5432 \
     dbname=testdb \
     user=testuser \
     sslmode=verify-ca \
     sslrootcert=./certs/ca.pem \
     sslcert=./certs/client-cert.pem \
     sslkey=./certs/client-key.pem"
     ```

     ```bash Expected response theme={null}
     psql (15.14 (Debian 15.14-0+deb12u1), server 18.0)
     WARNING: psql major version 15, server major version 18.
              Some psql features might not work.
     SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
     Type "help" for help.
     testdb=# 
     ```
   </CodeGroup>
2. Show all schemas in current database:

   <CodeGroup>
     ```shellscript Create database theme={null}
     \dn
     ```

     ```bash Example response theme={null}
     testdb=# \dn
           List of schemas
       Name  |       Owner       
     --------+-------------------
      public | pg_database_owner
     (1 row)
     ```
   </CodeGroup>
3. Show server version:

   <CodeGroup>
     ```bash Show server version theme={null}
     SELECT version();
     ```

     ```bash Example response theme={null}
     ----------------------------------------------------------------------------------------------------------------
     PostgreSQL 18.0 on x86_64-pc-linux-gnu, compiled by x86_64-pc-linux-gnu-gcc (MinimOS 15.2.0-r1) 15.2.0, 64-bit
     (1 row)
     ```
   </CodeGroup>
4. Check that TLS is active:
   <CodeGroup>
     ```bash Check TLS theme={null}
     SHOW ssl;
     SHOW ssl_cert_file;
     SHOW ssl_key_file;
     ```
   </CodeGroup>