Database TLS
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-serverrefuses to start, exit status1.COLOPHON_MIGRATION_DATABASE_URL, the CLI’s administration commands:colophon admin …exits with status2.
A good URL looks like this:
postgres://colophon_app:[email protected]:5432/colophon?sslmode=verify-fullWhat the refusal looks like
Section titled “What the refusal looks like”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:
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:
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 1A URL with no sslmode at all is refused the same way, as prefer:
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 1verify-ca, which checks the authority but not the name:
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 1And channel_binding=require, even with verify-full:
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 1The 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:
docker compose exec -T -e COLOPHON_MIGRATION_DATABASE_URL='postgres://[email protected]:5432/colophon?sslmode=require' \ colophon colophon admin migrate-statuserror: 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 withoutWhich certificates Colophon trusts
Section titled “Which certificates Colophon trusts”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-fullis 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.pemThe 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.
When TLS isn’t needed
Section titled “When TLS isn’t needed”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 in127.0.0.0/8) or::1; - a Unix socket, written as a
host=path or a host that starts with/, as inpostgres://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.
Managed databases
Section titled “Managed databases”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:
sudo install -d -m 0700 /etc/colophon/credentialshead -c 32 /dev/urandom | base64 | tr '+/' '-_' | tr -d '=\n' | sudo tee /etc/colophon/credentials/app_password >/dev/nullecho 'postgres://owner:…@db.example.com/colophon?sslmode=verify-full' | sudo tee /etc/colophon/credentials/migration_database_url >/dev/nullsudo 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_passwordsudo colophon admin migrate --database-url-file /etc/colophon/credentials/migration_database_urlThen /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-fullcolophon 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.
Check the connection
Section titled “Check the connection”Before starting the server, colophon admin migrate-status only reads, and proves the migration login connects
with verified TLS:
sudo colophon admin migrate-status --database-url-file /etc/colophon/credentials/migration_database_urlschema current: 68 migrations applied, none of 68 pendingOnce 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.
Related
Section titled “Related”- Configuration: every database setting and how to read secrets from files.
- Backups and restore: the third login,
colophon_backup, for base backups. - Security notes: what else an operator owns.
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.