Postgres RBAC: Building Hierarchical Roles for B2B SaaS

Your B2B SaaS product probably started with a single boolean: is_admin. Then a customer asked for a Manager role that could approve invoices but not delete them. Then another customer needed a user who's an Admin in one workspace and only a Viewer in a second one. A year in, permission checks are scattered across a dozen API routes, and nobody on the team is fully confident that one missed if statement can't leak another tenant's data.

That's what happens when postgres RBAC gets treated as an application-layer afterthought instead of a database-enforced design decision. Done right, RBAC in Postgres combines normalized role and permission tables with Row-Level Security, so authorization gets enforced at the data layer on every query, not just the ones a developer remembered to guard in the API.

This tutorial walks through the schema, RLS policies, connection-pooling setup, and indexing strategy needed to run hierarchical roles (Owner, Admin, Manager, Member) safely in a multi-tenant Postgres or Supabase database. It also covers the edge cases most tutorials skip: a user who belongs to two organizations with different roles, a role change that needs to take effect immediately, and support staff who need temporary access without bypassing security. If you haven't settled on a multi-tenant schema yet, it's worth starting with our complete guide to multi-tenant Supabase architecture before layering RBAC on top.

Table of Contents

1. Why Native Postgres Roles Fail as a B2B SaaS Permission Model
2. Designing a Normalized Schema for Hierarchical RBAC
3. How Do You Enforce RBAC Rules Inside Row-Level Security Policies?
4. Why Does Connection Pooling Break Your RLS Session Variables?
5. Why Is Your RBAC-Enforced RLS Query So Slow?
6. Quick Answers About Postgres RBAC and Hierarchical RLS
7. Handling Multi-Tenant Edge Cases: Org Switching, Revocation, and Impersonation
8. Testing Your RBAC Policies Before They Reach Production
9. Frequently Asked Questions

Why Native Postgres Roles Fail as a B2B SaaS Permission Model

Native PostgreSQL roles, the ones you create with CREATE ROLE and manage through GRANT and REVOKE, aren't built for thousands of individual SaaS end users. They're designed for static infrastructure privileges: a handful of internal database administrators, a handful of dedicated microservice connections, maybe a read replica user. AWS's own reference architecture for Aurora PostgreSQL is a good example of this model done well, and it makes no attempt to map individual application users onto database roles.

Simply put, role-based access control means assigning users permissions through named roles like Admin or Manager instead of authorizing each user individually. In Postgres you can implement this two ways: map SaaS users directly onto the database engine's own role system, or store roles and permissions in your own relational tables and enforce them with Row-Level Security. For any B2B SaaS product past a handful of users, only the second approach scales.

Should You Map Every SaaS User to a Native Postgres Role?

No. Provisioning a CREATE ROLE for every application user creates lock contention on the pg_authid system catalog once you're into the tens of thousands of users. It also requires DDL privileges for something as routine as onboarding a new signup, which complicates automation. And it breaks connection poolers like PgBouncer running in transaction pooling mode, which reuse a single physical connection across many clients and can't hold a different database login open per request.

The practical fix: keep one restricted database role for your entire application, something like app_user, and manage the actual RBAC hierarchy in your own schema, enforced through RLS instead of native grants.

Designing a Normalized Schema for Hierarchical RBAC

A hierarchical RBAC schema for B2B SaaS needs four tables: roles, permissions, role_permissions, and organization_members, plus a numeric hierarchy_level column so you can compare role seniority in code without hardcoding role names throughout your application.

Role and permission data lives in your own tables here, not in Postgres's native GRANT system and not hardcoded into JWT claims alone. That's what lets a support engineer promote a customer's Manager to Admin with a normal UPDATE statement instead of a database migration.

-- Role definitions with hierarchical weighting
CREATE TABLE public.roles (
  id TEXT PRIMARY KEY,          -- 'owner', 'admin', 'manager', 'member'
  hierarchy_level INT NOT NULL, -- owner=100, admin=80, manager=50, member=10
  description TEXT
);

-- Granular, action-based permissions
CREATE TABLE public.permissions (
  id TEXT PRIMARY KEY  -- 'documents:read', 'documents:write', 'documents:delete'
);

-- Maps which permissions belong to which role
CREATE TABLE public.role_permissions (
  role_id TEXT NOT NULL REFERENCES public.roles(id) ON DELETE CASCADE,
  permission_id TEXT NOT NULL REFERENCES public.permissions(id) ON DELETE CASCADE,
  PRIMARY KEY (role_id, permission_id)
);

-- Multi-tenant membership: which user has which role in which org
CREATE TABLE public.organization_members (
  organization_id UUID NOT NULL,
  user_id UUID NOT NULL,
  role_id TEXT NOT NULL REFERENCES public.roles(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (organization_id, user_id)
);

-- Example secured entity that RLS will protect
CREATE TABLE public.documents (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL,
  owner_id UUID NOT NULL,
  title TEXT NOT NULL,
  content TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Notice that organization_members uses a composite primary key of organization_id and user_id, not just user_id. That's the detail that lets one person be an Admin in one workspace and a Member in another, something a single role column on the users table can never represent.

How Do You Enforce RBAC Rules Inside Row-Level Security Policies?

You enforce RBAC inside RLS by wrapping the role and permission lookup in a single STABLE SECURITY DEFINER function, then calling that function from the USING and WITH CHECK clauses of every policy, instead of writing the join logic directly inside each one.

SECURITY DEFINER matters here for a specific reason: it runs the function with the privileges of the function's owner rather than the calling user, which lets it read organization_members even when that table has its own RLS policies. Skip this and you'll hit infinite recursion the moment a policy queries a table protected by another policy that, in turn, needs to check the first one.

CREATE OR REPLACE FUNCTION public.has_permission(
  target_org_id UUID,
  required_permission TEXT
) RETURNS BOOLEAN AS $$
DECLARE
  caller_user_id UUID := NULLIF(current_setting('app.current_user_id', TRUE), '')::UUID;
BEGIN
  IF caller_user_id IS NULL THEN
    RETURN FALSE;
  END IF;

  RETURN EXISTS (
    SELECT 1
    FROM public.organization_members om
    JOIN public.role_permissions rp ON om.role_id = rp.role_id
    WHERE om.organization_id = target_org_id
      AND om.user_id = caller_user_id
      AND rp.permission_id = required_permission
  );
END;
$$ LANGUAGE plpgsql STABLE SECURITY DEFINER SET search_path = public;

-- Enable RLS and apply granular policies per operation
ALTER TABLE public.documents ENABLE ROW LEVEL SECURITY;

CREATE POLICY select_documents_policy ON public.documents
  FOR SELECT
  USING (has_permission(organization_id, 'documents:read'));

CREATE POLICY insert_documents_policy ON public.documents
  FOR INSERT
  WITH CHECK (has_permission(organization_id, 'documents:write'));

CREATE POLICY update_documents_policy ON public.documents
  FOR UPDATE
  USING (has_permission(organization_id, 'documents:write'))
  WITH CHECK (has_permission(organization_id, 'documents:write'));

CREATE POLICY delete_documents_policy ON public.documents
  FOR DELETE
  USING (has_permission(organization_id, 'documents:delete'));

Once the function is in place, your policies read almost like plain English: a user can SELECT from documents only if has_permission confirms their role in that organization includes documents:read. Handle this carelessly, though, and SECURITY DEFINER becomes exactly the kind of silent vulnerability we cover in 5 Silent RLS Mistakes That Leave Your Database Exposed, so keep the function narrow, set an explicit search_path, and never let it accept anything the caller could use to check permissions on data outside their own organization.

Why Does Connection Pooling Break Your RLS Session Variables?

Because a plain SET mutates the physical database connection permanently, and connection poolers like PgBouncer running in transaction pooling mode return that same physical connection to a completely different user on the very next request. If your app sets app.current_user_id with SET instead of SET LOCAL, that value can leak into someone else's transaction.

This exact scenario comes up constantly in developer forums. Teams ask how to guarantee that a reused pooled connection doesn't carry one user's session state into the next request, and the answer is always the same: scope every context variable to the transaction, never the connection.

import { Pool, PoolClient } from 'pg';

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

interface RequestContext {
  userId: string;
  organizationId: string;
}

export async function withTenantContext(
  context: RequestContext,
  callback: (client: PoolClient) => Promise
): Promise {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');

    // set_config(..., true) scopes both variables to this transaction only,
    // so they can never bleed into the next request on a reused connection
    await client.query(
      `SELECT set_config('app.current_user_id', $1, true),
              set_config('app.current_org_id', $2, true)`,
      [context.userId, context.organizationId]
    );

    const result = await callback(client);
    await client.query('COMMIT');
    return result;
  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  } finally {
    client.release();
  }
}

Wrapping every database call in this pattern is non-negotiable in a pooled environment. Skip it, and a single missed reset can leak one tenant's session into another tenant's request, the exact failure mode RLS was supposed to make impossible.

Why Is Your RBAC-Enforced RLS Query So Slow?

Almost always because the role-lookup function inside your RLS policy is being re-evaluated as a SubPlan for every single row scanned, instead of once per statement, and that happens when the function isn't marked STABLE or the join columns aren't indexed.

When a function is declared without a volatility qualifier, Postgres defaults to VOLATILE. The planner assumes it might return a different result on every call, so it re-runs the authorization check for every candidate row, roughly O(N × M) complexity on a table join. Marking the function STABLE, as has_permission() is above, tells the planner the return value is deterministic within a single statement for identical arguments. That lets it fold repeated calls or execute the check once as an InitPlan instead of once per row.

This isn't a theoretical concern. Developers on r/Supabase have reported query latency increasing by as much as 94% after enabling RLS with unindexed policy joins on foreign key columns. STABLE marking alone won't fix that; you also need composite indexes on the exact columns your policy function joins on.

-- Fast lookup for "is this user a member of this org, and what's their role?"
CREATE INDEX idx_org_members_composite
  ON public.organization_members (organization_id, user_id, role_id);

-- Fast lookup for "what permissions does this role have?"
CREATE INDEX idx_role_permissions_composite
  ON public.role_permissions (role_id, permission_id);

-- Fast tenant-scoped scan on the protected table itself
CREATE INDEX idx_documents_org_id
  ON public.documents (organization_id);

For read-heavy endpoints where even an indexed join adds noticeable latency, there's a further step: embed permissions directly into the JWT at sign-in through a Supabase Auth Hook, so the RLS policy checks an in-memory claim through auth.jwt() instead of hitting organization_members at all. We cover the full pattern in Writing RLS Policies with Custom JWT Claims. Get the STABLE marking and composite indexes right first, though, since JWT caching alone doesn't solve mid-session revocation, which we'll get to shortly.

Quick Answers About Postgres RBAC and Hierarchical RLS

What Causes Slow Postgres RBAC Queries?

Simply put, a slow RBAC-enforced RLS query almost always means the role-lookup function is being re-evaluated once per row instead of once per statement. This happens when the function isn't marked STABLE, or when the columns it joins on, typically organization_id, user_id, and role_id, don't have a covering index. It matters most on tables with more than a few hundred thousand rows, where a sequential scan running a fresh authorization subquery per row becomes very noticeable.

Postgres RBAC at a Glance

When Does This Apply?

This applies to any B2B SaaS product with more than two role tiers (Owner, Admin, Manager, Member) where permissions need enforcing inside Row-Level Security itself, not just in application middleware. It's overkill for a single-tenant internal tool where every logged-in user is equally trusted.

Pros and Cons of Enforcing RBAC Inside RLS

• Pro: Fails closed by default, a missing or misconfigured policy blocks access instead of exposing it
• Pro: One definition point instead of scattered permission checks across API routes
• Pro: Still applies to direct database access, admin scripts, and backup restores, not just your API

• Con: Adds a join and function call to every query if left unindexed
• Con: Requires disciplined transaction-scoped session variables in pooled environments
• Con: Recursive policy errors are possible without a SECURITY DEFINER helper function

Handling Multi-Tenant Edge Cases: Org Switching, Revocation, and Impersonation

Three edge cases break most B2B RBAC implementations: a single user belonging to multiple organizations with different roles, a role change that needs to take effect immediately instead of waiting for a JWT to expire, and internal support staff needing temporary access without bypassing RLS entirely.

Multi-Organization Membership

A consultant might be an Admin in Org A and only a Viewer in Org B. Storing a single role on the users table can't represent that, which is exactly why organization_members ties a role to a specific organization_id and user_id pair instead. Your API middleware resolves the requested workspace on every request, confirms active membership, then injects both identifiers into the transaction:

SET LOCAL app.current_user_id = '8f3d6118-4a92-491b-b467-3e11d08e50b1';
SET LOCAL app.current_org_id = 'e9c20a1a-6415-4a11-b4ef-6fa88f8d68d0';

Mid-Session Role Revocation

When permissions live only in a client-side JWT, updating or revoking a role doesn't take effect until that token expires. For low-risk reads, cached claims are usually fine. For destructive operations, deleting a project, changing a billing plan, removing a teammate, that's not acceptable. The safer pattern splits the two: standard SELECT queries can use cached JWT claims for speed, while INSERT, UPDATE, and DELETE policies always re-check the live organization_members table. The has_permission() function from earlier already does this correctly by design.

Support Impersonation Without a Full RLS Bypass

Support teams frequently need temporary access to a customer's account to troubleshoot an issue. Reaching for a superuser connection or Supabase's service_role key is tempting, and it's also how RLS gets bypassed entirely, with no audit trail and real cross-tenant risk. A safer pattern uses an explicit impersonation session instead:

SET LOCAL app.current_user_id = '<support-agent-uuid>';
SET LOCAL app.current_org_id = '<target-customer-org-uuid>';
SET LOCAL app.is_impersonation_session = 'true';

Every query executed while that flag is set should be captured to an immutable log, typically through Postgres statement-logging tools like pgAudit rather than a hand-rolled trigger, since row-level triggers can't reliably capture arbitrary statement text on their own. That preserves the RLS boundary while still giving support staff a working path in, with a record of exactly what they touched.

None of these three cases get solved by a boolean on the users table. They all come down to the same principle: scope identity and role to the request, never to the session or the user record itself.

Testing Your RBAC Policies Before They Reach Production

Test RLS and RBAC policies the same way you'd test any other critical business logic: with automated assertions that simulate each role and run in CI on every pull request, not just checked by hand in the Supabase dashboard after a schema change.

The practical approach is to seed a test organization with one user at each hierarchy level, Owner, Admin, Manager, and Member, then assert what each one can and can't do. pgTAP is the standard tool for this in Postgres, since it lets you set the session context and run real assertions inside a transaction that gets rolled back automatically. Our full guide to pgTAP RLS testing covers the setup, the default-role bypass mistake, and five gaps most tutorials miss.

-- Simulate a Member role and confirm restricted access
BEGIN;
SELECT plan(2);

SELECT set_config('app.current_user_id', '<member-user-uuid>', true);
SELECT set_config('app.current_org_id', '<org-uuid>', true);

SELECT is(
  (SELECT count(*)::int FROM documents WHERE organization_id = '<org-uuid>'),
  0,
  'Member without documents:read cannot see any rows'
);

SELECT throws_ok(
  $$INSERT INTO documents (organization_id, owner_id, title) VALUES ('<org-uuid>', '<member-user-uuid>', 'test')$$,
  'new row violates row-level security policy for table "documents"',
  'Member without documents:write cannot insert'
);

SELECT finish();
ROLLBACK;

Run this suite in GitHub Actions on every pull request that touches roles, permissions, or RLS policy definitions. A policy that's only ever checked by hand in a dashboard is a policy that will eventually get broken by a schema change nobody thought to re-test.

Frequently Asked Questions

What is RBAC in Postgres?

RBAC, or role-based access control, means storing roles and permissions in relational tables and checking them against a user's organization membership, rather than authorizing each user individually. In Postgres, this is typically combined with Row-Level Security so the checks run inside the database itself, not just in application code.

What's the difference between Postgres roles and Row-Level Security?

Postgres roles, created with CREATE ROLE, are database engine login and privilege objects, usually one per service or administrator. Row-Level Security filters which rows a query can see or modify. For SaaS RBAC, you typically use one shared application role plus your own role and permission tables, enforced through RLS.

Does Postgres RLS slow down queries?

Only when policies aren't indexed or the helper function isn't marked STABLE. Unindexed policy joins have been reported to add significant latency, up to 94% in some cases. Indexed, STABLE-marked policies typically add negligible overhead compared to the underlying query itself.

How do I implement hierarchical roles like Admin, Manager, and Member in Supabase?

Store roles in a table with a numeric hierarchy_level, map users to organizations through a role_id foreign key, then check permissions inside RLS through a STABLE SECURITY DEFINER function rather than comparing role names directly in each policy.

Can I pass JWT claims into Postgres RLS policies?

Yes. Supabase Auth Hooks can embed role or permission claims into the JWT at sign-in, and RLS policies can read them through auth.jwt() and JSON operators, avoiding a database join for read-heavy paths. Destructive writes should still re-check the live role table.

How do you avoid infinite recursion in RLS policies?

Recursion happens when a policy on a table queries that same table, or another table with its own RLS, directly. Wrap the lookup in a SECURITY DEFINER function instead, which runs with the function owner's privileges and bypasses the caller's RLS for that specific check only. Get the search_path and permission checks wrong on that function and you've traded a recursion bug for a cross-tenant data leak, so treat it with the same care.

Should support staff bypass RLS with a service role key?

No. Bypassing RLS removes your audit trail and adds real cross-tenant risk. Use a scoped impersonation session with transaction-local session variables instead, and log every query executed under that flag through a tool like pgAudit.

Conclusion

Getting postgres RBAC right for a B2B SaaS product comes down to a handful of decisions made early: normalized role and permission tables instead of a single boolean, a STABLE SECURITY DEFINER function instead of inline policy joins, transaction-scoped session variables instead of a plain SET, and composite indexes before the first performance complaint shows up. None of it is exotic. It's the same hierarchical Owner, Admin, Manager, Member structure most B2B products need anyway, just enforced where it actually matters: inside the database, on every query, regardless of which API route or background job touches the table.

The edge cases, multi-org membership, mid-session revocation, and support impersonation, are where most implementations quietly fall short. Handle those upfront and you'll spend a lot less time firefighting a permission bug in production. If you're still working through the broader schema this sits on top of, our complete guide to multi-tenant Supabase architecture is the right place to start.