Building Multi-Tenant SaaS Dashboards with PostgreSQL Row-Level Security

The Hidden Risk of Application-Level Multi-Tenancy

Most SaaS developers start multi-tenancy by adding an org_id column to every table and appending WHERE org_id = current_org to every ORM query. This approach works in early prototypes, but it introduces a major security risk. A single omitted WHERE clause in a complex JOIN, dashboard export, or background job immediately leaks private customer data across tenant boundaries.

When building multi-tenant tools like [Inventory Management / InvMaster OS](/projects/1), godown operators and wholesale managers require strict data boundaries. Managers at one warehouse must never view orders, batch numbers, or profit margins belonging to another client. PostgreSQL Row-Level Security (RLS) solves this problem by moving access rules out of the application code and into the database engine itself.

How PostgreSQL Row-Level Security Works

PostgreSQL Row-Level Security works like an automatic, mandatory filter applied to every SELECT, INSERT, UPDATE, and DELETE query. Once enabled on a table, all rows become invisible by default to non-superuser roles until you write explicit permissive policies.

-- 1. Enable RLS on your target table
ALTER TABLE warehouse_orders ENABLE ROW LEVEL SECURITY;

-- 2. Force RLS for table owners to avoid accidental bypass in development
ALTER TABLE warehouse_orders FORCE ROW LEVEL SECURITY;

-- 3. Create a policy for selecting tenant rows
CREATE POLICY select_tenant_orders ON warehouse_orders
  FOR SELECT
  USING (
    org_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid
  );

-- 4. Create a policy for inserting rows matching the tenant context
CREATE POLICY insert_tenant_orders ON warehouse_orders
  FOR INSERT
  WITH CHECK (
    org_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid
  );

Integrating RLS with Supabase and JSON Web Tokens

If you are using Supabase or PostgREST, authentication runs via JSON Web Tokens (JWT). Rather than setting session variables on each transaction, PostgreSQL inspects the claims inside the authenticated request token. You can embed the tenant organization ID directly in the JWT metadata during user sign-in.

-- Helper function to read the active organization ID from the JWT
CREATE OR REPLACE FUNCTION auth.get_org_id() 
RETURNS uuid 
LANGUAGE sql 
STABLE 
AS $$
  SELECT COALESCE(
    (nullif(current_setting('request.jwt.claims', true), '')::jsonb -> 'app_metadata' ->> 'org_id')::uuid,
    NULL
  );
$$;

-- Policy using the helper function
CREATE POLICY tenant_isolation_policy ON warehouse_inventory
  FOR ALL
  USING (org_id = auth.get_org_id())
  WITH CHECK (org_id = auth.get_org_id());

Query Performance and Indexing Strategies

Because PostgreSQL appends RLS policy expressions to every query condition, poor indexing can cause sequential table scans. To keep dashboard queries fast under heavy traffic, structure your indexes so the tenant identifier is always the leading column.

• Always create a composite index starting with org_id: CREATE INDEX idx_orders_org_created ON warehouse_orders (org_id, created_at DESC).
• Mark policy functions as STABLE or IMMUTABLE so the PostgreSQL query planner evaluates them once per query instead of once per row.
• Avoid subqueries inside RLS USING expressions whenever possible; use JWT claims or indexed foreign-key lookups instead.
• Run EXPLAIN ANALYZE on your critical dashboard queries to ensure PostgreSQL selects the composite index and avoids sequential scans.

Need Help Implementing This in Your Product?

Designing multi-tenant database systems requires practical decisions around data safety, query speed, and clean code. Through my [Full-Stack Web Development Services](/services), I help companies build reliable SaaS backends, clear database models, and responsive web dashboards. [Get in touch with Aafaque](/contact) to discuss your application architecture.