Row-level security is one of those topics where the documentation is excellent and the real-world application is still full of surprises. On the Mandizo project we applied RLS to six tables, wrote a suite of SQL ownership tests that runs in CI on every change, and discovered several attack patterns that would have passed a read-only policy review. This post describes the patterns and what we would do again.
What Mandizo Needed from RLS
Mandizo is a GST-ready invoice generator for Indian small businesses. Users can create invoices as guests without an account. When they sign in, their invoices, clients and business details are saved to a Supabase Postgres database. The core requirement is simple: signed-in users must only see their own records. No user must be able to read, modify or delete another user's data, even by calling an RPC function directly with the anon key.
The application uses Firebase Auth alongside a Supabase Postgres database, which Supabase supports as a third-party auth provider. Because Firebase issues string UIDs rather than UUIDs, the RLS helper function reads the user identifier directly from the JWT sub claim as text rather than calling Supabase's built-in `auth.uid()`. The details of why that matters are covered in our auth migration post.
The Six RLS Tables
Rather than listing every column, which would expose schema detail that is not useful here, the relevant properties are the access pattern each of the six tables needed:
- User profile table: each row belongs to exactly one user. Only that user (or an admin) can read or write it. The per-user invoice number counter also lives here, so it inherits the same ownership rule.
- Invoice table: each row belongs to one user. SELECT, INSERT, UPDATE and DELETE are all filtered by owner. One deliberate exception: a public share link must let a non-authenticated visitor read one invoice by a random token, not by the user ID.
- Line items table: no direct owner column, so every policy checks that the parent invoice belongs to the caller.
- Client table: same as invoices but no public share path.
- Feedback table: anyone can submit feedback, but only an admin can read it.
- Analytics events table: events can be inserted by anyone, but only an admin can read them.
The public share path on the invoice table is the most interesting case. We did not solve it with a broad SELECT policy. The table stays owner-only, and a small `SECURITY DEFINER` function looks up one non-deleted invoice by its token and returns it with its line items. The token is a 32-character hex string generated server-side by the function that mints share links, and that function first checks the caller owns the invoice. Making an exception to an otherwise strict rule is where policies most often end up too broad, so this path gets its own tests.
Why RLS Enabled Is Not the Same as RLS Correct
Supabase's own guidance is to enable RLS on every table in an exposed schema, and it is a step you take yourself in the SQL editor or a migration. (Our post on AI-generated tables with no RLS covers how this gets missed.) Enabling it is only the first step. Supabase notes that once RLS is on, no data is accessible through the API with a publishable key until you create policies, and that a policy written as `to anon using (true)` lets every unauthenticated visitor read the rows. In other words, a permissive policy defeats the purpose of turning RLS on.
The risk we encountered was not disabled RLS but incorrectly scoped RPCs. A function marked `SECURITY DEFINER` runs with the privileges of the role that created it, which for the usual `postgres` owner means it bypasses RLS, and Supabase warns that such a function in an exposed schema is callable over the Data API with those privileges. A definer RPC that does not verify the caller's identity can therefore be called by any user, including an anonymous visitor with only the public key, and can read or modify any row. Before our fix, several of our RPCs were exactly this: they bypassed RLS and trusted whatever user ID they were passed.
The Ownership Test Suite
We wrote SQL tests that run in CI against a throwaway Postgres container loaded with our production schema baseline, using small plain-SQL assertion helpers. The tests cover four attack categories, each of which we considered a realistic threat given that the public key is embedded in the frontend bundle:
Attack 1: Caller uses their own UID with another user's email
The account-linking function accepts a UID and an email. An early version of this function looked up the email to find a legacy account and merged it with the new UID. An attacker calling the function with their own valid UID but the victim's email would have caused the victim's invoices to be reassigned to the attacker. The test seeds a victim with known invoices and an attacker with their own account, calls the function as the attacker with the victim's email, and asserts that the victim's invoices have not moved.
Attack 2: Caller uses another user's UID directly
Any RPC that accepts a user ID as a parameter must check that the JWT sub claim matches the parameter. If it does not, a caller can pass any UID they know and operate on another user's data. The tests call five RPCs (account link, client upsert, invoice number claim, invoice number increment, share token generation) as an authenticated user and pass a different user's UID as the parameter. Each call must throw `insufficient_privilege`, not succeed.
Attack 3: No JWT at all (bare anon key)
Any request to the Supabase REST API without a user token is treated as the `anon` role, which Supabase defines as an unauthenticated request. Tests call the same kind of RPCs with a JWT that contains only `{"role":"anon"}` and no sub claim, and every call must be rejected. This is the most common attack surface for publicly accessible Supabase projects, because the public key is by definition public.
Attack 4: Unverified email must not trigger a merge
Firebase tokens can carry an `email_verified` claim that stays `false` until the user confirms their address. If the account-linking function merges a legacy account based on an unverified email, an attacker who knows a victim's email can create a Firebase account with that email before the victim verifies it, and claim ownership of all of the victim's data. The test passes a JWT with `email_verified: false` and the victim's email, calls the linking function, and asserts that the victim's legacy invoices stay under the original owner while the caller still gets only their own new profile.
What We Would Do Again
- Write the ownership tests before going live, not after. The attack scenarios above are predictable given the architecture. We could have written them the moment we had a schema.
- Keep the test file in the repository and run it in CI. Tests that live in a scratchpad provide no ongoing protection.
- Use a text-based user ID helper from the first migration, not the built-in auth.uid(). The cost of retrofitting auth.uid() calls across policies and functions is high.
- Test the public share path explicitly. It is easy to write a policy that is too broad when you are making an exception to an otherwise strict rule.
- Check every `SECURITY DEFINER` RPC. Any such function that does not validate the caller is a potential privilege escalation, as the Supabase RLS guide also warns.
What We Would Do Differently
- We set up the auth-migration-related tests after the production symptoms appeared rather than before the migration ran. The test suite now covers the post-migration state, but it would have been more useful as a pre-migration gate.
The 37 Automated Security Checks
The Mandizo CI pipeline runs 37 automated security assertions on every change, in two SQL suites that run in a CI job separate from the build: 20 ownership assertions (the attack scenarios above plus normal-use checks such as minting and reusing a share token) and 17 assertions covering later hotfixes (profile-insert privilege escalation, fractional quantities and deleted invoices behind share links). A failing assertion fails the pipeline.
You can check your own Supabase project's RLS coverage with the free code review tool, which checks for common Supabase configuration problems including missing RLS. For a deeper review of your specific policies and RPCs, we offer a technical review that covers data ownership and access rules.
For more on how to verify your own RLS setup from the outside, testing with the anon key is the fastest starting point. For what the UpGuard study found about RLS gaps across Supabase projects in the wild, see our analysis of that study.
Common questions
Is enabling row-level security enough to protect my Supabase data?
No. Once RLS is on, no data is accessible through the API with a publishable key until you create policies, and a policy written as 'to anon using (true)' lets every unauthenticated visitor read the rows. SECURITY DEFINER functions that trust a passed user ID can also bypass RLS.
How do I test that one user cannot read another user's data in Supabase?
Write ownership tests that run in CI. Mandizo's tests call RPCs with another user's UID, with no JWT at all (a bare anon key), and with an unverified email, and assert that each call is rejected or leaves the victim's data unmoved.
Can someone review my Supabase policies and RPCs?
You can check RLS coverage with the free code review tool, which checks for common Supabase configuration problems including missing RLS. For a deeper look at your specific policies and RPCs, Zovintra offers a technical review that covers data ownership and access rules.
Sources
- Supabase: Row Level Security - Supabase docs, accessed 19 Oct 2026
- Supabase: Firebase Auth as a third-party provider - Supabase docs, accessed 19 Oct 2026
- Mandizo SQL ownership and hotfix test suites and CI configuration - Mandizo repository (private), reviewed 19 Oct 2026
- Mandizo project page - Zovintra, accessed 19 Oct 2026
