Convert a public table to backend-only access

From → To (what you’re converting)

From: Table is readable/writable from anon/authenticated clients (direct REST queries succeed).

To: Table is not directly accessible from client roles; access only through server endpoints using service_role.

Safety notes for Convert a public table to backend-only access (order of operations)

Conversions are safest when you change application call paths before you revoke access.

1. Add the backend endpoint that will replace the direct Supabase call.
2. Switch the frontend to call your backend endpoint (feature flag if needed).
3. Revoke direct client privileges (grants, bucket access, or RPC execute).
4. Verify: direct access fails; backend endpoint succeeds.

Conversion logic (step-by-step)

1. Identify all frontend code paths that query the table directly.
2. Add backend endpoints that implement the needed reads/writes with authorization.
3. Enable and force RLS on the table to prevent bypass.
4. Drop broad or legacy policies (or adopt zero-policy posture).
5. Revoke all grants from anon/authenticated on the table.
6. Update the frontend to call your backend endpoint instead of Supabase directly.
7. Verify direct access fails from client credentials and the app still works end-to-end.

Example conversions

Example 1

Input: Client can run SELECT on public.profiles and fetch all rows.

Output: Direct SELECT fails; /api/profiles returns only the current user’s safe fields.

Notes: The backend endpoint enforces ownership and redacts sensitive columns.

Example 2

Input: Authenticated users can INSERT into invitations table without limits.

Output: Direct INSERT fails; /api/invitations enforces rate limiting and validation.

Notes: Server logs invite events for abuse detection.

Verification checklist for Convert a public table to backend-only access

• Reproduce the risky behavior once before changing anything (so you can prove the fix).
• After the conversion, the same direct access test fails (401/403).
• The user-facing feature still works through backend endpoints.
• A scan/checklist re-run no longer flags the exposure.
• You recorded the rule so future migrations don’t reintroduce direct access.

Related converters (what to do next)

• Convert a public RPC to service_role-only → /conversions/public-rpc-to-service-role-only — A conversion playbook to revoke EXECUTE grants from public roles and expose RPC functionality only through backend endpoints.
• Convert a public Storage bucket to private + signed URLs → /conversions/public-bucket-to-private-signed-urls — A conversion playbook to stop public downloads and move to private buckets with backend-generated signed URLs.

Related templates

• Lock down a public table (backend-only access) → /templates/access-control/lock-down-public-table

Related glossary terms

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

Common pitfalls during conversions

• Revoking access before the backend endpoint is deployed (causes outages and rollback pressure).
• Leaving one legacy client call path in place (exposure still exists).
• Assuming “authenticated users” are authorized without ownership/tenancy checks.
• Fixing only one environment and forgetting staging/prod (drift).

If you must keep some client access during Convert a public table to backend-only access

Sometimes you can’t move everything server-side immediately. If you keep any client access temporarily, make it an explicit, reviewed exception.

• Keep the exception narrow: one operation, one resource, one documented reason.
• Add an expiration date and a follow-up task to remove the exception once the backend path is ready.
• Add extra verification: tests for cross-user/tenant access and direct access attempts that must fail outside the exception.
• Prefer least privilege (specific grants/policies) over broad authenticated access.

This keeps “temporary” from becoming permanent drift.

What to document after the conversion (prevents regressions)

• Which resource you converted (table/bucket/function) and the exact change in access model.
• Which direct access test proves the conversion worked.
• Which backend endpoint now owns the privileged operation and what authorization it enforces.
• Which drift guard you will re-run after migrations.