21 lines
956 B
SQL
21 lines
956 B
SQL
-- Least-privilege Postgres role + database for the optional Umami analytics
|
|
-- service. Umami must not connect as the shared superuser, which can read the
|
|
-- `app` database holding every user, flow and credential. This role owns only
|
|
-- its own database, so it has full rights there and none on `app`.
|
|
--
|
|
-- Invoked by `make umami` (piped into psql in the db container). Idempotent.
|
|
-- Identifiers and the password arrive as psql variables (-v role=… -v pw=…
|
|
-- -v db=…) and are quoted via format()'s %I / %L, so they are injection-safe.
|
|
|
|
SELECT format('CREATE ROLE %I LOGIN PASSWORD %L', :'role', :'pw')
|
|
WHERE NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = :'role')
|
|
\gexec
|
|
|
|
SELECT format('ALTER ROLE %I LOGIN PASSWORD %L', :'role', :'pw')
|
|
WHERE EXISTS (SELECT 1 FROM pg_roles WHERE rolname = :'role')
|
|
\gexec
|
|
|
|
SELECT format('CREATE DATABASE %I OWNER %I', :'db', :'role')
|
|
WHERE NOT EXISTS (SELECT 1 FROM pg_database WHERE datname = :'db')
|
|
\gexec
|