Row-Level Security Design for Multi-Tenant Analytics in Supabase

A user logs into an analytics dashboard, runs a query, and sees rows belonging to an organization they were never added to. That failure traces back to a single design assumption: most Supabase row-level security policies filter on auth.uid() = user_id, a pattern built for personal data ownership, not shared organizational data. It works well when the question is "can this person see their own row." It says nothing about the harder question multi-tenant analytics actually asks, which is whether a person belongs to the group that owns a whole set of rows. Without RLS turned on at all, the exposure is worse than a logic bug: any caller holding the anon key, unauthenticated visitors included, can read, update, or delete every row in a table through the PostgREST API, because the database itself applies no filter and no amount of middleware checking in application code can stand in for that missing enforcement. Analytics tables raise the stakes further, because they aggregate activity across many users by design, so one missing or miswritten policy doesn't just leak a single record. It exposes an entire tenant's metrics to any authenticated session that happens to query the table.

The org-level isolation schema: accounts, team_members, and org_id foreign keys

Closing that gap requires three tables working together. An accounts table holds one row per tenant. A team_members table joins auth.users to accounts and carries a role column, recording who belongs to which organization and what they're permitted to do inside it. Every data table in the system, including every analytics table, carries an account_id foreign key that points back to accounts, with RLS turned on. The resulting RLS policy checks membership rather than identity: account_id IN (SELECT account_id FROM team_members WHERE user_id = auth.uid()). Any member of the account sees the account's rows. Anyone outside it gets zero rows back, not an error, and that matters for how client applications need to handle empty results versus failures. This single subquery is the actual isolation mechanism in the whole system: the authorization boundary is organizational membership, not individual identity, and every other decision in the schema follows from that shift. Some teams encode membership as a claim inside the JWT itself rather than doing a live table lookup, and that trades a round-trip to team_members for a token that must be reissued when membership changes. The membership-join version stays accurate the moment a role changes in the database; the claims version is faster to check but can go briefly stale until the user's session refreshes. Which one to use depends on how often membership changes and how tolerant the application is of a short delay between a permission change and its enforcement.

Four performance traps that silently degrade RLS at analytics scale

A membership-based policy that returns instantly on a thousand-row table can turn into the slowest part of a query plan once that table holds tens of millions of rows, and none of the four common causes show up as a logic error.

The first trap is per-row subquery execution. Written naively, IN (SELECT account_id FROM team_members WHERE user_id = auth.uid()) gets re-run for every row the planner evaluates, turning a membership check meant to run once into a cost multiplied by table size. The fix wraps the same lookup in a STABLE, SECURITY DEFINER function and calls it inside a subquery, such as IN (SELECT get_accessible_account_ids()), and that tells Postgres to evaluate the function once as an InitPlan for the life of the query rather than once per row. The function itself is simple: CREATE OR REPLACE FUNCTION get_accessible_account_ids() RETURNS SETOF uuid LANGUAGE sql SECURITY DEFINER STABLE AS $$ SELECT account_id FROM team_members WHERE user_id = auth.uid() $$;. Marking a function STABLE on its own does nothing for performance here; it's the subquery wrapper around the call that gives Postgres the opening to cache the result.

The second trap is missing indexes on the columns RLS policies actually check. Because the policy expression functions as an implicit WHERE clause on every query against the table, a missing btree index on account_id, or on team_members(user_id), forces Postgres into a sequential scan each time, regardless of how well the policy logic itself is written. Three indexes close this gap: CREATE INDEX ON issues(account_id), CREATE INDEX ON team_members(user_id), and CREATE INDEX ON team_members(account_id).

The third trap involves auth.uid() called directly instead of wrapped. Writing auth.uid() = user_id forces Postgres to evaluate that function separately for every row under consideration. Writing (SELECT auth.uid()) = user_id instead lets Postgres evaluate it once and reuse the cached value for the rest of the query, the same InitPlan mechanism behind the fix for trap one.

The fourth trap is a membership check you write in the wrong direction. A policy like (SELECT auth.uid()) IN (SELECT user_id FROM team_members WHERE team_members.account_id = data_table.account_id) correlates the subquery against every row in the source table, re-running the lookup each time. The faster form inverts the relationship: pull the set of accounts the user belongs to once, then filter the data table against that fixed set, instead of re-running a correlated subquery for every row scanned.

What RLS cannot enforce: column-level access and UPDATE integrity

Row-level security governs which rows a user can touch, and it has nothing to say about which columns within those rows they can read or change. A policy that lets team members update a row in an accounts table grants them write access to every column on that row, including fields that should never be editable from the client, such as primary_owner_user_id, subscription_tier, or billing_status. WITH CHECK clauses can confirm that a proposed new value satisfies some condition, but they have no mechanism for comparing a new value against the old one, so preventing a specific field from ever changing after the row is written calls for a different tool. That tool is column-level GRANT statements layered on top of RLS: grant SELECT on specific columns to the authenticated role, and withhold UPDATE privilege on the sensitive ones. For analytics tables in particular, the cleanest design usually restricts INSERT and UPDATE to server-side processes and grants the authenticated role SELECT only, since analytics rows are meant to be written by a controlled pipeline and read by users, not edited by them. One more detail is easy to miss in testing: when a query joins two tables that each carry their own RLS policies, those policies are evaluated independently per table, so if either one fails to match, the join silently returns fewer rows rather than throwing an error. You have to test that behavior directly at the database level, since the application will not necessarily reveal it.

The service role key is the most dangerous footgun in a multi-tenant analytics build

Supabase issues two API keys built around opposite assumptions about trust. The anon key respects every RLS policy in the database and is safe to put in browser code or anything else exposed to the client. The service role key bypasses RLS entirely, operating as a privileged database role with the BYPASSRLS attribute (distinct from, and more limited than, a true PostgreSQL superuser), and any client initialized with it ignores every tenant isolation policy in the schema. So a single misconfigured analytics query run with the service role key can read every tenant's data across the entire system, and the danger compounds in development, where a developer's own test account often already has access to every row, so the bug produces no visible symptom until it reaches production with a real tenant's data on the other side of it. The service role key has narrow, legitimate uses: running migrations, powering background jobs that genuinely need cross-tenant reach (sending batched emails, recomputing organization-wide aggregates), and admin operations that explicitly require visibility across tenants. The rule that follows is simple to state and easy to violate under deadline pressure: API routes serving user-facing requests use the anon key paired with the user's JWT, full stop, and the service role key stays confined to server-side code for internal jobs and admin operations, never shipped anywhere a client can reach it.

RLS policy interaction with analytics query patterns: aggregation, cross-org reporting, and admin views

Most RLS documentation illustrates policies with simple SELECT statements that return individual rows, but analytics workloads run aggregations, and the policy still applies to each row before any aggregation happens. Only the rows a user's policy permits flow into a COUNT, a SUM, or a GROUP BY; everything else is excluded before the math runs, not after. For a standard per-tenant dashboard, that's exactly the behavior you want: a member of one organization aggregating usage data gets totals built only from that organization's rows, and another organization's data is excluded from the result set with no error or warning explaining why. Cross-org admin reporting, the kind that rolls up metrics across every tenant on the platform at once, can't run through that same anon-key, org-scoped-policy path at all, since no single user's membership record spans every tenant. So you need to run those queries server-side with the service role key, in a code path that stays architecturally separate from anything a user-facing API route can trigger. Testing any of this matters more than it might seem from the outside, because the SQL editor built into Supabase bypasses RLS entirely, so a query that looks correct there can still fail silently for a real user. Correct testing sets the role and JWT claim explicitly inside a transaction: BEGIN; SET LOCAL role = 'authenticated'; SET LOCAL "request.jwt.claims" = '{"sub": "user-uuid"}'; SELECT...; ROLLBACK;. Application behavior is not a reliable proxy for policy correctness: a UI that happens to show the right data can still be running an under-permissioned or over-permissioned query underneath. When a table carries multiple permissive policies for the same operation, Postgres combines them with OR, so a user who satisfies any single policy gains access, and an analytics table carrying both a user-scoped policy and an org-scoped policy side by side needs to be designed with that OR logic in mind, or the looser of the two policies quietly becomes the effective one.

Separating analytics workloads from transactional queries on Postgres

Even a perfectly indexed, correctly written set of RLS policies doesn't resolve a separate problem: analytical queries that scan large numbers of rows, join across tables, and aggregate results compete directly with transactional queries for the same CPU, memory, and I/O on a shared Postgres instance. Supabase Read Replicas offer one layer of relief, distributing read traffic away from the production primary and protecting transactional throughput, though a replica is still running Postgres and doesn't inherently make heavy analytical queries faster at scale. Supabase Pipelines, in Public Alpha, addresses the problem more directly by moving data into systems purpose-built for analytical workloads, keeping those heavy queries off the production database altogether; the first supported destination for Pipelines is Google BigQuery. The practical threshold is straightforward to recognize once a system reaches it: when analytics queries are routinely scanning very large numbers of rows, transactional latency degrades no matter how well-tuned the RLS policies are, and the fix at that point is architectural separation between analytical and transactional workloads, not another round of query optimization.

Governed, pre-modeled datasets as the analytics layer that RLS makes possible (and AI agents require)

Done well, org-level RLS produces something more durable than a security control: a predictable, policy-enforced boundary around each tenant's data that any system consuming it, whether a human-facing dashboard, an API call, or an AI agent, can rely on without reimplementing the access logic itself. That boundary turns out to matter most for the newest category of consumer: agents that query data directly. An agent connected straight to a production Postgres database runs into a structural mismatch, since transactional databases aren't built for the aggregation, joining, and lookback queries that agent context typically demands, and running those queries at agent speed drags down performance for the operational application underneath it. Worse, without a semantic layer sitting between the agent and the database, the agent can reach any table it holds technical permission to query, unable to tell data it was meant to access apart from data it merely can access, so it produces confident queries against datasets it should never have touched.

The fix mirrors the same pattern this piece has described for human users: pre-modeled, pre-calculated datasets, queryable through DuckDB against governed Parquet files and exposed through a single MCP server, give agents fast and accurate context without ever touching the production database, with the RLS-enforced org boundaries on the source data determining which rows flow into each tenant's governed dataset. MCP, the Model Context Protocol, is an open standard for connecting models and agents to external tools and data through one consistent interface, but the protocol itself makes no claim about correctness. An agent connected by MCP to a set of uncertified tables still gives you confident, wrong answers, because MCP only standardizes the connection; correctness comes from the semantic and governance layer applied to the data it exposes, which is what makes that connection trustworthy rather than merely convenient. Because MCP is stateless, identity has to be handled explicitly rather than assumed: the server needs to receive or resolve identity on every request, apply policy against it, and log the result, instead of leaning on hidden session state or a broad service credential shared across requests, the same discipline this piece described earlier for the service role key. Least-privilege design for multi-agent systems follows the identical logic as org-scoped RLS: agents should never hold global API keys, and scoping each agent's tools narrowly, so a CRM agent can't reach HRIS data and a finance agent can't modify CRM records, is the direct agent equivalent of an org-scoped policy. The risk doesn't disappear once each individual agent is scoped correctly, either: when agents orchestrate other agents, data can end up combined in ways that violate compliance requirements even though every agent involved stayed within its own stated permissions, as in the documented pattern of a compliance reporting agent ingesting unmasked PII from another agent's intermediate output. If you build org-level RLS correctly, from the accounts and team_members tables up through governed, pre-modeled datasets, that is what keeps the boundary intact for every consumer standing on the other side of it.