Skip to content

Runbook: DB User Setup & Ownership Transfer

Kdy použit

  • Nova sluzba potrebuje vlastniho DB usera
  • Existujici DB ma spatny ownership (TypeORM "must be owner" chyba)
  • Prechod ze sdileneho usera (s60_infra_user) na dedikovaneho

Konvence pojmenovani

Format Priklad
DB s60_{sluzba}_{env} s60_pulse_dev
User s60_{sluzba}_{env}_user s60_pulse_dev_user

PG server: pg-alfa (100.125.3.121)

  • PostgreSQL 17 na portu 5432 (primo)
  • pgBouncer na portu 6432 (transaction pooling)
  • Sluzby se pripojuji na port 6432 (pgBouncer)
  • pgBouncer pouziva auth_query — novy user je automaticky dostupny pres pgBouncer bez zasahu do userlist.txt

Postup

1. Vygenerovat heslo

openssl rand -base64 24 | tr -d '/+=' | head -c 32
Konvence formatu: {Sluzba}-{Env}-2026-{random} (napr. Auth-Prod-2026-hN3jT8vB5cYs)

2. Vytvorit usera na pg-alfa

ssh root@100.125.3.121 "sudo -u postgres psql -c \"CREATE ROLE s60_{sluzba}_{env}_user LOGIN PASSWORD '{heslo}';\""

3. Vytvorit DB (pokud neexistuje)

ssh root@100.125.3.121 "sudo -u postgres psql -c \"CREATE DATABASE s60_{sluzba}_{env} OWNER s60_{sluzba}_{env}_user;\""

4. Prevest ownership (pokud existujici DB)

ssh root@100.125.3.121 "sudo -u postgres psql" << 'EOSQL'
-- DB
ALTER DATABASE s60_{sluzba}_{env} OWNER TO s60_{sluzba}_{env}_user;

-- Pripojit se k dane DB
\c s60_{sluzba}_{env}

-- Predat vsechny objekty (jednoduse)
REASSIGN OWNED BY {stary_user} TO s60_{sluzba}_{env}_user;

-- Schema + default privileges
ALTER SCHEMA public OWNER TO s60_{sluzba}_{env}_user;
GRANT ALL ON SCHEMA public TO s60_{sluzba}_{env}_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO s60_{sluzba}_{env}_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO s60_{sluzba}_{env}_user;
EOSQL

5. Ulozit credentials na sentinel

mkdir -p /root/secrets/{sluzba}
cat > /root/secrets/{sluzba}/.env.{env} << EOF
DB_HOST=100.125.3.121
DB_PORT=6432
DB_USERNAME=s60_{sluzba}_{env}_user
DB_PASSWORD={heslo}
DB_DATABASE=s60_{sluzba}_{env}
DB_SSL=false
EOF

6. Aktualizovat .env na serveru

# Zjisti PRESNOU cestu .env kterou docker-compose cte!
ssh root@{server} "cat /opt/{sluzba}/docker-compose.yml | grep env_file"
# Uprav SPRAVNY soubor (ne /opt/{sluzba}/.env pokud compose cte jinou cestu!)
ssh root@{server} "vi /opt/{sluzba}/{cesta}/.env"

KRITICKE: Zkontroluj env_file: v docker-compose.yml — sluzba muze cist .env z jine cesty nez /opt/{sluzba}/.env!

7. Restart + overeni

ssh root@{server} "cd /opt/{sluzba} && docker compose up -d --force-recreate"
# Overit ze kontejner cte spravne env:
ssh root@{server} "docker exec {container} env | grep DB_"
# Health check:
ssh root@{server} "docker logs {container} --since 15s 2>&1 | tail -10"

8. Aktualizovat dokumentaci

  • /root/projects/sentinel/docs/docs/spec.md — DB mapping tabulka
  • /root/projects/sentinel/docs/docs/deploy/CHANGES.md — changelog
  • Memory: infra_db_mapping.md

pgBouncer — proc netreba menit userlist.txt

pgBouncer na pg-alfa pouziva auth_query s dedicanim pgbouncer userem: 

auth_type = scram-sha-256
auth_user = pgbouncer
auth_dbname = postgres
auth_query = SELECT usename, passwd FROM pgbouncer.get_auth($1)

Funkce pgbouncer.get_auth() cte hesla primo z pg_shadow. Novy PG user je automaticky dostupny pres pgBouncer — zadna zmena konfigurace pgBounceru neni potreba.

Jediny user v userlist.txt je bootstrap pgbouncer user pro auth_query samotny.

Checklist

  • [ ] User vytvoren na pg-alfa
  • [ ] DB ownership prevedena
  • [ ] Credentials ulozeny do /root/secrets/
  • [ ] .env aktualizovan na serveru (SPRAVNA cesta!)
  • [ ] docker exec {container} env | grep DB_ — potvrzeni spravnych env
  • [ ] Sluzba restartovana a healthy
  • [ ] Dokumentace aktualizovana