Database

This page helps you prepare the database Goiabada keeps everything in: users, clients, sessions, tokens and settings.

Only the auth server talks to the database. Goiabada supports PostgreSQL, MySQL, SQL Server and SQLite, and the setup wizard asks which one you use. You set the connection through the GOIABADA_DB_* environment variables.

Create the database yourself

By default the auth server creates its database at its first start, which needs a login allowed to create databases. To run with a login that isn’t, create the database once yourself:

1. Create the database with your engine’s statement, from its section below.

2. Give the login Goiabada uses full rights inside that database, and nothing outside it.

3. Set GOIABADA_DB_CREATE=false, so the auth server doesn’t look for the database through postgres or master, which such a login may not reach. It then issues no CREATE DATABASE either.

The auth server creates every table itself, at each start, as Upgrade Goiabada describes.

Where the database sits

The connection to PostgreSQL, MySQL or SQL Server carries the database password and everything Goiabada stores, apart from the secrets the AES key encrypts. You protect it with two settings, which work the same way on all three engines. SQLite has no connection to protect, so it ignores both.

GOIABADA_DB_TLS_MODE picks one of five modes. They’re PostgreSQL’s sslmode names:

Mode	Encrypted	Certificate checked
disable	Never, even when the server offers TLS. On SQL Server the login travels in plain text too.	No
prefer, the default	On PostgreSQL and MySQL, when the server offers TLS, and in plain text when it offers none. On SQL Server, the login, and the whole session when the server forces encryption, as Azure SQL Database does.	No
require	Always, the whole session. A server that offers no TLS is refused.	No
verify-ca	Always, the whole session.	The certificate must chain to a trusted authority. The host name isn’t checked.
verify-full	Always, the whole session.	The certificate must chain to a trusted authority and name GOIABADA_DB_HOST. An IP address must be one of the certificate’s IP addresses.

Only verify-full makes sure the auth server reached your database and nothing in between, so use it when you can. Use verify-ca only when the auth server reaches the database by a name its certificate doesn’t carry, such as an IP address or a connection pooler’s host name.

Three of the modes check no certificate

Never use disable, prefer or require across a network you don’t trust. They check no certificate, so anyone on the path can pose as your database and read or change the traffic.

Unless you use verify-full, keep the database on the auth server’s host, or on a private network that only the auth server and your administrators reach. The Docker Compose files the setup wizard generates write prefer, since their database runs on the file’s own network.

If you leave GOIABADA_DB_TLS_MODE unset, the auth server connects as prefer. Every start then writes this warning until you set a mode: the database tls mode is unset, so the auth server does not check whose database it reached. Set prefer yourself to keep the same behavior without the warning. To see which mode a start used, read tls_mode on its using database record.

If the database’s certificate doesn’t verify, or the database offers no TLS, the start stops with unable to create the database connection. Database TLS connection fails lists each error and its fix.

The CA file

GOIABADA_DB_TLS_CA_FILE names a PEM file of the authorities you trust to sign the database’s certificate. The auth server reads it once, at start, and only in verify-ca and verify-full.

• Leave it empty when an authority the system already trusts signed the certificate.
• Set it when an authority the system doesn’t trust signed it, such as your database provider’s own or yours. The auth server then trusts only the authorities in the file, not the system’s.

The auth server won’t start if the file can’t be read, holds no certificate, or is set with disable, prefer or require, which check no certificate. On Kubernetes, the setup wizard puts the file in a ConfigMap.

PostgreSQL’s own variables

On PostgreSQL, these two settings are the whole of the connection’s TLS. The auth server doesn’t read PostgreSQL’s own PGSSL* variables, such as PGSSLMODE and PGSSLROOTCERT, and won’t start while one is set: see PostgreSQL TLS variables stop startup.

PostgreSQL

CREATE DATABASE goiabada OWNER goiabada ENCODING 'UTF8';

Make the login the owner, as above. A login that isn’t the owner can’t create tables from PostgreSQL 15 on, even with every right on the database: also run GRANT ALL ON SCHEMA public TO goiabada; connected to that database. It needs no CREATEDB attribute and no access to the postgres database.

Quote the name if GOIABADA_DB_NAME isn’t all lower case. PostgreSQL folds an unquoted identifier, so CREATE DATABASE Goiabada makes a database called goiabada, which isn’t the one Goiabada connects to.

MySQL

CREATE DATABASE goiabada CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs;

GRANT ALL PRIVILEGES ON goiabada.* TO 'goiabada'@'%'; is enough. No server-wide privilege is needed.

Keep MySQL’s strict SQL mode, which is its default: sql_mode includes STRICT_TRANS_TABLES. Without it, MySQL doesn’t refuse a statement that leaves out a NOT NULL column with no default, or that writes a value too long for its column: it stores a value it makes up for the first, and cuts the second short.

SQL Server

CREATE DATABASE goiabada COLLATE Latin1_General_100_CS_AS_KS_WS_SC_UTF8;

Map the login into the database and add it to db_owner. It needs no rights in master beyond the access every login has there, and no server role such as dbcreator.

Caution

On SQL Server, spell out the COLLATE clause. A database’s default can’t be changed afterwards, because ALTER DATABASE ... COLLATE blocks while the application is connected, and every text column a future migration adds inherits it.

READ_COMMITTED_SNAPSHOT is supported, on or off, and both settings are tested. Goiabada never asks for SNAPSHOT isolation, so ALLOW_SNAPSHOT_ISOLATION makes no difference to it either way.

A PostgreSQL to try Goiabada on Kubernetes

A cluster you’re trying Goiabada on may have no database to give it. This runs one PostgreSQL in the cluster, in a namespace of its own, with its data on a volume from the cluster’s default storage class. It’s for trying Goiabada out: one replica, no backups and no tuning. For production, use a database you run properly, in the cluster or outside it.

1. Save this as postgres.yaml:

   apiVersion: v1
   kind: Namespace
   metadata:
     name: postgres
   ---
   apiVersion: v1
   kind: Service
   metadata:
     name: postgres
     namespace: postgres
   spec:
     selector:
       app: postgres
     ports:
     - port: 5432
   ---
   apiVersion: apps/v1
   kind: StatefulSet
   metadata:
     name: postgres
     namespace: postgres
   spec:
     serviceName: postgres
     replicas: 1
     selector:
       matchLabels:
         app: postgres
     template:
       metadata:
         labels:
           app: postgres
       spec:
         containers:
         - name: postgres
           image: postgres:18
           env:
           - name: POSTGRES_USER
             value: goiabada
           - name: POSTGRES_DB
             value: goiabada
           - name: POSTGRES_PASSWORD
             valueFrom:
               secretKeyRef:
                 name: postgres
                 key: password
           # A subdirectory, since a fresh volume can hold lost+found, which initdb refuses.
           - name: PGDATA
             value: /var/lib/postgresql/data/pgdata
           ports:
           - containerPort: 5432
           readinessProbe:
             exec:
               command: ["pg_isready", "-U", "goiabada", "-d", "goiabada"]
             periodSeconds: 5
           volumeMounts:
           - name: data
             mountPath: /var/lib/postgresql/data
     volumeClaimTemplates:
     - metadata:
         name: data
       spec:
         accessModes: ["ReadWriteOnce"]
         resources:
           requests:
             storage: 5Gi

2. Apply it with a generated password, and wait for it:

   kubectl apply -f postgres.yaml
   kubectl create secret generic postgres -n postgres --from-literal=password="$(openssl rand -hex 24)"
   kubectl rollout status statefulset/postgres -n postgres --timeout=5m

3. Read the password back for the setup wizard:

   kubectl get secret postgres -n postgres -o jsonpath='{.data.password}' | base64 -d; echo

4. Answer the wizard’s database questions with PostgreSQL, the host postgres.postgres.svc.cluster.local, the port 5432, the database goiabada, the username goiabada and that password. The host resolves inside the cluster only, so skip the wizard’s connection test.

To remove it, with everything Goiabada stored: kubectl delete namespace postgres.

SQLite

There’s nothing to create, and GOIABADA_DB_CREATE doesn’t apply: the driver creates the file. To refuse a missing file instead of creating one, write GOIABADA_DB_DSN as a file: URI with mode=rw, such as file:/data/goiabada.db?mode=rw. After a plain path, like the one the setup wizard writes, the driver ignores mode=rw. SQLite has the connection string for a file that survives restarts.

The auth server runs as uid 10001, so the directory holding the file must be writable by it. In Docker, mount the volume at /data, which the image creates owned by that user. A volume whose files belong to another user needs a one-time chown.

Back up and restore SQLite

While the auth server runs, part of the database is in goiabada.db-wal beside goiabada.db, so a copy of the file alone, or a copy taken while it writes, isn’t a backup. Stop the auth server first: a clean stop writes everything into goiabada.db and removes the other two files. Copy the whole directory anyway, since a stop that wasn’t clean leaves them, and they belong to the backup then: a server that was killed, or one whose requests or background work outlived its shutdown timeouts, which leaves the database to the process exit rather than close it under them.

Docker Compose

From the directory holding your docker-compose.yml:

docker compose stop goiabada-authserver
docker compose cp goiabada-authserver:/data ./goiabada-backup
docker compose start goiabada-authserver

To restore it, stop the auth server again, then copy the backup in as the image’s own user, which keeps the files its own, removing any WAL files the current database left first:

docker compose stop goiabada-authserver
docker compose run --rm --no-deps -v "$PWD/goiabada-backup:/backup:ro" --entrypoint sh \
  goiabada-authserver -c 'rm -f /data/goiabada.db-wal /data/goiabada.db-shm && cp /backup/* /data/'
docker compose start goiabada-authserver

Native binaries

sudo systemctl stop goiabada-authserver
sudo cp -a /var/lib/goiabada/. /root/goiabada-backup/
sudo systemctl start goiabada-authserver

To restore it, stop the auth server, remove goiabada.db-wal and goiabada.db-shm from /var/lib/goiabada if they’re there, copy the backup’s files back with cp -a, and start it.

A restored database still needs the AES key it was taken under: see Back up the AES key.

Refresh token storage

The refresh_tokens table keeps a revoked refresh token until the token itself would have expired, rather than deleting it once it is revoked. The stored row is what lets the auth server recognize a replayed refresh token at all: without it a replay would be refused, but the rest of its family would never be revoked.

So the table grows with how often clients refresh and how long their refresh tokens live. With the default 30-day offline idle timeout, a grant refreshed every five minutes holds about 8,600 rows before the oldest start to age out. A background job runs every 12 hours and deletes a row once its expiry or its maximum lifetime has passed.

Watch the table if clients hold many offline grants that refresh often. The offline idle timeout and maximum lifetime, under Admin, Tokens and on each client’s Tokens tab, bound how long rows are kept, but only the one that actually ends your grants changes the count: lowering a maximum lifetime that the idle timeout always reaches first changes nothing.

Caution

When importing refresh tokens from another system, fill in expires_at. A row with both expires_at and max_lifetime empty is never deleted by the background job, since neither comparison can find it expired, and removing it takes an explicit statement.

Next steps

Upgrade GoiabadaHow migrations run at start, and how to roll back.

SecretsWhat the database password and the AES key protect.

Unable to create the database connectionWhen the auth server can't reach the database.