AI AI Tools Guidebook
Prompt Library

Supabase RLS Policy Review Prompts for Auth and Roles

15 Supabase Row-Level Security review prompts: auth.uid() checks, WITH CHECK coverage, service_role bypass, storage bucket RLS, SECURITY DEFINER, and the (select auth.uid()) perf fix.

Published: Updated: Author: AI Productivity Guide Team 🌐 查看中文版本

Most “review my RLS” prompts return generic advice about enabling Row-Level Security. The bugs that actually leak data are subtler: a missing WITH CHECK on UPDATE, a service_role key that bypasses RLS through a helper function, an auth.uid() comparison against the wrong column. This is not theoretical. In May 2025, CVE-2025-48757 (CVSS 9.3) exposed 303 endpoints across 170 Lovable-built apps because tables were readable by unauthenticated requests with the public anon key. The 15 prompts below each hunt one specific RLS failure mode so AI review catches what a quick eyeball misses.

TL;DR

  • Run prompt 1 first. If RLS is off, every other policy is meaningless, which is exactly the gap CVE-2025-48757 exploited.
  • The highest-yield checks are WITH CHECK coverage (prompt 4) and the service_role audit (prompt 5). Most real leaks live there, not in the USING clause everyone reviews.
  • Feed these to a strong reasoning model. As of June 2026, Claude Opus 4.7 (87.6% on SWE-bench Verified) and GPT-5.5 are the most reliable for spotting SQL trust-boundary bugs; paste your actual \d schema dump and policy DDL, not a summary.
  • Pair the AI pass with Supabase’s free Security Advisor, which flags rls_disabled_in_public (lint 0013) automatically.
  • Fix performance and security together: wrap row-independent calls as (select auth.uid()) to trigger an initPlan, and index every column compared against it.

Who this is for

Solo founders shipping Supabase apps before public launch, code reviewers auditing schema PRs, and security engineers preparing pen-test fixtures. If you generated your schema with an AI builder (Lovable, Bolt, v0), treat this as mandatory, not optional.

Skip these when

The auth.uid() and storage.objects prompts assume Supabase Auth and Supabase Storage. They do not apply to plain self-hosted Postgres or to prototypes where RLS is deliberately off.

What every RLS review prompt needs

A prompt that returns useful findings (not “consider enabling RLS”) carries six things:

ElementWhat to give the model
RoleSupabase security reviewer / SQL trust-boundary auditor
ContextSchema dump (\d or pg_dump --schema-only), policy DDL, framework + Supabase versions
GoalOne deliverable: a findings table, a migration plan, or a red-team test seed
ConstraintsDo not rewrite silently, do not invent column names, flag uncertainty
Output formatNumbered findings, a markdown matrix, or a unified SQL diff
SignalOne example of a real bug class so the model knows what “bad” looks like

Best uses: a pre-launch RLS audit, a schema-migration PR review, post-incident hardening, a service_role usage audit, and a storage-bucket access review.

15 copy-ready prompt templates

1. RLS enabled coverage check

Run first. A missing RLS toggle invalidates every policy under it.

You are a Supabase security reviewer. Audit the schema dump below. For every table in `public`: (1) Is RLS enabled? (2) Does it have at least one policy? (3) Is FORCE ROW LEVEL SECURITY enabled (relevant for table-owner access)? Output a table: schema.table | RLS enabled | policy count | force RLS | risk.

This mirrors Supabase’s own rls_disabled_in_public linter (lint 0013): any public table without RLS is CRUD-able by anyone holding the project URL and anon key.

2. auth.uid() comparison correctness

Review every policy USING / WITH CHECK clause that references `auth.uid()`. For each: (1) Is it compared against the column that actually holds the owner (e.g., user_id, owner_id, profile_id)? (2) Could the comparison silently pass for nulls? (3) Are joined tables also constrained, or is the JOIN unprotected? List findings: policy name | issue | fix sketch.

3. INSERT / UPDATE / DELETE policy coverage

For each table with RLS enabled, list which commands have policies: SELECT, INSERT, UPDATE, DELETE. Flag tables missing policies for any command. The default is deny, but partial policies leave gaps (e.g., UPDATE without WITH CHECK lets a user move a row to another owner). Output as a matrix.

4. WITH CHECK vs USING audit

The single highest-yield prompt here.

Review every UPDATE and INSERT policy. For each: (1) Does it have BOTH USING (pre-image) and WITH CHECK (post-image)? (2) On UPDATE, does WITH CHECK stop reassigning ownership to another user? (3) On INSERT, does WITH CHECK stop inserting on behalf of another user? List violations with file:line.

5. service_role bypass audit

Find every usage of the `service_role` key in the codebase (server functions, edge functions, migrations, background jobs). For each: (1) Is it strictly necessary, or could `authenticated` work? (2) Is the call wrapped in a function that re-checks ownership? (3) Are any service_role queries reachable from user input without validation? File:line + severity.

service_role bypasses RLS entirely. Every path that uses it is a privileged surface, so this audit usually finds more real leaks than the policy text itself.

6. Storage bucket RLS review

Audit Supabase Storage bucket policies. For each bucket: (1) public vs private flag, (2) SELECT/INSERT/UPDATE/DELETE policies on `storage.objects` filtered to this bucket, (3) Does the path convention encode ownership (e.g., a user-id prefix) and does the policy enforce that prefix via storage.foldername? (4) Are signed URLs used where private buckets need temporary access? Output: bucket | policies | risks.

7. SECURITY DEFINER function audit

List every Postgres function marked SECURITY DEFINER (including any Supabase generated). For each: (1) Does it set `search_path` explicitly (e.g., `set search_path = ''`) to prevent search_path hijacking? (2) Does it re-validate auth.uid() if it bypasses RLS? (3) Is it callable by anon? (4) Are the GRANTs correct? Findings with severity.

A SECURITY DEFINER function runs as its owner and can silently bypass RLS. Supabase’s function_search_path_mutable advisor flags the missing search_path case.

8. Realtime subscription policy audit

Audit Supabase Realtime publication and policies: (1) Which tables are in the `supabase_realtime` publication? (2) Do those tables have SELECT policies that constrain rows visible per user? Realtime respects RLS, so a loose SELECT policy leaks broadcasts. (3) Are any sensitive columns published when they should not be? List risks.

9. Multi-tenant isolation review

This app is multi-tenant via a tenant column (default tenant_id). Audit: (1) Every tenant-scoped table has the tenant column and an RLS policy comparing it to the user's tenant claim, (2) JWT claim extraction (auth.jwt() ->> 'tenant_id' or similar) is consistent across policies, (3) Cross-tenant reads via JOINs are blocked. Output: table | tenant policy | leak risk.

Variables to swap: the tenant column name, for example tenant_id, org_id, or workspace_id.

10. Role-based policy audit

If this schema uses role columns (admin, member, viewer), audit role policies: (1) Are roles read from a profile table or a JWT claim? Pick one; mixing is a footgun. (2) Do admin-only mutations check role inside the policy, or only in the app? (3) Can a user escalate their own role via UPDATE on the profiles table? List findings.

11. Migration policy diff review

Use on a migration PR.

Below is a migration diff. For RLS changes: (1) Any policy dropped without replacement? (2) Any new table missing RLS? (3) Any column type change that breaks an existing policy comparison? (4) Are policy statements idempotent (CREATE OR REPLACE / DROP IF EXISTS)? Output PR-ready review comments.

Variables to swap: the migration diff text.

12. Anon vs authenticated split review

For every policy, classify which role it applies to (anon, authenticated, service_role). Flag: (1) policies applied to anon that should be authenticated only, (2) policies applied to authenticated when only specific roles should access, (3) policies with no role filter (default = all roles).

13. Policy performance review

Review RLS policies for performance: (1) Policies that call a function per-row instead of inlining or caching the auth check, (2) Policies that JOIN large tables, which become per-row subqueries, (3) Missing index on the column compared against auth.uid(), (4) auth.uid() invoked multiple times per policy. Suggest specific indexes and rewrites using the (select auth.uid()) initPlan pattern.

This is where security and speed meet. Wrapping a row-independent call as (select auth.uid()) lets Postgres run it once per statement (an initPlan) instead of once per row, and indexing the compared column can cut a sequential scan from timing out on a million rows to a few milliseconds.

14. RLS gap to red-team scenario

Take the policies below and write 5 red-team scenarios. Each is a JWT + a SQL query + the expected (denied) result. Then say which of the 5 would actually succeed against the current policies and why. Use this as a regression-test seed.

15. RLS findings to fix plan

Run last. Converts findings into a deployable migration sequence.

Take all RLS findings above and group them into ordered migrations. For each migration: (1) Title, (2) SQL diff (DROP / CREATE / ALTER), (3) Pre-deploy check (count rows that would now be denied, as a sanity check), (4) Rollback. Mark which migrations need a maintenance window.

Which model to run these on

As of June 2026, paste your real schema and policy DDL into a high-reasoning model rather than a chat default. Claude Opus 4.7 leads SWE-bench Verified at 87.6% and is consistently strong at SQL trust-boundary reasoning; GPT-5.5 (Thinking mode) is a solid second and edges ahead on long terminal-style tasks. Gemini 3.1 Pro works for a cheaper second opinion at $2/$12 per million tokens. All three carry a 1M-token context, so a full pg_dump --schema-only plus your edge-function source fits in one pass. For the workflow side, run the audit inside Cursor or Claude Code so the model can open the actual migration files.

The select-wrapper rewrite, concretely

The (select auth.uid()) pattern is the one rewrite worth memorizing. A bare auth.uid() = user_id re-evaluates the function for every candidate row. Wrapping it as (select auth.uid()) = user_id triggers a Postgres initPlan that evaluates it once per statement and caches the result, because the value does not depend on row data. Supabase’s performance advisor flags the un-wrapped form under auth_rls_initplan (lint 0003). Combine it with an index on user_id: on large tables, indexing the compared column is reported to give over 100x speedups versus a sequential scan. Use the wrapper only for calls whose result is constant across rows.

Common mistakes

  • Reviewing only USING and forgetting WITH CHECK. UPDATE and INSERT leaks fly under the radar.
  • Trusting service_role for “internal” code without re-checking ownership. Every service_role path is a privileged surface.
  • Comparing auth.uid() against the wrong column (id instead of user_id). Silent zero-row results hide the bug.
  • Enabling RLS but forgetting FORCE ROW LEVEL SECURITY. Table-owner queries then bypass policies.
  • Letting the realtime publication broadcast tables that have loose SELECT policies. The leak surface multiplies.
  • Mixing role-lookup sources (profile table vs JWT claim). Role changes create race conditions.
  • SECURITY DEFINER functions without an explicit search_path, which opens a hijack path.
  • Skipping migration review on RLS PRs. A dropped policy looks like added security but is a regression.

How to push results further

  • Always run prompt 1 first. Every later audit is meaningless if RLS is off, and the linter 0013 agrees.
  • Make the reviewer write a red-team scenario (prompt 14). It surfaces what static review misses.
  • Keep auth.jwt() claim extraction identical across every multi-tenant policy.
  • Wrap row-independent calls as (select auth.uid()) and index the compared column.
  • Pair the policy review with the service_role audit. Most leaks live there.
  • Re-run after each migration PR. RLS state drifts silently.
  • Snapshot policies to /supabase/policies.snapshot.sql and review the diff over time.

FAQ

  • How do I test policies without writing app code?: Static review covers structure. For runtime, use select set_config('request.jwt.claims', '...', true) plus set role authenticated in the SQL editor to impersonate a user, then run the query and confirm the row count.
  • Can AI generate the policies for me?: It writes solid first drafts, but run this review on every AI-generated policy. The common misses are WITH CHECK and the role filter, which is precisely the gap behind CVE-2025-48757.
  • Do I need RLS if my app already does auth checks?: Yes. App-only checks fail open the moment anyone reaches the database directly through PostgREST, the SQL editor, or a leaked anon key. RLS is the last line.
  • Why does my SELECT return zero rows after enabling RLS?: Default deny. You need at least one SELECT policy that matches the user’s rows. Run prompt 3 to find the missing command. See Supabase RLS blocks my data for the full fix.
  • Should service_role calls always re-check ownership?: If the call accepts a user-supplied identifier, yes. Re-check inside the call or wrap it in a SECURITY INVOKER function so RLS still applies.
  • How do I stop role escalation via UPDATE on profiles?: Add a column-level restriction or a trigger that rejects role changes from authenticated users. Prompt 10 covers it.

Tags: #Prompt #Coding #Supabase #Security

Related Articles