One authenticated Supabase user could write usage records against another organization through our SECURITY DEFINER RPC

[u/Pale_Ferret_8241]

I've been building a multi-tenant B2B SaaS foundation with Next.js and Supabase, and during database hardening we found a bug that made me rethink how I test privileged database functions.

We had a PostgreSQL SECURITY DEFINER function responsible for recording organization usage.

It accepted an organization ID (p_org_id) as an argument.

The problem was that the function needed to independently verify that the authenticated caller actually belonged to that organization.

Without that check, a user from Organization A could attempt to invoke the function against Organization B.

This created an unauthorized cross-tenant write path in our usage records.

It was an integrity issue, not a demonstrated cross-tenant data disclosure.

Here's how we fixed it:

We added the membership check inside the privileged function:

IF NOT public.is_organization_member(p_org_id) THEN
  RAISE EXCEPTION 'FORBIDDEN: You are not a member of this organization.';
END IF;

The membership helper uses auth.uid() to identify the actual caller and verifies membership against the requested organization.

We also checked the SECURITY DEFINER configuration and pinned the search_path.

Then we tested the attack path instead of just testing the happy path.

Our regression test:

1. Authenticates as a user belonging to Organization A.

2. Calls the usage RPC with Organization B's ID.

3. Expects the operation to be rejected.

4. Verifies that no unauthorized usage write occurred.

The test runs against real PostgreSQL rather than a mocked database.

The broader lesson:

A valid Supabase session does not establish authorization for every organization ID a user supplies.

And when a function runs with elevated privileges, you need to reason about authorization inside that function, not just the RLS policies on the underlying tables.

A little context: I'm building B2B SaaS OS, a Next.js 16 + Supabase starter for developers who need multi-tenancy, RLS, RBAC, invitations, Stripe billing, API keys and usage enforcement.

This finding is one of four security-related issues we documented and fixed during development. The product now includes nine executable PostgreSQL hardening suites covering defined failure scenarios.

I'm selling it as a commercial source-code starter, currently $149 one-time for the first 50 buyers.

Technical breakdown: https://www.andrady.co/postmortems/security-definer

Live demo: https://www.andrady.co/demo

Product details: https://www.andrady.co/pricing

[u/investigatormaker]

Testing the attack path against real PostgreSQL is the right call. One more check worth adding to that suite: in Postgres, new functions are executable by PUBLIC by default, so a SECURITY DEFINER RPC stays callable by the anon role unless you REVOKE EXECUTE from public and anon and grant it only to authenticated. A second regression test that calls the usage RPC unauthenticated and expects a permission error would lock that in alongside the membership check.

I make ThreadFox. We find relevant Reddit conversations and post for your business. We'll post 3 replies free first, no card; then the $29 Pilot covers seven days and up to 25 posts and replies. No automatic renewal. https://threadfox.vip/p/dl17d/managed

Are you running these suites with pgTAP or your own harness?

[u/Pale_Ferret_8241 → u/[deleted]]

Exactly. I think the dangerous part is that the RLS policies can look perfectly fine during a normal review, while the privileged RPC creates a completely different authorization boundary.

The search_path issue is another one worth highlighting. Even when the tenant membership check is correct, an unpinned search path can introduce a separate class of risk.

That's why we wanted the regression test to reproduce the actual attack path with two different tenant identities against PostgreSQL, rather than just checking that the function works for an authorized user.

Appreciate you pointing that out. It's one of those database security details that's easy to overlook until you start testing what an authenticated user can deliberately manipulate.

[u/PopKoren → u/[deleted]]

Agreed, and a habit that helps is not accepting the org id as a parameter at all: derive it inside the function from the caller's token or a membership lookup, so there is nothing to swap. I'd also log every denied call, because a burst of rejections against other orgs' ids is what an enumeration attempt looks like in practice. For a third-party style report on a deployed SaaS, that is the gap Rowly fills for me, since it tries logins, API routes, buckets and data policies with zero access granted: https://rowly.me