Supabase RLS in AI-Built Apps: A Hands-On Security Audit Walkthrough

In web applications built with AI coding generators like Lovable, Bolt, and Cursor, Supabase Row-Level Security (RLS) is frequently misunderstood or misconfigured. Auditing an AI-built Supabase project requires inspecting deep database catalog state: checking relrowsecurity and relforcerowsecurity in pg_class to catch tables where table owners or default roles bypass security, joining pg_policies to identify tables with RLS enabled but zero active policies, verifying that WITH CHECK clauses guard mutations against state corruption, and running pgTAP test suites across anonymous, authenticated, and cross-tenant roles.

When AI models build prototypes, prompt completions optimize for immediate UI rendering. If an RLS policy prevents an AI assistant from inserting test data, the model commonly writes overly permissive policies or leaves tables in half-configured security states.

The short version

A thorough Supabase RLS security audit follows five inspection phases:

1. Query pg_class for both relrowsecurity = true and relforcerowsecurity = true to ensure table owners cannot accidentally bypass policy enforcement.
2. Join pg_tables against pg_policies to find tables where RLS was enabled but zero policies were created, causing silent total read/write lockouts or unexpected behavior.
3. Validate that WITH CHECK expressions are explicitly declared on INSERT and UPDATE policies so authenticated users cannot assign records to arbitrary tenant identifiers.
4. Ensure all database views declare WITH (security_invoker = true) to avoid running with view creator superuser privileges.
5. Implement reproducible regression tests using pgTAP to verify tenant boundary isolation in CI before deploying schema migrations.

Compare these database controls with our 12 critical security flaws in vibe-coded apps and our guide on common auth and RLS mistakes in Bolt, v0, and Replit apps.

1. Deep Catalog Inspection: relrowsecurity and relforcerowsecurity

In standard PostgreSQL, running ALTER TABLE public.projects ENABLE ROW LEVEL SECURITY; protects rows against ordinary application users. However, table owners bypass RLS by default unless forced:

-- FORCE RLS binds table owners who are not superuser and do not possess BYPASSRLS
ALTER TABLE public.projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.projects FORCE ROW LEVEL SECURITY;

Crucially, superusers and roles with the BYPASSRLS attribute (such as postgres, supabase_admin, or the Supabase service_role key) always bypass RLS regardless of FORCE ROW LEVEL SECURITY. In serverless environments where connection pools or direct drivers mistakenly connect under the default postgres role or service_role key, queries bypass all RLS policies silently. Real isolation requires both enabling/forcing RLS on the table and ensuring client requests execute under a non-privileged role (such as authenticated or anon using user JWT claims).

To check both RLS enablement and forced execution, query pg_class qualified by the target schema:

SELECT 
  c.relname AS table_name,
  c.relrowsecurity AS rls_enabled,
  c.relforcerowsecurity AS rls_forced,
  pg_get_userbyid(c.relowner) AS table_owner
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public' 
  AND c.relkind = 'r'
ORDER BY c.relname;

If rls_enabled is false, the table is completely unprotected. If rls_forced is false, connections under the table owner role bypass policies. Inspect role attributes to confirm your application role is subject to RLS:

SELECT rolname, rolsuper, rolbypassrls 
FROM pg_roles 
WHERE rolname = current_user;

2. Detecting Zero-Policy Tables and Review Candidates

A common failure mode in AI-generated migrations is enabling RLS on a table without creating any policies. In PostgreSQL, a table with RLS enabled and zero policies defaults to denying all non-owner access. While secure against data leakage, it triggers unhandled runtime errors that prompt developers to disable RLS entirely or generate indiscriminate catch-all policies.

Use this outer join query to find tables with RLS enabled but zero policies defined:

SELECT 
  t.schemaname,
  t.tablename,
  t.rowsecurity AS rls_enabled,
  COUNT(p.policyname) AS active_policy_count
FROM pg_tables t
LEFT JOIN pg_policies p 
  ON t.schemaname = p.schemaname 
  AND t.tablename = p.tablename
WHERE t.schemaname = 'public'
GROUP BY t.schemaname, t.tablename, t.rowsecurity
HAVING t.rowsecurity = true AND COUNT(p.policyname) = 0;

Next, identify review candidates where policies use permissive filters:

SELECT 
  tablename,
  policyname,
  cmd,
  roles,
  qual,
  with_check
FROM pg_policies
WHERE schemaname = 'public'
  AND (qual ~* '\mtrue\M' OR with_check ~* '\mtrue\M');

Treat qual ~* '\mtrue\M' as an alert candidate, not an automatic flaw: public catalog tables or marketing pricing lists may legitimately be world-readable, but customer-owned data tables must be scoped to authenticated claims.

3. The Mutation Trap: USING vs WITH CHECK

When an AI assistant creates an UPDATE or INSERT policy, it often conflates the USING clause with the WITH CHECK clause.

• USING clause: Evaluated against existing rows in the table. For a SELECT, UPDATE, or DELETE, it determines which rows the caller can see and target.
• WITH CHECK clause: Evaluated against the new row data being inserted or updated. For INSERT and UPDATE, it ensures that the resulting row satisfies authorization constraints.

In PostgreSQL, an omitted WITH CHECK on an UPDATE or ALL policy reuses the USING clause as the check constraint. However, AI assistants frequently emit permissive WITH CHECK (true) on INSERT policies, or declare FOR ALL USING (true), which allows authenticated users to insert records with arbitrary tenant identifiers:

-- DANGEROUS: Permissive WITH CHECK (true) on INSERT allows arbitrary tenant_id injection
CREATE POLICY "Users can insert workspace documents"
ON public.documents
FOR INSERT
TO authenticated
WITH CHECK (true);

-- HARDENED: Explicit subquery ensures the caller can only insert into their own workspace
CREATE POLICY "Users can insert workspace documents"
ON public.documents
FOR INSERT
TO authenticated
WITH CHECK (
  workspace_id IN (
    SELECT workspace_id 
    FROM public.workspace_members 
    WHERE user_id = (SELECT auth.uid())
  )
);

Always explicitly provide both USING and WITH CHECK for UPDATE policies to prevent a user from moving a record out of their tenant into an unauthorized tenant:

CREATE POLICY "Users can update their own documents"
ON public.documents
FOR UPDATE
TO authenticated
USING (
  workspace_id IN (
    SELECT workspace_id 
    FROM public.workspace_members 
    WHERE user_id = (SELECT auth.uid())
  )
)
WITH CHECK (
  workspace_id IN (
    SELECT workspace_id 
    FROM public.workspace_members 
    WHERE user_id = (SELECT auth.uid())
  )
);

4. Securing Views with security_invoker = true

AI code generators frequently create database views for dashboard aggregates. By default, PostgreSQL views run with the permissions of the user who defined the view (security_definer behavior). When a user queries a standard view, PostgreSQL executes the underlying query as the view creator, completely bypassing the user's RLS policies on the underlying tables.

In PostgreSQL 15 and Supabase, always declare security_invoker = true on views exposed to clients:

-- HARDENED: View runs with the privileges of the querying user
CREATE VIEW public.workspace_summary
WITH (security_invoker = true) AS
SELECT 
  w.id AS workspace_id,
  w.name,
  COUNT(d.id) AS document_count
FROM public.workspaces w
LEFT JOIN public.documents d ON d.workspace_id = w.id
GROUP BY w.id, w.name;

To audit existing views that lack security_invoker:

SELECT 
  c.relname AS view_name,
  c.reloptions
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public' 
  AND c.relkind = 'v'
  AND (c.reloptions IS NULL OR NOT ('security_invoker=true' = ANY(c.reloptions)));

5. Automated Verification with pgTAP Test Fixtures

Testing RLS manually through the Supabase Dashboard leads to missed edge cases. Writing reproducible SQL regression tests with pgTAP ensures that authorization policies cannot silently regress during code deployments.

Create a reproducible test migration (e.g. supabase/tests/database/rls.test.sql):

BEGIN;
SELECT plan(5);

-- 1. Verify RLS is enabled and forced on critical tables
SELECT ok(
  (SELECT c.relrowsecurity AND c.relforcerowsecurity 
   FROM pg_class c 
   JOIN pg_namespace n ON n.oid = c.relnamespace 
   WHERE n.nspname = 'public' AND c.relname = 'documents'),
  'Documents table must have rowsecurity enabled and forced'
);

-- Seed test fixture data
INSERT INTO public.workspaces (id, name) VALUES (1, 'Workspace Alpha'), (999, 'Workspace Omega');
INSERT INTO public.workspace_members (workspace_id, user_id) 
VALUES (1, '11111111-1111-1111-1111-111111111111');
INSERT INTO public.documents (id, workspace_id, title) 
VALUES (101, 1, 'Alpha Confidential Document'), (909, 999, 'Omega Secret Document');

-- 2. Test Anonymous Role access (must return 0 rows)
SET LOCAL ROLE anon;
SELECT is_empty(
  'SELECT * FROM public.documents',
  'Anonymous users must not read any documents'
);

-- 3. Authenticate as User A (member of Workspace 1 only)
SET LOCAL ROLE authenticated;
SELECT set_config('request.jwt.claims', '{"sub": "11111111-1111-1111-1111-111111111111", "role": "authenticated"}', true);

-- Positive assertion: User A can read their own workspace documents
SELECT results_eq(
  'SELECT COUNT(*)::integer FROM public.documents WHERE workspace_id = 1',
  ARRAY[1],
  'User A must see documents in their own workspace'
);

-- Negative assertion: User A cannot read Workspace 999 documents
SELECT is_empty(
  'SELECT * FROM public.documents WHERE workspace_id = 999',
  'User A must not access Workspace 999 documents'
);

-- 4. Negative mutation assertion: Cross-tenant insertion is rejected
SELECT throws_ok(
  $$INSERT INTO public.documents (title, workspace_id) VALUES ('Spoofed Document', 999)$$,
  '42501', -- PostgreSQL insufficient_privilege error code
  NULL,
  'User A cannot insert documents into unauthorized workspace'
);

SELECT * FROM finish();
ROLLBACK;

Run these tests via the Supabase CLI in local development and continuous integration:

supabase test db

Next steps

Securing database policies is only one layer of defense in modern AI-generated architectures. For an end-to-end evaluation of your application's connection pooling, auth cookies, and client bundle secrets, review our production readiness guide for Lovable apps. To see how database authorization defects interact with payment webhooks and API route handlers under concurrency, explore our synthetic teardown lab for multi-tenant SaaS code rescue. If credentials or database URIs were exposed during prototyping, follow our containment runbook for secrets committed by AI coding tools.

When your team needs hands-on technical stabilization, database policy verification, or a full pre-launch audit, explore Aatvi's AI Code Rescue engineering services. We audit schemas, eliminate cross-tenant data leaks, and establish verifiable CI security gates.

Source notes

• The PostgreSQL Row Security Policies Documentation explains USING vs WITH CHECK semantics and catalog structure.
• The Supabase Row Level Security Guide outlines best practices for multi-tenant application design.
• The OWASP API Security Top 10 details Broken Object Level Authorization (BOLA) and multi-tenant data leaks.
• CWE-284: Improper Access Control catalogs the architectural consequences of unvalidated database authorization.