Remove over-permissive RLS policies (adopt deny-by-default)

What “Remove over-permissive RLS policies (adopt deny-by-default)” solves (and when it matters)

Eliminates policies that unintentionally allow access to too many rows or actions, reducing silent leaks and authorization bypasses.

Use this when you have multiple policies, unclear conditions, or a policy that isn’t tightly bound to auth.uid() or tenant ownership.

Before you apply “Remove over-permissive RLS policies (adopt deny-by-default)” (safety prerequisites)

• List every place your frontend currently calls Supabase directly for this surface (table, Storage, or RPC).
• Decide the backend endpoint(s) you will add so the app still works after you revoke client access.
• Confirm which environment you’re changing (dev/staging/prod) and plan to repeat the same verification in each.
• If this is production: plan a staged rollout (feature flag, canary) so you can roll back app behavior quickly without reopening exposure.

Download / copy‑paste instructions

List existing policies for the table, drop the ones that are too broad, and route access through server-only endpoints. If you adopt a strict zero-policy posture, remove all policies and revoke grants.

Template variations (choose the safest one that fits)

Remove one risky policy

When to use: When you know exactly which policy is too broad and want a small change.

-- Drop one policy (replace policy + table)
DROP POLICY IF EXISTS "allow_all" ON public.orders;

Adopt zero-policy (deny all)

When to use: When you want the simplest security posture: no direct client access, everything backend-only.

-- Enable+force RLS and remove all policies
ALTER TABLE public.orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.orders FORCE ROW LEVEL SECURITY;
DO $$
DECLARE pol RECORD;
BEGIN
  FOR pol IN
    SELECT policyname FROM pg_policies
    WHERE schemaname='public' AND tablename='orders'
  LOOP
    EXECUTE format('DROP POLICY IF EXISTS %I ON %I', pol.policyname, 'orders');
  END LOOP;
END $$;
REVOKE ALL ON TABLE public.orders FROM anon, authenticated;

Implementation steps (practical)

1. Identify tables with many policies or unclear authorization rules.
2. List policies and classify them: least-privilege vs broad/unclear.
3. Remove policies that are too broad (or adopt zero-policy posture).
4. Revoke grants that allow direct client access.
5. Move reads/writes to backend endpoints that enforce auth and business rules.
6. Add verification queries to ensure no drift reintroduces broad policies.
7. Re-scan and confirm permissive policy issues are resolved.

Verification checklist for “Remove over-permissive RLS policies (adopt deny-by-default)” (make sure the fix is real)

1. List remaining policies and confirm each ties rows to a user/tenant (or zero-policy).
2. Confirm anon/authenticated cannot access the table directly.
3. Confirm your backend endpoints enforce authorization checks.
4. Attempt ID enumeration from a client; it should not return cross-user data.
5. Review audit logs/metrics for denied access attempts.
6. Re-run scans after each migration affecting the table.
7. Document the intended access model for the team.

Common pitfalls (what breaks in real projects)

• Applying SQL but leaving the frontend calling Supabase directly (the exposure remains).
• Locking down one surface (tables) but forgetting Storage/RPC exposure.
• Fixing in dev but not re-checking staging/prod (config drift).
• Not adding verification steps, so regressions ship unnoticed.
• Treating service_role like a convenience key instead of a server-only secret.

Rollback plan for “Remove over-permissive RLS policies (adopt deny-by-default)” (without reopening exposure)

If the app breaks after you lock down direct access, the safest rollback is to restore application behavior (backend endpoints) — not to re‑grant direct client access.

A practical sequence:

1. Restore or hotfix the backend endpoint to return the needed data for authorized users.
2. Add temporary logging for denied direct access so you can spot missing code paths.
3. Only if absolutely necessary: create a narrowly scoped, time-limited policy for a specific table/bucket/function and remove it after the backend path is fixed.

Related glossary terms

• Over-permissive RLS Policies → /glossary/over-permissive-rls-policies
• Row Level Security (RLS) → /glossary/row-level-security

Related integrations

• Next.js backend-only Supabase access → /integrations/nextjs-backend-only-supabase