Database setup

This page prepares PostgreSQL for the server. All server state lives in one PostgreSQL 14+ database. The server connects to it at startup but never creates or changes the schema: you load schema.sql yourself.

Create the role and database

The schema grants everything to a role named ricochet. Create the role with a password, and a database it owns:

sudo -u postgres psql <<'EOF'
CREATE ROLE ricochet LOGIN PASSWORD 'change-me';
CREATE DATABASE ricochet OWNER ricochet;
EOF

If you skip the role, schema.sql creates ricochet itself, without a password. Set one before the server connects: ALTER ROLE ricochet PASSWORD '...'.

Load the schema

schema.sql ships in every release archive, at /opt/ricochet/schema.sql in the .deb, and at the root of the source tree.

psql -h localhost -U ricochet -d ricochet -f schema.sql

You can also load it as the postgres superuser (sudo -u postgres psql ricochet < schema.sql). Either way the file grants SELECT, INSERT, UPDATE and DELETE on every table, and usage on every sequence, to ricochet.

The file is idempotent. Every CREATE uses IF NOT EXISTS, and columns added after the first release are added in an “in-place upgrades” section with ADD COLUMN IF NOT EXISTS. Running it again on an existing database is safe, and is how you apply schema changes on upgrade. See Upgrades & draining.

The tables are listed in Database schema.

Point the server at the database

The connection settings and their defaults:

Setting	YAML key	Flag	Default
Host	database.host	--pg-host	localhost
Port	database.port	--pg-port	5432
Database	database.name	--pg-database	ricochet
User	database.username	--pg-username	ricochet
Password	database.password	--pg-password	empty
TLS mode	database.sslmode	--pg-sslmode	require (disable in the development preset)
Pool size	database.pool_size	none	25 (50 in the production preset)

/etc/ricochet/config.yaml

database:
  host: db.internal.example
  port: 5432
  name: ricochet
  username: ricochet
  sslmode: require
  pool_size: 50

Keep the password off the command line

Set the password in the RICOCHET_PG_PASSWORD environment variable:

export RICOCHET_PG_PASSWORD='change-me'
ricochet --production --pg-host db.internal.example

The password is resolved in this order, last one wins: config file, RICOCHET_PG_PASSWORD, --pg-password. Use --pg-password only for throwaway local runs: every local user can read a running process’s command line.

The .deb keeps the password in /etc/ricochet/env as DB_PASSWORD (mode 640, owner root:ricochet), and run.sh exports it as RICOCHET_PG_PASSWORD.

If you put the password in the YAML file instead, protect the file. The .deb sets /etc/ricochet/config.yaml to mode 640, owner root:ricochet.

TLS to PostgreSQL

The server requires TLS to PostgreSQL by default (sslmode: require). Use disable only when the database is on the same host or on a private network you trust. The development preset sets disable for you, since it expects a local database.

The value is passed to the driver (pgx) as the sslmode connection parameter.

Size the pool

database.pool_size is the maximum number of connections the server opens to PostgreSQL. It also sets the server’s admission bound: unless you set admission_control.max_in_flight, the server admits four requests in flight per pool connection, so 100 at the default pool of 25. See Admission & rate limits.

Make sure PostgreSQL’s max_connections covers pool_size for every server instance that shares the database, plus whatever else connects to it.

Check it works

At startup the server logs PostgreSQL connection pool initialized with the host, port and database. If it cannot reach the database, it exits with failed to start server and the cause, for example a failed ping.

Once the server runs, /readyz on the ops listener pings the database and reports the pool:

curl -s http://127.0.0.1:9090/readyz

The body’s checks.postgres says whether the ping succeeded, and details.postgresPool shows total, idle and acquired connections against the maximum. See Health & readiness.

Troubleshooting

• The server exits at startup with a TLS error against a local database that has no certificate: set --pg-sslmode disable (or DB_SSLMODE=disable in /etc/ricochet/env), or configure TLS on PostgreSQL.
• permission denied for table ...: the schema was loaded before the role existed, or by a role that does not own the tables. Run schema.sql again as the database owner or a superuser; the grants at the end are idempotent.
• password authentication failed: check which source the password came from. A --pg-password flag overrides RICOCHET_PG_PASSWORD, which overrides the file.
• Requests shed with 503 while the database is idle: the pool or the admission bound is too small for the load. Compare details.admission and details.postgresPool in /readyz.