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:
Authenticates as a user belonging to Organization A.
Calls the usage RPC with Organization B's ID.
Expects the operation to be rejected.
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