Lock down a public table (backend-only access)

What “Lock down a public table (backend-only access)” solves (and when it matters)

Prevents anon/authenticated clients from querying or mutating a sensitive table directly, reducing scraping and unauthorized access risk.

Use this when a table is reachable from the browser (or could be), especially if it contains user profiles, billing rows, invitations, or admin flags.

Before you apply “Lock down a public table (backend-only access)” (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

Copy the SQL block into Supabase Dashboard → SQL Editor. Replace public.profiles with your table. Then update your app to call a server endpoint instead of querying the table directly.

Template variations (choose the safest one that fits)

Immediate emergency lockdown

When to use: When you need to stop exposure quickly and you’re okay with temporarily breaking client features.

-- Emergency lockdown (deny all)
ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.profiles FORCE ROW LEVEL SECURITY;
REVOKE ALL ON TABLE public.profiles FROM anon, authenticated;

Lock down + keep admin access

When to use: When you have a backend job or admin tool that must continue to access the table.

-- Lock down for clients; backend continues via service_role
ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.profiles FORCE ROW LEVEL SECURITY;
REVOKE ALL ON TABLE public.profiles FROM anon, authenticated;
-- service_role remains server-only; no client grants needed

Implementation steps (practical)

1. Inventory where the table is accessed in your frontend and backend code.
2. Add a server-only endpoint (API route/server action) that implements the needed reads/writes.
3. Move authorization checks into the backend endpoint (validate user/session and ownership).
4. Apply the SQL hardening (enable+force RLS, drop policies, revoke grants).
5. Update the frontend to call your backend endpoint instead of direct Supabase table queries.
6. Add monitoring/rate limiting on the endpoint to reduce scraping and abuse.
7. Re-run your security scan and confirm the table is no longer reachable via anon/auth.

Verification checklist for “Lock down a public table (backend-only access)” (make sure the fix is real)

1. From a client session (anon/authenticated), attempt a direct SELECT: it should fail.
2. Confirm RLS is enabled and forced on the table.
3. Confirm there are zero policies on the table (or only the intended minimal ones if you intentionally deviate).
4. Confirm anon/authenticated have no grants on the table.
5. Verify the app still works through the backend endpoint with service_role.
6. Re-scan and ensure the public table issue disappears.
7. Review logs to ensure no unexpected spikes in denied queries.

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 “Lock down a public table (backend-only access)” (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

• Public Table Exposure → /glossary/public-table-exposure
• Row Level Security (RLS) → /glossary/row-level-security

Related integrations

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