Why We Didn’t
Just Use RLS
How database security actually works when an AI model is your trusted coder — and why Row Level Security alone isn’t enough.
Context
The standard Supabase security model
In a typical Supabase app, security is well-understood and elegant. Three things work together:
Anon Key + JWT
The client uses a public anon key. The user authenticates, gets a JWT, and the key + JWT travel with every request.
Row Level Security (RLS)
PostgreSQL policies on each table check the JWT claims. A user can only see rows that match their identity — enforced at the database layer.
Service Role Key (server-side only)
Admin operations use the service-role key. It lives in a .env file on the server, never exposed to the client, and the developer is trusted to handle it responsibly.
This works perfectly — when you have one database, one type of caller, and the service-role key stays firmly in the hands of a trusted developer. The moment either of those conditions changes, the model starts to break down.
The Problem
Three things that break the standard model
None of these are edge cases. All three apply to this platform simultaneously.
Multi-database reality
This platform serves multiple clients. Some have their own Supabase instance — separate URL, separate keys. The central chat orchestrator needs to query their data.
The gap
RLS can’t span two servers
The orchestrator lives on the central server. When querying a client’s Supabase, it cannot authenticate as that client’s end-user — there’s no anon-key + RLS path that works across two separate Supabase backends.
The problem isn’t RLS itself. It’s that RLS is per-database. The moment you have client data on a separate Supabase instance, you’ve already outgrown the model.
The AI is now your trusted coder
Service-role keys were designed with a specific trust model in mind. The human developer
holding the key is trusted. It lives in a .env file.
It never gets hardcoded. The developer understands the implications.
What’s changed
In a vibe-coding environment, the AI model is the coder. It has the service-role key in the environment. And the person directing it may be a non-technical staff member who has no idea what a service-role key even does.
“Once a model has a service-role key, RLS is bypassed by definition. The model routinely reaches for it when a query fails — it’s just solving a problem.”
RLS isn’t a deterrent to an AI. It’s a speed bump the model routes around automatically, without malicious intent, and without the human director realising it happened.
Some tables need different rules for humans vs. AI
RLS is identity-aware, but it only knows about user identity, not caller type. It can answer “is this user allowed?” but not “is this an AI or a human?”
The canonical example: app_logs
Humans (Logs App): Can query app_logs directly — the app is built to paginate and filter efficiently.
AI Models: Must not query app_logs directly — doing so pulls the entire table into context, burning tokens.
AI should instead call: get_logs() — a function that pre-filters before returning anything.
There is no auth.is_ai() in PostgreSQL. RLS has no concept of whether the request came from a browser or a model. This distinction can only be enforced outside the database.
The Solution
Introducing supabase-router
A single, unified permission boundary that sits in front of all database traffic.
Every .from() and .rpc() call goes through it.
Callers point a normal supabase-js client at the router endpoint —
no new SDK, no new patterns.
Hosted at: functions.bitcreative.com.au/supabase-router
Solves Problem 1 — Multi-database
The router holds credentials for every client backend in remote_databases. It routes each request to the right server based on central metadata for that org. No cross-server RLS required.
Solves Problem 2 — AI has service keys
The router is the only thing with backend credentials. Client app environments get a router endpoint URL — no service-role key in any .env. An AI model that doesn’t have a key can’t misuse one.
Solves Problem 3 — Human vs. AI rules
The router knows the caller via x-user-id. It can enforce different table access rules for AI callers (system identity 999) vs. real users, and redirect models to safer RPC wrappers before the request reaches the database.
No implicit defaults. Misconfigured or missing metadata raises an error rather than silently routing somewhere unexpected. Failures are loud by design.
Mechanics
How the router routes
Every request carries headers. The router uses these — plus central metadata — to decide where the request goes and whether it’s allowed.
| Request type | Routed by |
|---|---|
| Table CRUD / DDL | crm_tabs.fdw_server_name for that table |
| Generic RPC / function | settings.fdw_server_name for the org |
| Create new object | settings.fdw_server_name (no crm_tabs row yet) |
| Control-plane RPCs (e.g. add_crm_tab) | Central — always |
execute_query_flexible
The supported RPC for complex SQL (joins, CTEs, window functions). Always pass table_names — the router validates your declared tables match the SQL body before dispatch. Direct calls to the underlying execute_query_simple are blocked at the router boundary.
Key Benefit
Service-role keys are out of your codebase
Because the router holds all backend credentials centrally, client app environments no longer need a service-role key at all.
Before
Each app’s .env holds a service-role key. Multiply by the number of app environments. Each is a potential exposure point — in logs, in accident pastes, in source control.
After
App environments only hold the router endpoint URL. The service-role keys live inside the router — never in app code. Rotate a key once; every automation using it picks up the change immediately.
AI can’t expose a key it doesn’t have
A model operating in the app environment has no service-role key to accidentally paste into a chat, log to console, or commit to source control. The risk category doesn’t exist.
AI can’t do destructive things with a key it doesn’t have
Even if a model is misdirected by a non-technical user, it cannot bypass the router’s permission layer — because it has no credentials to go around it with.
One exception
The anon key is still present in each app’s .env. A direct call to central Supabase is needed to resolve user identity from the Auth JWT before the router client can be initialised with a user ID. That single auth call is the only direct backend contact the app makes.
Identity
Who is making this request?
The x-user-id header is how the router knows who is responsible for a request. Every call has one of two valid identities:
| x-user-id | Who | When to use |
|---|---|---|
| 42 (real ID) | Authenticated user | Normal logged-in requests. The hook resolves the Supabase Auth JWT to a central users.id, then sets this on the router client. All subsequent DB requests carry it. |
| 999 (system) | System / Automation | Scheduled cron runs, webhook endpoints that have already passed their own secret check, and diagnostic router tests. Not for use in human user sessions. |
Auth split — by design
locals.supabase.auth.* uses central Supabase (anon key). locals.supabase.from() uses the router.
They go to different places. This is deliberate, not an accident.
The hook sequence
- Request arrives
- Central Supabase Auth resolves JWT →
users.id - Router client is initialised with that ID
- All DB calls from here use the router
Limitations — Important
What the router cannot know
The router is an HTTP edge function. It sees headers and request bodies. There are things it structurally cannot determine — those rules must be enforced upstream by the caller.
Automation vs. user session
A scheduled cron job and a logged-in user both send HTTP requests with headers. The router cannot tell them apart. Attribution and identity discipline live in the cron-runner and the manual-trigger endpoint — not the router.
Advisory lock session state
PostgreSQL advisory locks are session-scoped. The router routes requests over HTTP — there’s no guarantee the same DB connection handles the lock and the work it protects. Advisory locks must go through centralSupabase directly, not through the router.
Caller honesty
The router validates what it can see: org scope, table registration, SQL body vs. declared table names. It cannot verify that the caller is accurately representing who they are. That’s the caller’s responsibility — documented, not assumed.
Advisory lock pattern
Developer Contract
What gets enforced where
- → Org scope — request must carry a valid x-org-id
- → Table registration — every queried table must exist in crm_tabs; unknown tables are rejected
- → SQL body validation — declared table_names are checked against the SQL in execute_query_flexible
- → Blocked RPCs — execute_ddl_command and direct execute_query_simple calls are blocked
- → User identity resolution — organisationGuard resolves the Auth JWT → users.id using the central client, then calls setRouterUserId() so all subsequent DB requests carry the correct x-user-id
- → Public API routes — routes that skip organisationGuard must establish their own identity and use a real user ID or system identity 999 explicitly
- → Use locals.supabase for all normal DB work after hooks have run — this is the router client
- → Use the central client (anon key) only for auth — resolving users.id from the JWT before the router client has a user ID. This is the only direct call to Supabase the app makes
- → Never fetch client Supabase credentials and call the client backend directly — route through supabase-router
- → Identity attribution — use config.chat_session_user_id for automations that act for a specific user; use 999 for org-wide automations. This cannot be enforced by the router
- → Concurrency control — framework guards prevent same-path concurrent runs; advisory locks via centralSupabase protect cross-process scenarios
Summary
The key takeaways
AI is the new trusted coder
RLS relied on trusted humans keeping service keys safe. In a vibe-coding environment that trust model doesn’t hold — the AI has the key and is directed by non-coders.
RLS is not a real boundary here
Models use service-role keys. That bypasses RLS by definition — and the model does it automatically when blocked. It’s not malicious, it’s just problem-solving.
supabase-router is the boundary
Every DB call goes through it. It validates org scope, table registration, and SQL bodies — and routes to the right backend. No implicit defaults; failures are loud.
Service keys are out of app code
No service-role key lives in any app’s .env. The router holds backend credentials. An AI that doesn’t have a key can’t expose or misuse one.
One direct call remains — auth only
The anon key resolves user identity from the Auth JWT. That’s the single permitted direct call. Everything else goes through the router.
Auth and DB are deliberately split
locals.supabase.auth.* uses central Supabase. locals.supabase.from() uses the router. They go to different places. This is by design, not accident.