Database users¶

Tenax backs up each database with one user, tenax, whose password lives in a Secret named tenax-* next to the database. It never uses an administrator account. Creating the user takes two commands, plus one line of server configuration for restore to any second.

PostgreSQL 15, 16, 17MySQL 8.4

NS=team-a                         # the database's namespace
PASSWORD=$(openssl rand -hex 24)  # hex: no quoting surprises

# 1. The Secret Tenax reads
kubectl -n $NS create secret generic tenax-postgres \
  --from-literal=username=tenax --from-file=password=<(printf %s "$PASSWORD")

# 2. The user, as a superuser, through the database's pod (deploy/…, sts/… or a pod)
kubectl -n $NS exec -i deploy/postgres -- psql -U postgres -v ON_ERROR_STOP=1 <<SQL
\set tenax_password '$PASSWORD'
SELECT 'CREATE ROLE tenax LOGIN REPLICATION' WHERE NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'tenax') \gexec
ALTER ROLE tenax WITH LOGIN REPLICATION PASSWORD :'tenax_password';
GRANT pg_read_all_data, pg_monitor TO tenax;
ALTER SYSTEM SET max_slot_wal_keep_size = '2GB';
SELECT pg_reload_conf();
SQL

3. For restore to any second, let the user replicate: add this line to pg_hba.conf and reload (no restart).

host  replication  tenax  all  scram-sha-256

If an operator or Helm chart manages Postgres, add the line where it configures pg_hba (for example spec.postgresql.pg_hba in CloudNativePG, or the chart's pgHbaConfiguration value).

The same SQL as a file, with comments (docs/sql/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();

NS=team-a
PASSWORD=$(openssl rand -hex 24)

# 1. The Secret Tenax reads
kubectl -n $NS create secret generic tenax-mysql \
  --from-literal=username=tenax --from-file=password=<(printf %s "$PASSWORD")

# 2. The user, as root (the official image has the root password in MYSQL_ROOT_PASSWORD)
kubectl -n $NS exec -i deploy/mysql -- sh -c 'MYSQL_PWD="$MYSQL_ROOT_PASSWORD" mysql -uroot' <<SQL
CREATE USER IF NOT EXISTS 'tenax'@'%' IDENTIFIED BY '$PASSWORD';
GRANT SELECT, SHOW VIEW, TRIGGER, EVENT, LOCK TABLES, PROCESS, RELOAD, SHOW_ROUTINE,
      REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'tenax'@'%';
SQL

3. Restore to any second needs the binlog on, in row format: both are MySQL 8 defaults. select @@log_bin, @@binlog_format shows 1, ROW.

The same SQL as a file, with comments (docs/sql/mysql-users.sql)

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;

That's it. Point the target at the Secret:

spec:
  credentials:
    secretRef: { name: tenax-postgres }

Without step 3¶

Nothing fails. Tenax tries to replicate with the user, and if the database refuses:

full backups are logical, on the policy's schedule;
the target's Replication condition, the console and each backup's warnings say exactly what to add;
Tenax checks again every few minutes and starts streaming by itself once you have added it.

The user may	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	physical backups (Postgres) and the change stream: restore to any second	Postgres `REPLICATION` plus the `pg_hba.conf` rule; MySQL `REPLICATION SLAVE`, `REPLICATION CLIENT`

Replication slots

The Postgres stream keeps WAL in a replication slot until Tenax has stored it. The SQL caps what a stopped streamer can retain at 2 GB (max_slot_wal_keep_size), Tenax alerts on TenaxSlotWALRetentionHigh well before that, and it drops the slot when the target is deleted or point-in-time restore is turned off.

Advanced: keep the user in sync automatically, for GitOps setups where passwords rotate and databases get rebuilt.