How I tested Row Level Security before shipping a SaaS starter kit (so one user can't see another's data)

When constructing a multi-tenant application, one of the most critical bugs to avoid is a user inadvertently viewing another user's data. This privacy failure far surpasses any other issue, such as a crash or broken button. To ensure the security of the database layer, I recently created a next.js, supabase, and stripe starter kit.

Before deeming the database layer complete, I needed to confirm that the security measures were effectively in place, rather than merely assuming that everything was correct due to the code appearing to be right.

Supabase's row level security (RLS) allows for the direct writing of policies in Postgres that filter rows according to the identity of the user making the request. The database becomes the security boundary, rather than relying solely on application code. While powerful, this approach means that a single incorrect policy (or the absence of a policy) could inadvertently expose all data. In order to verify the effectiveness of the policies, I employed a specific policy pattern for an owner-scoped table:

create policy "projects: owner reads" on public.projects for select to authenticated using ((select auth.uid()) = owner_id);

While the policy may seem straightforward, it is essential to verify it rather than merely trusting it. The actual test involved running an impersonation test within the SQL editor. By briefly pretending to be a specific user (inside a transaction that does not affect any data), I could check what that user would actually see. If the test was performed using the real owner's ID, the expected result was their own rows.

However, if a different, fake, or nonexistent user's ID was used, the result should have been zero rows returned, not an error. This positive outcome, rather than an error, indicated that the Row Level Security (RLS) policy was functioning correctly and effectively filtering out unauthorized data.

The same check was conducted for write operations, such as attempting to delete another user's row from a different account's session. This ensured that the deletion operation also respected the RLS policy and returned 0 rows affected instead of either successfully deleting the row or throwing an error. This meticulous testing process took only a couple of minutes but could catch potential failures that might only be discovered in a production environment, involving real user data.

This approach is particularly crucial for anyone building a similar application. Here are a few key points to keep in mind:

- Test with at least two different accounts to ensure that the policy works as intended for legitimate owners and blocks access for other users.

- Perform separate tests for both read and write operations. A table can have a working select policy but may have a broken or missing delete policy.

- The success condition for a negative test often involves silence, such as returning 0 rows, rather than encountering an error. Understanding what success looks like is paramount before executing the test.

I compiled this testing pattern, along with tested Stripe billing and authentication, into a small starter kit available at https://liviug.gumroad.com/l/saas-starter. The RLS testing pattern, as discussed, is beneficial even beyond the context of this specific starter kit.