staging_engineer   ← shared permission container (NOLOGIN)
│                    Holds all grants: full CRUD + DDL on `public` schema.
│                    Never used to log in directly.
│
├── eng_<name1>    ← personal login (LOGIN, has password)
├── eng_<name2>    ← personal login (LOGIN, has password)
└── eng_<name3>    ← personal login (LOGIN, has password)
                     Each engineer gets their own. Inherits all grants
                     from staging_engineer via role membership.

-- Shared permission container. NOLOGIN by design.
CREATE ROLE staging_engineer NOLOGIN BYPASSRLS;

-- Grants on public schema
GRANT USAGE, CREATE ON SCHEMA public TO staging_engineer;
GRANT ALL ON ALL TABLES     IN SCHEMA public TO staging_engineer;
GRANT ALL ON ALL SEQUENCES  IN SCHEMA public TO staging_engineer;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO staging_engineer;

-- Same for future objects CREATED BY postgres (see note below for engineers)
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL     ON TABLES    TO staging_engineer;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL     ON SEQUENCES TO staging_engineer;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT EXECUTE ON FUNCTIONS TO staging_engineer;

-- Grant postgres membership in staging_engineer. Required so that
-- offboarding's `REASSIGN OWNED ... TO staging_engineer` works —
-- PostgreSQL requires the caller to be a member of both the source
-- AND target roles, and Supabase's `postgres` role is NOT a true
-- superuser (SELECT rolsuper FROM pg_roles WHERE rolname='postgres'
-- returns false), so the membership can't be bypassed. Safe: postgres
-- already has BYPASSRLS and admin privileges, so inheriting
-- staging_engineer's grants adds nothing it doesn't already have.
GRANT staging_engineer TO postgres;

openssl rand -base64 32 | tr -d '=+/' | cut -c1-32

psql "postgresql://postgres@db.evmxqxlurxfhzvwjxqrf.supabase.co:5432/postgres?sslmode=require" -W
# psql will prompt: Password for user postgres:
#                   <paste the postgres password from the Supabase dashboard>

-- Create the login role WITHOUT a password; we'll set it via \password below.
CREATE ROLE eng_<firstname>
  LOGIN
  IN ROLE staging_engineer;

-- BYPASSRLS is a Postgres role ATTRIBUTE and is NOT inherited through
-- role membership. Even though staging_engineer has BYPASSRLS, the
-- personal login role must be granted it directly, otherwise the
-- engineer sees zero rows on any RLS-protected table (which on Silvia
-- is every table). Staging is not customer-sensitive; see FAQ for the
-- SOC 2 reasoning.
ALTER ROLE eng_<firstname> BYPASSRLS;

-- Grant postgres membership in this engineer's role. Required because:
--   (a) the ALTER DEFAULT PRIVILEGES FOR ROLE below runs as postgres
--       and needs postgres to be a member of eng_<firstname>, AND
--   (b) offboarding's REASSIGN OWNED / DROP OWNED commands require
--       the caller to be a member of this role (postgres is not a
--       true superuser on Supabase, so membership can't be bypassed).
GRANT eng_<firstname> TO postgres;

-- Make objects CREATED BY this engineer (e.g. migration tables) also
-- accessible to the shared role, so other engineers can read / write
-- them. Without this, migrations from one engineer would create tables
-- that only they can touch — fine for one engineer, broken as soon as
-- a second engineer joins.
ALTER DEFAULT PRIVILEGES FOR ROLE eng_<firstname> IN SCHEMA public
  GRANT ALL ON TABLES    TO staging_engineer;
ALTER DEFAULT PRIVILEGES FOR ROLE eng_<firstname> IN SCHEMA public
  GRANT ALL ON SEQUENCES TO staging_engineer;
ALTER DEFAULT PRIVILEGES FOR ROLE eng_<firstname> IN SCHEMA public
  GRANT EXECUTE ON FUNCTIONS TO staging_engineer;

COMMENT ON ROLE eng_<firstname> IS
  '<Full Name> (<work-email>). Personal login for staging DB.
   Inherits from staging_engineer. Created YYYY-MM-DD.';

postgres=# \password eng_<firstname>
Enter new password for user "eng_<firstname>": <paste password from Step 1>
Enter it again: <paste again>

SELECT
  r.rolname,
  r.rolcanlogin,
  r.rolbypassrls,
  string_agg(m.rolname, ', ') AS memberships
FROM pg_roles r
LEFT JOIN pg_auth_members am ON am.member = r.oid
LEFT JOIN pg_roles m ON m.oid = am.roleid
WHERE r.rolname = 'eng_<firstname>'
GROUP BY r.rolname, r.rolcanlogin, r.rolbypassrls;

postgresql://eng_<firstname>:<password>@db.evmxqxlurxfhzvwjxqrf.supabase.co:5432/postgres?sslmode=require

STAGING_DB_URL="postgresql://eng_<firstname>:...@db.evmxqxlurxfhzvwjxqrf.supabase.co:5432/postgres?sslmode=require"

supabase db push --db-url "$STAGING_DB_URL"

psql "$STAGING_DB_URL" -f path/to/migration.sql

-- Prereq: postgres must be a member of both eng_<firstname> and
-- staging_engineer for REASSIGN OWNED / DROP OWNED to work. This is
-- set up at provisioning time (see one-time setup for staging_engineer
-- and onboarding step 2 for eng_<firstname>). If offboarding ever
-- fails with "permission denied to reassign objects", re-run:
--   GRANT eng_<firstname>  TO postgres;
--   GRANT staging_engineer TO postgres;

-- 1. Reassign objects the engineer owns (migration tables, functions,
--    etc.) to the shared role. Required: `DROP ROLE` fails with
--    "role ... cannot be dropped because some objects depend on it"
--    while the engineer still owns any objects.
REASSIGN OWNED BY eng_<firstname> TO staging_engineer;

-- 2. Drop any remaining privileges granted to or by the engineer
--    (there usually aren't any, but this is cheap insurance against
--    DROP ROLE failing on a stray grant / default-privilege entry).
DROP OWNED BY eng_<firstname>;

-- 3. Now the role has nothing hanging off it and can be dropped.
DROP ROLE eng_<firstname>;

postgres=# \password eng_<firstname>
Enter new password for user "eng_<firstname>": <paste new password>
Enter it again: <paste again>

-- Force-disconnect existing sessions for the role:
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE usename = 'eng_<firstname>';

supabase start   # boots local Postgres, PostgREST, GoTrue, etc.
# then hit the local PostgREST (http://localhost:54321) with a JWT
# whose `sub` claim is a test user id, e.g.:
curl -H "Authorization: Bearer $JWT" \
     "http://localhost:54321/rest/v1/your_table?select=*"

CREATE ROLE eng_rls_test LOGIN IN ROLE authenticated;
-- Important: no BYPASSRLS, no membership in staging_engineer.
-- Then set its password via \password eng_rls_test

SELECT set_config('request.jwt.claims',
                  '{"sub": "user_<some_clerk_id>"}',
                  false);
SELECT * FROM your_table;