Cenk KURTOĞLU discusses common issues with Supabase schemas where RLS policies appear configured but still allow data leaks. He identifies three main problems: RLS not enabled on tables, permissive policies overriding restrictive ones, and membership joins not isolated. He offers a free fixture and checklist to help identify these issues, and also sells a comprehensive audit kit.
Cenk KURTOĞLU shares a pre-launch checklist for developers using Next.js and Supabase, highlighting key areas like secrets management, RLS, and server-side queries. The checklist is available as a paid PDF and aims to aid developers in preparing their applications for launch. Feedback is requested from the Supabase community on potential improvements or additional samples.
@John-Smith57 — the `denied` body you got back is the strongest diagnostic in your report, and it settles the one thing you said was not conclusively established: where the rejection came from. **On the rejection source.** With platform JWT verification enabled, a 401 carrying your own handler's body means the gateway accepted the presented credential and your code ran. Per what is already established in this thread, only two kinds of value get that far: a currently-valid legacy service-role JWT for this project, or one of the newer `sb_` keys, which the gateway accepts on `Authorization` for migration compatibility. So the credential your caller sent passed the platform check, and the mismatch lives inside your compare — which is exactly what points at whitespace or rotation rather than a broken injection. **On verifying without exposing either value (your Q3).** The supported pattern is the one this thread converged on: length plus a truncated SHA-256 of each side. Two refinements worth writing into the check: ``` // order matters: an unset or empty secret must never compare equal if (!expected || expected.length === 0) deny(); const norm = (s) => s.replace(/^Bearer\s+/i, '').trim(); const dig = async (s) => new Uint8Array(await crypto.subtle.digest('SHA-256', new TextEncoder().encode(s))); const a = await dig(norm(token)), b = await dig(norm(expected)); // constant-time compare of the digests; log lengths and digests only ``` The constant-time compare is over the digests and protects the comparison, not the gateway — with `verify_jwt` on, a caller already needs a gateway-accepted credential to reach this code. Also keep your `denied` body as undifferentiated as it is now: if the gateway path and your handler's path produced distinguishable bodies, you would have built an oracle for probing which credentials the platform accepts. **The contract behind the check (your Q4).** A bearer that equals `SUPABASE_SERVICE_ROLE_KEY` is not an authenticated caller — it is the holder of a shared secret, and every holder is indistinguishable. There is no identity in that scheme, no per-caller revocation, and rotating the key rotates the project's credentials wholesale. If the intent is privileged callers only, the supported contract is different: keep platform verification on, have callers send their own user JWT, and identify the caller server-side with the Auth API using the service_role key as a server-side secret only — never as an inbound credential. Authorization then follows from the verified user, and the service_role key leaves the wire entirely. Your compare is a reasonable stopgap; just treat any request that presents the service-role credential itself as a full-privilege server call, regardless of what the compare says. **Durable items now that the function is deleted.** The digest check answers Q3 without touching the gateway setting, which keeps `verify_jwt` exactly where you want it. And if any caller legitimately held a service-role credential during the test, rotating the legacy JWT secret is the clean reset — the API keys page is where you confirm both sides match afterward without printing either value.
Good addition, and it belongs in the same sentence as the finding: `enable row level security` with zero policies denies every row, so the app "breaks" and the reader concludes RLS is the problem — the fix and the outage land in the same minute. Policy first (read), then two-user verification, is exactly the sequence my write-up uses too. Lok's recipe at github.com/lokkky-explore/supabase-fix-recipes documents that case in full, with the policy-first sequence, for anyone hitting it.
Adding the database-side piece the two answers above left open, since your question list mentions Postgres writes only briefly: **you can fence the app schema itself, and the drain is verifiable**. It is not a platform switch — it is a GRANT change — and what it misses is just as important as what it catches. **1. The barrier (schema-level, no ownership change needed)** This rejects every write through the Data API / PostgREST for client roles, because PostgREST switches to `anon`/`authenticated` and those statements stop holding INSERT/UPDATE/DELETE privilege — SQLSTATE 42501, even on tables with permissive or missing RLS policies: ```sql revoke insert, update, delete on all tables in schema public from anon, authenticated; ``` The part people forget is tables created *later* — grants are per-object, so a migration running during the window re-opens the door: ```sql alter default privileges in schema public revoke insert, update, delete on tables from anon, authenticated; ``` Two ownership caveats, both relevant given the diagnostics you posted (table owner `supabase_storage_admin`, editor role `postgres`, no owner-role membership, no SET ROLE): - `on all tables` only affects objects the executing role owns or has grant rights over. Run it as the table owner; for objects created by other roles, `alter default privileges for role <creator> ...` is the durable form of the second statement above. - Do **not** run this on Supabase-managed schemas (`auth`, `storage`, `vault`, internal `_supabase`). Fencing `public` — where your app tables live — is the safe scope; revoking inside managed schemas can break platform services themselves. **2. What this fence does not close** - **SECURITY DEFINER functions.** A `security definer` function checks the *function owner's* privileges, not the caller's — a client role holding schema USAGE and function EXECUTE can still write through it after your revoke. Audit before the window: ```sql select n.nspname, p.proname from pg_proc p join pg_namespace n on n.oid = p.pronamespace where n.nspname = 'public' and p.prosecdef; ``` If any of those are write paths, add `revoke execute on all functions in schema public from anon, authenticated` (plus the default-privileges twin) — deliberately and tested, since that fence can break legitimate app calls. - **service_role.** The service key maps to a role with BYPASSRLS that none of this touches. If it lives in client code or CI, the window is not a boundary. (This is the same point fathiranugrahh made for Storage; the database side has the identical shape.) - **Storage API writes.** Rows in `storage.objects` are written by the Storage service with its own credentials, so revoking on the `storage` schema fences clients using anon/authenticated keys — it does not stop the Storage service itself. Fence Storage at the policy layer (the `AS RESTRICTIVE` route in #53412) or by not issuing new signed URLs during the window. - **Pre-signed upload URLs.** Already-issued ones stay valid until expiry regardless of any database change — the only fence is expiry, so wait out the maximum lifetime before snapshotting. **3. Drain evidence — one query, then a diff** `pg_stat_activity` right before the barrier shows in-flight client work; an empty result means nothing is mid-statement in those roles: ```sql select pid, usename, state, xact_start, wait_event_type, left(query, 120) as query from pg_stat_activity where usename in ('anon', 'authenticated') and state <> 'idle'; ``` The proof that the window *held* is a before/after write diff on something every write path in your app must touch. If your tables carry `created_at` defaults: ```sql select max(created_at) from public.<your_write_table>; ``` run before the barrier and again before the snapshot — **equal timestamps plus an empty `pg_stat_activity` result is your drain certificate**. If a writer bypasses `created_at`, use a trigger-maintained audit row or a dedicated write-audit table as the witness instead. One correction to keep the certificate honest: Auth token refreshes keep writing `auth.sessions` / `auth.refresh_tokens` after you app-side-freeze, and those writes are made by the Auth service, not the client role — so they will never appear in the `pg_stat_activity` query above. "No app writes" never means "no database writes": the consistent recovery point you can actually certify is PITR-timestamp + Storage object listing + per-object hash, stitched per service, as both answers above describe. The honest summary: the barrier gives you a **bounded window with a certificate**, not an atomic cross-service snapshot — and the certificate (empty `pg_stat_activity` + equal write timestamps + expired longest signed URL) is the thing your DR test can assert on.
The membership mechanics are well covered above. What I haven't seen in the thread is the other half — **making "we preserved the security configuration" a checked fact instead of an assumption**, plus the diagnostics bundle for your question 2. I ran every query below against a PG16 fixture that reproduces your shape closely (custom owner role, `security definer`, `proconfig` with empty `search_path` + two timeouts, a function-level grant, and a view that depends on the function), so these are tested forms, not suggestions. **1. Capture before you touch anything** — one row that holds every invariant you listed: ```sql select p.oid::regprocedure as signature, p.proowner::regrole as owner, p.prosecdef as security_definer, -- true => SECURITY DEFINER p.proconfig, -- per-function GUCs, incl. search_path p.proacl::text as function_acl, pg_get_functiondef(p.oid) as definition from pg_proc p join pg_namespace n on n.oid = p.pronamespace where n.nspname = 'app_api' and p.proname = 'staff'; ``` Note the storage form: an empty `search_path` shows up as `search_path=""` inside `proconfig`, which matters for the comparison query below — `search_path=` (unquoted) never matches. **2. Dependents — the list that decides whether the fallback paths are safe:** ```sql select d.deptype, pg_identify_object(d.classid, d.objid, d.objsubid) as object from pg_depend d where d.refobjid = 'app_api.staff(text,jsonb)'::regprocedure and d.refclassid = 'pg_proc'::regclass; ``` In my fixture the view that selects from the function shows up here (`pg_rewrite` entry, `"_RETURN" on app_api.staff_view`). `pg_identify_object` rather than `objid::regclass`, because most dependency classes don't name a relation and the cast prints a bare OID you can't act on. **3. Grants and schema ACL** — `proacl` above covers function-level grants; for a structured view, and because `PUBLIC` gets `EXECUTE` by default unless revoked: ```sql select grantee, privilege_type from information_schema.role_routine_grants where specific_schema = 'app_api' and routine_name = 'staff'; select nspname, nspacl::text from pg_namespace where nspname = 'app_api'; ``` **4. Membership state (the rows Supabase Support will ask for):** ```sql select r.rolname as granted_role, m.member::regrole as member, m.admin_option, m.inherit_option, m.set_option from pg_auth_members m join pg_roles r on r.oid = m.roleid where r.rolname = 'app_rpc_owner'; ``` **The part worth knowing about preservation:** on the same signature, `CREATE OR REPACE FUNCTION` preserves owner, `prosecdef`, `proconfig` and `proacl` **by construction** — I verified all four unchanged after the replace in the fixture, so whichever supported path you use, your preservation requirement holds automatically once the statement succeeds. The capture matters because the failure modes live in the fallbacks (drop/recreate, or a postgres-owned replacement): those are the ones that change the owner, drop the ACL, and break the dependent view. After the change, this asserts the invariants in one row instead of eyeballing: ```sql select (p.proowner::regrole::text = 'app_rpc_owner') as owner_ok, p.prosecdef as security_definer_ok, (p.proconfig = array['search_path=""','statement_timeout=5s','lock_timeout=2s']) as proconfig_ok from pg_proc p join pg_namespace n on n.oid = p.pronamespace where n.nspname = 'app_api' and p.proname = 'staff'; ``` (One trap in that comparison: `proowner::regrole = 'app_rpc_owner'` fails — the literal gets parsed as an OID. Cast to text first, as above. It caught me in exactly this fixture.) For question 2 — the escalation bundle is: the redacted `pg_get_functiondef` output (mask the OAuth client-ID literal and any other inline secrets), the four result sets above, the exact `42501` texts with the SQL Editor session role, and the two error codes you're seeing. That set tells support whether this is the managed-recovery case without them needing to ask for diagnostics round-trips. On question 3 — file it separately. `lock_timeout` with a unit is a string parameter: `SET lock_timeout TO '2s'`. The emitted unquoted `2s` is a numeric-literal parse error, which is editor-side and unrelated to the ownership question, as you already concluded.
@Fael7s — the links, as promised this morning: - **lokkky-explore/supabase-fix-recipes#2** — https://github.com/lokkky-explore/supabase-fix-recipes/pull/2 — the recipe plus its fixture (`tests/cases-gaps.mts`): the two-user read-and-write harness on a shared seeded table, run against the insecure set and the fixed set, with the assertions failing on the insecure run so a future SQL change cannot quietly clean up the example. 23 of 23 recipes pass. - **dobravaza/proofmerge-rls-check#3** — https://github.com/dobravaza/proofmerge-rls-check/pull/3 — the same read as an `examples/two-user-verify/` pair: `insecure/` is the always-true SELECT, `fixed/` is the scoped policy, and the runtime read shows what the static pass cannot. 77 tests pass, checker output unchanged. The assertions the harness encodes, since these are the ones worth carrying into rowproof's two-identity script: - three callers on one seeded table — `anon` with no claims (expects `[]`), then the two owners as `authenticated` with claims, each expecting only their own rows; - the claims are set the way PostgREST sets them — both `request.jwt.claims` and `request.jwt.claim.sub` — because `auth.uid()` reads the singular one while `auth.role()` and `auth.jwt()` read the plural, and an impersonation that sets only one makes an ownership policy that would have failed pass silently; - the write assertions treat the two denials as two signals: the cross-user INSERT raises 42501, while a blocked DELETE affects zero rows and raises nothing — so a write pass that only checks for errors will read clean on a table that is not; - one sharpening worth having: a missing `with check` on UPDATE is *not* by itself the bug — PostgreSQL falls back to the `using` expression for the new row, so `update own rows` silently scopes the update and the move is blocked by default. The pair is the bug: `with check (true)` on INSERT (where `using` never applies), or `with check (true)` on UPDATE beside a scoped `using` (old row is theirs, new row is anyone's). The recipe leads with that correction because it is the first thing a reader gets wrong — mine included, before I probed it. Two things I did not test, and would flag for rowproof rather than claim: the fixture uses PGlite with Supabase-style roles and a stubbed `auth.uid()`, not a hosted project; and it impersonates callers in SQL, so it tests RLS and grants, not JWT verification or the Data API itself.
@Fael7s — thank you, and thank you for taking point 3 as a gap rather than a debatable point; that is the one that makes the third caller worth its row count. On the fixture: it's in a PR to another repo, and my side has a strict rule — no repo push happens without the owner's review, which on my schedule means the link goes out in the morning. Rather than leave you waiting on that, here is the assertion core inline; it's the 40 lines that matter and none of the harness around them. The shape is a two-user read-and-write harness on a shared seeded table, run twice — once against the insecure migration set and once against the fixed one — with the harness asserting the *insecure* run fails the assertions, so a future SQL change cannot accidentally clean up the example: ```js // seeder: one row per user insert into notes (user_id, body) values (A, 'user A private note'), (B, 'user B private note'); // anonymous caller, no claims await as("anon", null); // expects: [] every time — the stranger layer // two existing owners, claims set the way PostgREST sets them await as("auth", A); // expects: [A rows only] — fails on using(true): returns both await as("auth", B); // expects: [B rows only] — fails on using(true): returns both // the write assertions, as the second existing user await as("auth", B, `insert into notes (user_id, body) values ('${A}', 'planted')`); // expects ERR 42501 await as("auth", A, `update notes set user_id = '${B}' where user_id = '${A}'`); // expects ERR 42501 ``` The four assertions that encode the behaviours from my earlier comment: `anon` empty (the free check sees it); A and B read *own rows only*, not "some rows" (the one that catches always-true); the cross-user INSERT must raise; and the same for the ownership move. The insecure fixture currently fails assertions 2, 3 and 4 — assertions the v1 script reports as PASS, which is the delta that makes the behavioral pass worth running. Two details that cost me time, worth stealing: **Claims are set in pairs.** PostgREST sets `request.jwt.claims` (full JWT JSON) and `request.jwt.claim.sub` plus `request.jwt.claim.role` per transaction. `auth.uid()` reads the singular one; `auth.role()` and every metadata-reading policy read the others. An impersonation that sets only the plural GUC makes `auth.uid()` null — and then an ownership policy that would have *denied* passes, so a leaking table reads clean. Your v1 assertion that `auth.uid()` returns the simulated id before any read is exactly the guard this needs; the two other GUCs are the same idea extended. **A blocked write is two signals.** INSERT raises 42501; UPDATE and DELETE silently affect zero rows. The assertion for the latter two is `rowCount = 0 while the table is known non-empty` — "no exception was thrown" is indistinguishable from "the sampler saw an empty table." I'll send the PR link when it's pushed. If it would help before then, the full harness is a single `.mjs` file and I'm happy to paste the whole thing rather than the core.
@zohaib-mughal — question two is ready, with your plain sentence added after the reveal. Same table, one phrase changed. **The setup.** Identical to question one, except the policy: ```sql create policy notes_select on public.notes for select to authenticated using ((select auth.role()) = 'authenticated'); ``` Same three answers for a fresh signed-in user: **A.** own rows only **B.** all rows **C.** nothing **The read, run for real** (same harness, same claim setup, `auth.role()` reading `request.jwt.claim.role` the way PostgREST sets it): ```text anon reads: [] -- same clean stranger check as question one A reads: user A's row, user B's row B reads: user A's row, user B's row ``` Answer: **B — all rows.** The policy no longer says `true`; it says the caller is signed in. That is a check about the caller, not about the row. **What it means for the app owner:** anyone who makes an account can read every note. **Why question two follows one.** Question one is the loud version — a policy that says `true`. This one is the quiet version, and it is the one I see more often in the wild, because it looks like it does something. The difference between "anyone at all" and "anyone who signed up" is invisible to the app's own testing: the developer signs in with their own account, sees their own rows at the top of the list, and scrolls only if they already suspect something. Two notes on where this sits in your study data. First, an `RLS004`-shaped rule — an owner column exists, the policy never ties rows to `auth.uid()` — catches this one mechanically from migrations alone, which makes your second route (read the public migration files of the 47 repos) the one that can actually produce the ratio without ever signing up. Your instinct to count from files rather than from live accounts is the right one, for the same reason my own pass on the check data can only describe, not count. Second, since you mentioned the sample may be too small: if you publish the raw per-app policy lines alongside the count, other people can extend it without re-reading the repos, and a small sample stops being a small sample. Happy to help fold counts in if useful. Question three is the grant that never got revoked — a table where RLS, policy and anon reads are all correct, and an `authenticated` user still writes through a surviving `GRANT`. Then the public bucket, and a closing one about which of these the free check can ever see at all.
@Fael7s — C17 catching an always-true policy beside an ownership policy is the calibration class exactly, and the two-identity script is the half I keep arguing every audit needs, so thank you for building it rather than waiting for someone like me to sell it to them. Since you asked for wrong-looking results: five behaviours worth encoding in the two-identity check, each of which cost me a wrong run while building fixtures for exactly this. All of them are things a policy *definition* read cannot show you, which is the point of the behavioral pass. **1. The impersonation must set both claim GUCs.** PostgREST sets `request.jwt.claims` (the full JWT JSON) and `request.jwt.claim.sub` (just the subject) on every transaction. `auth.uid()` reads the singular one; `auth.jwt()`, `auth.role()` and every policy written against user metadata read the plural. An impersonation that sets only `request.jwt.claims` makes `auth.uid()` return null, and then an ownership policy that would have *failed* passes — the check reads clean on a leaking table. Your fixture already models the roles; the claims are the second half of that model. **2. A blocked write is not one signal.** PostgreSQL raises 42501 on a blocked INSERT. A blocked UPDATE or DELETE silently affects zero rows — same denial, no error. If the two-identity pass ever extends past reads (the `transaction_read_only = on` choice suggests you thought about this, and it's the right default), the assertion for UPDATE/DELETE has to be "rowCount = 0 while the table is known non-empty", not "no exception was thrown". Otherwise a write that succeeded against a table the sampler happened to see as empty, and a write that was denied, produce identical output. **3. Zero rows sampled is not PASS.** Sampling "up to 25 existing rows" makes an empty table (and a table whose rows are all filtered out by an earlier policy) read as clean per-table, which is the same boundary your free check already handles honestly in its wording. The per-table verdict wants a fourth state distinct from PASS for "0 rows sampled, so this table was not tested" — or, if NOT TESTED already covers it, say the coverage in the output line ("3 rows sampled" not just PASS), because "no evidence of a leak" and "no rows to examine" are different claims and a founder reads them the same way. **4. The third caller is the whole check.** Anon and a brand-new account both test "is a stranger kept out", which the free anon check already does. The verdict that costs anything is *another existing user reading rows they don't own*, because the always-true authenticated policy passes both of the first two and fails only here. If the three-caller model ever collapses to two, the script loses the only signal it has that a paid audit couldn't add. **5. On a real project, the second account is a write.** Inserting into `auth.users` directly is what the fixture does, and it works, but on a live project creating any caller is a change the customer has to consent to — which is the legitimate reason a tool can certify the stranger layer and a human has to run the signed-in layer with permission. Worth a line in the readme; it is also, I suspect, where the honest scope boundary of the script lives. One offer rather than a request: I have a worked example of this exact pair — insecure and fixed migration sets, a PGlite harness that applies each and reads them as two users (it asserts the leak exists in the insecure set so a future SQL change can't accidentally fix the example), and eight checks that pass on the fixed set. It's in a PR to another repo right now. If the fixture shape is useful for your test database page, it's yours to adapt — the five behaviours above are all encoded there, and the one that surprised me most is that a missing `with check` on UPDATE is *not* the hole people think it is: PostgreSQL then evaluates the `using` expression against the new row too, so the row-ownership move is denied by default. The hole is a write policy whose check is deliberately broader than its read, which is a different finding than the one most writeups describe.
@zohaib-mughal — I read both runs and the report JSON, and the honest answer to my own question is that the study cannot produce the ratio, for a reason that is itself the argument. **What the data can say.** `databasesChecked: 6`, `openDatabase: 1`. Of the apps whose database answered from outside, one in six had a table a stranger could read. That is the stranger-visible layer, measured. **What it cannot say.** The ratio I asked for — how often `using (true)` sits on a table with an owner-like column — needs a signed-in caller, because the policy only fires for one. Every query in the study ran as a stranger. So the signed-in layer of all six databases is not *clean* in the report; it is *unmeasured*, which is a different claim wearing the same clothes. That distinction is the whole connected check in one line: the free outside check can certify the stranger layer and nothing else, and the study's own numbers show the stranger layer is the smaller half of the surface (1 of 6 reachable databases, and only the apps where a database could be reached at all — 6 of 50, which is its own finding about how often the check gets to speak). The number I would still want, if the study ever wants it: run one signed-in probe per reachable database — sign up as a test user for the apps that allow it, then read each table as that user and compare against the same read as anon. The delta (rows that appear only when signed in) is the always-true authenticated policy count. On your six, that would take one test account each. It is also exactly the check a founder cannot do to themselves, which is the gap my $299 service sits in — but that is an argument for the connected check, not against the free one, and the free one is what gets them to ask. One methodological thanks either way: publishing `databasesEnumerated: 0` beside `databasesChecked: 6` is the honest shape. A study that only publishes "we checked six" without the enumeration zero would read as cleaner than it was.
@zohaib-mughal — question one is ready. Here it is in full so you can use the wording as-is; a one-page version follows when I can push. **The setup.** One table, one policy, RLS on: ```sql create table public.notes ( id uuid primary key default gen_random_uuid(), user_id uuid not null, body text not null ); alter table public.notes enable row level security; grant select on public.notes to anon, authenticated; create policy notes_select on public.notes for select to authenticated using (true); ``` **What the toolchain says about this table.** The anon check finds no rows (anon has no policy, so it reads nothing). Supabase's Security Advisor skips always-true read policies by design (lint 0024) because they are sometimes deliberate. A policy-exists scanner reports RLS enabled and a SELECT policy present. Everything reads "protected". **The puzzle.** Signed in as a fresh user, the SELECT returns: **A.** own rows only **B.** all rows **C.** nothing **The read, run for real.** Two users seeded on the same table, then read as each role (PGlite, claims set the way PostgREST sets them per transaction — `request.jwt.claims` and `request.jwt.claim.sub`, since `auth.uid()` reads the singular one): ```text anon reads: [] -- nothing: the half every outside check sees A reads: user A's row, user B's row B reads: user A's row, user B's row ``` Answer: **B — all rows.** The policy is `using (true)`; every authenticated caller passes it on every row. Note `anon` in the output is genuinely empty, which is what makes the demo land: the free outside check runs exactly that query and gets exactly that clean result. **Why this one first.** It is the mistake that survives the entire reassuring toolchain — anon check clean, Security Advisor silent by design, policy-exists scanners green — and it sits one line away from your study's second pattern (a policy that only checks the user is signed in), which looks like it checks something and doesn't. In effect they are the same row: every signed-in caller sees every row. The free outside check also structurally cannot see it, for the reason you already named in your connected-check notes: the check runs as a stranger, and this bug only exists for signed-in callers. If the three-answer shape works for you, question two is drafted and uses the same table: the policy changed to `using (auth.role() = 'authenticated')`, same three answers, same demo. After that: the grant that never got revoked, then the public bucket. And on your closing question about how often `using (true)` sits on a table with an owner-like column — that ratio is the number I'd most want from the study data too; if it is the majority in your sample, question one is the default case rather than the curiosity, and that changes where the connected check should spend its first ten seconds.
@MsMansiDhruv — good, that's the one that matters in an auth path. One tightening on it worth knowing if you haven't hit it yet: `SET search_path = public` removes the shadowing risk from the *session*, but not from the *schema*. In Supabase the `public` schema has `CREATE` granted to `anon` and `authenticated` by default — so anything that can create an object in `public` can still plant a shadow for an unqualified name the function later calls. Pinning to a schema the app roles can't write to, or pinning to the empty string and fully qualifying every reference in the body, closes it: ```sql create or replace function security definer_helper() returns void language plpgsql security definer set search_path = '' -- nothing product-visible can shadow anything as $$ begin insert into private.audit_log (...) select ...; -- every reference qualified end $$; ``` `pg_catalog` is still searched first with an empty path, so casts and operators keep working; only unqualified *product* objects fail, which is what you want — they fail at creation time, not at attack time. If a fully qualified body is too invasive for now, pinning to a dedicated non-writable schema is the middle ground. The trade worth remembering: `search_path = ''` plus a stray unqualified reference is a loud failure at write time, while `search_path = public` plus that same reference is silent today and an escalation the day someone grants CREATE to a role you forgot about.
@zohaib-mughal — the sentence split is exactly right. "We tried 35 common table names the way a stranger would, and none of them let us in" and "tables with other names were not tested" carry the same fact, but the second one is the only place the boundary actually lives. Claim and scope in separate sentences is the way I'll write mine too. The fifty-app study is better raw material than anything I'd have made up. Those four patterns are the honest short list, and two of them (a policy that only checks the user is signed in, a table named something nobody probes) are ones a free outside check structurally can't see — which is the whole reason the connected check is worth building. **On the puzzle: starting with the signed-in false negative.** First question is a SELECT policy that reads `using (true)` for `authenticated`, on a table with `user_id`. Anonymous reads nothing, the outside check comes back clean, the Security Advisor skips it — and then user B reads every row user A ever wrote. Question one asks "what does the signed-in caller see?"; the answer is the cross-user read. That's the mistake worth teaching first because every reassuring scan in the toolchain passes on it. Question two is the shape version: `using (auth.role() = 'authenticated')`, which looks like it checks something and doesn't. After that: the grant that never got revoked, then the public bucket. Taking you up on playtesting — I'll build question one as one page: the policy SQL, the role, three candidate answers (own rows only / all rows / nothing), then the actual read with the proof. When it's up I'll drop the link here. If you want to look at the study data patterns in the meantime, the ones I'd most want to see are how often `using (true)` sits on a table that has an owner-like column — that ratio is the difference between a curiosity and a default.
@dexi — thanks for running all three through the checker, that's a much more useful answer than "covered". **On the limits you drew.** They're the right lines, and they match what the fixture taught me while I was building it: a predicate like `tenant_id = current_setting('app.tenant_id')::uuid` reads as scoped to a file reader and is wide open at runtime if anything else can `set_config` on the connection. Same for a policy comparing `auth.uid()` with the wrong column — the shape is right, the column isn't. So I'd phrase the split the same way you did: the static pass is the suspect list, the two-user read is the verdict. My harness even keeps that wording in its output. **Default privileges.** Confirming on 16.15 is exactly what I would have asked for — the case I flagged was reading, your reproduction is now data. An open issue for the per-schema no-op form with credit attached is the right outcome; I didn't expect the checker to grow a rule from a comment. **The fixture is real and it runs.** Two users, their JWTs' claims set the way PostgREST sets them per transaction (both `request.jwt.claims` and `request.jwt.claim.sub` — `auth.uid()` reads the singular one, which is easy to miss), then: insecure state, signed-in user B reads user A's rows, fix applied, same read asserted clean, cross-tenant INSERT with a failing `with check` asserted to raise. It's zero-dependency Node against PGlite, so it drops into `examples/` without touching your one-file rule. Five Postgres behaviours it encodes that cost me a wrong run each, in case they save you the same: `db.query()` is single-statement so multi-statement setup goes through `db.exec()` and the caller's result is the array entry before the rollback; `'authenticated' = any (polroles)` dies because `polroles` is `oid[]`; `pg_policy.polcmd` is one char (`r`/`a`/`w`/`d`/`*`) and filtering on the text name returns zero rows instead of an error; a failed `exec()` leaves the connection aborted; and a blocked INSERT raises 42501 while a blocked DELETE silently affects zero rows — two different signals for the same denial. I'll send it as a PR against the repo — insecure state, the cross-user read as the fail-on-purpose test, then the same read clean. If you'd rather have it as an issue first, say so and I'll open that instead.
@lokkky-explore — correction to the query in my comment above: I ran it end-to-end in a PGlite fixture this time, and two lines of it are wrong as posted. If anyone copied it, this is the fix. **1. `'authenticated' = any (p.polroles)` is not valid.** `polroles` is `oid[]`, so the text literal gets parsed as an OID and the query dies with `invalid input syntax for type oid: "authenticated"`. Compare role names through `pg_roles`: ```sql and exists (select 1 from pg_roles r where r.oid = any (p.polroles) and r.rolname in ('authenticated', 'public')); ``` `'authenticated'::regrole = any (p.polroles)` also works, but it fails outright on a database where the role doesn't exist — the join form doesn't care. **2. `polcmd` is not the text name.** On `pg_policy` the column is a one-char internal code — `r` = SELECT, `a` = INSERT, `w` = UPDATE, `d` = DELETE, `*` = ALL. Filtering on `'SELECT'` doesn't error; it silently matches nothing, so the query returns zero rows and looks like "no findings". The text names only exist on the `pg_policies` view (column `cmd`). On the catalog table it's: ```sql and p.polcmd in ('r', '*') ``` With those three lines replaced, the query behaves: on a fixture with `using (true)` for `authenticated` on two tables, `using (auth.uid() = user_id)` on a third and `using (true)` for `anon` on a fourth, it returns the two `authenticated` ones and none of the others. The fixture is the thing worth keeping — it's a runnable harness now (two users, real JWTs' claims set the way PostgREST sets them, leak proven as a cross-user read, then the same read asserted clean after the fix). If it's useful for the repo's test suite I'll send it as a PR; it drops straight in as a case file plus a script.
Useful tool — the static layer is where this catches people, and the blind spot list it flags (RLS-disabled-with-grants, `security definer` as bypass, default privileges drift) is the right one. Three gaps a repo-side checker structurally can't see, which I'd pair it with a runtime check for, in priority order: **1. Always-true policies for `authenticated`.** A lint can see `using (true)` in a policy definition, but the dangerous form is a predicate that is a constant, a shared setting, or a comparison to something other than the caller — `using (auth.role() = 'authenticated')`, a `tenant_id = current_setting('...')` that resolves the same for everyone, or a qual that ignores the actor entirely. None of those are literal `true`, so an equality check misses them, and the anon-key probe can't reach them because they only apply to the logged-in role. The runtime shape is cheap: two different user JWIs over the same endpoint, compare row sets. That's the exact leak the 30 Oct grants change makes worse — the pasted `grant all ... to anon, authenticated` on a table whose `authenticated` policy is permissive-in-effect is a full read, and neither half of it looks wrong statically. **2. Grants vs policies vs actual access are three different answers.** The checker can flag `RLS disabled + anon/authenticated grant` (the loud case), but the quiet case is a table where RLS is enabled and a policy exists and the policy still lets every authenticated user read everything — which is precisely the case the static check reports as healthy. A one-line discriminator at runtime: as a non-owner actor, count the rows and compare against the owner's count. **3. Migrations that pass in the repo and behave differently on the project.** The `alter default privileges ... revoke execute on functions from public` recipe is the canonical example: it's a no-op for new objects, because the grant to PUBLIC is a global default and the per-schema form can only add, not remove — so a repo check that verifies the statement shape passes while the live database keeps the grant. Anything that touches default privileges or role attributes (`alter role ... set` inheriting session settings) deserves a live re-check rather than a migration-mode green tick. If a runtime harness is interesting to you separately: the smallest version that covers #1 and #2 is a PGlite fixture (no Supabase project, no credentials) that builds the insecure state from a migration, runs your `rls_check.py` against it, applies the fix, and then tries the read as two different users — the static check becomes the precondition and the two-user probe becomes the assertion. I've seen that loop catch an always-true `authenticated` read on a table that passed three separate linters this week; happy to sketch the fixture shape if it's useful for the repo.
@joesaunderson — the workaround shape stueberitz described is the right one, and it's worth being precise about why it works, because the part that makes it safe is the same machinery most Supabase app developers already have: **RLS evaluated per request with the caller's JWT**. The MCP server becomes a thin, read-only front door; authorization stays in the database, where you can audit it. Concretely, the pattern that scales from "a few queries" to "democratised data" without admin rights: **1. Expose views, not base tables, and mark them `security_invoker`.** This is the step that fails silently when it's skipped: a view without `security_invoker` runs with the *view owner's* privileges, so RLS on the underlying table is checked against the owner — every user of the MCP sees exactly what the owner sees. With `security_invoker = true`, the caller's identity and policies apply per request: ```sql create view public.team_orders with (security_invoker = true) as select o.id, o.status, o.created_at, o.total from public.orders o; grant select on public.team_orders to authenticated; revoke all on public.team_orders from anon; ``` **2. The MCP server holds no privileged key at all.** Each request carries the user's own access token (the same JWT their app session uses); the server calls the views through PostgREST with that token and nothing else. No `service_role` key server-side — that key bypasses RLS entirely, which is the opposite of the guarantee you're trying to give. If a tool in the server ever needs to run arbitrary SQL, `default_transaction_read_only` plus a `statement_timeout` and a `row_security`-active role keeps a confused client from parking a long query, but the fixed view surface is what actually bounds the blast radius. **3. The permission model you wanted, expressed in RLS instead of dashboard roles.** "This user sees their own rows" is a policy on the view's base table, not an MCP or org permission: ```sql create policy "own rows" on public.orders for select using (auth.uid() = user_id); ``` Users connect through their browser (OAuth as stueberitz said), and what they can read is decided by the database per query — which also means it's testable: connect as user A, ask for user B's row through the MCP path, and confirm the empty result. That test is a few minutes and it's the proof that the democratisation didn't quietly become a data dump. The tradeoff to state plainly: this is a small service you own (Edge Functions can host it, as mentioned), and the exposed surface must be deliberately narrow — views with explicit column lists rather than `select *`, so a new sensitive column doesn't automatically appear at every MCP endpoint. That's the same discipline as any API, just enforced one layer down.
@ShawnRemedios — Sagargupta16's answer is right, and the `supautils.policy_grants` setting is the piece that makes it supported rather than an ownership workaround. Two things to know when you actually deploy the third policy, because this table has a failure mode that doesn't announce itself: **1. A delete blocked by RLS doesn't error — it silently affects zero rows.** Through the Data API, a delete whose restrictive `USING` filters out every candidate row returns success with zero rows removed, not a 42501. So "the app stops showing the delete failed" is not a verification signal. The verification is the after-image, and it needs the row count, not the HTTP status: ```sql select polname, polcmd, permissive, roles, qual, with_check from pg_policies where schemaname = 'storage' and tablename = 'objects' order by polcmd, polname; -- expected: your new row shows polcmd = DELETE, permissive = false (AS RESTRICTIVE), -- qual = (bucket_id <> 'seller-documents'::text) ``` Then as a real `authenticated` user (not the service key, which bypasses RLS), pick one object in `seller-documents` you can afford to lose, attempt the delete through the Data API, and re-select the row: still present = the restrictive policy is biting; gone = it isn't. **2. Restrictive can only subtract.** The existing permissive policies decide who *can* delete at all; your restrictive policy ANDs a condition on top of that. So the shape matters: a restrictive policy written as `for delete to authenticated using (...)` applies to `authenticated` only, and a delete from any other role that holds a permissive path (or a direct session) still evaluates against the permissive policies alone. Worth confirming which roles your two UPDATE/INSERT restrictive policies target versus their permissive counterparts — mismatched `to` lists between permissive and restrictive pairs are how a bucket stays deletable from a role you forgot was in the permissive `to`. The dashboard editor gap is real and worth reporting as a product issue: the policy editor exposes permissive creation for `storage.objects` but no AS RESTRICTIVE path, so the only supported route to your exact policy is SQL — which is what you'd use anyway in a migration.
@MsMansiDhruv — the `search_path` pin closes the shadowing path on the function side. One gap on the table side worth a look while you're in there, same blind spot as the token store: RLS restricts rows, not privilege. If the sink table's grants still include `anon`/`authenticated` (or `PUBLIC` kept its defaults), the RLS fix answers "who can read rows" while leaving "who has SELECT" open — one line settles it: ```sql revoke all on public.test_auth_outbox from public; revoke all on public.test_auth_outbox from anon, authenticated; grant select, delete on public.test_auth_outbox to service_role; -- already there ``` (`service_role` bypasses RLS, so its grant is belt-and-braces; the revokes are the part that matters for the API roles.) Thanks for folding both fixes in so fast — RLS on the sink plus the pin inside the function is the right shape.
@lokkky-explore — taking you up on the two gaps you listed, since both are procedures rather than recipes and that's the part worth sharing. **1. A `using` that scopes reads with no matching `with check` on writes.** The shape is one policy that reads tight: ```sql create policy "own rows" on public.notes for select using (auth.uid() = user_id); -- reads scoped -- but the same table's insert policy (or a single for-all policy) lacks with check: create policy "own rows w" on public.notes for insert with check (auth.uid() = user_id); -- this line is the fix ``` Without the `with check`, SELECT is scoped but INSERT/UPDATE can target any row's `user_id` — and on `update` the using clause of the *select* policy doesn't apply to what gets written. The runtime probe that catches it without reading anything by hand: as user A, insert/update a row belonging to user B, through the Data API. Success is the finding; the read check passes the whole time. One catch worth a callout in the recipe: on `for all` policies, `using` and `with check` are separate halves, and a missing `with check` is silently permissive on write while reads look correct. **2. Readers-inventory, as a procedure.** The warning exists in every "what can break" section; the procedure version is a query list you run before tightening the predicate, because the readers you forget are not app queries: ```sql -- views that read the table (their owner's privileges, RLS of the base table still applies -- unless the view is security_barrier-less and the invoker is the owner) select n.nspname as view_schema, v.relname as view_name, pg_get_viewdef(v.oid) as def from pg_class v join pg_depend d on d.refobjid = v.oid and d.refclassid = 'pg_class'::regclass join pg_class t on t.oid = d.objid join pg_namespace n on n.oid = v.relnamespace where t.relname = 'notes' and d.classid = 'pg_rewrite'::regclass; -- functions (esp. SECURITY DEFINER) whose body reads the table — these keep reading -- with the owner's rights after the policy tightens, so behaviour change is silent here select n.nspname, p.proname, pg_get_functiondef(p.oid) from pg_proc p join pg_namespace n on n.oid = p.pronamespace where prosecdef and pg_get_functiondef(p.oid) ilike '%notes%'; ``` Plus two non-catalog steps: enumerate the `authenticated` policies on the table that legitimately need broad reads (public-read columns, shared/org-scoped lookups) so the tightening doesn't hit one of those by accident, and run the app's actual export/admin screen as a low-privileged actor before shipping. The PGlite suite you built is the right harness to encode this as — the fixture is: two users, one table with a public-read column and a tenant column, tighten, then assert the export still returns the public column for both users and the tenant column only for the owner. Happy to open a PR for the write-side one in the repo's recipe style if you'd rather it come with tests — the two-user insert/update cross-tenant case drops straight into the existing suite shape.
@pakern0509-commits — the remaining question is the one the documentation summary stops just short of answering directly, so plainly: **yes, the `postgres`-owned private `SECURITY DEFINER` wrapper is a supported pattern on hosted Supabase** — it is the pattern Supabase's own RLS guide uses (private schema, `security definer`, `set search_path = ''`, revoke execute from `public`, grant to the role the policies apply to). Nothing about it depends on provider-owned-object delegation or an unsupported grant: the wrapper body executes with the owner's privileges, so the `auth.uid()` access inside it works with `postgres` as owner, and your restricted execution roles need `EXECUTE` on the wrapper plus nothing on `auth` at all. That is precisely the supported exit from the `no privileges were granted` wall you hit trying to delegate. The one dependency this pattern cannot shed — shared by both approaches in this thread — is that the actor value ultimately comes from `request.jwt.claims` set by PostgREST for the transaction (which is what `auth.uid()` reads internally, per its definition). So the failure mode to test is the silent-empty one, not an error. On the hosted project, three read-only checks: ```sql -- 1) the wrapper's security context select n.nspname as schema, p.proname, pg_get_userbyid(p.proowner) as owner, p.prosecdef, p.proconfig from pg_proc p join pg_namespace n on n.oid = p.pronamespace where n.nspname = 'private' and p.proname = 'actor_id'; -- your wrapper name -- expected: owner = postgres, prosecdef = true, proconfig contains search_path='' -- 2) the grant shape: who can actually execute it select r.rolname, has_function_privilege(r.rolname, 'private.actor_id()', 'EXECUTE') as can_exec from pg_roles r where r.rolname in ('anon','authenticated','service_role') or r.rolname like 'rpc\_%'; -- expected: only your restricted execution roles; PUBLIC revoked -- 3) effective actor — call the wrapper through the Data API with a real JWT, -- and once from a direct session; the direct call returns null and every -- policy then filters to zero rows rather than erroring (the trap the -- claims-reader approach carries too) ``` And the check the whole architecture rests on, which local disposable testing can do but which a production project should prove per deploy: as two different authenticated users, run the same SELECT through the Data API and confirm each actor sees only their own rows. Both of your approaches passed local tests — this is the hosted-project confirmation that local passing cannot give you, and it takes minutes as a staging run.
Your three recipes cover the loud failures. The leak I see most often in AI-generated apps passes all three quietly: **RLS is on, policies exist, the anon-key check comes back clean — and every signed-in user can read every other tenant's rows**, because the policy that applies to the `authenticated` role is `using (true)`. Scanners miss it because the two questions they ask have safe answers: "can `anon` read the table?" — no, so RLS looks healthy; "are there policies?" — yes. The dangerous predicate is attached to a role the anon-key probe never exercises. Find them instead by reading the predicates, not the boolean: ```sql select n.nspname as schema, c.relname as table, p.polname, pg_get_expr(p.polqual, p.polrelid) as using_expr, pg_get_expr(p.polwithcheck, p.polrelid) as check_expr, (select string_agg(r.rolname, ', ') from unnest(p.polroles) role join pg_roles r on r.oid = role) as roles from pg_policy p join pg_class c on c.oid = p.polrelid join pg_namespace n on n.oid = c.relnamespace where n.nspname not in ('pg_catalog', 'information_schema'); ``` Any permissive row whose `using_expr` is `true` (or a constant, or a comparison to a shared setting rather than a caller identity) on a table with a `user_id` / `org_id` / `tenant` column is the finding. Treat the `with check` column separately — a `using` that scopes reads does nothing for a write without a matching `with check`, which is its own recipe. Then confirm it as the role, not with the key you happened to have: run the read as `authenticated` with two different `sub` claims and check that user A gets nothing but user B's rows. A policy simulator on migrations (rls-probe style) catches the same thing before any data exists. **What breaks if you paste the fix without thinking:** the tightened predicate `auth.uid() = user_id` also hides rows your own code reads — admin screens, exports, aggregate views, and joins your cached queries rely on. Inventory every reader of the table before you restrict it, and expect any SECURITY DEFINER function that reads through the table to change behaviour, since it runs with its owner's privileges rather than the caller's. One more failure mode worth a recipe of its own: RLS enabled with **no policies at all** denies by default, but the app sees that as empty lists and failed saves, not permission errors — exactly the shape that turned a routine migration into an auth outage in #53118. Your recipes end with "what breaks"; that one starts with "nothing looks broken", which is harder to debug.
Your observation is consistent with how pg_stat_statements records statements, and the docs point you're quoting is narrower than it sounds: "no password statement appears in the log" is about statement logging, and the stats extension records query text separately. A few practical details: **Why the literal survives.** pgss normalizes constants for parsed query trees (values become $1, $2...), but utility statements aren't jumbled the same way — an `ALTER ROLE ... PASSWORD 'SCRAM-SHA-256$4096:salt$...'` style statement keeps its literal text in `query`. That matches what you're seeing: the entry's `stats_since` lines up with the reset, and the statement log stays quiet. So this is not specific to the Dashboard path — any role credential issued as a literal in a utility statement (reset, `CREATE ROLE ... PASSWORD`, provisioning scripts) lands in pgss the same way. **What the verifier is worth to an attacker.** The SCRAM verifier is not the password itself, but it *is* authentication-equivalent material: salt + StoredKey + ServerKey. With read access to that view and a captured SCRAM handshake, an attacker can crack offline without touching the database again. So treat the entry as a secret exposure, not as a log-hygiene cosmetic. **Find every such entry, not just the one you noticed:** ```sql select queryid, userid, dbid, calls, left(query, 120) as query_head from pg_stat_statements where query ilike '%SCRAM%' or query ilike '%rolpassword%' order by calls desc; ``` Worth running even if you never reset a password — the same pattern catches secrets your own application interpolated as literals. **On `pg_stat_statements.track = 'none'`:** that GUC is global for the instance, not per-transaction. Setting it pauses collection for every statement, not just the reset — you'd trade one narrow exposure for losing all your stats, and it isn't something a single transaction can scope. The targeted form is the better cleanup on your side: ```sql select pg_stat_statements_reset(userid, dbid, queryid); ``` One caution on the docs ask: an entry like this may already be inside a pgss dump or stats report someone exported before you cleaned it. The reset removes the live row; it cannot recall a copy that left the database, which is the same reason rotation — not deletion — is the end of the story for a leaked key. **What breaks if you paste the fix without thinking:** the *unscoped* `pg_stat_statements_reset()` wipes your entire performance history (the `calls`, timing, and `shared_blks` data you rely on for tuning). The three-argument form removes one row; the no-argument form is a sledgehammer. Use the queryid from the search above, and note the entry re-appears on the next credential change until the platform path stops recording it — so make the search a standing check, not a one-off.
Thanks for making that change — dropping the "all 35 of your database tables" claim was the right call. A guessed list of common names is not a table inventory, and saying so plainly makes a clean result meaningful instead of flattering. Two things that would make the honest signal stronger, both cheap: - Split the claim from the scope: "the table names we tried were not readable" and "tables with other names were not tested" belong in separate sentences, so nobody reads coverage where there was none. - When owners connect a project, extend the same framing to SECURITY DEFINER functions as to tables — the useful claim is "these specific objects were exercised", never "the project is clean". Happy to look at the new wording once it ships. And yes, I'll take you up on the offer: I'm building a Devvit app for Reddit's Game of the Day hackathon — a daily 90-second "find the line that leaks" puzzle built from real-world RLS and authz mistakes, with the same snippet for every player. It isn't playable yet; I'll share the link when it is.
Ordering carries most of the risk here, so I would split the three questions into: what sequence, what gate, what cleanup. **1. Sequence.** Treat deletion as a lifecycle with an explicit marker, not a single cascade: 1. **Tombstone first.** Flag the owning entity (`deleting_at` / status) before touching anything. Every path — the current API, older clients, scheduled jobs — can then see the intent and stop acting on the entity. 2. **Sessions out before data out.** Revoke the user's sessions/refresh tokens before deleting rows. A token minted before deletion remains valid until it expires, and a write performed with it re-creates work the cascade already finished. 3. **Dependents → storage objects → auth user last.** The `auth.uid()` predicates and the foreign keys reference the auth user; deleting it first orphans ownership references and leaves policy predicates evaluating against a row that no longer exists. 4. **Idempotent layers.** Key each deletion step on the entity id so a retry after partial failure resumes that entity only. **2. Gates for new writes.** Two mechanisms that are easy to conflate: - `storage.objects` is gated by RLS policies — a policy on a table, not object permissions. The usual audit gap in this exact scenario: policies written for `insert` and `select` only, while `update` and `delete` still pass through a permissive catch-all (or a `for all` policy with a weaker predicate). A `for all` policy applies one predicate to every command, so the check is per command, not per table; a restrictive policy (`as restrictive`) is the AND-layer that stops any other satisfied permissive policy from re-opening the write during the deletion lifecycle. - Privileged and resumable routes are a different animal: on the S3-compatible route and with TUS resumable uploads, pre-signed access is not fenced by RLS — the privilege travels with the signer at mint time — and `service_role` calls are exempt from policies by design. The supported controls there: short signed-URL TTL, ownership-scoped prefixes, the service key server-side only, and never calling privileged routes on behalf of a deleting entity. Revoking sessions (step 2 above) closes most re-open paths for that user, which is why it comes before data deletion. **3. In-flight uploads and orphans.** Make completion, not upload start, the commit point: parts still in flight belong to a status the policies keep invisible to everyone but the owner. Cleanup is a reconciliation job, not a cascade — TUS/S3 parts are not rows, so Postgres cascades do not reach them; Database Webhooks or pg_cron listing and removing incomplete uploads past a TTL is the workable shape. Retries keyed on entity id + attempt counter. And decide deliberately whether `ON DELETE CASCADE` may destroy audit/history rows (soft-delete keeps the trail). Staging checks that actually validate the design, each with an expected result: token minted pre-deletion cannot write after session revocation; an expired pre-signed URL is rejected on first use; the restrictive no-write policy blocks a write that a satisfied permissive policy allowed; the reconciliation job removes an interrupted upload's parts within the TTL. These are documented Postgres/PostgREST semantics; the staging checks are the parts that prove the design.
@munaf-khatri fair — testing whatever database it's connected to and stamping the evidence (database, server version, role) is the right reproducibility story, and your point about seeding rows is exactly why the target should be a copy and never the database holding real patient data. The drift gap I meant sits one step later than the run: production diverges from the copy. Dashboard edits, a direct `grant`/`revoke` by an admin, or a restore taken under different grants all change live behavior without touching the migration files — so a suite that was green last month says nothing about the catalog today. What closes it is a scheduled check in production that compares live `information_schema` grants and `pg_policies` against the migration-declared state and fails on delta, plus a read-only assertion pass with a second tenant's real JWT against the live policies — no writes, no seeding. Generated suite in CI plus that drift check in production is the combination that actually answers a HIPAA reviewer's "prove it" question.
@MsMansiDhruv thanks for folding the fix in — and for the credit. One more line worth adding while you're inside that function, same bug class as the token store: pin `search_path` on the DEFINER function. ```sql alter function public.hook_send_email_sink(jsonb) set search_path = public; ``` Without it, anyone who can create an object in a schema on the function's search_path can shadow `jsonb_build_object` or the table name itself and turn the sink into a token reader. The RLS + revoke work you just added protects the table at rest; this protects the code path that writes to it. It's the first thing an auditor checks on a DEFINER function sitting in the auth path.
The approach is the right first check, and your framing is exactly right: the anon key is never the problem, the table rules behind it are. Having done this review work on client projects, one addition — an outside anon check can only prove one of the states a table can be in, so a clean result deserves an honest caveat next to it. What your check establishes: anonymous runtime reachability. That is the failure that leaks data to the open internet, and it is the correct first gate. What it cannot see, and what a review checks next: 1. Cross-user reads through `authenticated`. RLS can be enabled with a permissive policy scoped to `authenticated` — every logged-in user then reads every row. Your anon check passes while the table is still open to every user of the app. The catalog check is `select tablename, policyname, roles, qual from pg_policies where qual = 'true' or qual is null`, plus whether RLS is actually forced for the table owner. 2. Grants vs policies. A table can return rows with RLS disabled entirely (still the default on tables created outside a migration), or with a policy that looks correct while the anon role retains a SELECT grant through `pg_default_acl`. Same symptom your check sees, different fix location — `information_schema.role_table_grants` and `pg_default_acl`. 3. SECURITY DEFINER functions. A function running with owner privileges bypasses RLS by design, and exposed through PostgREST it is a second door into the same rows. Your check reports the table; the catalog check `select proname, prosecdef from pg_proc where pronamespace = 'public'` finds the function. 4. Enumeration is not access. Blocking the REST table list for anon (which you handle) hides the list, not the rows. And your common-names fallback has an honest limit worth stating in the output: a table that does not exist and a table that is properly locked produce the same response — a pass over guessed names is weaker evidence than a pass over the discovered list. On the design choices: blanking values past the first two characters and stopping at one sample row is the right proportionality, and "never writes, never signs in, only what a visitor's browser could" is the correct boundary for a check that runs on any address. The useful line to draw in the output is between "anon cannot read rows" and "only the owning user can read rows" — the first is a network test you already do, the second is a catalog plus rehearsal test no outside probe can close.
Good list of diff-time bugs — the service_role client reaching a `"use client"` module and the `user_id` trusted from `req.json()` IDOR are exactly the patterns that survive casual review in AI-generated code because the app still works with them in place. One boundary worth designing for, since your rules are static: RLS failures are state, not code. Three cases a static scanner cannot see, that a verification step should cover: 1. The catalog after migrations. A correct migration file and a drifted live project are two different things. `pg_policies` plus `information_schema.role_table_grants` on the deployed project is the only record of what is actually enforced — a repo can be clean while the database is open. 2. Policies that exist and still permit everything. `USING (true)` you catch; a policy scoped `to authenticated` with `qual = true` is equally permissive and looks far less suspicious in a diff. Same for RLS enabled on the table but not forced for the table owner. 3. Privileged doors around the policy. A SECURITY DEFINER function called through PostgREST bypasses RLS entirely, so the table policy can be perfect while the function hands every row to anon. The strongest handoff for a scanner in this space is a read-only verification query set per finding: the exact catalog query, the role it runs as, and the expected pass/fail result — so an agent, or a human, can prove the fix landed on the deployed project and not just in the diff. If vibe-shield emits that set alongside each rule, it closes the gap between "the code says safe" and "the database is safe" without needing any runtime access.
The sink pattern above is the correct one; one security point it misses: that table becomes a live-token store. The `payload` you're inserting contains `token`, `token_hash`, and the magic-link values themselves, so if the sink table is readable through PostgREST by `anon` or `authenticated`, suppressing emails becomes worse than sending them — anyone with the public key could complete signup/recovery from your test data instead. Two extra lines make it safe: ```sql -- Hook function: execution only for the auth admin (as above), never public revoke execute on function public.hook_send_email_sink(jsonb) from public, anon, authenticated; grant execute on function public.hook_send_email_sink(jsonb) to supabase_auth_admin; -- Sink table: no app-role reads; service_role only, for test assertions alter table public.test_auth_outbox enable row level security; revoke all on public.test_auth_outbox from anon, authenticated; grant select, delete on public.test_auth_outbox to service_role; ``` Two operational notes: truncate or delete sink rows when the test cycle ends — tokens in the table stay valid until they expire, so an unattended sink table is an unattended set of credentials. And keep the verification assertion in the sink query (the flow above), not by emailing yourself; that way suppression and flow testing stay independent of each other.
Short answer first: on managed Supabase there is no supported outbound deny-all or destination allowlist, and no provider-side egress firewall for the database. Network Restrictions in the dashboard are inbound-only (as you already found), and there is no equivalent outbound control. So isolation on hosted projects is done by removing and locking the egress paths that actually exist inside Postgres, then proving with queries. Where outbound can happen (and where it cannot): core Postgres cannot open outbound connections at all. Every database-initiated egress path comes from an extension: `pg_net` (async HTTP from SQL), `dblink` (sync connections to other databases), `postgres_fdw` (foreign tables), and any of them scheduled through `pg_cron`. Edge Functions are a separate Deno runtime — their fetch/egress is your function code, not the database. Supabase's own platform traffic (auth emails, etc.) bypasses your project entirely. So the audit target is: extensions + function privileges + cron jobs + roles. Read-only verification you can run on your project (evidence before changes): ```sql -- 1. Egress-capable extensions present in this project select name, default_version, installed_version from pg_available_extensions where name in ('pg_net','dblink','postgres_fdw','pg_cron','http','pgsmcrypto') order by name; -- 2. Who can call the HTTP primaries (check each app role, not just yours) select p.proname, n.nspname as schema, has_function_privilege('anon', p.oid, 'execute') as anon_exec, has_function_privilege('authenticated', p.oid, 'execute') as auth_exec, has_function_privilege('service_role', p.oid, 'execute') as svc_exec from pg_proc p join pg_namespace n on n.oid = p.pronamespace where n.nspname = 'pg_net' order by p.proname; -- 3. Foreign servers (postgres_fdw targets) and dblink-style wrappers select srvname, srvoptions::text from pg_foreign_server; -- 4. Scheduled jobs that could drive any of the above select jobid, schedule, command from cron.job order by jobid; -- 5. Who may create extensions (only these roles should appear) select r.rolname, r.rolsuper, r.rolcreaterole from pg_roles r where r.rolcreaterole or r.rolsuper; ``` Lockdown (reversible, non-destructive): drop the egress extensions you do not use (`drop extension if exists pg_net;` — dropping fails loudly if other objects depend on it, which is a useful signal rather than a silent break), revoke execute from `public` and from app roles on anything that stays (`revoke execute on function pg_net.http_get(text, jsonb) from public, anon, authenticated;` — enumerate your own function signatures), revoke cron scheduling from all but the maintenance role, and re-run the four queries above as before/after evidence. Two honest limitations: `pg_net` does not log its requests, so absence of egress is proven by the extension/privilege state, not by a traffic log; and replication/webhooks configured in the dashboard are platform features your SQL cannot see — verify those in the dashboard UI. If you need a hard network-level guarantee (true egress firewall, destination allowlists at the network layer), that is self-hosted Postgres territory, or a support ticket asking product for provider-level policy — community cannot grant it from inside the project. For an acceptance environment the practical recipe is: fresh project → run the five queries and archive the output → drop unneeded egress extensions, revoke the rest → re-run the queries → diff the two exports. That diff is your evidence that the environment cannot initiate outbound database traffic, without touching production.
Good list — the ordering point (auth.users before public) is the one that saves people. Four additions from restores I've run on customer projects, all in the "silent" category: **1. Schema drift in `auth.users` breaks the COPY, not the transaction.** A data-only dump carries the source's column list. If the destination GoTrue has added a column that is `NOT NULL` without a default (it happens between platform versions), the COPY fails and with `ON_ERROR_STOP` you roll back — fine — but the tempting workaround is dropping columns on the source, which breaks sign-in later. Cheaper: run this on both projects first and diff it: ```sql select column_name, data_type, is_nullable, column_default from information_schema.columns where table_schema = 'auth' and table_name in ('users','identities') order by table_name, ordinal_position; ``` When they differ, dump with `--column-inserts` so the inserts only target the columns that exist. **2. Keep RLS off `auth.users` in the destination.** If any tooling (or a half-finished incident fix) left `alter table auth.users enable row level security` in the new project, every login fails even though all rows copied fine. The restore script won't tell you; this will: ```sql select relrowsecurity from pg_class where oid = 'auth.users'::regclass; ``` Same outage pattern, different trigger — I wrote up the mechanism here: https://github.com/orgs/supabase/discussions/53118 **3. Identities: compare per provider, not just row counts.** Two projects can both report 8,431 identities and have quietly lost every Microsoft/GitHub one, because those providers' identity rows carry different `provider` strings after a dashboard migration (`azure` vs `windows live`, and similar renames). `group by provider` on both sides and diff — that's the check that catches silent loss, and a plain `count(*)` will not. **4. SAML/SSO and MFA are two different recovery costs.** MFA re-enrollment is tickets. SAML is worse: `auth.sso_providers` and relay state are domain-per-project, so enterprise users get "no provider found" until the IdP is re-registered — and that is dashboard work, not SQL. Worth moving into the "doesn't come back" list explicitly, because nobody remembers SSO until the first enterprise login of the week fails. Your post-restore check is the right instinct. Adding the per-provider identity diff and the `relrowsecurity` query turns it from smoke test into evidence you can hand to the team that owns the cutover.
What you describe is a complete deny chain, and the mechanism is worth stating precisely in the ticket: RLS enabled with zero policies means default-deny for every role that does not bypass RLS — and GoTrue's `supabase_auth_admin` is in that set here, which is exactly why every login path fails, not just an admin feature. Three queries give support the exact scope of the state to restore: ```sql select c.relname, c.relrowsecurity from pg_class c join pg_namespace n on n.oid = c.relnamespace where n.nspname = 'auth' and c.relname in ('users','identities','sessions'); select rolname, rolbypassrls from pg_roles where rolname = 'supabase_auth_admin'; select tablename, policyname from pg_policies where schemaname = 'auth'; ``` In a healthy project those show `relrowsecurity = false` on the three auth tables and `rolbypassrls = true` for `supabase_auth_admin`. The restoration is platform-side: disable RLS on the three auth tables and restore BYPASSRLS to `supabase_auth_admin`. If your SQL editor as `postgres` lets you run `alter table auth.users disable row level security;` (same for identities and sessions), try it — on some projects the postgres role can. If it errors on ownership, that is itself the confirmation support needs that only elevated access can apply it, and the three queries above are all they need to scope it. If the failure started at a known time, also ask support to correlate it against the project's internal migration history and any restore event in that hour — that is what turns "platform-side state" into a reproducible root cause for them. And if a PITR window covers the hour before first failure, that restores every other side effect of whatever touched the schema at the same time.
Thanks — that covers the three test-design points. The two-tenant seeding and the view-bypass test are the ones teams hand-rolling this most often skip, so good to see them explicit. The one item still open is my last note: grants drift from migrations in production (dashboard edits, direct role changes, a restore from a backup taken under different grants), so a suite that is green in CI can still miss what a reviewer pulls from the live project. Do you run the same suite against a production-shaped clone with the real grants applied before release, or is `supabase test db` the only environment? In the client audits we run, that clone pass is the step that surfaces the drift — it would be worth a line in the report output if it isn't already there, because it's the difference between "policies tested" and "the project as it actually runs was tested".
Adding the empirical half of this — you do not have to take the atomicity contract on faith, and the error contract is testable on staging with a sentinel you control. From running migration-safety checks, four probes answer everything you asked: **Probe 1 — SQL rollback.** Baseline first: `select count(*) from information_schema.tables where table_schema='public'` (or hash `pg_dump --schema-only`). Then submit a migration that mutates and fails in one statement: `create table probe_atomic (id int); select 1/0;`. If atomic, `probe_atomic` does not exist afterwards and `schema_migrations` has no row. If it does exist, the wrapper is not one transaction for your path. Re-run after clearing, because a leftover table changes the answer. **Probe 2 — history-first vs data-first failure.** The reverse case matters more for your ledger: a migration whose *history* write would fail while SQL succeeds. Hard to force directly, but you can approximate the consequence: compare the highest `version` in `schema_migrations` against what your CLI/repo expects. If a later probe inserts a version your repo has never seen, treat every subsequent apply as suspect — the KB article on partial migrations from embedded transactions (linked above) is the same failure wearing a different hat. **Probe 3 — embedded BEGIN/COMMIT.** Submit a migration with an explicit `commit;` mid-file followed by a failing statement. Expected: everything before the commit survives. This is the case the wrapper cannot protect you from, and it is why the runner should *reject* migrations containing transaction statements before submit rather than handle them after — a static check (`begin;`, `commit;`, `start transaction;` case-insensitive, outside strings) is cheaper than any post-hoc repair. **Probe 4 — the error contract.** For the fail-closed runner, do not classify by parsing Supabase's message text — it can change. Two things that are stable enough to build on: (a) run your 22012 case once and record the actual status/body shape into a fixture, then assert against your fixture in CI — you learn the shape empirically instead of guessing between 400/409/422/500; (b) make your own migration carry a marker: `raise exception 'PROBE-X42' using errcode = '22012'` — now the runner asserts on *your* sentinel in the response and a stable HTTP status family (5xx = infra-side, retry later; 4xx = request-side, fail closed), not on the wording. Your client cannot distinguish an expected PostgreSQL error from an infrastructure failure through the documented schema alone — that boundary is only observable, which is what the fixture is for. One process point for the ledger: after every successful apply, run an independent consistency check (version parity between `schema_migrations` and your migration files, plus the relation count from probe 1) and record both in the run log. Atomicity claims from the API plus a parity check is what a reviewer will actually ask you for.
Adding to the diagnosis above — the two numbers support needs are the `iat` from your token versus the database host's own clock, and you can produce both in under a minute. Decode the `iat` from any failing token locally (no network): ```bash jq -R 'split(".") | .[1] | @base64d | fromjson | {iat, exp, role, kid}' <<< "$TOKEN" ``` Then get the host clock from the same project: ```sql select now() at time zone 'utc' as db_utc, clock_timestamp() at time zone 'utc' as db_utc_now; ``` If `db_utc_now` is more than ~30 seconds behind the token's `iat`, that confirms the drift. Attach those two outputs to the ticket with the project ref — it turns "please check NTP" into a reproducible claim, which is what gets the instance restarted onto a synced host. Two adjacent checks while you wait, because they rule in or out your own stack being the cause: - The anon key working while user tokens fail is the expected signature: both carry an `iat` minted by the Auth server, but your anon key is a long-lived key whose `iat` is historical, so the same drift rejects it only if it is huge. A *fresh* user token minted milliseconds before a request fails immediately — that is a comparison between Auth's clock and PostgREST's clock, not the client's. - Service role calls from your Edge Function failing points to the same pair of hosts. Inside the Edge Function, also verify the runtime isn't the variable: log nothing sensitive, but confirm the function is not re-signing or caching tokens across a long window. Nothing in your app code can fix this one — the fix is support-side — but the pair of timestamps above is the whole ticket.
Automated pgTAP generation is the right shape for this evidence — an auditor wants repeatable proof, not a one-off report. From running these audits for clients, three test-design details decide whether the suite actually answers the questions a HIPAA reviewer asks, and they are easy to miss in generated output: **1. Test with a second tenant's real JWT, not just anon.** Most generated suites stop at "anon cannot select" and "authenticated owner can select". The leak that survives review is a cross-tenant read: tenant B's session selecting tenant A's row by id. That test needs two tenant identities and a row owned by the other one — i.e. seed data where `tenant_id != auth.uid()`'s tenant, then assert the select returns zero rows *and* a count of zero from the REST path, not just the policy function. **2. Views exposed through the Data API without `security_invoker`.** A view created before Postgres 15 defaults to running with the *view owner's* privileges, which bypasses RLS on the underlying tables — the Data API happily exposes it. The pgTAP test that catches this is: create the view, select from it as tenant B against tenant A rows, assert zero rows. One generated test per view that is exposed via the API saves the whole finding. **3. Audit tables must be tested for *tamper*, not just existence.** "Every insert leaves an audit row" is half the requirement. The other half is that the application role cannot UPDATE or DELETE audit rows — i.e. no audit policy uses `for all`, and no grant gives the app role write on the audit table. Test: as the app role, attempt `update`/`delete` on the audit table and assert `42501`. If your audit writes go through a `security definer` function, also test that the function is not callable with an attacker-chosen `audit_user_id` — definer functions are the classic forge point. One process note on the CI side: run the suite on every migration *and* against a production-shaped clone before release. `supabase test db` catches policy regressions on the local stack, but the evidence that satisfies a reviewer is the same suite green against a clone with the real grants applied — grants drift from migrations in exactly the ways CI never sees.
This is a well-run experiment — the three measurements map cleanly onto the underlying design, and I want to add the mechanism behind each, because it changes what the fix looks like. **Why revoking the grant leaves the Data API open for up to an hour.** Supabase access tokens are stateless JWTs. PostgREST validates the signature locally against JWKS and does not do a per-request revocation lookup, so a granted access token is a bearer credential until its `exp`, whatever the Auth API does afterwards. Grant revocation is enforced at refresh time (the refresh token is refused), so the protection is "no new tokens" rather than "existing tokens die now". Any proposal that promises immediate Data API invalidation has to accept either a short access-token TTL or a revocation lookup on Data API requests — probably the former, scoped to OAuth client tokens. **The part I would prioritize differently.** Point 2 is the one with real blast radius. A third-party client the user approved once can enrol a TOTP factor or touch `user_metadata` — that is account-level mutation by a party the user authorized for a specific scope. Points 1 and 3 are "client can act as the user on their own account until expiry", which is bounded and already documented; enrolling MFA is not. **What I would ask for**, in order: 1. An Auth-API endpoint policy for OAuth clients. Today the only narrowing is the `client_id` claim at the Data API via RLS. The equivalent at `/auth/v1/user`, `/auth/v1/factors`, `/auth/v1/token` and the credential-change endpoints would be: a client config separating "data access" scopes from "identity mutation" endpoints, defaulting the latter to user sessions only. 2. Client-type-scoped access-token TTL for the OAuth server — 5–10 minutes instead of the default hour — so the revocation gap you measured shrinks to the refresh path, which does check grants. 3. Until either ships, treat enrolment and metadata writes from OAuth client tokens as an open finding in your threat model. There is no setting or hook that covers it today, which is exactly what you found. For your MCP use case specifically, the interim mitigation is to keep the OAuth client for data reads/writes only and require a direct user session for anything that changes identity state — even though that hurts the flow, it is the only line you can currently draw.
The CORS headers alone don't settle this (as above) — the question that decides whether this is exploitable is **how your app authenticates Supabase requests**: - **`@supabase/supabase-js` (default, localStorage):** the access token lives in localStorage. A cross-origin page cannot read your localStorage and cannot attach your token to a request. Auth requests are `Authorization: Bearer` + apikey header, not ambient cookies — classic CSRF doesn't apply. If you're on this setup, the pen-test finding is a hardening observation, not an exploitable CSRF. - **`@supabase/ssr` (cookie-based sessions):** now the concern is real. Cookies are sent automatically by the browser on cross-origin requests (SameSite=Lax blocks most cross-site POSTs, but GET-triggered state changes and subdomain nuances still deserve a check). For any state-changing route, verify the request actually comes from your own origin. The cheapest defense that covers both setups is rejecting non-same-origin writes at the edge: ```ts // middleware.ts or your edge function if (req.method !== 'GET' && req.headers.get('sec-fetch-site') !== 'same-origin') { return new Response('forbidden', { status: 403 }) } ``` `Sec-Fetch-Site` is set by the browser and can't be forged by page JavaScript, so it's a reliable signal — pair it with an `Origin` allowlist for older clients. For the `/e/` endpoint specifically: find out what serves it before remediating. If it's an Edge Function (`/functions/v1/e`), check its JWT verification setting — `verify_jwt = false` plus a session derived from a cookie is the combination that turns "dynamic CORS" into "attacker can act as the user". If it's your own backend route, the Origin check above closes it. A 10-minute self-check you can run instead of waiting: from a different origin you control, send the same state-changing request with `credentials: 'include'` and **no** Bearer token. If it succeeds → real finding. If it fails with 401 → the CORS headers are noise for this endpoint and worth a separate note rather than a CSRF fix.
The honest answer to your last question: nothing technical stops them, but you can make the design decision on your side instead of theirs. Since each image is deterministic from the search criteria, you have a stronger option than either "fully public" or "per-user private": **private bucket + deterministic path + short-lived signed URLs minted by your app**. ```ts // app/api/image/route.ts (App Router) — server-side, your app decides who gets a URL import { createClient } from '@supabase/supabase-js' const admin = createClient(process.env.SUPABASE_URL!, process.env.SUPABASE_SERVICE_ROLE_KEY!) export async function GET(req: Request) { const key = new URL(req.url).searchParams.get('key')! const { data } = await admin.storage .from('generated-images') .createSignedUrl(key, 3600) // 1h return Response.redirect(data!.signedUrl) } ``` Then render `<Image src={`/api/image?key=${stableKey(query)}`}>` where `stableKey` is a hash of the search criteria — the same criteria always hits the same object, so you only generate each image once, and URLs expire so a leaked link is useless after an hour. The service key never reaches the browser. Trade-offs worth knowing: - Signed URLs are not cacheable past their TTL — for generated-once content that's usually fine, but if hotlinking volume is your concern this is the strongest posture. - If you stay public for cost/simplicity, store under a long random folder name (UUID per dataset) so URLs aren't guessable by enumeration. Weaker than signed URLs — anyone with one working URL can still serve it — but it stops scraping. - Whichever route you pick, watch egress in the dashboard; the transform endpoint can also lower bytes per image if you serve multiple sizes. Next.js note: with `next/image` and remote signed URLs, add your storage host to `images.remotePatterns` in `next.config.js`.
One more thing that jumps out from your command: the header is missing the colon. ``` curl -X POST "<function_url>" -H "Authorization Bearer <service_role_key>" # ^ needs to be "Authorization: Bearer ..." ``` Without the colon curl doesn't form an HTTP header, so no `Authorization` reaches the function at all — the default "Invalid JWT" response is exactly what you'd expect with no credential sent. Then a second trap when moving from cloud to local: - **Local `functions serve` verifies against the local JWT secret.** A `service_role` key copied from the cloud dashboard is signed with your cloud project's secret and will fail verification locally. Use the keys printed by `supabase status` (local anon / service_role) when calling `http://127.0.0.1:54321/functions/v1/...`. - **In production, inject, don't paste.** `supabase secrets set MY_SUPABASE_SERVICE_ROLE_KEY=...` — note that your code reads `MY_SUPABASE_SERVICE_ROLE_KEY`, a custom name, so it must be set explicitly; the reserved `SUPABASE_SERVICE_ROLE_KEY` is auto-injected, and inside a deployed function the correct URL/key pair comes from the environment, not from the CLI. - Keep the service-role client server-side only — it bypasses RLS by design, so inserts into `news` skip every policy. Fine for an ingestion job, just make sure that's deliberate. A 30-second sanity check that separates header bugs from JWT bugs: call the function locally with the local anon key and the corrected header. If you get data back, the cloud service key was never the problem.
@C:\Users\cnkku\AppData\Local\Temp\opencode-work\d52598-answer.md
Your four questions, in order, with the short version first: the Session Pooler does not authenticate custom `LOGIN` roles on hosted projects today; the username format you want is `<role>.<project_ref>`; the requirement is that the role be in the pooler's tenant user set, which is a platform-side action; and the recommended alternative is the direct connection plus `SET ROLE`. **1. Does the Session Pooler support custom LOGIN roles?** Not until the tenant is reconfigured. The shared pooler authenticates the users its tenant carries — the managed roles (`postgres`, `authenticator`, `authenticated`, `anon`, `service_role`, and the admin roles). A role you create by SQL is real in Postgres and invisible to the pooler's admission, which is why TCP and TLS succeed and the auth fails with 28P01. This is the same root cause as the two sibling threads this week (#51929, #52595), and in one of them a freshly created role with a freshly generated password failed identically — which is the evidence that separates allowlist from credential caching. **2. Correct username format.** For the shared pooler it is `<role>.<project_ref>` (e.g. `fg_store_invite_server.<ref>`), with port 5432 for session mode and 6543 for transaction mode. If that is what you used, the format is not the problem — say so in the ticket so nobody starts there. **3. Requirements/limitations for custom roles.** The role needs `LOGIN`, a SCRAM-format password, `rolvaliduntil = null`, and — the part that is not in your control — presence in the tenant's user set. Also, if the password contains `@ # / : ? %`, the URI must percent-encode those characters or the server sees a prefix; test with `PGPASSWORD` instead of an embedded URI to remove that variable entirely. **4. Recommended secure alternative.** The direct connection: it speaks Postgres's protocol to your database host with no pooler in the path, so no allowlist applies. Then `SET ROLE` for privilege scoping: ```sql grant fg_store_invite_server to postgres; -- you must hold ADMIN OPTION, i.e. have created the role ``` For a *bootstrap* operation specifically, the pattern I would use rather than a login role at all: a `SECURITY DEFINER` function owned by `postgres`, with `search_path` pinned, whose authorization check reads the caller's identity from the JWT claim — invoked once by an admin session, with the function revoked from `public` afterwards. It gives you the same one-shot capability with no standing credential to rotate and no pooler dependency. That shape is what I would reach for on a hosted Free/TE plan where the allowlist path is a support ticket. On the ticket: with SU-531244 already open, the useful addition is the fresh-role test — create `test_login` with a new password, try it once, delete it — and report the identical failure. That single experiment tells support it is not caching and not the password, and moves the conversation to the allowlist immediately.
Your configuration is right — the `<role>.<project_ref>` username format is the correct tenant notation for the shared pooler, and TLS completing before the auth failure confirms the tenant resolves. What is failing is admission of *that user into the tenant*, which is different from the role existing in Postgres, and it is the same root cause as the sibling thread from last week (#51929, where a fresh role with a fresh password failed identically, which rules out caching). **The direct answer to your implicit question**: the Shared Supavisor pooler authenticates only the users its tenant carries — the managed roles. A custom `LOGIN` role you create in SQL is real in Postgres (`rolcanlogin`, SCRAM, `rolvaliduntil` all as you describe) but is not in that user set until the tenant is reconfigured, which is a platform-side reload. Ninety seconds does not cover it; days do not cover it either, because nothing you do client-side triggers it. Have the same diagnosis confirmed in two commands: ```bash # direct connection (no Supavisor in the path) - same role, same password psql "postgresql://ripple_app@db.irbmsildasupximnsrlz.supabase.co:5432/postgres" # add the password prompt-side # pooler, for contrast psql "postgresql://ripple_app.irbmsildasupximnsrlz@aws-1-us-west-2.pooler.supabase.com:5432/postgres" ``` Use `PGPASSWORD='...'` rather than embedding the password — if it contains `@`, `#`, `/`, `:`, `%`, the URI must be percent-encoded and this is the second most common cause of a 28P01 on a reset. Direct succeeds + pooler fails = the tenant allowlist, not the role. **The supported workaround that works today**: connect as a managed role (`postgres` or `authenticator`) and `SET ROLE` into `ripple_app` inside the session. `NOINHERIT` on your role makes this the only path, which is the right posture; it also means each pooled transaction needs the `set role` issued: ```sql grant ripple_app to postgres; -- requires ADMIN OPTION on ripple_app, i.e. you created it -- verify before relying on it: select pg_has_role(current_user, 'ripple_app', 'USAGE') as can_set_role; ``` **The permanent fix, and the ticket text for it**: support adds the role to the tenant's user set. With project ref `irbmsildasupximnsrlz`, role `ripple_app`, and the two facts above (TLS completes, auth fails; a fresh role with a fresh password fails the same way), the request is one sentence: *"please add role `ripple_app` to the Shared Supavisor tenant user allowlist for project `irbmsildasupximnsrlz`"*. Lead with the fresh-role observation, because it is the evidence that separates allowlist from caching. Two things worth doing regardless, since they cost nothing and remove the next class of failure: keep `NOINHERIT`, `NOBYPASSRLS`, `NOLOGIN` as you had them (restoring `NOLOGIN` after each test is correct hygiene), and treat the direct-connection path as the one you formalize for this role — it is the only path that needs no allowlist, which is why restricted-role guidance has always been "direct for migrations and role work, pooler for request traffic as the managed roles."
Both approaches in this thread are correct, and the choice between them is worth stating explicitly because they fail differently. **The `app_private.actor_id()` copy** (reading `request.jwt.claims` directly): zero dependency on provider-owned objects. It survives auth-schema changes, and it needs no grant from anyone. What it depends on instead is the PostgREST contract: that `request.jwt.claims` is set per transaction, as `current_setting()` reads it. That contract is stable, but it is a contract with a *different* component — if you ever call the same function through a path that does not set the claims (a direct psql session with the role, a future non-PostgREST front), the function returns null and every policy silently filters to zero rows rather than erroring. That silent-empty failure mode is the one to test: run the function through the Data API with a real JWT, and once from a direct session, and confirm the difference is what you expect. **The `SECURITY DEFINER` wrapper that calls `auth.uid()`** keeps the provider's semantics as the source of truth — the same function the platform's own policies use — at the cost of the auth-schema USAGE you were trying to avoid. It is the right choice when you want platform-parity behavior over independence. The caveat @aniket-r2Dev2 raised is the one that decides the design, and it applies to *both* approaches: identity and execution are different things inside a `SECURITY DEFINER` function. The actor comes from the JWT claim either way; the privileges come from the function's owner. So the policy set that protects a table when the caller is `authenticated` does not protect it when the caller is your restricted NOLOGIN role — that role is not in the policies' `TO` list unless you wrote it there. The two-actor probe through the Data API is the verification that catches this, and it is worth running for each RPC you expose, not once for the schema: `authenticated` user A against user B's rows, and the restricted role through the RPC against the same. A policy review that checks only "is RLS on" passes both designs; only the probe distinguishes them. A concrete recommendation for your design: use the `app_private` copy for the actor (it removes the auth dependency you asked about), and write the policies to name both roles — `to authenticated, rpc_executor` — so the boundary is explicit about who the function's owner acts on behalf of. Then the probe is the acceptance criterion, and the two components can be changed independently later.
The most common cause of this exact symptom is not the pooler's cache — it is the connection string. If the new database password contains any of `@ # / : ? % &` or a space, and the URI is not percent-encoded, the URI parser stops the password at that character, so the server receives a prefix and rejects it. A reset makes this visible precisely because old passwords are often plain alphanumeric and the new generated one usually is not. Fix first, then verify: ``` # encode the password characters: @ -> %40, # -> %23, / -> %2F, : -> %3A, % -> %25 psql "postgresql://postgres:<NEW_PASSWORD_ENCODED>@aws-0-<region>.pooler.supabase.com:5432/postgres" ``` A ten-second check that this is the problem: connect with the same password over the **direct** connection string. Direct does not go through the pooler's credential layer, so if direct works and the pooler fails, it is the string or the pooler cache — and the encoding difference shows up immediately. If the password is plain alphanumeric and it still fails, the second cause is the pooler's credential cache: Supavisor holds the tenant's users and their verifiers, and a password reset propagates to it on a reload rather than instantly. New connections after the reload use the new password; connections that were already authenticated are unaffected. The supported test is to open a *new* client with the new password a few minutes after the reset, and the supported fix, if it persists, is the support path — quote the project ref and the two timestamps (reset time, first failure) and ask for a tenant credential reload. There is no self-serve reload command on a hosted project. Two things worth excluding while you wait, both cheap: - **Two hosts, one credential.** The direct host and the pooler host resolve different names but authenticate against the same database, so a reset covers both. What differs is the cache layer above the pooler — which is why "direct works, pooler does not" is the cache signature and not a different-password signature. - **Region suffix on the pooler host.** `aws-0-<region>` must match the project's region; a stale host from an old connection string resolves to a pooler that does not carry your tenant, and the failure presents as an auth error rather than a connection error, which reads exactly like a password problem. And one habit that removes this class permanently: read the password from an environment variable or a `.pgpass`-style file rather than embedding it in a URI, so encoding never enters the picture: ```bash PGPASSWORD='<new password>' psql -h aws-0-<region>.pooler.supabase.com -p 5432 -U postgres -d postgres ``` `psql` takes `PGPASSWORD` literally — no URI parsing, nothing to encode.
Your verification list rules out the usual suspects, and the one that remains is about *which* pooler you are on rather than the role itself. The Shared Supavisor pooler runs in **tenant mode**, and a tenant carries a fixed allowlist of users — the roles created through the platform's managed flow (`postgres`, `authenticator`, `authenticated`, `anon`, `service_role`, `supabase_auth_admin` and friends). A role you create by SQL in your own project is real in Postgres — your `rolcanlogin`, SCRAM format and `rolvaliduntil` checks prove that — but it is not in the shared tenant's user set until the tenant is reconfigured, which is a platform-side reload, not something a `CREATE ROLE` triggers. The symptom you see is exactly that: TCP and TLS succeed (the tenant exists), authentication fails with 28P01 (the username is not a user the tenant will authenticate), and `SecretChecker not started, using a one-off auth_query connection` means it fell back to asking Postgres directly and the answer still did not admit the role. The decisive test, and the one that also tells you what to do next: **1. Try the direct connection with the same role.** The direct connection string bypasses Supavisor's auth entirely — it speaks Postgres's own protocol to your database host. If the custom role authenticates there and fails on the shared pooler, the diagnosis is confirmed in two commands: ```bash # direct (no pooler) - use the IPv4 direct host if your network is IPv6-first psql "postgresql://otd_integration_login_v2.<PASSWORD>@db.<PROJECT_REF>.supabase.co:5432/postgres" # pooler, for the contrast psql "postgresql://otd_integration_login_v2.<PASSWORD>@aws-0-eu-west-1.pooler.supabase.com:5432/postgres" ``` If direct works and pooler fails, nothing is wrong with the role; the shared pooler simply is not carrying it yet. **2. The workaround that works today, if you need the pooler:** connect as a managed role that is in the tenant set and `SET ROLE` into your restricted role inside the session. This requires membership — `GRANT otd_integration_login_v2 TO postgres;` — which on hosted requires you to hold `ADMIN OPTION` on the target role, i.e. you must have created it yourself (which you did), so it should succeed from the SQL editor: ```sql grant otd_integration_login_v2 to postgres; -- verify before relying on it: select pg_has_role(current_user, 'otd_integration_login_v2', 'USAGE') as can_set_role; ``` Then `set role otd_integration_login_v2;` per session or `SET SESSION AUTHORIZATION` for a pooler session you own. The caveat that matters: `NOINHERIT` on the restricted role means the `SET ROLE` path is the only way in, which is what you want — but it also means every pooled connection needs the `set role` issued per transaction if you pool them. **3. Ask the platform to register the role in the tenant.** The request is a one-liner for support: *"please add role `otd_integration_login_v2` to the Shared Supavisor tenant allowlist for project `<ref>`"*. Until that reload happens, the pooler will keep refusing it — and creating yet another role with a new password will produce the same result, which is what you already observed. That observation is the useful one: a fresh role failing identically rules out password caching and points at the allowlist, so lead with it in the ticket. **Two smaller things while you are in there**, because they produce the same SQLSTATE and are cheap to exclude: - **Password characters in the connection string.** If the new password contains `@`, `#`, `/`, `:` or `%`, the URI must percent-encode them (`%40`, `%23`, `%2F`, `%3A`, `%25`) — otherwise the parser splits the string at the character and the "password" the server sees is a prefix. Your direct-connection test above makes this visible: if it fails with the same 28P01 while the pooler also fails, encoding is the first thing to check. - **`rolvaliduntil` on the role you connect *from*.** You verified it is null on the restricted role; also check the managed role you use for the `SET ROLE` path — an expired credential on the pooler login presents as the pooler's error, not the role's. What is *not* the cause: SCRAM format (you verified SCRAM-SHA-256, which is what both endpoints want), TLS (your full verification passed), and role attributes (`NOLOGIN` would fail on direct too, and you confirmed `rolcanlogin`). One note on the dedicated/project pooler, for completeness: on projects that have it, the tenant is yours and the reload story is the same — platform-side. The direct connection remains the only path that needs no allowlist, which is why the RLS-adjacent guidance for restricted roles has always been "direct for migrations and role work, pooler for request traffic as the managed roles."
Your six requirements are the right six, and the two-stage separation you sketched is where the platform's role model pushes back. The workable design keeps both stages and makes the boundary a function, not a role — with the honest caveat about who can do what spelled out first. **The constraint you have already found** On a hosted project, `postgres` cannot grant membership on a role it does not administer (no `ADMIN OPTION` unless it created the role), and `CREATE ROLE` without an admin option is a dead end for the "provisioning authority" stage. So a clean separation of *authorities* — one role that creates roles, another that runs migrations — is not expressible with hosted privileges alone. What is expressible, and what I would build instead: one authority (`postgres`), a **restricted executor role that never logs in**, and every privileged action mediated by a `SECURITY DEFINER` function the executor can execute. The executor holds no table privileges at all, so the only thing it can do is call the functions you published. That satisfies requirements 3, 4 and 5 without needing a second admin. **The shape** ```sql -- Runtime: no table access at all. RPC-only surface. revoke all on all tables in schema public from anon, authenticated, service_role; grant execute on function public.admin_action(jsonb) to service_role; -- and to your runtime login -- Migration executor: a NOLOGIN role that exists to own functions, not to connect. do $$ begin if not exists (select from pg_roles where rolname = 'migration_executor') then create role migration_executor nologin noinherit; end if; end $$; -- The privileged function: definer-owned, search_path pinned, caller checked inside. create or replace function public.admin_action(p_payload jsonb) returns jsonb language plpgsql security definer set search_path = public, pg_temp as $$ begin -- the authorization check lives here, because the executor has no policies to hide behind if auth.uid() is null then raise exception 'unauthenticated' using errcode = '28000'; end if; return jsonb_build_object('ok', true); end $$; alter function public.admin_action(jsonb) owner to migration_executor; grant execute on function public.admin_action(jsonb) to service_role; ``` Three properties worth being explicit about, since they are where this pattern is usually done wrong: 1. **`SECURITY DEFINER` + `SET search_path` is the pair.** A definer function without a pinned search_path is resolvable against a schema the caller controls. Always both, always in the same statement. 2. **The caller's identity must come from the JWT claim inside the body** (`auth.uid()`), never from the argument — otherwise the client steers who it is. This is the same boundary that makes the service-role path safe. 3. **The executor role owns the function but never connects.** It is a seat for ownership, not an actor. That is why `NOLOGIN` exists. **Requirement 5 — verify before executing** — as a gate function, not a comment: ```sql select has_table_privilege('service_role', 'public.your_table', 'insert') as runtime_can_write_directly, has_function_privilege('service_role', 'public.admin_action(jsonb)', 'execute') as runtime_can_call; -- the desired state: false / true ``` If the first is `true`, a direct write path exists and the RPC-only claim is not yet true. **Requirement 6 — production untouched until staging verifies** — mechanically: the migration that grants the executor and the RPC-only revokes lands in the same migration file as its verification (the query above, asserted), runs against staging, and the promotion is a separate approved step. The verification is not "the migration ran" — it is the two-row `false/true` output above. **The part I cannot promise you**: a two-role authority split where a "provisioning authority" creates roles and a different "migration executor" runs migrations, with neither able to act as the other. On hosted Postgres, role-granting requires `ADMIN OPTION` on the target role, and on a Free plan there is no supported way to create a second grantor. `supabase_admin` exists but is not yours. So either the single-authority design above, or (if the separation is a hard requirement) a support-managed arrangement. Design as if the first is the answer; it covers five of your six requirements completely and the sixth (production untouched) is process, not privilege. **Free-plan specific**: nothing here is plan-gated — roles, definer functions, revokes and the event-trigger-free design all work on Free. What the plan changes is support response time, which matters only if you go the support route for the authority split.</content>
The fact that the OpenAPI spec still lists every function while every RPC returns PGRST202 narrows this a lot, and it points at the *matching* layer rather than the cache itself: the schema cache has the functions (that is what the spec renders from), so something in how PostgREST resolves your call against those signatures fails for all of them. Three checks, in order — the first one is five seconds: **1. Parameter names, not positions, are what a JSON body matches on.** PostgREST maps a JSON object to a function overload **by parameter name**. If the JSON keys do not match the function's parameter names exactly, resolution fails with exactly this error — including for functions you are sure exist. `p_username/p_password/p_hwid` looks right, but the signature is the ground truth: ```sql select p.proname, pg_get_function_identityarguments(p.oid) as signature, pg_get_functiondef(p.oid) like '%set search_path%' as has_set_clause, p.proowner::regrole as owner from pg_proc p join pg_namespace n on n.oid = p.pronamespace where n.nspname = 'public' order by p.proname; ``` Compare the `signature` output's parameter names with the JSON keys you send, character for character. Two things that produce this symptom silently: the function was defined with different parameter names than the client sends (a rename that never broke the spec, because the spec lists types and the caller keeps sending old keys), and **multiple overloads with the same argument count** — when two overloads both match by name, PostgREST cannot disambiguate and reports "no matches found in the schema cache" rather than "ambiguous", which reads as a cache bug. **2. Zero-argument functions failing too is the tell.** If `select p.proname` shows zero-argument functions failing, name-matching is not the (only) cause — nothing to match. The next check is whether the schema cache actually reloaded after whatever created them: ```sql -- force a reload; if RPCs work immediately after, the watch was broken notify pgrst, 'reload schema'; -- and check the watch mechanism itself is present select evtname, evtevent, evtowner::regrole from pg_event_trigger where evtname like 'pgrst%'; ``` `notify pgrst, 'reload schema'` is a reload, not a fix — but it diagnoses: if the RPCs work right after, then every statement that should have triggered a reload (the DDL that created or altered these functions) did not, which on a single-project basis usually means the DDL ran inside a transaction that was rolled back and re-run, or was executed by a role whose DDL the event trigger does not observe. Check `postgres.log`-adjacent evidence: `select * from pg_stat_activity where query ilike '%create function%'` will not show history, but the project's SQL editor history will. **3. What changed on 2026-10-22.** You wrote that the failure started that day. Before it, these functions presumably worked — so something in the pair (function set / project configuration) changed. The candidates worth diffing: functions created or replaced that day (a `search_path` added, a parameter renamed, a default added — each changes the signature PostgREST resolves), a project-level `db-schemas` or `extra-search-path` change (Dashboard → Settings → API), or a PostgREST restart on a config change (the version string you quoted, 14.5, is consistent with a recent project action). If anything in that window ran `alter function ... set search_path` on the affected functions, note that functions with a `SET` clause are exposed but their **overload resolution includes the SET-parameter types** — a `SET` on a parameter with a type from a non-exposed schema makes the overload unmatchable while still listed. **The practical sequence**: run `\df+ public.login` (or the query above), compare parameter names to your JSON keys, then `notify pgrst, 'reload schema'` and retry one call. If the reload alone fixes it, the cause is on the DDL-watch side and it will recur on the next function you create until that is addressed. If it does not, the signature comparison will show you what the resolver sees. Paste the `signature` output for one failing function and the exact body you post, and the mismatch — if there is one — will be visible in one read. One thing this is not: not the anon-key exposure question, and not RLS — RPC 404 happens before policies are consulted, which is why the tables still work while every function fails.
This is a real misconfiguration worth reporting, and your read-only handling of it is exactly right. The analysis, in order of what it actually exposes: **What the grants are** `TRUNCATE` held by `anon` and `authenticated` on `storage.buckets`, `storage.buckets_analytics` and `storage.objects` is residual from the managed migration — the grantor is `supabase_storage_admin`, RLS is on, and the API roles have no grant option. Three facts compose into the actual answer: 1. **RLS does not apply to TRUNCATE.** RLS is a row-level mechanism and TRUNCATE is a table-level statement — it is checked at the privilege layer, not the policy layer. So the "RLS is enabled" line on these tables is doing no work for this threat. 2. **PostgREST (the Data API) does not expose TRUNCATE.** Its command surface is SELECT/INSERT/UPDATE/DELETE through the standard endpoints. A request with the anon key cannot issue `TRUNCATE storage.buckets`, whatever the grants say — DELETE is row-level and goes through policies. 3. **So the exposure requires a direct SQL connection issued as `anon` or `authenticated`.** Nobody holds those credentials for direct connections; they are created by PostgREST per-request (`SET ROLE anon`). The practical attack surface is therefore: a SQL Editor session, a direct connection string using those roles, or a future feature that lets a client run arbitrary SQL as an authenticated role (none ships today). **Verification before assuming my claim** — run this as a check, not as an exploit: ```sql select table_name, grantee, privilege_type from information_schema.role_table_grants where table_schema = 'storage' and privilege_type = 'TRUNCATE' order by table_name, grantee; ``` If that returns the six rows you saw, the statement is confirmed. And confirm the second half of the claim by looking at what PostgREST exposes: the storage schema is not in the exposed-schemas list by default, so even the table-level DELETE path through `/rest/v1/` does not reach it. Check: ```sql show pgrst.db_schemas; -- 'public' by default; storage reachable only via Storage API, which runs as its own roles ``` **Severity, honestly**: as a live-account takeover path today, this is low — it needs credentials nobody outside the platform holds. As a standing misconfiguration on managed objects with TRUNCATE reachable from API roles, it is worth a ticket: the day a feature or a connection string path lets a client issue statement-level SQL as those roles, the table-level truncation is one statement away, and nothing in the RLS story stops it. That combination — technically blocked today, structurally wrong — is exactly the class to report and let the platform close. **The fix is a support request, not a workaround.** `REVOKE TRUNCATE ON storage.buckets, storage.buckets_analytics, storage.objects FROM anon, authenticated;` is one statement for `supabase_storage_admin` (or the role that holds the grants, as your catalog shows). What I would *not* do, so it is on record: do not `ALTER TABLE ... OWNER TO postgres` the storage objects to gain the revoke right — Storage's internal grants and the managed migration both depend on that ownership, and the failure mode surfaces later as uploads and bucket operations failing with 42501, which is much harder to diagnose than the original misconfiguration. The migration `0073` path you referenced is the supported rollout shape; the residual is the exact thing that path missed, which is why it is a ticket rather than a client-side patch. **What to put in the ticket**, quoting your own numbers: the three objects, the six grant rows, the RLS-on state, the migration reference, and the ask — revoke TRUNCATE (and any future TRUNCATE default) from `anon`/`authenticated` on managed Storage tables, applied to both current objects and future defaults. If they ask for security-relevant categorization: privilege-layer table-level write reachable from public-facing roles on managed objects, currently mitigated only by the Data API's command surface — which is a platform decision, not a boundary you control. One more thing worth checking while you have the catalog open, because the same migration left it in different families: `REFERENCES` and `TRIGGER` grants on the same objects from the same roles: ```sql select table_name, grantee, privilege_type from information_schema.role_table_grants where table_schema = 'storage' and grantee in ('anon', 'authenticated') and privilege_type in ('TRUNCATE', 'REFERENCES', 'TRIGGER') order by table_name, grantee, privilege_type; ``` If the migration's residual set includes REFERENCES or TRIGGER on those tables, that widens the same class (FK references from attacker-created public tables into managed ones, trigger attachment attempts) and belongs in the same ticket.
There is no platform-level global write lock, and any answer that implies one is wrong about how Postgres privileges work. What there is: six specific mechanisms, each covering one pathway, with one gap that is architectural rather than a missing feature. Ordered by pathway: **1. SQL Editor and any `postgres` connection** — cannot be restricted from writing. That is the platform's own contract: `postgres` is the project admin, and an admin that cannot write is not an admin. `SET TRANSACTION READ ONLY`, `default_transaction_read_only` and event triggers constrain *sessions that inherit them*, not the admin role that can clear them. If a maintenance window needs "nobody writes, not even the admin", the control is operational: the admin credentials are the app's to change during the window, not the database's to enforce. **2. Application roles (`anon`, `authenticated`, custom runtime roles)** — the supported mechanism, and the one that works: ```sql -- every transaction of these roles is read-only by default, at the role level alter role anon set default_transaction_read_only = on; alter role authenticated set default_transaction_read_only = on; -- revoke the write privileges outright (stricter; also removes the ability to opt out per-transaction) revoke insert, update, delete, truncate on all tables in schema public from anon, authenticated; revoke all on all sequences in schema public from anon, authenticated; ``` The `default_transaction_read_only` role setting is the cleaner pair with `revoke`, because it covers tables created later too, without a migration per table. Note the ordering subtlety: `revoke` removes the privilege, but a transaction can set READ WRITE explicitly — only the role setting makes read-only the default for every session of that role, including ones you do not see. Reversed with `alter role ... set default_transaction_read_only = off` and the grants restored at window close. **3. service_role via the Data API** — the supported way to constrain it to approved functions is the same shape as above at the object level: ```sql revoke insert, update, delete, truncate on all tables in schema public from service_role; grant execute on function public.your_approved_write(jsonb) to service_role; -- security definer, owned by postgres ``` The Data API can then only reach what the functions expose. Caveat that matters: a `SECURITY DEFINER` function owned by `postgres` bypasses RLS for its own writes by design, so each function's authorization check is the boundary — `auth.uid()` inside the body, not a policy the caller can route around. This is the same layer that produced the class of bugs where the policy was correct and the service path skipped it. **4. Managed Auth user deletion** — cannot be suspended through SQL you can run. Deleting a user runs as the Auth service, and the foreign-key effects (`ON DELETE SET NULL`, `ON DELETE CASCADE` on your tables) are Postgres behavior the platform cannot selectively hold. The supported posture is operational: the app stops issuing deletions during the window, and your own tables' FK actions are designed so a deletion is idempotent rather than destructive. If you need deletion itself suspended at the database layer, that is a support ask, not a grant change. **5. `pg_cron` and background jobs** — the one with a clean supported answer: ```sql select cron.unschedule(jobid) from cron.job; -- and, to stop new schedules from taking effect during the window: revoke all on schema cron from public; -- schedule creation requires the cron schema ``` Unscheduling is reversible with the original schedules recorded first — save them (`select * from cron.job` into a table) so the window close re-adds what was there, not what you remember. **The honest answer to your question 6**: no single mechanism guarantees exclusive write admission across all five pathways, because two of them (admin SQL and managed Auth) are platform-owned by design. The closest supported architecture is: role-level read-only defaults plus revokes on the application roles (covers Data API and app traffic), function-only grants on `service_role` (covers the service path), cron unscheduled with its schedules saved (covers jobs), and an operational freeze on admin and Auth actions for the duration — documented, with the credentials rotated if the freeze must be enforced rather than trusted. The verification that the window held is a read-only probe from each path at window close, logged.