> ## Documentation Index > Fetch the complete documentation index at: https://docs.cfosilvia.com/llms.txt > Use this file to discover all available pages before exploring further. # Staging database access # Staging Database Access — Engineer Onboarding **Applies to:** Any engineer who needs to run migrations, create tables, backfill data, or otherwise write to the Supabase **staging** branch. **Does NOT apply to:** Production access — production migrations are applied by an org Admin (currently @shain, @felipe, @jacob, @pliancy) after a merged PR. Engineers should never write to `main` directly. *** ## Access policy (the rules this doc implements) 1. **Supabase Dashboard access is reserved for org admins.** Owner + Administrators only. Admins are people who need to manage projects, billing, extensions, backups, or team membership — not people who need to write SQL. 2. **Engineers get scoped Postgres credentials, never dashboard access.** Staging DB write access via a personal `eng_` login (this doc). Prod DB access only if required for a specific task, scoped to what they need (see "prod schema reference" below), revoked when the task ends. 3. **The built-in Supabase "Read-Only" dashboard role is not used.** Its underlying Postgres role (`supabase_read_only_user`) has `pg_read_all_data` + `BYPASSRLS`, which grants blanket SELECT on every table with no per-table restriction possible. This fails least privilege (SOC 2 CC6.3) for any workspace with sensitive tables like `ai_chat_logs`. 4. **If an engineer needs prod schema reference** (rare, and usually avoidable by looking at migration files in Git), grant a custom read-only Postgres role on prod with USAGE on schema `public` but no table-level SELECT grants. Revoke when task is complete. This sidesteps the `pg_read_all_data` issue above. *** ## Why this exists Supabase's built-in dashboard roles (Owner / Administrator / Developer / Read-Only) apply at the **project** level, not the **branch** level: | Dashboard role | Read prod | Write prod | Write staging | | - | - | - | - | | Owner / Administrator | ✅ | ✅ | ✅ | | Developer | ✅ | ✅ | ✅ | | Read-Only | ✅ | ❌ | ❌ | There is **no built-in role** that grants write on staging but not on main. Giving an engineer the Developer role to unblock staging work would also give them write access to production, which fails least privilege (SOC 2 CC6.3) and is unnecessary for the work they need to do. Instead, we bypass the dashboard entirely for engineer write access and give each engineer a **personal Postgres login** on the staging branch database. They connect via their SQL client / migration tool of choice (Supabase CLI, `psql`, TablePlus, DBeaver, etc.) using a connection string. They cannot reach production because they don't have credentials for it. ## Why we do it this way (architecture) Two-tier role setup on the staging DB: ``` staging_engineer ← shared permission container (NOLOGIN) │ Holds all grants: full CRUD + DDL on `public` schema. │ Never used to log in directly. │ ├── eng_ ← personal login (LOGIN, has password) ├── eng_ ← personal login (LOGIN, has password) └── eng_ ← personal login (LOGIN, has password) Each engineer gets their own. Inherits all grants from staging_engineer via role membership. ``` **Why the split (shared role + personal logins) matters:** | Concern | How this addresses it | | - | - | | **Accountability** (SOC 2 CC7.3) | Every query is attributed to `eng_` in Postgres logs. If a bad migration lands, we know who ran it. | | **Clean offboarding** (SOC 2 CC6.2) | Removing an engineer is three lines (`REASSIGN OWNED`, `DROP OWNED`, `DROP ROLE`). Other engineers are unaffected. No password rotation, no re-distribution. | | **Least privilege** (SOC 2 CC6.3) | Grants are pinned to the staging DB. Engineers cannot see or touch production even if their laptop is compromised. | | **Easy permission changes** | Want all engineers to have access to a new schema? One `GRANT ... TO staging_engineer;` — every personal login inherits it automatically. | | **Per-person suspension** | Engineer on PTO or under investigation? `ALTER ROLE eng_ NOLOGIN;` — locks just them out. | ## One-time setup (already applied — reference only) The shared `staging_engineer` role was created on the staging branch DB (`evmxqxlurxfhzvwjxqrf`) on 2026-09-30. You do NOT need to re-run this — it's here so future maintainers understand what exists. ```sql theme={null} -- 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; ``` **Important gotcha about `ALTER DEFAULT PRIVILEGES`:** without an explicit `FOR ROLE `, these defaults only apply to objects created by the role that ran the `ALTER` statement — in this case, `postgres`. When `eng_` runs a migration that creates a new table, that table is **owned by `eng_`**, and the defaults above do NOT fire. Other engineers would see no grants on it. That's why the onboarding step below also runs `ALTER DEFAULT PRIVILEGES FOR ROLE eng_` — once per engineer. Note: `BYPASSRLS` on `staging_engineer` is documentation-only — Postgres does NOT inherit this attribute through role membership. Every personal login role must also be granted `BYPASSRLS` directly (see "Add a new engineer" below). We keep it on `staging_engineer` anyway to make intent clear. **Why we intentionally did NOT apply this via `apply_migration`:** role changes are cluster-wide, not schema-scoped. Tracking this as a migration would mean it gets applied to `main` on branch merge, which we do not want (production has stricter access rules). Role setup is manual, one-off SQL and lives here in this doc as the source of truth. ## How to add a new engineer Prereqs: * The engineer needs a place to receive credentials (1Password shared vault). * You need `postgres` admin access to the staging DB (Shain / Owner + Admins). Supabase-hosted projects do not expose a true PostgreSQL superuser; the `postgres` role is the admin role we use. ### Step 1 — Generate a strong password ```bash theme={null} openssl rand -base64 32 | tr -d '=+/' | cut -c1-32 ``` Store this in 1Password under the vault entry `Silvia Staging DB — Engineer Logins`, item `eng_`. Do not paste it into Slack, GitHub, or this doc. ### Step 2 — Create their login role on staging Connect to the **staging** DB **via local `psql`** as `postgres`. Using `psql` (not the Supabase SQL editor) is required because the SQL editor cannot run psql's `\password` meta-command — and we need `\password` to avoid logging the cleartext password (see "Why `\password`" below). Get the staging `postgres` connection details from Supabase Dashboard → switch to `staging` branch → Project Settings → Database. Omit the password from the URI and pass `-W` so psql prompts for it interactively — this keeps the admin password out of your shell history (`~/.zsh_history`, `~/.bash_history`) and out of process-list output (`ps auxww` on multi-user systems). ```bash theme={null} psql "postgresql://postgres@db.evmxqxlurxfhzvwjxqrf.supabase.co:5432/postgres?sslmode=require" -W # psql will prompt: Password for user postgres: # ``` Then, inside the psql session, run: ```sql theme={null} -- Create the login role WITHOUT a password; we'll set it via \password below. CREATE ROLE eng_ 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_ 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_, 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_ 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_ IN SCHEMA public GRANT ALL ON TABLES TO staging_engineer; ALTER DEFAULT PRIVILEGES FOR ROLE eng_ IN SCHEMA public GRANT ALL ON SEQUENCES TO staging_engineer; ALTER DEFAULT PRIVILEGES FOR ROLE eng_ IN SCHEMA public GRANT EXECUTE ON FUNCTIONS TO staging_engineer; COMMENT ON ROLE eng_ IS ' (). Personal login for staging DB. Inherits from staging_engineer. Created YYYY-MM-DD.'; ``` Then set the password with `\password` (NOT with `ALTER ROLE ... PASSWORD`): ``` postgres=# \password eng_ Enter new password for user "eng_": Enter it again: ``` **Why `\password`:** staging has `log_statement = 'ddl'`, which means PostgreSQL writes the full text of `CREATE ROLE ... PASSWORD '...'` and `ALTER ROLE ... PASSWORD '...'` statements to the server log — password and all. `\password` is a psql client-side meta-command that computes the SCRAM-SHA-256 hash locally and only sends the hash to the server, so the cleartext never appears in logs. **Alternative for shared ownership of objects (migrations):** if engineers want objects they create to be owned by the shared role (simplifies offboarding — see below), they can start their migration / session with `SET ROLE staging_engineer;`. Role membership (granted by `IN ROLE staging_engineer` above) is sufficient to `SET ROLE` to it — no extra grant needed. The `ALTER DEFAULT PRIVILEGES` block above is a belt-and-suspenders backstop for when engineers forget to `SET ROLE`. ### Step 3 — Verify ```sql theme={null} 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_' GROUP BY r.rolname, r.rolcanlogin, r.rolbypassrls; ``` Expect: `rolcanlogin: true`, `rolbypassrls: true`, `memberships: staging_engineer`. ### Step 4 — Hand off credentials Send the engineer a message with: * 1Password link to the vault entry * The connection string template (below, with their username filled in) * A link to this doc * The "Rules of the road" section (below) ## How the engineer uses it ### Connection string template ``` postgresql://eng_:@db.evmxqxlurxfhzvwjxqrf.supabase.co:5432/postgres?sslmode=require ``` Store in your local `.env.staging.local` (name ends in `.local` on purpose — the repo's `.gitignore` has an `.env*.local` glob that keeps it out of Git): ```bash theme={null} STAGING_DB_URL="postgresql://eng_:...@db.evmxqxlurxfhzvwjxqrf.supabase.co:5432/postgres?sslmode=require" ``` **Never commit this to Git.** Verify locally with `git check-ignore .env.staging.local` before your first commit on a new machine. Never share your personal password with another engineer — if they need access, they get their own login. ### Running migrations **Supabase CLI** (preferred for anything tracked in `Silvia-Web/supabase/migrations/`): ```bash theme={null} supabase db push --db-url "$STAGING_DB_URL" ``` **Raw psql** (for one-off SQL not committed as a migration): ```bash theme={null} psql "$STAGING_DB_URL" -f path/to/migration.sql ``` **Any Node / Python migration tool** (Prisma, dbmate, Alembic, etc.) that accepts a connection URL — swap in `$STAGING_DB_URL`. ### Interactive querying Any Postgres-compatible SQL client works — TablePlus, DBeaver, Postico, `psql`. Connect using the values from the connection string above. ### What you can do * ✅ Full CRUD on any table in `public` * ✅ `CREATE TABLE`, `ALTER TABLE`, `DROP TABLE`, indexes, views, functions * ✅ Backfill / cleanup data as needed for your feature * ✅ Read every table on staging, including RLS-protected ones — your role has `BYPASSRLS`. This is intentional for staging (see FAQ). Do not rely on this to test RLS behavior — see the FAQ "Why does the engineer login have BYPASSRLS?" below for the three supported ways to actually test RLS policies. ### What you cannot do * ❌ Anything on the production DB (`byoneafwuigeyehkoacw` / `main` branch) * ❌ Modify roles, extensions, or DB-level settings (those are admin-only operations reserved for the `postgres` role) * ❌ Access the `auth` or `storage` internal schemas' management tables * ❌ Use the Supabase Studio UI for staging (you're a DB user, not a dashboard user) ### Rules of the road 1. **Never use the `postgres` admin password from the Supabase dashboard for day-to-day staging work.** Use your `eng_` login. The `postgres` password is reserved for admins doing role management, extension installs, etc. 2. **Every migration that should also apply to production goes through a PR.** Commit the SQL to `Silvia-Web/supabase/migrations/`. On PR merge to `main`, an admin applies it to prod. 3. **Do not share your password with another engineer.** They get their own login. If someone needs write access, ping @shain in Slack. 4. **Suspicious activity, lost laptop, quit the company?** Ping @shain immediately — we'll rotate or drop your role. ## How to remove an engineer (offboarding) When an engineer leaves or moves off engineering work, run this on the staging DB as `postgres` (via psql, or the Supabase SQL editor — these commands don't need `\password`): ```sql theme={null} -- Prereq: postgres must be a member of both eng_ 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_). If offboarding ever -- fails with "permission denied to reassign objects", re-run: -- GRANT eng_ 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_ 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_; -- 3. Now the role has nothing hanging off it and can be dropped. DROP ROLE eng_; ``` Other engineers are unaffected. Then delete the 1Password entry for their login and strike through their row in the current-roster table below (keep the row so the audit trail remains). If they had any prod dashboard access (via `@cfosilvia.com`), remove it from **Supabase Dashboard → Team → Manage access** as well. ## Password rotation Recommended annually or on any suspicion of compromise. Generate a new password (see Step 1 above), then rotate it the same way you set it originally — via psql's `\password`, NOT `ALTER ROLE ... PASSWORD` (staging's `log_statement = 'ddl'` would write the new cleartext password to the server log): ``` postgres=# \password eng_ Enter new password for user "eng_": Enter it again: ``` Update the 1Password entry and notify the engineer to update their local `.env.staging.local`. Their old sessions will remain connected until they disconnect, so if this is a compromise response, also run: ```sql theme={null} -- Force-disconnect existing sessions for the role: SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE usename = 'eng_'; ``` ## Current roster | Login role | Person | Email | Created | Status | Notes | | - | - | - | - | - | - | | `eng_robert` | Robert Valmassoi | [valmassoi@pm.me](mailto:valmassoi@pm.me) / [robert@cfosilvia.com](mailto:robert@cfosilvia.com) | 2026-09-30 | Active | Staging DB write access only. Prod dashboard access removed 2026-09-30 per access policy #2. | Update this table on every add / remove / suspension. ## SOC 2 posture This pattern satisfies the following Trust Services Criteria for the staging database access surface: | Control | Evidence | | - | - | | **CC6.1** — Logical access controls | Per-user Postgres logins with strong (32-char, `openssl`-generated) passwords. SSL required on all connections. | | **CC6.2** — Provisioning / deprovisioning | Process documented in this file. Add: `CREATE ROLE ... IN ROLE staging_engineer` + `ALTER ROLE ... BYPASSRLS` + `ALTER DEFAULT PRIVILEGES FOR ROLE` + `\password` via psql. Remove: `REASSIGN OWNED` + `DROP OWNED` + `DROP ROLE`. | | **CC6.3** — Least privilege | Staging engineer role grants are scoped to `public` schema on the staging DB only. Zero access to production. | | **CC7.3** — Incident attribution | Per-user login role means every query in Postgres logs is attributable to a specific person. | Related gaps still to close (tracked separately): * Enable **pgaudit** with object-audit on `ai_chat_logs` and other sensitive tables (production). * Enable **Log Drains → S3** for 12-month Postgres log retention. * Quarterly **access review** cadence with screenshot evidence. ## FAQ ### Why not just give engineers the Developer dashboard role? Because Developer grants write on `main` too (see the table at the top). Giving Developer to unblock staging work would give an engineer the ability to accidentally `DROP TABLE users` on production. Least privilege says: give them exactly what they need, nothing more. ### Why not give them a shared `engineer` login instead of per-person? Two reasons: 1. **Auditability**: If a bad migration runs, "some engineer did it" is not an acceptable answer. Postgres logs need to attribute to a named person. 2. **Offboarding**: A shared password means every departure requires rotating the shared password and re-distributing to everyone still employed. Per-person logins mean one `DROP ROLE` and done. ### What if they need to use Supabase Studio UI for staging? They can't with this setup — Studio requires org membership. If someone genuinely needs Studio (e.g. to use the visual Table Editor rather than a SQL client), the options are: 1. Give them **Developer** role and accept the prod-write risk (bad). 2. Wait for Supabase to ship per-branch dashboard permissions (not on their roadmap as of 2026-09). 3. Use a third-party SQL GUI that talks to the same connection string (TablePlus / DBeaver — arguably nicer than Supabase's SQL editor). Option 3 is what we do. ### Can engineers see production data through the staging branch? No. Supabase branches are separate physical Postgres databases (different `project_ref`, different hosts, different data). The staging branch is created from prod's schema but its own data. Engineers with `eng_` credentials cannot reach production even by manipulating the connection string. ### Why does the engineer login have BYPASSRLS? Isn't that dangerous? On staging, yes intentionally. Every table on staging has RLS enabled (inherited from the prod schema clone) with policies scoped to `authenticated` role checking `clerk_id`. Without `BYPASSRLS`, an engineer connecting as `eng_` would see zero rows on virtually every table — they'd be unable to inspect data, verify a migration worked, or debug a query. The SOC 2 tradeoff: * **Risk**: staging PII (if any) is visible to engineers with a login. * **Mitigation 1**: Never copy raw prod PII into staging. Use synthetic seed data or scrubbed snapshots. * **Mitigation 2**: Engineers have per-user logins, so any access to data on staging is attributed to a named person in Postgres logs. * **Mitigation 3**: BYPASSRLS is granted ONLY on staging. Production engineers never have BYPASSRLS on the prod DB (they don't have DB credentials at all). If you need to **test RLS behavior itself** (i.e. "does my policy correctly block user B from seeing user A's rows?"), do NOT use your engineer login — your `BYPASSRLS` attribute defeats the test, AND your login is not a member of the `authenticated` role, so a bare `SET ROLE authenticated` would also fail with "permission denied to set role". Test RLS using one of these methods instead: **Option 1 — Local Supabase stack** (recommended; fully reproducible, exercises the real JWT → PostgREST → policy path): ```bash theme={null} 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=*" ``` Setup: [https://supabase.com/docs/guides/local-development](https://supabase.com/docs/guides/local-development) **Option 2 — Staging PostgREST with a crafted JWT**: hit the staging REST endpoint with `Authorization: Bearer `. Craft the JWT with `sub` set to a `clerk_id` that exists in staging. Same end-to-end path as production — the realest test short of prod itself. **Option 3 — Dedicated test role in psql** (for ad-hoc policy probing): Create a one-time test login role (as `postgres`, via psql + `\password` per Step 2 above) that is a member of `authenticated` and does NOT have `BYPASSRLS`: ```sql theme={null} 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 ``` Connect as `eng_rls_test` and run: ```sql theme={null} SELECT set_config('request.jwt.claims', '{"sub": "user_"}', false); SELECT * FROM your_table; ``` Because `eng_rls_test` is a member of `authenticated` and lacks `BYPASSRLS`, Postgres evaluates your RLS policies as a real authenticated user would. ### An engineer needs to test something that requires production data. Options in order of preference: 1. **Seed staging with a synthetic subset** that mirrors the shape of the prod data (best practice; scriptable). 2. **Restore a scrubbed prod snapshot** to staging (ask an admin; PII must be redacted first). 3. **Grant a scoped, per-table read-only Postgres role on prod**, with `SELECT` only on the specific tables they need and NO blanket read. Admin creates a dedicated role (e.g. `reader__`), grants `USAGE` on `public` + `SELECT` only on the needed tables, hands over credentials via 1Password, and `DROP ROLE`s it when the task ends. **Do NOT grant the built-in Supabase "Read-Only" dashboard role** — its underlying `supabase_read_only_user` has `pg_read_all_data` + `BYPASSRLS` (see access policy #3 at the top) and cannot be restricted per-table, so it would expose sensitive tables like `ai_chat_logs`. Never copy raw prod data into staging without scrubbing PII first (SOC 2 CC6.6). ### Can I use the pooler URL instead of the direct connection? For migrations (DDL, transactions, `pg_dump`): **use the direct connection** at port `5432`. The Supabase transaction pooler at port `6543` does not preserve session state across statements and can break migration tools. For app-style connections (short-lived queries, many concurrent connections): the pooler is fine. Get the pooler URL from the Supabase Dashboard → Settings → Database → Connection Pooling. ## Related docs * [DATABASE-VIEW-RLS-SAFETY.md](../DATABASE-VIEW-RLS-SAFETY.md) — RLS rules for views and migrations (required reading for any migration that touches views). * [SECURITY-INCIDENT-RPC-FUNCTIONS.md](./SECURITY-INCIDENT-RPC-FUNCTIONS.md) — RPC function security posture. * [slack-revenue-data-flow.md](./slack-revenue-data-flow.md) — data-flow SOC 2 template used as reference for this doc's structure. This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.