agreed, that's the cleaner design. if "who can see this stock" lives in its own table that only the server (or a SECURITY DEFINER function that checks the caller) can write, there's no client-writable column for a policy to trust, and it's obvious in an audit. the column grant / trigger in the post is more the minimal patch for an app that already has the column wired through everything
yeah that's the nasty version, nothing about the old policy changed so nobody re-reviews it. two things that help: put the invariant in a RESTRICTIVE policy (those AND together, so a later permissive policy can't widen it), and re-run something that lists policies per table after every migration so a new one on a sensitive table gets noticed
Good addition. Permissive policies OR together, so the "would this still be fine as the only rule" test is a great way to review them.
It's a real app, LabStock, built by a Turkish developer. Gözlemci means observer. Published with his permission after the fix.
Agreed on the framing, the write policy is what's wrong and this isn't Supabase-specific. I've edited that line. The point I was going for: any column a policy reads inside exists() has to be read-only for the client, and table-wide grants make that easy to miss.
Fair, "fine on their own" was the wrong framing. The profiles policy is the bug; I meant they each pass a one-at-a-time review. What stops it: either column grants (revoke table-wide update, grant back only the editable columns) or a BEFORE UPDATE trigger that rejects changes to gozlemci\_of when current\_user is authenticated/anon. Both are in the post under "The fix". LabStock went with the trigger.
Fair catch, that query only covers the grant half. On projects created before Supabase's Oct 30 grant change, anon has select on almost everything by default, so on its own it's mostly noise. These two are the ones that actually find exposure: \-- 1) policies that let anon (or everyone) through unconditionally select schemaname, tablename, policyname, cmd, roles, qual, with\_check from pg\_policies where schemaname = 'public' and permissive = 'PERMISSIVE' and roles && array\['anon','public'\]::name\[\] and (qual = 'true' or with\_check = 'true'); \-- 2) tables anon can read with RLS switched off select c.relname from pg\_class c join pg\_namespace n on n.oid = c.relnamespace where n.nspname = 'public' and c.relkind in ('r','p') and not c.relrowsecurity and has\_table\_privilege('anon', c.oid, 'select'); An empty result on both, plus the view query above, covers the common "anon can read it" cases. Thanks for flagging it.
Same here, they're easy to miss because the policies on the table look fine. This lists the views anon can read that skip RLS (no security\_invoker), plus materialized views, which never have RLS: \`\`\`sql select c.relname as view\_name, case c.relkind when 'm' then 'materialized view, no RLS' else 'runs as owner, skips RLS' end as why from pg\_class c join pg\_namespace n on n.oid = c.relnamespace where n.nspname = 'public' and c.relkind in ('v', 'm') and has\_table\_privilege('anon', c.oid, 'select') and not coalesce(c.reloptions::text\[\] && array\['security\_invoker=true', 'security\_invoker=on', 'security\_invoker=1'\], false) order by 1; \`\`\` Fix for a normal view (Postgres 15+): \`alter view public.x set (security\_invoker = on);\`. For a materialized view, revoke select from anon and read it through a function or the server instead.
Not staff; this is what the public contract says, plus how to make the SQL safe anyway. What is documented: - The OpenAPI spec describes `Idempotency-Key` on `POST .../database/migrations` as ensuring the same migration "is tracked only once". That's history, not protection from a second execution. - The 3-minute figure in the API introduction sits in the rate-limits section (120 requests / 3 minutes for this endpoint), not as a cancellation contract. - Size limits, wrapper transactions and same-connection guarantees aren't documented. - `GET .../migrations/{version}` returns the stored `statements` array, to compare with your file. - `PUT` on the same path records history without applying ("Upsert a database migration without applying"). Changes that remove the unknowns: - Move the prefix into the transaction: `select set_config('application.target_acknowledgement', '...', true);` (or `SET LOCAL`). Then connection sharing doesn't matter and nothing leaks into a pooled session. - First statements: `select pg_advisory_xact_lock(<fixed bigint>);` and an "already applied" guard that raises, so a duplicate or concurrent run aborts. On BEGIN/COMMIT: if the service wraps the body in its own transaction, Postgres only warns on the inner `BEGIN` ("there is already a transaction in progress") and your `COMMIT` ends the outer one, which is the history-atomicity gap you describe. That needs a maintainer answer.
Until there's a native token, you can get short-lived vendor credentials (no static AWS/GCP keys) by making the project its own OIDC issuer. 1. Generate an RS256 key pair and store the private key as a function secret, e.g. `OIDC_PRIVATE_KEY` (names can't start with `SUPABASE_`). 2. Deploy a public function `oidc` (`verify_jwt = false`) that serves `/.well-known/openid-configuration` with issuer `https://<ref>.supabase.co/functions/v1/oidc` and a `jwks_uri` returning the public JWK. 3. Mint a token per call: ```ts import { SignJWT, importPKCS8 } from 'npm:jose@5' const key = await importPKCS8(Deno.env.get('OIDC_PRIVATE_KEY')!, 'RS256') const jwt = await new SignJWT({ function_slug: 'crm-sync' }) .setProtectedHeader({ alg: 'RS256', kid: 'k1' }) .setIssuer(ISSUER) .setSubject('project:<ref>:function:crm-sync') .setAudience(AUDIENCE) .setIssuedAt().setExpirationTime('5m') .sign(key) ``` 4. Register the issuer as an OIDC provider in AWS IAM or GCP Workload Identity Federation, pin `sub` and `aud` in the trust policy, and exchange the JWT (e.g. `AssumeRoleWithWebIdentity`). Verify: `curl https://<ref>.supabase.co/functions/v1/oidc/.well-known/openid-configuration` and decode one token. The limit: the signing key is still a long-lived secret, and anyone who can deploy functions or read secrets can mint any `sub`. It replaces 40 vendor secrets with one key and per-function scoping, not platform attestation, so it supports the request rather than replacing it.
A few things you can establish yourself, plus a way to make the retry safe whatever happened to the earlier requests. Your client timeout says nothing about the server side. The API docs give `POST /v1/projects/:ref/database/migrations` a 3-minute window ("Database migrations may require more processing time"), so a 35 to 45 s client timeout can end before the request does. Read-only check for anything still running or idle in a transaction: ```sql select pid, state, xact_start, left(query, 120) from pg_stat_activity where backend_type = 'client backend' and pid <> pg_backend_pid() order by xact_start nulls last; ``` plus `GET /v1/projects/{ref}/database/migrations` for history. Since your SQL is one transaction, no receipt table, no new functions and no session means none of it committed. You can't prove from outside that nothing is queued, so make a second run harmless. First statements inside the transaction: ```sql select pg_advisory_xact_lock(724001); -- any fixed bigint do $ begin if to_regclass('app.migration_receipt') is not null then raise exception 'migration already applied'; end if; end $; ``` A concurrent run waits on the lock, then aborts on the guard once the first commits. `Idempotency-Key` is documented as ensuring the migration is tracked only once (history, not execution), so don't rely on it for this. With the guard, retrying through the same endpoint is reasonable and keeps history consistent.
One correction and one concrete risk to add. Correction: signUp with an existing email returns `User already registered` only when email/phone confirmation is off. With both Confirm email and Confirm phone on, Auth returns an obfuscated user and no error, so the response doesn't reveal that the account exists (see the `signUp` reference). Either way it doesn't link. Risk: automatic linking matches on email, not the provider's subject (https://supabase.com/docs/guides/auth/auth-identity-linking). #52451 shows the effect: after moving from `twitter` to `x`, users whose X email had changed got a new user. For your invariant: as far as I can tell there's no managed setting or pre-commit hook for links onto an existing user; Before User Created only covers new users. On hosted you can keep your own linking explicit (`linkIdentity()` after a fresh re-auth plus your consent record) and detect everything else. Links should show up in the audit log (check the exact action name in yours): ```sql select created_at, payload->>'actor_id' as user_id, payload->'traits' as traits from auth.audit_log_entries where payload->>'action' = 'identity_linked' order by created_at desc; ``` Compare with your consent records and alert on anything unmatched. The link is already committed when you see it, so this is detection, not prevention. Prevention needs Auth you control (self-hosted) or your own IdP in front.
I'd log the parsed URL once instead of the classified values, so you can see what the runtime actually receives: ```ts const u = new URL(req.url); console.log(req.method, u.protocol, u.host, u.pathname); ``` Supabase's routing docs match paths that start with the function name (e.g. `new URLPattern({ pathname: '/restful-tasks/:id' })`), so expect `/online-delivery-mvp/device/session`, not `/functions/v1/...` (https://supabase.com/docs/guides/functions/routing). Normalize, then allowlist exact method + path: ```ts const path = u.pathname.replace(/^\/functions\/v1(?=\/)/, ''); if (req.method === 'POST' && path === '/online-delivery-mvp/device/session') { return handleDeviceSession(req); // device auth runs here, fail closed } return Response.json({ error: 'invalid_endpoint' }, { status: 400 }); ``` On scheme and host: I wouldn't validate them inside the function. They describe the internal hop to the runtime, which isn't documented as a contract. I also can't find documentation that `x-forwarded-proto` is guaranteed or sanitized on hosted, so I wouldn't build a security check on it as an earlier reply suggests. The check that protects you is your device authentication, so make sure every allowlisted route runs it and rejects by default. If you also need an origin check for browsers, compare the `Origin` header against an exact list, but treat it as a CORS decision, not authentication.
Supabase has a KB article that answers the open question: https://supabase.com/kb/troubleshooting/cant-change-the-default-privileges-of-supabase_admin-in-the-public-schema Short version: - You can't change those defaults. `postgres` isn't a member of `supabase_admin` and can't become one, so that statement fails by design. - They only apply to objects `supabase_admin` creates. Per the KB, Supabase services don't create tables, views or functions in `public` as `supabase_admin`. What it owns there comes from extensions installed into `public`. Auth, Storage and Realtime use their own schemas. - "Automatically expose new tables" only changes the `postgres` defaults, and TRUNCATE, REFERENCES, TRIGGER and MAINTAIN still stay. So your plan is the supported approach. For the `postgres` side: ```sql alter default privileges for role postgres in schema public revoke truncate, references, trigger, maintain on tables from anon, authenticated; -- the PUBLIC execute default for functions can only be removed without IN SCHEMA alter default privileges for role postgres revoke execute on functions from public; ``` (`maintain` exists on PG17 only.) Install extensions into `extensions`, not `public`, and keep a check for anything `supabase_admin` owns in `public`: ```sql select relname, relkind from pg_class where relnamespace = 'public'::regnamespace and relowner = 'supabase_admin'::regrole; ``` Then grant each table only what it needs and verify with `has_table_privilege`.
I'd treat the specific numbers above (1-3 s convergence, draining behaviour) as unverified. I can't find them in Supabase's docs, and as far as I know Supabase doesn't publish a bound for version cutover. The documented wall-clock limit caps how long a running invocation can live (https://supabase.com/docs/guides/functions/limits), but that's a limit, not a routing guarantee. So I wouldn't make the security property depend on timing. Make the old version unable to do the privileged work instead: 1. Give the new version its own credential. Create a new secret key used only by this function, store it under a new secret name the new code reads, deploy, confirm the new version works, then delete the old key in Settings > API Keys. Any old isolate still alive fails on its next Storage call, because the key check happens outside your code (https://supabase.com/docs/guides/api/api-keys). 2. Gate the processing step in the database. If jobs are claimed through an RPC or table, require a version value that only the new code sends, and keep the minimum accepted version in a table. Bump it at cutover and the old code gets refused. Either way you can verify the cutover without trusting platform internals: after deleting the old key, a request made with it should fail, and the function logs should stop showing activity from the old version. If you need a written upper bound for compliance, that has to come from support, not from community answers.
The SQL in the replies runs, but the explanation isn't right, and two details change what the policy actually does. Why `postgres` can do it: neither `GRANT ALL` nor `pg_write_all_data` lets a non-owner run CREATE POLICY; Postgres requires ownership. On Supabase this goes through supautils' `policy_grants`, which lets a listed role manage policies on tables it doesn't own (https://github.com/supabase/supautils). From your migration connection: ```sql select current_user, current_setting('supautils.policy_grants', true); ``` 1. Scope a restrictive policy with `<>`, not `=`. `bucket_id = 'x' AND ...` as RESTRICTIVE blocks `authenticated` from inserting into every other bucket. 2. INSERT alone doesn't cover overwrites. Upsert needs SELECT and UPDATE too (https://supabase.com/docs/guides/storage/security/access-control), so restrict UPDATE as well: ```sql create policy restrict_insert on storage.objects as restrictive for insert to authenticated with check (bucket_id <> 'your-bucket' or owner_id = (select auth.jwt() ->> 'sub')); create policy restrict_update on storage.objects as restrictive for update to authenticated using (bucket_id <> 'your-bucket' or owner_id = (select auth.jwt() ->> 'sub')) with check (bucket_id <> 'your-bucket' or owner_id = (select auth.jwt() ->> 'sub')); ``` Restrictive policies only narrow access: a request still needs a permissive policy, they apply only to the roles in `TO`, and the secret key skips RLS. Test with a real user session.
Some statements in the longer answer above won't do what it says, so test before relying on it: - `REVOKE TEMP ON DATABASE postgres FROM backup_role` is a no-op. TEMPORARY is granted to PUBLIC by default, so the role keeps it. - `postgres` doesn't own the `pg_catalog.lo_*` functions. Without a grant option, Postgres only prints a "no privileges could be revoked" warning, which the SQL Editor doesn't show (https://supabase.com/kb/troubleshooting/cant-change-the-default-privileges-of-supabase_admin-in-the-public-schema). Same risk for other platform-owned schemas. - `pg_read_all_data` is SELECT on every table and view: password hashes in `auth.users`, possibly `vault.decrypted_secrets`. - Use the session pooler (5432) or direct connection for pg_dump, not transaction mode (6543). Check the effective result: ```sql select has_database_privilege('backup_role', current_database(), 'TEMPORARY') as can_temp, has_function_privilege('backup_role', 'pg_catalog.lo_create(oid)', 'EXECUTE') as can_lo_create, has_table_privilege('backup_role', 'vault.decrypted_secrets', 'SELECT') as can_read_vault; ``` If any is still true after the revokes, the boundary isn't there. A customer role can't remove PUBLIC EXECUTE from functions it doesn't own, so for the strict version I agree with kirlyr: keep this credential away from anything that can run arbitrary SQL, and get a written answer from support.
One case worth adding before people copy the fix onto views: a view in `public` hits the same 42501 once the grant is gone, and the obvious fix makes it leak. A view runs with its owner's privileges by default. Created by `postgres`, it skips the RLS on the tables underneath. So this exposes every row, even though the base table has RLS and policies: ```sql create view public.my_orders as select * from public.orders; grant select on public.my_orders to anon, authenticated; -- reads ALL orders ``` On Postgres 15+, make the view run as the caller before granting: ```sql alter view public.my_orders set (security_invoker = true); ``` Read-only check for views that don't have it: ```sql select c.relname from pg_class c join pg_namespace n on n.oid = c.relnamespace where n.nspname = 'public' and c.relkind = 'v' and not coalesce(c.reloptions @> array['security_invoker=true'], false); ``` Same idea for SECURITY DEFINER functions. EXECUTE goes to PUBLIC by default, separate from table grants, and the function body skips RLS. So check `has_function_privilege('anon', 'public.fn(args)', 'execute')` alongside the table test. Supabase docs on this: https://supabase.com/docs/guides/database/postgres/row-level-security
Good list. Point 3 is wider than reads, and it trips people up because the fix for one operation depends on a policy for another: - **UPDATE and DELETE need a SELECT policy too.** `.update(...).eq('id', id)` has a WHERE clause, and Postgres applies SELECT policies to the rows it reads. With an UPDATE policy but no SELECT policy, it changes 0 rows with no error. - **`.insert(...).select()` can fail even when the INSERT policy is fine.** The `.select()` adds RETURNING, and the new row must pass a SELECT policy. If it doesn't, you get 42501 "new row violates row-level security policy". Without `.select()` the same insert works. - **upsert needs INSERT, UPDATE and SELECT policies.** A quick way to see silent zero-row updates: ```ts const { data, error } = await supabase .from('todos').update({ done: true }).eq('id', id).select('id') if (!error && data.length === 0) console.warn('0 rows matched (RLS or filter)') ``` And when adding the missing SELECT policy, scope it, e.g. `using ((select auth.uid()) = user_id)`. A quick `using (true)` exposes every row to every caller. The behavior is in the CREATE POLICY docs, under "Policies Applied by Command Type": https://www.postgresql.org/docs/current/sql-createpolicy.html
One thing doesn't fit the "frozen schema cache" theory: PostgREST builds the OpenAPI output at `/rest/v1/` from its schema cache. If that output lists your functions with the right signatures, PostgREST can see them, unless something in front of it is caching that response. Newly created functions and a new schema failing too also suggests a platform-side issue, so I'd keep pushing the ticket with those two facts and a timestamp. A security note for when RPC works again. `login(p_username, p_password, p_hwid)` and `admin_list_users()` are callable with the publishable key once they resolve. Check what anon can execute: ```sql select p.proname, p.prosecdef as security_definer, has_function_privilege('anon', p.oid, 'execute') as anon_can_execute from pg_proc p join pg_namespace n on n.oid = p.pronamespace where n.nspname = 'public' order by 1; ``` New functions get EXECUTE for PUBLIC by default, so anon usually ends up with it. For admin-only functions: ```sql revoke execute on function public.admin_list_users() from public, anon; ``` Also check `authenticated` if it shouldn't have access either. Then put an admin check inside the function, or move it to a schema the Data API doesn't expose. A login RPC also has no brute-force protection built in, so it needs its own rate limiting.
The ones I see most often: 1. The membership table lets you join any tenant. Every policy checks `org_members`, but the insert policy on `org_members` is often just this: ```sql with check (user_id = (select auth.uid())) ``` That lets anyone insert themselves into any org_id. Inserts into memberships should go through an invite flow, or a server-side path that checks the inviter is an admin of that org. 2. Tenant or role taken from `user_metadata`. `auth.jwt() -> 'user_metadata'` can be changed by the user with `supabase.auth.updateUser({ data })`, so a policy that reads org or role from there can be bypassed. Use `app_metadata` or a membership lookup instead. 3. Cross-tenant foreign keys. A policy checks that `tasks.org_id` is yours, but `tasks.project_id` can point at another tenant's project. Check the parent too: ```sql with check ( exists (select 1 from projects p where p.id = project_id and p.org_id = tasks.org_id) and org_id in (select org_id from org_members where user_id = (select auth.uid())) ) ``` 4. Things that skip RLS: views without `security_invoker = true`, and SECURITY DEFINER functions in an exposed schema. New functions get EXECUTE for PUBLIC by default, so anon can call them unless you revoke it. Testing #1 and #3 with two users in different orgs catches most of these. Docs: https://supabase.com/docs/guides/database/postgres/row-level-security I wrote up the details here: https://www.guardlayer.io/supabase-rls-checker
The safe way to build this is to not let the model talk to the database directly. Give it a few tools you write yourself. Each tool runs as the signed-in user, so your RLS policies decide what it can see. In an Edge Function or a Next.js route, create the client with the caller's JWT, not the service_role key: ```ts const supabase = createClient(SUPABASE_URL, SUPABASE_PUBLISHABLE_KEY, { global: { headers: { Authorization: req.headers.get('Authorization')! } }, }) // a "tool" the model can call async function searchDocuments(q: string) { const { data, error } = await supabase .from('documents') .select('id, title, content') .ilike('title', `%${q}%`) .limit(5) if (error) throw error return data } ``` A few rules that save trouble later: - No service_role key anywhere the model's output can steer. It bypasses RLS. - If you want free-form SQL, run it as a dedicated role that only has SELECT on the tables it needs, plus a statement_timeout. Rely on grants, not just a read-only session setting, because SQL can turn that setting off. - Treat document text as untrusted input. A document can contain instructions. Don't let the model call write tools or outside APIs based on it without a confirmation step, and keep an allowlist of the APIs it may call. Supabase also just shipped a template for exactly this, an MCP server for your app where each request runs as the signed-in user: https://github.com/orgs/supabase/discussions/52171
Not a maintainer, so I can't give you the contract. But one thing in the snippet above is worth changing whatever the answer turns out to be: ```ts req.headers.get('cf-connecting-ip') || req.headers.get('x-real-ip') ``` If the first header is ever missing, this falls through to a header you haven't shown to be trusted. For a security control, a missing or invalid value should fail closed: a shared "unknown" bucket with a strict limit, or a reject. ```ts import { isIP } from 'node:net' const raw = req.headers.get('cf-connecting-ip') ?? '' const ipKey = isIP(raw) ? `ip:${raw}` : 'ip:unknown' // authenticated routes: limit on the verified user id first const key = userId ? `user:${userId}` : ipKey ``` Two other notes: - Cloudflare documents what CF-Connecting-IP means on its own network (https://developers.cloudflare.com/fundamentals/reference/http-headers/). Whether Supabase's setup always overwrites it on every route is the part only Supabase can confirm. - You can check today's behavior without touching production: deploy a throwaway function that only returns the headers it got, then call it with forged and duplicated X-Forwarded-For, CF-Connecting-IP and X-Real-IP. That tells you what happens now, not what's guaranteed, but it shows whether a forged value gets through. For signed-in traffic, the user id from a verified JWT is a better rate-limit key than any IP header.
RagedPanda's CLI-source answer matches what I can verify. A few points in the longer answer don't match the docs: - The endpoint (https://supabase.com/docs/reference/api/v1-create-login-role) is marked Beta. The body is `read_only`, the response has `role`, `password`, `ttl_seconds`. The TTL value and reuse aren't documented, so read `ttl_seconds` instead of assuming "1 hour" or "idempotent". - Supabase's backup-role KB uses port 5432 (session pooler or direct), with username `backup_reader.<project-ref>` on the pooler, not 6543: https://supabase.com/kb/troubleshooting/pg_dump-fails-with-query-would-be-affected-by-row-level-security-policy - `REVOKE TEMP ON DATABASE postgres FROM backup_reader` does nothing by default. TEMPORARY is granted to PUBLIC, and revoking from one role doesn't override that. Check: `select has_database_privilege('backup_reader', current_database(), 'TEMP');` - It isn't a hard boundary. The KB lists what the role can still do, so drop or rotate it after the export. For a full dump without the `postgres` password, the KB recipe fits: `bypassrls` + `pg_read_all_data` + `default_transaction_read_only`, plain `pg_dump` on 5432, and `--no-large-objects` (large objects aren't covered by `pg_read_all_data`).
The fix above (move PostGIS out of `public`) is the right one. On why the REVOKE "silently does nothing": The relacl shows `anon=arwdDxtm/supabase_admin`, so the grantor is `supabase_admin`. Per the REVOKE docs (https://www.postgresql.org/docs/17/sql-revoke.html), a non-owner's REVOKE only removes privileges it holds grant options for, and otherwise issues a warning, not an error. The SQL Editor doesn't show those warnings (Supabase mentions this in https://supabase.com/kb/troubleshooting/revoking-access-to-pg_net-objects-has-no-effect). In psql you'd see the WARNING. To check any revoke actually worked, look at grantors, not just `has_table_privilege`: ```sql select a.grantee::regrole, a.grantor::regrole, a.privilege_type from pg_class c, aclexplode(c.relacl) a where c.oid = 'public.spatial_ref_sys'::regclass order by 1, 3; ``` Any row whose grantor isn't you is one you can't revoke. That needs the owner, support, or moving the object out of the exposed schema.
A correction to Step 1 in the first answer: `ALTER DEFAULT PRIVILEGES IN SCHEMA app, api REVOKE EXECUTE ON ROUTINES FROM PUBLIC` has no effect. From the docs (https://www.postgresql.org/docs/17/sql-alterdefaultprivileges.html): "per-schema default privileges can only add privileges to the global setting, not remove privileges granted by it." For functions `postgres` creates, the global form works (also in https://supabase.com/docs/guides/database/functions): ```sql alter default privileges for role postgres revoke execute on functions from public; ``` If migrations run as another role, repeat it for that role. Existing functions still need an explicit `revoke execute on function ... from public, anon, authenticated`. Verify with `has_function_privilege('anon', 'api.your_fn(...)', 'EXECUTE')` instead of trusting the migration. None of this changes the second answer's point about `supabase_admin`-owned routines.
A correction, since the two answers above contradict each other. The first one is right. PostgreSQL 17 docs (https://www.postgresql.org/docs/17/role-attributes.html): the created role is granted back "just as if the bootstrap superuser had executed" `GRANT created_user TO creating_user WITH ADMIN TRUE, SET FALSE, INHERIT FALSE`, and "because this automatic grant is granted by the bootstrap superuser, it cannot be removed or changed by the CREATEROLE user". So it isn't INHERIT TRUE / SET TRUE, and a REVOKE run as `postgres` won't remove it: "A user can only revoke privileges that were granted directly by that user" (https://www.postgresql.org/docs/17/sql-revoke.html). See the grantor yourself: ```sql select r.rolname as role, m.rolname as member, g.rolname as grantor, am.admin_option, am.inherit_option, am.set_option from pg_auth_members am join pg_roles r on r.oid = am.roleid join pg_roles m on m.oid = am.member join pg_roles g on g.oid = am.grantor where r.rolname = 'trucking_importer_owner'; ``` If your checker needs zero memberships, it has to allow this one row (bootstrap superuser grantor, admin yes, inherit/set no), or it will never pass on hosted Supabase.
There's a middle option between moving the schedule outside Postgres and accepting the exposure: keep pg_cron, but make the queued credential worthless outside one call. The scheduling docs say any login role can read the key while it sits in `net.http_request_queue` (https://supabase.com/docs/guides/functions/schedule-functions), and the grants can't be changed per project (https://supabase.com/kb/troubleshooting/revoking-access-to-pg_net-objects-has-no-effect). So the fix is what you put in the queue: 1. Generate a random value (`openssl rand -hex 32`). Store it in Vault and as a function secret (`supabase secrets set CRON_SECRET=...`). 2. Send it from pg_net as a custom header like `x-cron-secret`, never your secret or service_role key. 3. Set `verify_jwt = false` for that function, compare the header in constant time, and run only one fixed, idempotent recovery job. Ignore request input for anything privileged. 4. Privileged keys come from the function's own env, so they never pass through the queue. A leak then lets someone run your recovery job early, and nothing else. Your migration check will still flag the PUBLIC grants, since that part can't change, but the risk goes from "credential in the queue" to "can trigger one idempotent job".
Adding sources and one design change to the answer above. `anon`/`authenticated` can't reach the queue from the client: `net` isn't exposed through the Data API and those roles can't log in. But the scheduling docs say any role that can log in can read the key while the request waits in `net.http_request_queue` (https://supabase.com/docs/guides/functions/schedule-functions), and revoking won't help (https://supabase.com/kb/troubleshooting/revoking-access-to-pg_net-objects-has-no-effect). Before production: - keep `net` out of Exposed schemas - list login roles: `select rolname from pg_roles where rolcanlogin;` - don't expose views or RPCs that read `vault.decrypted_secrets` Design change: the docs example queues your project secret key, which bypasses RLS. Queue a credential that can only trigger this one job instead: a random value (`openssl rand -hex 32`) in Vault, sent as an `x-cron-secret` header, checked in constant time by an Edge Function with `verify_jwt = false` that runs one fixed, idempotent job and takes privileged keys from its own env. If someone reads the queue, all they can do is run that job early, and you rotate one value.
One thing in the `get_worker_user_profile` example above needs fixing before anyone copies it: it can leak any user's email to anonymous callers. - It's in `public`, so the Data API exposes it as `/rest/v1/rpc/get_worker_user_profile`. - It's `security definer` owned by `postgres`, so it bypasses RLS. - `REVOKE ... FROM PUBLIC` doesn't remove the direct grants to `anon`/`authenticated` that `postgres`'s default privileges add in `public` (https://supabase.com/kb/troubleshooting/cant-change-the-default-privileges-of-supabase_admin-in-the-public-schema). Check: ```sql select has_function_privilege('anon', 'public.get_worker_user_profile(uuid)', 'EXECUTE'); ``` Safer, in a schema that isn't exposed: ```sql create schema if not exists private; create or replace function private.get_worker_user_profile(target_user_id uuid) returns table (id uuid, email text) language sql stable security definer set search_path = '' as $ select u.id, u.email::text from auth.users u where u.id = target_user_id $; revoke execute on function private.get_worker_user_profile(uuid) from public, anon, authenticated; grant execute on function private.get_worker_user_profile(uuid) to assessment_worker; ``` Return only the columns the worker needs.
Fair on all three. Revised version, no ban: 1. Keep Mastermind's block as the primary fence and check it on every business request, together with a check that `auth.jwt()->>'session_id'` still exists in `auth.sessions`. Global logout deletes those rows, so old JWTs stop working at your layer right away instead of at expiry. 2. Your flow stays as it is: block → recovery-session `updateUser({ password })` → `signOut({ scope: 'global' })` → unblock. Nothing is banned in Auth, so the middleware never gets involved. 3. Managed Auth won't give you a commit-time fence or a durable outcome, so build both around it: - Serialize: allow one reset in flight per user, e.g. `pg_advisory_xact_lock` on the user id when you set the block. - Lost response: retry the same `updateUser` with the same password. If the first call committed, the retry returns 422 `same_password`. If it didn't, the retry commits. Either way you end with a definite answer. - In-flight logins and refreshes: a Custom Access Token Hook runs on every token issue, refresh included. Have it return an error while the user is blocked. After unblock, have it reject any `session_id` whose `auth.sessions.created_at` falls inside the block window. A login that passed the old-password check before the update can still create a session, but it never gets a token. I'd test the `same_password` retry and the hook's error return on a throwaway project before relying on either. > Message ID: <supabase/supabase/repo-discussions/51516/comments/19245523@ > github.com> >
Based on the public docs: no. The documented default secrets for hosted functions are the project URL, DB URL, API keys, `SUPABASE_JWKS`, and the metadata values `SB_REGION`, `SB_EXECUTION_ID` and `DENO_DEPLOYMENT_ID` (https://supabase.com/docs/guides/functions/secrets). None of them is a signed per-invocation identity token. `SUPABASE_JWKS` is for verifying user JWTs, which you've ruled out. A separate feature request from 2026-10-16 (#51849) asks for exactly this: per-invocation OIDC tokens with a public issuer and JWKS for AWS STS federation. It has no response yet. So there's no public issuer, audience and subject contract you could put in an IAM trust policy. Until that exists: - **Scoped credential.** Use a long-lived IAM credential stored as a function secret, allowed only to invoke that one Lambda, and rotate it on a schedule. - **Intermediary.** Route the call through a service in an environment AWS already trusts, and authenticate the Edge Function to it with a secret you control. Both still leave a secret in the Edge runtime. If that's a hard requirement, get a statement from support, and add your use case to #51849.
Correction first: `auth.admin.signOut()` takes a user's JWT, not a user ID. From the Auth source (https://github.com/supabase/auth/blob/c3348e8f1cb31454e9783e4eb9eab1e71ad9652f/internal/api/token.go#L120-L195): - A sign-in that re-encrypts the hash writes only `encrypted_password`, with no session changes or audit entry. That's the write your guard broke, so don't guard that column with a trigger. - A real password change runs `UpdatePassword`, which deletes sessions in the same transaction. A Settings change keeps the current session; an admin reset deletes all of them. To block access first, ban the user: `updateUserById(id, { ban_duration: '876000h' })`. The password grant and refresh both reject banned users, and `'none'` lifts the ban. The race: a login checks the ban, verifies the password, and only later creates the session, without re-checking. So: 1. Ban. 2. Wait longer than your longest Auth request. 3. Set the new password. This deletes every session, including late ones. 4. Unban. Access tokens already issued stay valid until `exp`. For an immediate cut-off, check the JWT's `session_id` against `auth.sessions` (https://supabase.com/docs/guides/auth/sessions).
Correction to both replies: Supabase's SSL enforcement docs say the setting covers the pooler's upstream too. "For the shared pooler, this setting governs both legs of the connection: client to pooler, and pooler to Postgres." Enabling it triggers a fast database reboot (https://supabase.com/docs/guides/platform/ssl-enforcement). That fits what you observed: with enforcement off, PostgreSQL reports `ssl = false` for pooler backends. To check it read-only, before and after enabling: ```sql select a.usename, a.client_addr, a.application_name, s.ssl, s.version from pg_stat_activity a join pg_stat_ssl s using (pid) where a.backend_type = 'client backend'; ``` `pg_stat_ssl` has one row per backend and joins to `pg_stat_activity` on `pid` (https://www.postgresql.org/docs/17/monitoring-stats.html). After enforcement, the rows coming from the pooler should show `ssl = true`. The docs don't cover whether the shared pooler verifies the upstream server certificate and hostname. If you need verify-full on every hop, connect directly with `sslmode=verify-full` and the project CA. That needs IPv6, or the IPv4 add-on.
Correction to the second reply: `postgres` can change these settings for its own transaction on hosted projects. Supabase's supautils config lets `supabase_privileged_role` set `log_statement`, `log_min_error_statement` and `pg_stat_statements.*`, and `postgres` is a member of that role (https://github.com/supabase/postgres/blob/develop/ansible/files/postgresql_config/supautils.conf.j2). A hosted user confirmed this works in #51831. Without that, yes: `ddl` logging records the `ALTER ... PASSWORD 'SCRAM-SHA-256$...'` statement, failing statements are logged too, and pg_stat_statements stores it. Send each of these as a separate call, not one multi-statement string. Postgres logs a whole query string before running any of it. ``` BEGIN SET LOCAL log_statement = 'none' SET LOCAL log_min_error_statement = 'panic' SET LOCAL pg_stat_statements.track_utility = off PQchangePassword(conn, 'role', pw) COMMIT SELECT 1 -- clears pg_stat_activity.query ``` Supavisor vs direct doesn't change what Postgres logs. Redaction in Supabase's own log pipeline isn't documented, so get that in writing from support (https://www.postgresql.org/docs/17/runtime-config-logging.html).
On PG16+ you need ADMIN OPTION on a role to grant membership in it; `CREATEROLE` alone isn't enough. That ADMIN grant is created automatically only for roles the creator made itself: "just as if the bootstrap superuser had executed ... WITH ADMIN TRUE, SET FALSE, INHERIT FALSE" (https://www.postgresql.org/docs/17/role-attributes.html). So `atlas_original_runtime` wasn't created by `postgres` in a way that produced that grant. This shows what exists: ```sql select m.rolname member, g.rolname grantor, am.admin_option from pg_auth_members am join pg_roles m on m.oid = am.member join pg_roles g on g.oid = am.grantor where am.roleid = 'atlas_original_runtime'::regrole; ``` Two ways forward: - **Keep the role.** Ask support to run, as `supabase_admin`: `grant atlas_original_runtime to postgres with admin true, inherit false, set false;`. Then grant it to your login yourself, with `inherit true, set false`. - **No support needed.** Create a new runtime role as `postgres` (ADMIN comes automatically), re-apply its grants, and use that instead. You still can't drop the old role, because DROP ROLE needs ADMIN OPTION on it.
One thing can be ruled out from source: Hono's `csrf()` middleware doesn't produce this message. When its check fails it throws a 403 whose body is plain `Forbidden` (https://github.com/honojs/hono/blob/main/src/middleware/csrf/index.ts). It also only checks form-compatible content types (`application/x-www-form-urlencoded`, `multipart/form-data`, `text/plain`) and skips `application/json` entirely (https://hono.dev/docs/middleware/builtin/csrf). Note that a request with no Content-Type is treated as `text/plain`. If `csrf()` is in your stack, a bare `curl -X POST` with no body would get `Forbidden`, not this message. A 520 with a different body means something else is rejecting it. The requests never show up in your function logs, so they're stopped before your code runs. To find which layer, run a quick test: ```ts // supabase/functions/probe/index.ts — no Hono, no middleware Deno.serve(async (req) => { console.log(req.method, req.headers.get('content-type')); return Response.json({ method: req.method }); }); ``` ```bash supabase functions deploy probe --no-verify-jwt curl -si -X POST "$SUPABASE_URL/functions/v1/probe" \ -H 'content-type: application/json' -d '{}' ``` - If `probe` also returns 520 on POST, the problem is at the project or gateway level and isn't in your code. Add the `sb-request-id`/`cf-ray` headers from that response to ticket SU-517126. - If `probe` returns 200, re-add your Hono app piece by piece until the 520 comes back. Don't widen CORS or turn off auth to make it go away. CORS is enforced by browsers, not by curl, so it can't explain a 520 you get from curl. And `verify_jwt` should stay on for user-facing functions anyway (https://supabase.com/docs/guides/functions/auth).
The message `Email address "..." is invalid` is the `email_address_invalid` code. It is different from the default-SMTP restriction, and the fix is different, so check `error.code` first: - `email_address_invalid`: Auth's email validator rejected the recipient. Per the error-codes docs, example and test domains aren't supported (https://supabase.com/docs/guides/auth/debugging/error-codes). In practice it fires for blocked test hosts such as `example.com`, `test.com` or `localhost`, and for domains whose MX lookup comes back empty when Auth goes to send. - `email_address_not_authorized`: you're on the built-in SMTP service, which only sends to members of your Supabase organization. Fix it with custom SMTP (https://supabase.com/docs/guides/auth/auth-smtp). ```ts const { error } = await supabase.auth.resetPasswordForEmail(email.trim(), { redirectTo: 'https://your-app.vercel.app/reset-password', }); if (error) console.error(error.code, error.status, error.message); ``` The validator only runs when Auth is actually about to send. For an address with no account, Auth sends nothing and returns success, so it never trips. That's why a real user can fail while a fake address "works". Test against the deployed domain's actual MX with `dig +short MX yourdomain.com` from outside your network. The earlier localhost link is a separate issue. It comes from the Site URL under Authentication → URL Configuration still being `http://localhost:3000`. Set it to your production URL and keep your Vercel preview URLs in the Redirect URLs allow-list (https://supabase.com/docs/guides/auth/redirect-urls). One security note: on your reset form, show the same "if an account exists, we've sent a link" message whatever the error. Showing different text for this error reveals which addresses have accounts. `resetPasswordForEmail` is designed not to reveal that (https://supabase.com/docs/guides/auth/passwords). I wrote up the details here: https://www.guardlayer.io/blog/supabase-email-address-invalid-password-reset
The public docs don't cover this setting, but the Auth server source settles it. In `supabase/auth`, `CheckValidity` in `internal/models/sessions.go` (https://github.com/supabase/auth/blob/master/internal/models/sessions.go) returns `SessionLowAAL` only when all three of these are true: 1. The AAL1 limit is configured. 2. The session's AAL is lower than the user's highest possible AAL. 3. `now` is past session `CreatedAt` plus the limit. `HighestPossibleAAL()` in `internal/models/user.go` (https://github.com/supabase/auth/blob/master/internal/models/user.go) returns `aal2` only if the user has at least one factor with status verified. Otherwise it returns `aal1`. So your interpretation is correct: 1. Users with no factors, or only unverified ones, are never affected, because aal1 is not lower than aal1. 2. Users with a verified factor who stay at aal1 are cut off once the window has passed since session creation (not since the last refresh). Reaching aal2 takes the session out of this check. 3. The limit is enforced when the session is checked, so the next token refresh fails. Unenrolled users see no difference. 4. Time-box (from `CreatedAt`) and inactivity timeout (from the last refresh) are separate checks in the same function, and they apply to everyone (https://supabase.com/docs/guides/auth/sessions). For Flutter with optional MFA: the SDK's auto-refresh will start failing for an enrolled user who never stepped up. Handle the sign-out event and send them to the MFA challenge, rather than treating it as an error. Use `mfa.getAuthenticatorAssuranceLevel()` on app start: if `currentLevel` is aal1 and `nextLevel` is aal2, show the challenge before the window runs out (https://supabase.com/docs/reference/javascript/auth-mfa-getauthenticatorassurancelevel). If you also want MFA users held to aal2 for sensitive data, enforce it in RLS with a restrictive policy on `(auth.jwt()->>'aal')`, as shown in the MFA guide (https://supabase.com/docs/guides/auth/auth-mfa).
Short answer: yes. For any table in a schema the Data API exposes (by default `public`), RLS is the standard and expected control. Supabase's RLS guide says to "Enable RLS on every table in an exposed schema" and warns that a table without RLS "is readable and writable by any role with a grant on it" (https://supabase.com/docs/guides/database/postgres/row-level-security). Your app not querying a table doesn't protect it. Anyone with your publishable/anon key can call the REST endpoint directly. The alternatives are different ways of making a table not reachable, not ways of skipping protection: 1. Move it out of the exposed schema. Put server-only tables in a `private` schema that isn't in "Exposed schemas". The Data API can't reach them at all (https://supabase.com/docs/guides/database/hardening-data-api). 2. Remove the grants. "A table isn't reachable through the Data API unless you have granted a role privileges on it" (same page). From Oct 30, new tables in `public` no longer get those grants automatically. 3. Disable the Data API entirely if you only talk to Postgres from your own server. Find your exposed, unprotected tables: ```sql select c.relname as table_name, has_table_privilege('anon', c.oid, 'select') as anon_can_read from pg_class c join pg_namespace n on n.oid = c.relnamespace where n.nspname = 'public' and c.relkind = 'r' and not c.relrowsecurity; ``` Any row with `anon_can_read = true` can be read by anyone. Fix it with: ```sql alter table public.your_table enable row level security; create policy "owner reads" on public.your_table for select to authenticated using ((select auth.uid()) = user_id); ``` Two caveats from the same RLS page: - Views bypass RLS unless they're created with `security_invoker = true` (PG15+). - A `security definer` function in an exposed schema runs with its creator's privileges. Check both of those as well as your tables. I wrote up the details here: https://www.guardlayer.io/blog/supabase-row-level-security-complete-guide
The PostgREST docs explain the asymmetry. PostgREST validates the time-based claims `exp`, `iat` and `nbf` and allows 30 seconds of clock skew (https://docs.postgrest.org/en/stable/references/auth.html). "JWT issued at future" means the `iat` in your token is more than 30 s ahead of the clock on the host running PostgREST. Supabase Auth minted that `iat` from its own clock, so the two services disagree about the time. That explains everything you saw: - `/auth/v1/user` returns 200 because Auth checks the token against its own clock. - The legacy `service_role` JWT works because its `iat` is years in the past, so skew can't push it into the future. - Every freshly minted token fails (user sessions, and a fresh token behind your `sb_secret_` request) because its `iat` is "now" by Auth's clock. Your laptop's clock doesn't matter, because both the minting and the checking happen server-side. To prove it in one request, decode the access token and compare `iat` with the `Date` header PostgREST sends back: ```bash TOKEN=... # user access token curl -si "$SUPABASE_URL/rest/v1/profiles?select=id&limit=1" \ -H "apikey: $PUBLISHABLE_KEY" -H "Authorization: Bearer $TOKEN" | grep -i '^date:' # then: echo "$TOKEN" | cut -d. -f2 | base64 -d 2>/dev/null -> look at "iat" ``` If `iat` minus the `Date` header is more than about 30 s, you have server-side clock drift. You can't fix that in app code. Put the two timestamps, the `sb-request-id` response header and your project ref in the support ticket. The numbers show it's the platform, so the ticket can't be closed as a client bug. A project restart may move or restart the services and clear it in the meantime, but report it either way. Another user reported the same symptom on a different project the same day, so it may not be specific to yours. Two things not to do while you wait: - Don't switch the frontend to the secret or `service_role` key to get around the error. That bypasses RLS for every user. - Don't try to fake `iat` on the client. Also make sure the publishable or secret key goes in the `apikey` header only. It isn't a JWT, and only the user's access token belongs in `Authorization: Bearer` (https://supabase.com/docs/guides/api/api-keys).
If anyone lands here in 2026, there's a much cleaner path now than the `convert_to_uuid()` hashing trick — and worth flagging that the SHA-512 approach still quietly breaks Storage, since Storage policies read the raw `sub` claim directly and never call your custom function. Now that Clerk ships a native third-party auth integration for Supabase, you don't need to force those KSUIDs into UUIDs at all. Register Clerk as a third-party auth provider in your Supabase project (it passes the JWT through so `auth.jwt()->>'sub'` returns Clerk's real string id), keep your `user_id` columns as `text` instead of `uuid`, and just compare them directly: ```sql create policy "Users manage their own rows" on public.notes for all using (user_id = auth.jwt()->>'sub') with check (user_id = auth.jwt()->>'sub'); ``` No hashing, no lossy casts, and it behaves identically for Storage buckets because those policies read the same claim. The originally accepted answer was the right call back in 2023 — this is really just the modern way to do it now that the native integration exists. I wrote up the full breakdown here: https://www.guardlayer.io/blog/clerk-supabase-rls
Since you're building off Gary's answer here, the cleanest path in 2025 is Clerk's native third-party auth integration rather than the old JWT-template route — Clerk deprecated the `supabase` JWT template back in April 2025, so any tutorial telling you to mint one is out of date. With the native integration, Clerk's session token is handed to Supabase directly and the `sub` claim carries the Clerk user id (e.g. `user_2abc...`), which is exactly why your current flow chokes: those ids aren't UUIDs, so a `uuid` column or an `auth.uid()` cast will throw. On the database side, store your user-id columns as `text` instead of `uuid` and have your RLS policies read the id straight off the JWT: ```sql create policy "Users manage own rows" on public.your_table for all using (auth.jwt()->>'sub' = user_id) with check (auth.jwt()->>'sub' = user_id); ``` That lets the rest of your app stay mostly as-is — you stop trying to fit Clerk into Supabase's auth session and just let RLS trust Clerk's token. And since you're on Next.js 14, you can drop the Supabase session-refresh juggling entirely and pass the Clerk token when you construct the client. It's a bit of upfront migration on the column types, but it's stable once it's in. I wrote up the full walkthrough here: https://www.guardlayer.io/blog/clerk-supabase-rls
Two things are probably going on here, and they're worth separating. The "no rows unless I turn RLS off" symptom is almost certainly your `EXISTS` subquery running into RLS on `profiles` itself — if `profiles` has RLS enabled but no policy letting a user read their own row, that inner `SELECT 1 ... WHERE id = auth.uid()` returns nothing, so every `residents` row fails the check and you get an empty result. A self-read policy on `profiles` usually makes the data reappear: ```sql CREATE POLICY "read own profile" ON public.profiles FOR SELECT TO authenticated USING (id = (select auth.uid())); ``` The "Slow Queries" flag is a separate issue: `auth.uid()` gets re-evaluated for every row in `residents`, so wrap it as `(select auth.uid())` in both policies — Postgres then treats it as a one-time init plan instead of a per-row call, which is the single biggest win on this pattern. Keep `profiles.id` indexed (it already is if it's your PK) and hold onto the `TO authenticated` scoping you've got. One gotcha when you benchmark: run `EXPLAIN ANALYZE` after `set local role authenticated` and setting `request.jwt.claims`, otherwise you're executing as the table owner, which bypasses RLS and hides the real cost you're trying to measure. I put the full breakdown here if it helps: https://www.guardlayer.io/blog/supabase-rls-performance
Nice debugging on this one — the `declare user_id text := auth.uid()::text` trick works for exactly the reason you'd hope, and there's a lighter-weight version that keeps it inline. When you write `WHERE customer = auth.uid()::text`, Postgres treats `auth.uid()` as volatile and re-evaluates it per row, so it can't push the filter down — which with the Stripe wrapper means it pulls every subscription and paginates through the whole API instead of doing an ID lookup. Wrapping it in a scalar subquery gets you the same one-time evaluation your PL/pgSQL variable does, but without leaving SQL: ```sql WHERE customer = (select auth.uid())::text ``` That hoists it into an initPlan that runs once per statement, and the wrapper can then do the direct lookup. It's the same pattern that matters on regular RLS policies too — on an indexed column Supabase measured a filter go from ~179ms to ~9ms just from the `(select ...)` wrap, so if `customer` isn't indexed on the underlying table, add that as well. Even though this is answered, leaving it here for anyone who lands on it from search. I put together the full breakdown with the query plans here: https://www.guardlayer.io/blog/supabase-rls-performance
Since your trigger on `auth.users` depends on the function, that dependency is exactly why the plain `drop` kept fighting you — and the good news is you don't have to drop anything to clear the warning. You can pin the search path on the existing function in place: ```sql alter function public.create_profile_for_new_users() set search_path = ''; ``` The one gotcha: once the search path is empty, nothing resolves by bare name anymore, so open the function body and fully qualify every reference — `public.user_profile`, `auth.users`, and so on — otherwise it'll start throwing "relation does not exist" the next time it fires. If you'd rather rebuild it cleanly instead of altering in place, do the same thing at creation time with `security definer set search_path = ''` and the qualified names baked in from the start. Both approaches silence the 0011 linter; the `alter` route just spares you from touching the trigger, which is nice given the `auth` schema is Supabase-managed. And keeping profile creation server-side via the trigger is perfectly fine — you don't need to push it to the client just to make the linter happy, though that's a reasonable design choice too if you prefer it. I wrote up the full reasoning on why the empty search_path matters here: https://www.guardlayer.io/blog/supabase-security-definer-search-path
Yeah, your instinct is right — with the default `postgres` connection string those queries never touch RLS at all. That role owns the tables and carries `BYPASSRLS`, so Prisma is effectively running as a superuser and your policies just don't fire, no matter what you do with the `public."Users"` mirror. Two things fix it: point `DATABASE_URL` at a dedicated login role that isn't the table owner and has `NOBYPASSRLS`, then wrap each request in a transaction where you switch into `authenticated` and inject the JWT claims so `auth.uid()` actually resolves. ```ts await prisma.$transaction(async (tx) => { await tx.$executeRaw`select set_config('request.jwt.claims', ${JSON.stringify(claims)}, true)`; await tx.$executeRaw`set local role authenticated`; // queries in here now run under RLS, with auth.uid() available }); ``` Because it's `set local`, the role and claims reset when the transaction commits, so nothing leaks between requests. Switching to the restricted role is also the fastest way to prove the policies were being skipped before — you'll suddenly get empty results where the same query used to hand back every row. Even if you've long since moved past this, it trips up a lot of people who land here from search, so figured it was worth spelling out. Full breakdown of why ORMs slip past Supabase RLS: https://www.guardlayer.io/blog/orm-bypasses-supabase-rls
Yeah, a storage upload is just an `INSERT` into `storage.objects` under the hood, so RLS treats it exactly like any table write — and a lone condition like `bucket_id = 'products'` won't authorize it unless it's attached to the INSERT command as a `WITH CHECK`. INSERT policies are evaluated against `WITH CHECK`, not `USING`, so if the policy you added only has a `USING` expression (or the CRUD policies got created as SELECT-only), every upload gets default-denied even though it looks covered. Add an explicit insert policy on `storage.objects` scoped to the `authenticated` role: ```sql create policy "authenticated can upload to products" on storage.objects for insert to authenticated with check (bucket_id = 'products'); ``` If you read the file back right after uploading (or the client lists it), add a matching `select` policy with `using (bucket_id = 'products')`, otherwise the read fails the same way. Worth double-checking the target role too — if the policy is set to `anon`/`public` but you're uploading as a signed-in user, it never matches. The mental model that trips everyone up: once RLS is on, nothing is permitted until a policy explicitly allows it, per command. I wrote up the full breakdown of this error here: https://www.guardlayer.io/blog/supabase-new-row-violates-rls-policy
What's happening is that your `admin.auth.signUp()` call sets a session on that same client, so by the time you run `admin.from('account').insert()`, supabase-js has swapped in the newly created user's JWT as the `Authorization` header instead of your service_role key. That's why RLS kicks back in and you get the 42501 — the insert is running as the just-signed-up user, not as the service role. It also explains why it worked on your other tables: those probably had policies that happened to allow the user. The fix is to keep a dedicated server-only client for privileged work and never let it store or refresh a session: ```js import { createClient } from '@supabase/supabase-js' // server-side ONLY — never ship this key to the browser export const supabaseAdmin = createClient( process.env.SUPABASE_URL, process.env.SUPABASE_SERVICE_ROLE_KEY, { auth: { persistSession: false, autoRefreshToken: false } } ) ``` Use `supabaseAdmin` for the `.insert()` and keep the auth/signUp flow on a separate client, and the service_role key will actually bypass RLS like you expect. The `persistSession: false` bit is the important part — without it, any auth call on that instance quietly overrides the key. Just make sure this client only ever lives on the server, since the service_role key ignores every policy you have. I wrote up the full breakdown of this exact error here: https://www.guardlayer.io/blog/supabase-new-row-violates-rls-policy
That 42501 on an UPDATE is Postgres telling you the *new-row* check failed, and pulling the `WITH CHECK` off won't change that — for an UPDATE, Postgres reuses your `USING` expression as the row check when no explicit `WITH CHECK` exists, so `auth.uid() = user_uid` still has to hold for the row you're writing back. The near-certain cause is `auth.uid()` returning `NULL` inside that PATCH: the comparison becomes `NULL = uuid`, which is never `true`, so the write is blocked — even though `getUser()` looks perfectly fine in your app. The trap is that `getUser()` succeeding client-side doesn't prove the *specific* client instance issuing the delete is actually attaching that access token; a second client, an SSR/server client without the session, or a just-expired token will quietly fall through as `anon`. So confirm it from Postgres's side instead of the client's. Add a tiny function and call it with the exact same client you use for the `deleted_at` patch: ```sql create function public.whoami() returns table(uid uuid, jwt json) language sql security invoker as $$ select auth.uid(), auth.jwt() $$; ``` If `whoami()` comes back with `null` / `null`, the JWT isn't reaching PostgREST and that's your bug, not the policy. Once it resolves to `d1dc7a43-…`, put `USING (auth.uid() = user_uid)` back and you're done — your types already line up (both `uuid`), so no cast is needed. I dug into every way `auth.uid()` ends up NULL like this here: https://www.guardlayer.io/blog/supabase-auth-uid-null-rls