Database users¶

Keeper never uses an administrator account for backups. Each database gets two users:

User	Used for	Privileges
`keeper_backup`	inventories, logical dumps, exports, checks	read-only: Postgres `pg_read_all_data` + `pg_monitor`; MySQL `SELECT`, `SHOW VIEW`, `TRIGGER`, `EVENT`, `LOCK TABLES`, `PROCESS`, `RELOAD`, `REPLICATION CLIENT`, `SHOW_ROUTINE`
`keeper_repl`	physical base backups and the change stream	Postgres `REPLICATION`; MySQL `REPLICATION SLAVE`, `REPLICATION CLIENT`

Their passwords live in secrets named keeper-* in the database's namespace (secretPrefix in the chart), with the keys username and password.

PostgreSQL 15, 16, 17MySQL 8.4

Run as a superuser:

psql -v backup_password="$KEEPER_BACKUP_PASSWORD" -v repl_password="$KEEPER_REPL_PASSWORD" -f postgres-users.sql

docs/sql/postgres-users.sql

-- Keeper database users for PostgreSQL 15/16/17 (run once as a superuser, e.g. with psql):
--
--   psql -v backup_password="$KEEPER_BACKUP_PASSWORD" -v repl_password="$KEEPER_REPL_PASSWORD" -f postgres-users.sql
--
-- keeper_backup: read-only. Inventory queries, logical dumps (pg_dump), exports, checks.
-- keeper_repl:   physical base backups (pg_basebackup) and the WAL stream (pg_receivewal with a slot).
--
-- Also required (outside SQL):
--   * pg_hba.conf must allow replication connections for keeper_repl, for example:
--       host replication keeper_repl 0.0.0.0/0 scram-sha-256
--   * wal_level = replica (the default) and max_wal_senders >= 2.
\set ON_ERROR_STOP on

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

SELECT format('CREATE ROLE keeper_repl LOGIN REPLICATION PASSWORD %L', :'repl_password')
WHERE NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'keeper_repl') \gexec
ALTER ROLE keeper_repl WITH LOGIN REPLICATION NOSUPERUSER NOCREATEDB NOCREATEROLE PASSWORD :'repl_password';
GRANT pg_monitor TO keeper_repl;

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

Physical backups and the WAL stream also need a replication entry in pg_hba.conf, for example host replication keeper_repl 0.0.0.0/0 scram-sha-256, and wal_level = replica (the default).

Run as root:

docs/sql/mysql-users.sql

-- Keeper database users for MySQL 8.4 (run once as root, replace the passwords):
--
--   mysql -uroot -p < mysql-users.sql
--
-- keeper_backup: consistent logical dumps (mysqldump --single-transaction --source-data=2), inventory, checks.
-- keeper_repl:   binlog stream (mysqlbinlog --read-from-remote-server --raw --stop-never).
--
-- Also required (server settings): binlog_format=ROW (default), log_bin=ON (default), 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 'keeper_backup'@'%' IDENTIFIED BY 'CHANGE_ME_BACKUP_PASSWORD';
GRANT SELECT, SHOW VIEW, TRIGGER, EVENT, LOCK TABLES, PROCESS, RELOAD, REPLICATION CLIENT, SHOW_ROUTINE ON *.* TO 'keeper_backup'@'%';
CREATE USER IF NOT EXISTS 'keeper_repl'@'%' IDENTIFIED BY 'CHANGE_ME_REPL_PASSWORD';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'keeper_repl'@'%';
FLUSH PRIVILEGES;

The binlog must be on in row format: select @@log_bin, @@binlog_format, @@server_id shows 1, ROW, <unique id>. Keep binlog_expire_logs_seconds longer than the point-in-time window.

Replication slots

The Postgres stream uses a physical replication slot per target, so WAL is kept until Keeper has stored it. Set max_slot_wal_keep_size to cap what a stopped streamer can retain; Keeper alerts on KeeperSlotWALRetentionHigh well before that and drops the slot when the target is deleted or point-in-time recovery is turned off.