-- scripts/db/create-app-role.sql
--
-- SEC-P1 tooling — creates (idempotently) the `seo_insight_app` role: a
-- non-superuser, NOBYPASSRLS, DML-only LOGIN role for the Next.js runtime
-- (DATABASE_URL). Migrations keep running under the existing owner role
-- (MIGRATION_DATABASE_URL) — that owner is the role that CREATEs tables,
-- which is exactly why the ALTER DEFAULT PRIVILEGES block below exists: it
-- makes tables created by *future* migrations visible to the app role
-- automatically, without a re-grant after every `bun run db:migrate`.
--
-- IMPORTANT — run this CONNECTED AS THE OWNER ROLE (the same role
-- MIGRATION_DATABASE_URL uses / that runs migrations). ALTER DEFAULT
-- PRIVILEGES with no `FOR ROLE` clause binds to `current_user` — if you run
-- this script as a different role, future migration-created tables will NOT
-- be covered and you'll be back to manual re-grants.
--
-- Note on style: this script deliberately avoids wrapping CREATE/ALTER ROLE
-- in a PL/pgSQL DO $$ ... $$ block. psql does NOT interpolate `:'pw'`
-- variables inside dollar-quoted strings, so a DO block would silently swallow
-- the password variable (or, worse, error out). `\gset` + `\if` below keep
-- everything at the top level, where `:'pw'` (quoted literal) and `:"dbname"`
-- (quoted identifier) interpolation actually apply.
--
-- Usage (password is a psql variable — never paste it inline, never commit
-- it, never put it in shell history unquoted):
--
--   psql "$MIGRATION_DATABASE_URL" \
--     -v pw="$(openssl rand -base64 24)" \
--     -f scripts/db/create-app-role.sql
--
-- Re-runnable: safe to execute again (e.g. to rotate the password, or to
-- re-sync grants after an out-of-band schema change) — CREATE vs ALTER
-- branches on whether the role already exists; every GRANT below is
-- idempotent on its own.
--
-- See docs/project/ops/db-least-privilege.md for the full prod runbook
-- (env wiring, deploy ordering, rollback).

\set ON_ERROR_STOP on

-- 1. Role: create if missing, otherwise just rotate the password. Privilege
-- flags (NOSUPERUSER/NOCREATEDB/NOCREATEROLE/NOBYPASSRLS/LOGIN) are re-stated
-- on ALTER too, so re-running this script also *repairs* the role if someone
-- manually loosened it later.
SELECT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'seo_insight_app') AS role_exists \gset

\if :role_exists
ALTER ROLE seo_insight_app NOSUPERUSER NOCREATEDB NOCREATEROLE NOBYPASSRLS LOGIN PASSWORD :'pw';
\echo 'role seo_insight_app already existed — flags re-asserted, password rotated'
\else
CREATE ROLE seo_insight_app NOSUPERUSER NOCREATEDB NOCREATEROLE NOBYPASSRLS LOGIN PASSWORD :'pw';
\echo 'created role seo_insight_app'
\endif

-- 2. Database + schema visibility. `:"dbname"` is psql's quoted-identifier
-- interpolation (vs. `:'var'` for quoted literals) — needed because GRANT
-- ... ON DATABASE takes an identifier, not an expression like
-- current_database().
SELECT current_database() AS dbname \gset

GRANT CONNECT ON DATABASE :"dbname" TO seo_insight_app;
GRANT USAGE ON SCHEMA public TO seo_insight_app;

-- 3. DML on tables that already exist. Deliberately NOT "ALL PRIVILEGES":
-- no TRUNCATE, no REFERENCES/TRIGGER, no DDL of any kind.
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO seo_insight_app;

-- 4. Sequences (serial/bigserial/identity columns): USAGE only — lets the
-- app call nextval()/currval() through inserts, nothing else.
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO seo_insight_app;

-- 5. Same grants, applied automatically to objects created by future
-- migrations (run under the owner role — see the header note above).
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO seo_insight_app;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT USAGE ON SEQUENCES TO seo_insight_app;

-- 6. Explicitly withheld, by omission (nothing to revoke — these were never
-- granted):
--   - DDL (CREATE/ALTER/DROP TABLE, CREATE INDEX, etc.) — schema changes
--     stay exclusively on the owner/migration role.
--   - TRUNCATE — bulk wipes (seed/backfill scripts) must run with the owner
--     role's connection string, not the app role's.
--   - BYPASSRLS — this role is NOBYPASSRLS by construction, so any RLS
--     policy added later (SEC-P2, defense-in-depth) actually binds to it.