-- Creates the non-superuser role the application must connect as.
--
-- CRITICAL, found by actually running the RLS integration suite against a
-- real database in this session: Postgres superusers (and any role with
-- BYPASSRLS) skip row-level security entirely, and `FORCE ROW LEVEL
-- SECURITY` does NOT override that — FORCE only removes the *table owner's*
-- exemption, it has no effect on superuser/BYPASSRLS. Connecting the app as
-- a superuser (e.g. Postgres's default `postgres` role) makes every policy
-- in rls.sql a no-op with no error, no warning — RLS silently does nothing.
-- Migrations may run as an owner/superuser (they need DDL rights); the
-- application's runtime `DATABASE_URL` must NOT.
--
-- Run once per database, as a superuser, after the schema exists:
--   psql "$SUPERUSER_DATABASE_URL" -v app_password="change-me" -f libs/data/sql/app-role.sql
--
-- IMPORTANT: pass the raw password to -v with NO inner quotes. The SQL
-- below uses `:'app_password'`, which is psql's own syntax for substituting
-- a variable as a properly-escaped SQL string literal — wrapping it in
-- quotes yourself at the shell level (`-v app_password="'change-me'"`)
-- double-quotes the value and breaks the substitution.
--
-- The role is created conditionally via \gexec (Postgres has no
-- `CREATE ROLE IF NOT EXISTS`) rather than a DO block, because psql's
-- `:'var'` substitution does not reliably interpolate inside a dollar-quoted
-- plpgsql body.

select format(
  'create role slackwsh_app with login password %L nosuperuser nobypassrls nocreatedb nocreaterole',
  :'app_password'
)
where not exists (select from pg_roles where rolname = 'slackwsh_app')
\gexec

grant connect on database slackwsh to slackwsh_app;
grant usage on schema public to slackwsh_app;
grant select, insert, update, delete on all tables in schema public to slackwsh_app;
grant usage, select on all sequences in schema public to slackwsh_app;
alter default privileges in schema public grant select, insert, update, delete on tables to slackwsh_app;
alter default privileges in schema public grant usage, select on sequences to slackwsh_app;

-- pg-boss creates and migrates its own tables at worker startup. Keep that
-- DDL isolated in a dedicated schema owned by the runtime role instead of
-- granting the role broad CREATE permission on the whole database.
-- On a brand-new database pg-boss still emits CREATE SCHEMA IF NOT EXISTS;
-- give slackwsh_app database CREATE only for that first worker bootstrap and
-- revoke it immediately afterwards. Once the version table exists, normal
-- worker restarts only need ownership of this schema.
create schema if not exists pgboss authorization slackwsh_app;
alter schema pgboss owner to slackwsh_app;
