TLS PostgreSQL

TLS between the CDP4-COMET WebServices and PostgreSQL

The CDP4-COMET WebServices can encrypt the connection to PostgreSQL with TLS and verify the identity of the database server. This page explains how to configure both sides of the connection, how to run it with Docker, and how to read the log messages when something is wrong.

TLS for the database connection is available in the CDP4-COMET WebServices releases, after 11.0.0-RC3.

When do you need it?

Deployment	Recommendation
WebServices and PostgreSQL in the same Docker Compose project	The traffic stays on the private Docker network of the host. TLS is optional (defence in depth).
PostgreSQL on another machine	Use TLS with SslMode=VerifyFull. Without it, the database password and all model data travel in plain text.
Managed PostgreSQL (Azure, AWS, ...)	These services usually require TLS. Use SslMode=VerifyFull, see Managed PostgreSQL.

The default behaviour

Without any TLS setting, the WebServices use SslMode=Prefer:

• when the server has TLS enabled, the connection is encrypted, but the server certificate is not verified, so it is not protected against a man-in-the-middle;
• when the server has TLS disabled (the default of the PostgreSQL Docker images), the connection silently falls back to plain text.

The startup log tells you which of the two applies, see Log messages.

WebServices configuration

The TLS settings are part of the Backtier section, see Configuration. Like any setting, they can be overridden by environment variables (Backtier__SslMode=VerifyFull).

"Backtier": {
    "HostName": "comet-db",
    "Port": 5432,
    "UserName": "cdp4",
    "Password": "cdp4",
    "Database": "cdp4server",
    "DatabaseRestore": "cdp4serverrestore",
    "DatabaseManage": "cdp4manage",
    "StatementTimeout": 180,
    "IsDbSeedEnabled": false,
    "IsDbRestoreEnabled": false,
    "IsDbImportEnabled": false,
    "SslMode": "VerifyFull",
    "RootCertificate": "/app/certs/db-ca.crt",
    "ClientCertificate": "",
    "ClientCertificateKey": "",
    "ClientCertificatePassword": "",
    "CheckCertificateRevocation": false
}

Setting	Default	Description
SslMode	Prefer	Disable, Allow, Prefer, Require, VerifyCA or VerifyFull, see the table below. A misspelled value stops the server at startup and lists the valid values.
RootCertificate	empty	Path to the CA certificate (PEM or DER) used to verify the PostgreSQL server. See Which CA is used.
ClientCertificate	empty	Optional client certificate, only needed when PostgreSQL authenticates with certificates (cert in pg_hba.conf). PEM, or PFX containing the key.
ClientCertificateKey	empty	Private key (PEM) of the ClientCertificate. Not needed for a PFX file, or when the key is in the same PEM file.
ClientCertificatePassword	empty	Password of an encrypted private key or of a PFX file.
CheckCertificateRevocation	false	Checks whether the server certificate has been revoked. The CRL/OCSP endpoints of the CA must be reachable from the WebServices. Only true or false are accepted.

SslMode

SslMode	Encrypted	Server identity verified	Use it for
Disable	no	no	never in production
Allow	only if the server insists	no	not recommended
Prefer (default)	if the server supports it	no	backwards compatibility
Require	always	no	only on a network you trust
VerifyCA	always	the certificate chain	when the host name cannot match the certificate
VerifyFull	always	the certificate chain and the host name	recommended

With VerifyFull, the certificate of the PostgreSQL server must be valid for the name in Backtier:HostName. In Docker Compose that is the service or host name (for example comet-db), not localhost and not an IP address, unless that address is in the certificate.

Which CA is used

To verify the server certificate, the WebServices look for a CA certificate in this order, the same order Npgsql uses:

1. the file in Backtier:RootCertificate;
2. the file in the PGSSLROOTCERT environment variable;
3. the default root.crt of the user running the WebServices, when it exists (~/.postgresql/root.crt on Linux, %APPDATA%\postgresql\root.crt on Windows);
4. the trust store of the operating system. The WebServices Docker image contains the public CA bundle, so certificates from a public CA are trusted without any setting.

The startup log shows which one is used: Backtier-RootCertificate: /app/certs/db-ca.crt (Backtier:RootCertificate).

Startup checks

Before connecting, the WebServices check the configured certificate files: they must exist and be readable, the CA file must contain a certificate, and the client certificate must load together with its key and password. When a check fails, the server logs the problem and the setting to change, and stops with exit code 1.

PostgreSQL configuration

The client settings alone cannot prevent a fallback to plain text: the server has to refuse unencrypted connections.

1. Enable TLS, in postgresql.conf or as -c arguments of the postgres command:

ssl = on
ssl_cert_file = '/etc/postgresql-tls/server.crt'
ssl_key_file = '/etc/postgresql-tls/server.key'
ssl_min_protocol_version = 'TLSv1.2'

2. Only accept encrypted network connections: use hostssl instead of host in pg_hba.conf. The local line is used by the initialisation scripts of the Docker image:

local   all  all  trust
hostssl all  all  all  scram-sha-256

3. The private key must be owned by the user PostgreSQL runs as, and must not be readable by others (chmod 600). Otherwise PostgreSQL does not start. In the stariongroup/cdp4-database-community-edition and stariongroup/cdp4-test-database-community-edition images (Alpine) that user is postgres with uid 70; in Debian-based PostgreSQL images it is uid 999.

Creating a test certificate

For a test installation, a CA and a server certificate can be created with OpenSSL. The server certificate must be valid for the host name the WebServices connect to (comet-db in the examples). For production, use certificates from your organisation's CA.

mkdir certs && cd certs
openssl req -x509 -newkey rsa:2048 -nodes -days 365 -subj "/CN=COMET Database CA" -keyout ca.key -out ca.crt
openssl req -newkey rsa:2048 -nodes -subj "/CN=comet-db" -keyout server.key -out server.csr
printf "subjectAltName=DNS:comet-db\n" > san.cnf
openssl x509 -req -in server.csr -CA ca.crt -CAkey ca.key -CAcreateserial -days 365 -out server.crt -extfile san.cnf
printf "local   all  all  trust\nhostssl all  all  all  scram-sha-256\n" > pg_hba.conf

Keep ca.key out of the deployment: only ca.crt goes to the WebServices, and only server.crt, server.key and pg_hba.conf go to PostgreSQL.

Docker Compose example

The example below runs PostgreSQL with TLS only, and WebServices that connect with VerifyFull. The certs folder is the one created above.

services:

  comet_db:
    image: stariongroup/cdp4-database-community-edition:4.2.0
    container_name: comet-db
    hostname: comet-db
    shm_size: 2gb
    restart: always
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=change-me
    # copy the certificate files into the container with the ownership PostgreSQL requires;
    # this works on Linux, Windows and macOS hosts alike
    entrypoint: >
      sh -c 'mkdir -p /etc/postgresql-tls &&
             cp /certs/server.crt /certs/server.key /certs/pg_hba.conf /etc/postgresql-tls/ &&
             chown -R postgres:postgres /etc/postgresql-tls && chmod 600 /etc/postgresql-tls/server.key &&
             exec docker-entrypoint.sh "$$@"' --
    command: >
      postgres -c max_locks_per_transaction=1024
               -c ssl=on
               -c ssl_cert_file=/etc/postgresql-tls/server.crt
               -c ssl_key_file=/etc/postgresql-tls/server.key
               -c ssl_min_protocol_version=TLSv1.2
               -c hba_file=/etc/postgresql-tls/pg_hba.conf
    volumes:
      - comet-db-data:/var/lib/postgresql/data
      - ./certs:/certs:ro
    networks:
      - comet

  comet_webservices:
    image: stariongroup/comet-webservices-community-edition:<version with TLS support>
    container_name: comet-webservices
    restart: always
    depends_on:
      - comet_db
    environment:
      - Backtier__HostName=comet-db
      - Backtier__SslMode=VerifyFull
      - Backtier__RootCertificate=/app/certs/db-ca.crt
    volumes:
      - ./certs/ca.crt:/app/certs/db-ca.crt:ro
      - comet-ws-logs:/app/logs
      - comet-ws-storage:/app/storage
      - comet-ws-upload:/app/upload
    ports:
      - "5000:5000"
    networks:
      - comet

networks:
  comet:

volumes:
  comet-db-data:
  comet-ws-logs:
  comet-ws-storage:
  comet-ws-upload:

Notes:

• On a Linux host you can leave out the entrypoint and mount the files directly at /etc/postgresql-tls, after giving them the right ownership on the host: sudo chown 70:70 certs/server.key certs/server.crt certs/pg_hba.conf && sudo chmod 600 certs/server.key.
• The database port is not published. The WebServices reach PostgreSQL over the comet network. If you need access from the host, for example for pgAdmin, publish it on the loopback address only: '127.0.0.1:5432:5432'.
• The WebServices image runs as a non-root user ($APP_UID). The mounted CA file must be readable by that user, for example chmod 644 certs/ca.crt. The CA certificate is not secret.
• The continuous integration of the WebServices runs the Integration Test Suite against a PostgreSQL configured this way, see the steps "Create TLS certificates for PostgreSQL" and "Start PostgreSQL Container (TLS only)" in CodeQuality.yml.

Managed PostgreSQL

Managed services such as Azure Database for PostgreSQL and Amazon RDS for PostgreSQL support TLS and often require it.

• Use SslMode=VerifyFull and set Backtier:HostName to the host name the provider gives you; the provider's certificate is issued for that name.
• Use the CA certificate published by the provider (for Amazon RDS, the regional or global certificate bundle) in Backtier:RootCertificate. When the provider's certificate chains to a public root CA, the CA bundle of the WebServices image already trusts it and RootCertificate can stay empty.
• Check the provider's documentation for CA rotations: when the provider changes its CA, the file in RootCertificate must be updated.

Client certificate authentication (optional)

Instead of a password, PostgreSQL can authenticate the WebServices with a client certificate:

1. On the server: set ssl_ca_file to the CA that issues the client certificates, and use cert in pg_hba.conf: hostssl all cdp4 all cert. The common name (CN) of the client certificate must equal the database user name (cdp4).
2. In the WebServices: set Backtier:ClientCertificate (and ClientCertificateKey and ClientCertificatePassword as needed).

Default passwords

The Docker examples in this wiki and in the repository use default passwords. Change them before you put a server into production:

Account	Default	Where it is set
PostgreSQL superuser postgres	pass (in docker-compose.yml)	POSTGRES_PASSWORD of the database container. It is only applied when the database volume is created for the first time; afterwards change it with ALTER ROLE postgres WITH PASSWORD '...';
COMET database user cdp4	cdp4	Created by the database image. Change it with ALTER ROLE cdp4 WITH PASSWORD '...'; and set the same value in Backtier:Password (Backtier__Password)
CDP4-COMET administrator admin	pass	See Restore default admin password
Persons seeded from an Annex C.3 file	pass	Defaults:PersonPassword, see Configuration

Log messages

At startup the WebServices log the TLS configuration and the result of the first connection. This table lists the messages, what they mean and what to do.

Log message	Meaning	What to do
INF The connection to the data store at comet-db:5432 is encrypted using TLSv1.3 TLS_AES_256_GCM_SHA384 (Backtier:SslMode VerifyFull)	Encrypted and verified	Nothing
WRN The connection to the data store ... is NOT encrypted (Backtier:SslMode Prefer). Credentials and data are sent in plain text	The server does not support TLS, or SslMode is Disable	Enable TLS on the server and set SslMode to VerifyFull
WRN The identity of the data store server is not fully verified (Backtier:SslMode Require)	Encrypted, but the server certificate is not (fully) checked	Set SslMode to VerifyFull and configure the CA
FTL Invalid TLS setting for the data-store connection: The file '...' configured in Backtier:RootCertificate does not exist	A configured certificate file is missing, unreadable or invalid	Fix the path, the mount or the file permissions
FTL ... The client certificate '...' could not be loaded. Check the files and Backtier:ClientCertificatePassword	Client certificate, key or password do not match	Check the files and the password
FTL ... failed: The TLS handshake failed or the server certificate was rejected followed by TLS diagnostics: lines	The server certificate was rejected. The PROBLEM: lines explain why	See the next rows
TLS diagnostics: PROBLEM: the server certificate issued by '...' is not trusted by Backtier:RootCertificate '...' containing ['...']	The configured CA did not issue the server certificate	Configure the CA that issued the server certificate, or add missing intermediate certificates to ssl_cert_file on the server
TLS diagnostics: PROBLEM: the server certificate is not valid for the host name '...' ... It is valid for [...]	Backtier:HostName is not in the certificate	Connect with one of the listed names, or issue a certificate for the host name
TLS diagnostics: PROBLEM: the server certificate expired on ... / the CA certificate '...' ... is not valid now	Expired (or not yet valid) server or CA certificate	Renew the certificate; check the clock of both machines
FTL ... Backtier:SslMode is VerifyFull, which requires TLS, but the PostgreSQL server does not accept TLS connections	TLS is disabled on the server	Enable TLS on the server (ssl = on)
FTL ... The PostgreSQL server only accepts encrypted connections, but Backtier:SslMode is Disable	The server only allows hostssl	Set SslMode to VerifyFull
FTL ... The PostgreSQL server rejected the credentials of user 'cdp4'	Wrong database user or password	Check Backtier:UserName and Backtier:Password
INF Waiting for the data store ... Reason: 3D000: database "cdp4server" does not exist. The database 'cdp4server' is created when an Annex C.3 file is posted to /Data/Exchange	A new installation that has not been seeded yet	Seed the server, see [[REST API - ICD

Configuration and TLS errors stop the server at once, with exit code 1, instead of waiting for Midtier:BacktierWaitTime. Temporary problems, such as a database that is still starting, are retried until BacktierWaitTime has passed.