Skip to content

Database TLS

View as Markdown

This page is for anyone whose PostgreSQL runs somewhere other than the machine Colophon runs on: a managed database, or a database server of your own. Colophon talks to such a database only over TLS that proves the server is the database, and refuses to start otherwise. The quick start needs none of this: its database is on loopback.

The rule: sslmode=verify-full off this machine

Section titled “The rule: sslmode=verify-full off this machine”

Encryption alone doesn’t stop someone on the network path from answering as your database and reading every password and query. Only sslmode=verify-full checks both that a trusted authority signed the server’s certificate and that the certificate names the host in the URL. So for any host that isn’t this machine, Colophon accepts that mode and no other:

sslmode Off this machine Why
disable, allow Refused Sends passwords and content in plain text
prefer (the default when the URL names none) Refused Falls back to plain text and takes any certificate
require Refused Encrypts, but takes any certificate
verify-ca Refused Doesn’t check the name, and the driver trusts the public roots as well as sslrootcert, so any publicly trusted certificate for any name passes
verify-full Accepted Checks the certificate and the name

channel_binding=require is refused on every host, loopback included: the driver doesn’t implement channel binding and would silently ignore it. Remove it from the URL; verify-full is what proves who the server is.

The rule is checked before any connection is made, and applies to both URLs Colophon uses:

  • COLOPHON_DATABASE_URL, the server’s: colophon-server refuses to start, exit status 1.
  • COLOPHON_MIGRATION_DATABASE_URL, the CLI’s administration commands: colophon admin … exits with status 2.

A good URL looks like this:

postgres://colophon_app:[email protected]:5432/colophon?sslmode=verify-full

The server names the host, what is wrong and how to fix it, and never prints the URL or its password. You can watch it refuse in the quick start’s folder: this function starts one extra colophon-server with the database URL you give it (in a file, as production does), prints what it said and its exit status, and removes the file:

Terminal window
server_with_url() {
printf '%s' "$1" > database-url.test
docker compose --progress quiet run --rm --no-deps -T \
-v "$PWD/database-url.test:/run/test/database_url:ro" \
-e COLOPHON_DATABASE_URL_FILE=/run/test/database_url colophon
echo "exit status $?"
rm -f database-url.test
}

db.example.com doesn’t exist, so a server that tried to connect would fail differently; these refusals come before any connection. sslmode=require:

Terminal window
server_with_url 'postgres://colophon_app:[email protected]:5432/colophon?sslmode=require'
Error: loading configuration
Caused by:
invalid configuration (1 problem(s)):
COLOPHON_DATABASE_URL: database URL refused: the database at db.example.com isn't checked to be who it says it is: sslmode=require encrypts but takes any certificate. Use sslmode=verify-full, with sslrootcert=<CA file> if a private authority signs its certificate; only a loopback host or a Unix socket may go without
exit status 1

A URL with no sslmode at all is refused the same way, as prefer:

Terminal window
server_with_url 'postgres://colophon_app:[email protected]:5432/colophon'
Error: loading configuration
Caused by:
invalid configuration (1 problem(s)):
COLOPHON_DATABASE_URL: database URL refused: the database at db.example.com isn't checked to be who it says it is: sslmode=prefer falls back to plain text and takes any certificate (it is the default when the URL names no sslmode). Use sslmode=verify-full, with sslrootcert=<CA file> if a private authority signs its certificate; only a loopback host or a Unix socket may go without
exit status 1

verify-ca, which checks the authority but not the name:

Terminal window
server_with_url 'postgres://colophon_app:[email protected]:5432/colophon?sslmode=verify-ca'
Error: loading configuration
Caused by:
invalid configuration (1 problem(s)):
COLOPHON_DATABASE_URL: database URL refused: the database at db.example.com isn't checked to be who it says it is: sslmode=verify-ca doesn't check that the certificate names the host, and this driver trusts the public roots as well as sslrootcert, so any publicly trusted certificate passes. Use sslmode=verify-full, with sslrootcert=<CA file> if a private authority signs its certificate; only a loopback host or a Unix socket may go without
exit status 1

And channel_binding=require, even with verify-full:

Terminal window
server_with_url 'postgres://colophon_app:[email protected]:5432/colophon?sslmode=verify-full&channel_binding=require'
Error: loading configuration
Caused by:
invalid configuration (1 problem(s)):
COLOPHON_DATABASE_URL: database URL refused: channel_binding=require can't be honoured: this driver doesn't do channel binding and would ignore it. Remove channel_binding from the URL; sslmode=verify-full is what proves the server is the database
exit status 1

The CLI prints the same reason on one line, then exits 2. In the quick start’s folder, with a sslmode=require URL for a database on another host:

Terminal window
docker compose exec -T -e COLOPHON_MIGRATION_DATABASE_URL='postgres://[email protected]:5432/colophon?sslmode=require' \
colophon colophon admin migrate-status
error: database URL refused: the database at db.example.com isn't checked to be who it says it is: sslmode=require encrypts but takes any certificate. Use sslmode=verify-full, with sslrootcert=<CA file> if a private authority signs its certificate; only a loopback host or a Unix socket may go without

Colophon speaks TLS to PostgreSQL itself (rustls), with the Mozilla set of public root certificates built into the binary. It trusts those, not the operating system’s certificate store, so the same URL behaves the same on any host and in the container.

  • A certificate from a public authority, as managed databases usually have: nothing more to do. sslmode=verify-full is enough.

  • A certificate from your own authority: add sslrootcert= with the path of the authority’s certificate (PEM). It is added to the built-in roots, not used instead of them.

    postgres://colophon_app:[email protected]:5432/colophon?sslmode=verify-full&sslrootcert=/etc/colophon/db-ca.pem

    The file isn’t secret, but the server must be able to read it: under the systemd unit, which runs as a dynamic user, keep it world-readable (0644) somewhere under /etc, outside the root-only /etc/colophon/credentials/.

In both cases the host in the URL must be a name the certificate lists. Connecting by IP address works only if the certificate names that address.

The database on this machine may be reached in plain text, because nothing on the network can sit in between:

  • the host localhost;
  • a loopback address: 127.0.0.1 (or anything in 127.0.0.0/8) or ::1;
  • a Unix socket, written as a host= path or a host that starts with /, as in postgres://colophon_app:PASSWORD@/colophon?host=/var/run/postgresql.

Any other name is “another machine”, including names such as db.localhost and a Docker service name like postgres. That is why the quick start runs PostgreSQL inside the same network namespace as Colophon: it is on 127.0.0.1 there, unreachable from the host or any other container.

The general rule covers them: take the URL the provider gives you and make sure it ends in sslmode=verify-full with no channel_binding=require. One provider needs more care:

Neon. Its dashboard copies a URL ending ?sslmode=require&channel_binding=require. Change the end to ?sslmode=verify-full. Neon’s certificates chain to a public root, so no sslrootcert is needed. Use the direct (unpooled) endpoint, not the pooled one: Colophon relies on LISTEN for live updates and on session advisory locks, and neither survives a transaction-mode pooler. Size COLOPHON_DB_MAX_CONNECTIONS to your plan’s connection limit.

The same caution about poolers applies to any provider: point Colophon at a direct connection.

Two logins: the server’s and the migration login

Section titled “Two logins: the server’s and the migration login”

Colophon uses two different database logins, and they should never be swapped:

Migration login Application login
Who uses it The CLI’s colophon admin … commands colophon-server
Setting COLOPHON_MIGRATION_DATABASE_URL or …_FILE (or --database-url, --database-url-file) COLOPHON_DATABASE_URL or …_FILE
What it is The owner of Colophon’s tables: it creates the roles and applies migrations. In production, a dedicated owner that isn’t a superuser. colophon_app, made by colophon admin init-roles with a password you generate. It owns no tables and can’t bypass row-level security.
When Install, upgrades, backups and restores Always, while the server runs

Both follow the same TLS rule. The Installation guide makes them in this order:

Terminal window
sudo install -d -m 0700 /etc/colophon/credentials
head -c 32 /dev/urandom | base64 | tr '+/' '-_' | tr -d '=\n' | sudo tee /etc/colophon/credentials/app_password >/dev/null
echo 'postgres://owner:…@db.example.com/colophon?sslmode=verify-full' | sudo tee /etc/colophon/credentials/migration_database_url >/dev/null
sudo chmod 0400 /etc/colophon/credentials/*
sudo colophon admin init-roles --database-url-file /etc/colophon/credentials/migration_database_url \
--app-password-file /etc/colophon/credentials/app_password
sudo colophon admin migrate --database-url-file /etc/colophon/credentials/migration_database_url

Then /etc/colophon/credentials/database_url holds the server’s URL, with the same host and the same sslmode=verify-full:

postgres://colophon_app:<app_password>@db.example.com/colophon?sslmode=verify-full

colophon admin init-roles is safe to run again: roles that exist, passwords included, are left as they are. So it can’t change a password: to rotate colophon_app’s, connect with psql as the migration login and run \password colophon_app (it sends only a hash, so the password doesn’t land in PostgreSQL’s logs), write the new URL to /etc/colophon/credentials/database_url, and restart the server.

Before starting the server, colophon admin migrate-status only reads, and proves the migration login connects with verified TLS:

Terminal window
sudo colophon admin migrate-status --database-url-file /etc/colophon/credentials/migration_database_url
schema current: 68 migrations applied, none of 68 pending

Once the server runs, colophon doctor says whether the server’s own login reaches the database (database up); see Monitoring. A database that is reachable but refuses the certificate shows as database down, and the reason is in the server’s log.

Was this page helpful?

Either answer opens a short issue on GitHub, so you can say what worked or what was missing.

Colophon home · the product, its blog and how to try it.