Database users¶

Tenax never uses an administrator account for backups. Each database gets one user, tenax, stored in one Secret named tenax-* in the database's namespace (secretPrefix in the chart), with the keys username and password (ADR 0016).

What the user may do	Gives you	Privileges
Read everything	logical backups, inventories, exports, checks	Postgres `pg_read_all_data` + `pg_monitor`; MySQL `SELECT`, `SHOW VIEW`, `TRIGGER`, `EVENT`, `LOCK TABLES`, `PROCESS`, `RELOAD`, `SHOW_ROUTINE`
Replicate (optional)	physical backups (Postgres) and the change stream, so restore to any second	Postgres `REPLICATION` plus a `pg_hba.conf` replication rule; MySQL `REPLICATION SLAVE`, `REPLICATION CLIENT`

Tenax tries to replicate with the user. If the database refuses, nothing fails: - full backups are logical, on the policy's schedule; - the target's Replication condition and every backup's warnings say exactly what to grant; - Tenax checks again every few minutes and switches to streaming by itself once you have granted it.

PostgreSQL 15, 16, 17MySQL 8.4

Run as a superuser:

psql -v tenax_password="$TENAX_PASSWORD" -f postgres-users.sql

docs/sql/postgres-users.sql

-- Tenax's database user for PostgreSQL 15/16/17 (run once as a superuser, e.g. with psql):
--
--   psql -v tenax_password="$TENAX_PASSWORD" -f postgres-users.sql
--
-- One user, `tenax`, does everything (ADR 0016): inventory queries, logical dumps (pg_dump), exports and checks
-- with pg_read_all_data + pg_monitor; physical base backups (pg_basebackup) and the WAL stream with REPLICATION.
--
-- For physical backups and point-in-time restore pg_hba.conf must also allow it to replicate, then reload:
--     host replication tenax all scram-sha-256
-- Without that rule Tenax still works: it takes logical backups and tells you the rule to add.
-- Also needed: wal_level = replica (the default) and max_wal_senders >= 2 (default 10).
\set ON_ERROR_STOP on

SELECT format('CREATE ROLE tenax LOGIN REPLICATION PASSWORD %L', :'tenax_password')
WHERE NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'tenax') \gexec
ALTER ROLE tenax WITH LOGIN REPLICATION NOSUPERUSER NOCREATEDB NOCREATEROLE PASSWORD :'tenax_password';
GRANT pg_read_all_data, pg_monitor TO tenax;
-- Keep backup sessions bounded.
ALTER ROLE tenax SET idle_in_transaction_session_timeout = '10min';

-- A stopped streamer must never fill the database disk: cap WAL retained by replication slots (no restart needed).
ALTER SYSTEM SET max_slot_wal_keep_size = '2GB';
SELECT pg_reload_conf();

For restore to any second, also allow the user to replicate in pg_hba.conf and reload (no restart):

host  replication  tenax  all  scram-sha-256

If a Postgres operator or Helm chart manages the server, add the line where it configures pg_hba (for example spec.postgresql.pg_hba in CloudNativePG, or the chart's pgHbaConfiguration value). wal_level = replica and max_wal_senders are fine at their defaults.

Run as root:

docs/sql/mysql-users.sql

-- Tenax's database user for MySQL 8.4 (run once as root, replace the password):
--
--   mysql -uroot -p < mysql-users.sql
--
-- One user, `tenax`, does everything (ADR 0016): consistent logical dumps (mysqldump --single-transaction
-- --source-data=2), inventory and checks, and the binlog stream (mysqlbinlog --read-from-remote-server).
-- Without REPLICATION SLAVE / REPLICATION CLIENT Tenax still takes dumps, but without point-in-time restore.
--
-- Server settings (all MySQL 8 defaults except GTIDs): log_bin=ON, binlog_format=ROW, a unique server_id;
-- gtid_mode=ON with enforce_gtid_consistency=ON is recommended; binlog_expire_logs_seconds long enough for the
-- streamer to catch up after an outage (default 30 days).
CREATE USER IF NOT EXISTS 'tenax'@'%' IDENTIFIED BY 'CHANGE_ME_PASSWORD';
GRANT SELECT, SHOW VIEW, TRIGGER, EVENT, LOCK TABLES, PROCESS, RELOAD, SHOW_ROUTINE,
      REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'tenax'@'%';
FLUSH PRIVILEGES;

Restore to any second needs the binlog on in row format, both MySQL 8 defaults: select @@log_bin, @@binlog_format, @@server_id shows 1, ROW, <unique id>. Keep binlog_expire_logs_seconds longer than the point-in-time window.

Then store the password next to the database:

kubectl -n team-a create secret generic tenax-postgres --from-literal=username=tenax --from-literal=password=…

Replication slots

The Postgres stream uses a physical replication slot per target, so WAL is kept until Tenax has stored it. Set max_slot_wal_keep_size to cap what a stopped streamer can retain. The SQL above sets 2 GB, applied on reload. Tenax alerts on TenaxSlotWALRetentionHigh well before that, and drops the slot when the target is deleted or point-in-time recovery is turned off.