Prompt of the Day: Add Row Level Security to Your Supabase Tables

Part 13 of 30 — Prompt of the Day Series

---

In September 2025, a security researcher at DeepStrike ran a systematic scan of Supabase-backed applications. The method was straightforward: look for the X-Client-Info: supabase-js header in HTTP traffic, then hit the predictable REST endpoints Supabase auto-generates for every table. No exploits. No special tools. Just /rest/v1/users?select=*&limit=100.

The results were grim. Hundreds, possibly thousands, of organizations — small projects, startups, and mid-sized businesses — had tables fully exposed to the public internet. Active projects, user emails, private records: all retrievable with trivial filter queries. The ilike, eq, and gt operators Supabase provides for legitimate querying became a data exfiltration toolkit. Not one line of custom exploit code was needed.

The fix for every single one of those exposures was the same: Row Level Security with proper policies.

(We covered what RLS is and why it matters in Day 10 of this series. Today is about the prompt that generates it correctly — copy it, fill in your table details, and hand it to your AI assistant.)

---

The Prompt

I'm building a Supabase application and need you to generate correct
Row Level Security (RLS) policies for my database table(s).

Here is the context:

1. Table name and schema (paste your CREATE TABLE SQL or describe the columns):
   [PASTE YOUR TABLE DEFINITION HERE]

2. Authentication model:
   - Users authenticate via: [Supabase Auth / custom JWT / service role only]
   - The column linking rows to users is: [e.g., user_id UUID references auth.users]
   - Are there multiple user roles? [e.g., admin, member, viewer — describe them]

3. Access rules I want to enforce:
   - SELECT: [e.g., users can only read their own rows / all authenticated users can read / public can read]
   - INSERT: [e.g., authenticated users can insert rows where user_id = their own ID]
   - UPDATE: [e.g., users can only update rows they own / admins can update any row]
   - DELETE: [e.g., only the row owner can delete / admins only]

4. Any team/organization structure:
   [e.g., rows belong to an organization, users belong to organizations via a memberships table]

Please generate:
a) The ALTER TABLE statement to enable RLS
b) One named CREATE POLICY statement per operation (SELECT, INSERT, UPDATE, DELETE),
   using USING for filtering existing rows and WITH CHECK for validating new rows
c) Use (select auth.uid()) instead of auth.uid() directly, for query plan caching
d) Apply TO authenticated (or TO anon if intentional) on each policy — never leave the
   role unspecified
e) If team/org lookups are needed in policies, wrap them in a security definer function
   to avoid recursive RLS and performance issues
f) Add an index on any column used in the policy that isn't already a primary key
g) Show me how to test each policy in the Supabase SQL Editor by temporarily
   setting the JWT role to verify access is correctly restricted

---

Why It Works

This prompt works because it forces you to supply the information an AI needs to generate policies that actually match your application's data model — not generic boilerplate.

Here's why each section matters:

The schema (CREATE TABLE SQL) tells the AI which column to use in the USING clause. Without it, the AI guesses — and it usually guesses user_id, which may not be your column name.

The access rules per operation prevent the single most common RLS mistake: writing a FOR ALL policy and thinking it covers everything. It doesn't. FOR ALL only applies a single USING clause. An INSERT operation uses WITH CHECK, not USING. If you only write a FOR ALL policy, your INSERT validation is silent — Supabase won't block bad inserts, it will just silently drop the WITH CHECK enforcement. Separate policies per operation eliminate this trap.

The (select auth.uid()) wrapper (item c) is a performance optimization that the Supabase docs explicitly recommend. Calling auth.uid() directly re-evaluates the JWT on every row. Wrapping it in select tells the PostgreSQL optimizer to cache the result for the duration of the query — up to 100x faster on large tables.

The TO authenticated role (item d) is what MakerKit found across 100+ production deployments to be a near-universal omission. When you leave the role unspecified, the policy runs for the anon role too — meaning unauthenticated visitors trigger the full policy evaluation on every request.

The security definer function (item e) matters the moment you have a membership or team table. Without it, looking up team membership from within an RLS policy triggers RLS on that table too — and you either get infinite recursion or a permissions error. The security definer function runs as the database owner, bypasses RLS on the lookup, and returns a safe boolean result.

Example: Policies Generated by This Prompt

For a tasks table where each task belongs to a user:

-- Enable RLS
ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;

-- Index the ownership column for performance
CREATE INDEX IF NOT EXISTS idx_tasks_user_id ON tasks (user_id);

-- SELECT: users see only their own tasks
CREATE POLICY "tasks_select_own"
  ON tasks
  FOR SELECT
  TO authenticated
  USING ((select auth.uid()) = user_id);

-- INSERT: users can only create tasks assigned to themselves
CREATE POLICY "tasks_insert_own"
  ON tasks
  FOR INSERT
  TO authenticated
  WITH CHECK ((select auth.uid()) = user_id);

-- UPDATE: users can only update their own tasks
CREATE POLICY "tasks_update_own"
  ON tasks
  FOR UPDATE
  TO authenticated
  USING ((select auth.uid()) = user_id)
  WITH CHECK ((select auth.uid()) = user_id);

-- DELETE: users can only delete their own tasks
CREATE POLICY "tasks_delete_own"
  ON tasks
  FOR DELETE
  TO authenticated
  USING ((select auth.uid()) = user_id);

For an org-scoped table, the AI will generate a security definer function first:

-- Helper function: check membership without RLS recursion
CREATE OR REPLACE FUNCTION is_org_member(org_id UUID)
RETURNS BOOLEAN
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
  RETURN EXISTS (
    SELECT 1 FROM memberships
    WHERE memberships.org_id = is_org_member.org_id
      AND memberships.user_id = auth.uid()
  );
END;
$$;

-- SELECT: any org member can read the table
CREATE POLICY "docs_select_org_members"
  ON documents
  FOR SELECT
  TO authenticated
  USING ((select is_org_member(org_id)));

Testing the Policies (SQL Editor)

-- Simulate a specific user's JWT to verify SELECT restriction
SET LOCAL role TO authenticated;
SET LOCAL request.jwt.claims TO '{"sub": "user-uuid-here", "role": "authenticated"}';

-- This should only return rows owned by that user
SELECT * FROM tasks;

-- Verify anonymous access is blocked
SET LOCAL role TO anon;
SELECT * FROM tasks;  -- Should return 0 rows or a permissions error

---

The Anti-Prompt

Bad version:

Add RLS to my Supabase tables so only logged-in users can see their data.

Why it fails: This prompt produces something like:

-- What the AI generates with the vague prompt
ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Enable access for authenticated users"
  ON tasks
  FOR ALL
  USING (auth.uid() = user_id);

This looks right. It is wrong in three ways:

1. FOR ALL with a single USING clause doesn't properly validate INSERTs. An authenticated user can insert a row with someone else's user_id and it won't be caught — only the SELECT is filtered.
2. auth.uid() without the (select ...) wrapper re-evaluates the JWT per row. On a tasks table with 500,000 rows, this is the difference between an 8ms query and a 2-second query.
3. No TO authenticated role specifier means the policy also runs for anonymous users, burning database resources on every public request.

In January 2025, over 170 apps built on the Lovable vibe-coding platform were found with exposed databases. The developers had enabled RLS — but the AI-generated policies were incomplete or missing the WITH CHECK clause entirely, leaving data writable by anyone authenticated. Enabling RLS is not enough. The policies have to be correct.

---

Variations

For public read + authenticated write:

I need a Supabase table that is publicly readable (anon can SELECT)
but only authenticated users can INSERT, and only the row owner can UPDATE or DELETE.
The table is: [paste schema]
Generate the exact RLS policies with correct USING and WITH CHECK clauses
for each operation, with role specifiers on every policy.

For admin bypass:

My app has an admin role stored in a profiles table (profiles.role = 'admin').
Generate RLS policies for [table name] where:
- Regular users see only their own rows
- Admins can SELECT, UPDATE, and DELETE any row
Use a security definer function to check admin status so it doesn't
create RLS recursion on the profiles table.

For auditing existing policies:

Here are the current RLS policies on my Supabase table [paste policies].
Review them for these common mistakes:
1. FOR ALL policies that should be split by operation
2. Missing WITH CHECK on INSERT and UPDATE
3. auth.uid() calls not wrapped in (select ...)
4. Missing TO authenticated / TO anon role specifiers
5. Policy conditions that could be bypassed by an authenticated user
List every issue found and provide corrected SQL.

Quick terminal check — tables missing RLS:

# Run in psql or Supabase SQL Editor to find unprotected tables
SELECT schemaname, tablename, rowsecurity
FROM pg_tables
WHERE schemaname = 'public'
  AND rowsecurity = false
ORDER BY tablename;

---

Checklist: Before You Ship

• ☐ Run the SQL above to confirm zero tables have rowsecurity = false in your public schema
• ☐ Every table with RLS enabled has at least one policy — an enabled table with no policies blocks all access, including your own app
• ☐ Each policy uses (select auth.uid()) not bare auth.uid()
• ☐ Every policy has TO authenticated or TO anon — never leave the role blank
• ☐ INSERT and UPDATE policies have both USING and WITH CHECK — not just USING
• ☐ Any policy that JOINs another table uses a SECURITY DEFINER function to avoid RLS recursion
• ☐ You have an index on the column used in the USING clause (e.g., user_id, org_id)
• ☐ You've tested as an anonymous user — confirmed they cannot read or write protected rows
• ☐ The service_role key is server-side only — never in frontend code or a public repo
• ☐ Supabase dashboard shows no "RLS disabled" warnings on any table

---

Ask The Guild

Have you found an RLS misconfiguration in your own app — or someone else's — that made your stomach drop? Did your AI coding assistant generate a policy that looked correct but had a subtle hole? Share the exact policy that failed and what the correct version turned out to be. The more specific, the more useful for everyone building here.