Postgres Row-Level Security for Multi-Tenant SaaS: Design and Pitfalls
Postgres row level security for multi-tenant SaaS: why 170 Lovable apps leaked, the owner-bypass trap, tenant policy design, and the test that catches leaks.
Row-level security didn’t fail in the Lovable incident. It was missing or misconfigured. In 2025, a researcher found that more than 170 apps built on Lovable were exposing user records; they reported it in March and published in May. Their Supabase tables had missing or permissive row-level security policies, so anyone holding the public anon key could read them. Getting row level security for multi-tenant SaaS right starts with knowing which state a table is actually in.
That distinction matters if you’re building multi-tenant SaaS on Postgres. The database did exactly what it was configured to do. The configuration said every row was visible to every caller.
What actually happened in the 170 apps?
Nobody broke into anything. There was no flaw in Postgres or Supabase to exploit. Each app had tables whose row-level policies were missing or allowed every caller, and the public key that ships in every browser bundle was enough to query them directly. Lovable apps are generated by an AI coding agent from natural-language prompts (Lovable), so the agent produced working apps, and nothing in its loop checked whether the wrong rows were readable.
Matt Palmer, the researcher, scanned 1,645 apps from Lovable’s public showcase. He found 170 with 303 vulnerable endpoints, about 10.3% of the sample, according to Superblocks’ write-up of the incident and the CVE-2025-48757 record. Palmer first reported it to Lovable on March 21, 2025, re-notified them on April 14, and published on May 29, 2025.
The exposed data was ordinary and sensitive at the same time: emails, usernames, phone numbers, subscription records, and API keys for third-party services. None of it needed an exploit. It needed a query.
Why is RLS being off by default dangerous?
Postgres creates every new table with row-level security disabled, so any role that has been granted SELECT on it reads every row. On Supabase, the anon and authenticated roles get those grants on the public schema by default, which is why a forgotten table is readable with the public key.
The PostgreSQL manual is blunt about the rule: when row security is enabled, access has to be allowed by a policy, and “if no policy exists for the table, a default-deny policy is used, meaning that no rows are visible or can be modified.” That sentence describes the safe state. The unsafe state comes one step earlier, in a table where ENABLE ROW LEVEL SECURITY never ran.
The two failure modes are mirror images, and both are silent. Enable RLS without a policy and every query returns zero rows. The app shows an empty dashboard, and nobody files a bug. Leave RLS off entirely and every query returns every row. The app works perfectly, and nobody notices the leak.
Many of the Lovable apps lived in the second state, and others had a policy that allowed every caller, which is the same leak with RLS technically on. Superblocks put the mechanism well: “When RLS is misconfigured, the database may return more rows than intended, or even the entire table.” The apps passed every functional test their builders ran, because functional tests ask whether the right rows come back. They almost never ask whether the wrong ones do.
Is the table owner the real hole?
Table owners are a second, quieter gap. The Postgres manual says owners “normally bypass row security,” and an owner has to opt in to the policy with ALTER TABLE ... FORCE ROW LEVEL SECURITY. Superusers and roles with BYPASSRLS bypass it no matter what.
This matters for multi-tenant portals because the migration role that creates the tables is usually the owner. If the application connects as that role, every policy you wrote is decorative. Forcing RLS on each tenant table closes that path. It’s one line of SQL, and it’s easy to forget, because nothing fails when it’s missing.
The same logic applies to the row_security setting. The manual says setting it to off “does not in itself bypass row security.” It only makes a query error out when a policy would have filtered its results. It’s a diagnostic, not a switch that opens the tables.
Does Postgres row-level security hurt performance?
Mostly when a policy column isn’t indexed or a policy calls a function on every row; wrapping auth.uid() in a subselect lets Postgres evaluate it once per statement, which Supabase measured at 179 ms down to 9 ms. Supabase’s row-level security guide and its performance troubleshooting page show that a bare auth.uid() = user_id re-evaluates the function for every row. Wrapping it as (select auth.uid()) = user_id lets Postgres cache the result for the statement. On a test table of 100,000 rows, Supabase reports the query dropping from 179 ms to 9 ms. Those are the vendor’s numbers on one table shape, so measure your own. The trick only works when the function doesn’t depend on the row. Index every column a policy filters on, starting with tenant_id; Supabase lists this as the first fix. The Supabase guide also notes that TO authenticated stops anonymous requests from evaluating the rest of a policy.
The strongest counterargument is that authorization belongs in application code, where it’s cheaper to reason about, and that a policy evaluated per row is a tax on every query. The performance half is partly true.
The application-layer argument falls apart on the Lovable evidence. The anon key let a client skip the front end entirely and talk to the database directly. Application checks only protect the paths the application takes. A portal that exposes its database to a browser needs the database to refuse on its own.
How should RLS policies be structured for a multi-tenant portal?
Give every tenant-owned table a tenant_id column with an index on it, have each policy compare that column against the tenant the current session claims, and keep the claim in a transaction-scoped setting. Then write separate USING and WITH CHECK clauses, and treat views and SECURITY DEFINER functions as places where RLS can be skipped.
Put the tenant in a tenant_id column on every tenant-owned table, and index it. Every policy then filters on that same column, so the tenant predicate reads the same way everywhere and is straightforward to test.
Read the tenant from current_setting('app.tenant_id', true), and set it with SET LOCAL or set_config('app.tenant_id', $1, true) inside each transaction. A plain SET persists for the whole database session. Behind a pooler in transaction mode such as PgBouncer, that server connection is handed to the next client, so one tenant’s value can leak into another tenant’s queries. The transaction-scoped form ends when the transaction does. The SET command reference describes the LOCAL form.
-- Tenant-owned table: enforce RLS, including for the table owner
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;
-- Rows are visible and writable only for the tenant set on this transaction
CREATE POLICY tenant_isolation ON invoices
USING (tenant_id = current_setting('app.tenant_id', true)::uuid)
WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::uuid);current_setting(…, true) returns NULL when the setting is missing, and NULL never equals a tenant_id, so a request that forgot to set the tenant sees zero rows instead of all of them.
Write USING and WITH CHECK as separate clauses, even when they contain the same expression. USING limits which existing rows a statement can read or change. WITH CHECK limits what an INSERT or UPDATE is allowed to write. Keeping them explicit makes it clear that a caller cannot move a row into another tenant.
Views need security_invoker = true. A view runs by default with its owner’s privileges, which skips the caller’s row-level policies on the underlying tables. Setting security_invoker = true (available from Postgres 15) makes the caller’s policies apply. See the CREATE VIEW reference.
SECURITY DEFINER functions have the same effect. The function runs as its owner and can bypass RLS, so each one needs its own tenant check inside the function body.
What does this look like in a client portal?
A client portal for agencies is an unusually exposed shape. AgencyHandy, the client portal software I build, keeps services, clients, projects, tasks, files, invoices, and payments in one place. (I wrote separately about what I would do differently if I started AgencyHandy today.) Data like that has to answer the same question for every row: which tenant owns this, and is the caller allowed to see it?
I’ve seen the same silent-success pattern in another system of mine. The Telegram Skip button did nothing for months, and every skipped item got counted as a timeout, so the approval stats were wrong and nothing flagged it. In the Twitter bot, four hardcoded handles that no longer existed soaked up 88% of comment attempts for about six weeks, and every run logged success. Those weren’t database failures, and I’m not claiming a causal link to RLS. The point is structural. When a component fails and produces output that looks like normal operation, the system keeps looking healthy until someone measures what it’s actually doing.
Row-level security has exactly that property, which is why the Lovable apps could ship with it missing.
What RLS alone doesn’t solve for a client portal. RLS protects rows, not file storage objects, unless storage has its own policies. In an agency portal, agency staff and client users inside the same tenant typically need different permissions, which means a second dimension beyond tenant_id. And cross-tenant tests only cover the tables the test list knows about, so a new table that nobody added to the list goes untested.
What single change would have prevented the leak?
A cross-tenant test that connects as anon and as tenant A, selects from every tenant table, and fails the build if any row belonging to another tenant comes back. It tests for wrong rows rather than right ones, so it catches leaks that ordinary feature tests never exercise.
Not a test that checks whether a user can see their own invoices. That test passes in both the safe and the leaking state. The test that would have caught Lovable connects as the anon role, with no session, tries to select from every tenant table, and asserts the row count is zero. A second connection, authenticated as tenant A, asserts it sees none of tenant B’s rows. Both assertions fail loudly on a leaking configuration, which is exactly the property the original apps lacked.
BEGIN;
SET LOCAL ROLE anon;
SELECT count(*) FROM invoices; -- expect 0
ROLLBACK;
BEGIN;
SET LOCAL ROLE authenticated;
SELECT set_config('app.tenant_id', '<tenant-a-uuid>', true);
SELECT count(*) FROM invoices WHERE tenant_id <> '<tenant-a-uuid>'::uuid; -- expect 0
ROLLBACK;Add FORCE ROW LEVEL SECURITY to the same migration, so the owner can’t bypass the policy the test is checking. Then run the test in CI on every schema change. If you run more than one product alone, a CI check like this is cheaper than remembering to look (I wrote about running three products as one person). The failure that cost 170 apps their user data becomes a red build instead of a headline.
Sources: