PostgreSQL Row-Level Security for Multi-Tenant Analytics

When you embed an analytics dashboard inside your SaaS product, every query must return exactly one tenant's data — not a neighbor's, not all tenants', not an intersection. Getting this wrong is a data breach. Getting it right with application-layer filtering is fragile: it holds until someone forgets a WHERE clause, passes the wrong parameter, or a third-party library rewrites the query internally.

PostgreSQL Row-Level Security lets the database enforce tenant isolation at the storage engine level, so even a buggy query or a misconfigured API token cannot leak cross-tenant rows. This guide covers the schema decisions, policy syntax, safe context-passing patterns, and the performance considerations you need to ship RLS-backed embedded analytics in production.

Key Takeaways

• PostgreSQL Row-Level Security — available since version 9.5 (2016) — attaches a Boolean predicate to each table that applies automatically to every SELECT, even if the application query omits a WHERE clause (PostgreSQL documentation).
• The standard pattern pairs a tenant_id column on every tenant-scoped table with a session variable (app.current_tenant) that your API sets before running queries.
• Always use SET LOCAL inside a transaction, never SET at the session level, when running behind a connection pool — otherwise tenant context bleeds into the next request.
• Index every tenant_id column (and compound indexes with time columns for analytics) or policy predicates will trigger full table scans.

What Is Row-Level Security and Why Does It Matter for Embedded Analytics?

Row-Level Security, available in PostgreSQL since version 9.5, lets you attach a Boolean predicate — a policy — to a table. Every query against that table, regardless of who wrote it, has that predicate automatically appended as an invisible WHERE clause by the database engine.

For multi-tenant SaaS products, this shifts data isolation from an application responsibility to a database contract. Application-layer filtering works until it doesn't: a developer adds a new endpoint and forgets the tenant filter, a query builder library rewrites the SQL, or a direct database connection skips the ORM entirely. RLS makes the filter unconditional. Even if the application sends SELECT * FROM events, PostgreSQL rewrites it internally to SELECT * FROM events WHERE tenant_id = current_setting('app.current_tenant')::uuid. The application cannot bypass this without explicitly disabling RLS for the connected role — a privilege you never grant to the analytics service account.

The architectural shift matters most for embedded analytics specifically. Your customers' end-users query data through tokens and iframes that your customers' developers may misconfigure. Internal dashboards are accessed by your own employees in trusted environments. Embedded dashboards are accessed by anyone your customers decide to give access to. RLS holds the boundary regardless of who built the query.

According to the PostgreSQL 9.5 release notes, RLS was introduced precisely to let multi-tenant applications enforce per-row access control without relying on application code — a recognition that database-level enforcement is more reliable than application-level enforcement for security-critical boundaries.

How to Design the Multi-Tenant Schema for RLS

RLS works best when it's part of the schema design from the start. The minimal requirement is a tenant_id column on every table that holds tenant-specific data.

The Standard Schema Pattern

-- Tenants table (metadata only; no per-tenant data lives here)
CREATE TABLE tenants (
  id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name       TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- Every tenant-scoped table carries tenant_id
CREATE TABLE events (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id   UUID NOT NULL REFERENCES tenants(id),
  user_id     UUID NOT NULL,
  event_name  TEXT NOT NULL,
  properties  JSONB,
  occurred_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Critical: index tenant_id on every large table
CREATE INDEX idx_events_tenant_id ON events (tenant_id);

-- Compound index for time-range analytics queries
CREATE INDEX idx_events_tenant_occurred ON events (tenant_id, occurred_at DESC);

The compound index on (tenant_id, occurred_at) is not optional for analytics workloads. Most analytics queries filter by tenant and then by a time range. Without it, PostgreSQL satisfies the tenant filter using the single-column index but then sorts the full result set to apply the time filter — an expensive operation on large tables.

Choosing the Right Isolation Level

There are three common multi-tenancy architectures, each with different tradeoffs:

For most SaaS analytics use cases, shared schema with RLS is the right call. Separate schemas become unmanageable past a few hundred tenants — you're running migrations hundreds of times, debugging schema drift across namespaces, and complicating cross-tenant aggregations your internal team needs. RLS gives you strong isolation without forking your migration history.

How to Enable and Configure RLS Policies

Enabling RLS on a table takes two steps: turning it on, and writing at least one policy. A table with RLS enabled but no policies blocks all access by default — a safe state, but not useful.

Step 1: Create a Dedicated Analytics Role

Never run analytics queries as a superuser or as the table owner. Both bypass RLS by default. Create a read-only role with the minimum permissions the analytics connection needs.

-- Base read-only role (no login)
CREATE ROLE analytics_reader NOLOGIN;
GRANT CONNECT ON DATABASE yourdb TO analytics_reader;
GRANT USAGE ON SCHEMA public TO analytics_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analytics_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO analytics_reader;

-- Login user for the connection pool
CREATE ROLE analytics_api LOGIN PASSWORD 'use-a-secrets-manager';
GRANT analytics_reader TO analytics_api;

Step 2: Enable RLS and Write the Policy

ALTER TABLE events ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON events
  AS PERMISSIVE
  FOR SELECT
  TO analytics_reader
  USING (
    tenant_id = current_setting('app.current_tenant', true)::uuid
  );

The second argument true to current_setting makes it return NULL instead of raising an error when the variable isn't set. Since NULL = <any UUID> is always false, an unset context produces zero rows rather than an error — and an error in your analytics layer might cause the application to fall back to an unrestricted query as a workaround. Zero rows is the safer failure mode. See the CREATE POLICY documentation for the full policy syntax reference.

Step 3: Verify the Policy Behaves Correctly

-- Test from the analytics service account
SET ROLE analytics_api;
BEGIN;
  SET LOCAL app.current_tenant = '550e8400-e29b-41d4-a716-446655440001';
  SELECT count(*) FROM events;
  -- Should return only tenant 001's row count
COMMIT;

-- Verify context is cleared after commit
SELECT current_setting('app.current_tenant', true);
-- Should return empty string or NULL

RESET ROLE;

Run this test with a known tenant that has rows and a known tenant that has zero rows, and verify the counts match what's in the database directly.

One mistake worth calling out: writing the USING clause with a subquery like tenant_id IN (SELECT id FROM allowed_tenants WHERE user_id = current_user). This works but PostgreSQL evaluates the subquery once per row scanned, not once per query. A simple equality check against a session variable evaluates once per query, at planning time. For analytics tables with millions of rows, that difference is significant.

How to Pass Tenant Context Safely at Query Time

The session variable app.current_tenant has to get set before each query. How you set it determines whether your RLS implementation is actually safe in a connection-pooled environment.

Why SET at Session Level Breaks Connection Pools

Most production analytics setups use a connection pool — PgBouncer, RDS Proxy, or built-in application pooling. Connections are reused across requests. If you use SET app.current_tenant = 'tenant-A' at the session level, the next request that reuses that connection inherits tenant A's context — even if it belongs to tenant B.

-- DANGEROUS: session-level set persists across request boundaries
SET app.current_tenant = '550e8400-e29b-41d4-a716-446655440001';
SELECT * FROM events;
-- Connection returned to pool. Next request still has tenant_id set to tenant 001.

The Safe Pattern: SET LOCAL Inside a Transaction

SET LOCAL is scoped to the current transaction block. When the transaction commits or rolls back, the setting reverts to what it was before — typically unset. The PgBouncer documentation specifically recommends transaction-mode pooling for applications that use connection-scoped state, because it releases connections at transaction boundaries rather than session boundaries.

BEGIN;
  SET LOCAL app.current_tenant = '550e8400-e29b-41d4-a716-446655440001';

  SELECT
    event_name,
    count(*)                        AS event_count,
    date_trunc('day', occurred_at)  AS day
  FROM events
  WHERE occurred_at >= now() - interval '30 days'
  GROUP BY event_name, day
  ORDER BY day DESC, event_count DESC;
COMMIT;
-- After COMMIT, app.current_tenant is cleared for the session.

A Wrapper Function in Your API Layer

Wrap every analytics query in a helper that handles the transaction and context injection. This is the single seam between your API's authentication context and the database's isolation guarantee.

// Node.js / pg example
async function runTenantQuery(
  tenantId: string,
  sql: string,
  params: unknown[] = []
): Promise<QueryResult> {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    // SET LOCAL scopes the variable to this transaction only
    await client.query('SET LOCAL app.current_tenant = $1', [tenantId]);
    const result = await client.query(sql, params);
    await client.query('COMMIT');
    return result;
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

Every analytics query should go through this wrapper — no direct pool queries, no raw SQL outside the transaction. If a code review finds a pool.query(sql) without a wrapping transaction, treat it as a security finding.

When building analytics pipelines for multi-tenant products, the most common production incident isn't a missing WHERE clause — it's a missing COMMIT. A query that runs SET LOCAL but never commits (because the connection was released prematurely on timeout) leaves the tenant context sitting in an undefined state. Wrap the pattern above in a finally block that always commits or rolls back, never leaves the connection dirty.

How to Connect RLS to Your Embedded Analytics Layer

Once RLS policies are in place and tenant context is injected via SET LOCAL, you need to connect that pattern to your analytics layer. For hand-written queries the runTenantQuery wrapper from the previous section handles it. For embedded analytics tools that generate SQL from a visual interface, the integration point varies by how the tool connects to your database.

Token-Based Tenant Context (Preferred Architecture)

The cleanest architecture uses signed embed tokens. Your server generates a token that encodes the tenant_id. The analytics backend validates the token, extracts the tenant scope, and injects SET LOCAL app.current_tenant = <extracted_id> before executing any generated SQL.

User (iframe in your app)
  → Your API server (validates session, issues signed embed token with tenant_id)
  → Analytics backend (validates token signature, extracts tenant_id,
                       wraps all queries with SET LOCAL)
  → PostgreSQL (RLS policy applies automatically)

The key property here is that tenant context flows from authentication, not from the frontend. The iframe cannot pass a different tenant_id even if a user modifies the DOM — the token is signed and the analytics backend ignores any tenant parameter not embedded in the token.

Tools like Draxlr implement this pattern: your server generates a signed token with the tenant scope, and every query the embedded PostgreSQL dashboard runs is wrapped with that tenant's context automatically.

Verifying the Integration Works End-to-End

After connecting your analytics tool, run these checks:

1. Log in as tenant A, open an embedded dashboard, and verify the row counts match your direct SQL query for tenant A.
2. Decode the embed token and verify it contains only tenant A's ID with no way to override scope from the client side.
3. Try to construct a request for tenant B's data using tenant A's token. Verify the database returns zero rows.
4. Check your database logs for any queries that reach the analytics role without a SET LOCAL app.current_tenant preceding them — those are a misconfiguration.

Does RLS Slow Down Analytics Queries? Indexing and Policy Cost

An RLS policy adds a predicate to every query. If that predicate forces a full table scan, RLS makes your analytics slower, not just safer.

The Indexing Rule

Every table with an RLS policy must have an index that satisfies the policy predicate. Without it, every query scans every row to evaluate the tenant_id = condition.

-- Minimum: single-column index on tenant_id
CREATE INDEX idx_events_tenant ON events (tenant_id);

-- Better for time-range analytics queries
CREATE INDEX idx_events_tenant_time ON events (tenant_id, occurred_at DESC);

Use EXPLAIN (ANALYZE, BUFFERS) with a realistic tenant context to verify index usage:

BEGIN;
  SET LOCAL app.current_tenant = '<your-test-tenant-id>';
  EXPLAIN (ANALYZE, BUFFERS)
    SELECT event_name, count(*)
    FROM events
    WHERE occurred_at >= now() - interval '7 days'
    GROUP BY event_name;
COMMIT;

Look for Index Scan using idx_events_tenant_time in the output. A Seq Scan on a table with more than a few hundred thousand rows is a problem to fix before going to production.

In a 10M-row events table, a missing tenant_id index turns a typical tenant analytics query from a 4ms index scan into a 1.2-second sequential scan. With 50 concurrent users across a shared pool, that becomes 60 seconds of blocked queries. Index your tenant columns before you go live — retrofitting under load is painful.

Partitioning for Very Large Tables

At tens of millions of rows, declarative table partitioning combined with RLS is a powerful combination. PostgreSQL's partition pruning eliminates entire partitions before the RLS predicate even runs.

-- Hash partition by tenant_id (16 buckets)
CREATE TABLE events (
  id          UUID NOT NULL,
  tenant_id   UUID NOT NULL,
  occurred_at TIMESTAMPTZ NOT NULL,
  event_name  TEXT NOT NULL,
  properties  JSONB
) PARTITION BY HASH (tenant_id);

CREATE TABLE events_p0  PARTITION OF events FOR VALUES WITH (MODULUS 16, REMAINDER 0);
CREATE TABLE events_p1  PARTITION OF events FOR VALUES WITH (MODULUS 16, REMAINDER 1);
-- ... create p2 through p15 ...

-- RLS policy on the parent table is inherited by all partitions
ALTER TABLE events ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON events
  FOR SELECT TO analytics_reader
  USING (tenant_id = current_setting('app.current_tenant', true)::uuid);

Queries for a specific tenant hash to 1-2 of the 16 partitions. PostgreSQL prunes the other 14 before scanning, cutting query cost roughly by the partition factor even before RLS applies.

Monitoring for Scan Problems

SELECT
  relname          AS table_name,
  seq_scan,
  idx_scan,
  n_live_tup       AS estimated_rows,
  round(
    seq_scan::numeric / nullif(seq_scan + idx_scan, 0) * 100, 1
  )                AS seq_scan_pct
FROM pg_stat_user_tables
WHERE relname IN ('events', 'users', 'orders')
ORDER BY seq_scan DESC;

A seq_scan_pct above 20% on a table with more than 100,000 rows and an active RLS policy is a signal to add or tune an index.

Frequently Asked Questions

Does RLS work correctly with PgBouncer and other connection poolers?

Yes, but the pooler mode matters. Use transaction-mode pooling. In transaction mode, PgBouncer returns the connection to the pool immediately after each transaction commits — which is exactly when SET LOCAL variables expire. In session mode, SET LOCAL still expires at transaction boundaries, but connections live longer and the tenant variable is more likely to leak on unexpected disconnects. Always wrap tenant context in BEGIN/COMMIT regardless of the pooler mode.

Can I use RLS with views?

Standard views respect RLS policies on the underlying tables — the policy predicate is applied when the view's query runs against the base table. Materialized views do not apply RLS at query time; they snapshot data at refresh time. If you use materialized views for analytics performance, either maintain per-tenant materialized views, or apply a tenant filter at the application layer before the materialized view query runs.

What happens if app.current_tenant is not set when a query runs?

With current_setting('app.current_tenant', true) (the true argument suppresses the error on a missing variable), the USING predicate evaluates to tenant_id = NULL, which is always false. The query returns zero rows. This is the safe default: a missing context produces empty results rather than a data leak or an application crash that might trigger a fallback to an unrestricted query.

Does RLS add noticeable query latency?

With proper indexes, RLS adds negligible overhead — well under 1ms per query for the predicate evaluation itself. The performance risk is not the policy evaluation cost but an index miss: a large table without a tenant_id index will do a full sequential scan on every analytics query. Profile first with EXPLAIN ANALYZE, then add indexes to fix any sequential scans you find.

Can a superuser or table owner bypass RLS?

Yes. PostgreSQL superusers and table owners bypass RLS by default. This is why the analytics connection role must never be either. If you want RLS to apply even to the table owner (though not superusers), use ALTER TABLE events FORCE ROW LEVEL SECURITY. For analytics connections, grant only SELECT on specific tables to the analytics role, with no ALTER TABLE or SET ROLE privileges.

Conclusion

Row-Level Security in PostgreSQL makes tenant isolation a database contract rather than an application assumption. The implementation distills to four decisions: a tenant_id column on every tenant-scoped table, a session variable for context passing, SET LOCAL inside transactions for connection-pool safety, and compound indexes on (tenant_id, time_column) for analytics query performance.

The pattern scales from a startup with a hundred tenants to a platform with hundreds of thousands. RLS is a built-in PostgreSQL feature — available without external services, policy engines, or proxy layers. The investment pays off every time a bug, a misconfigured embed token, or a third-party library fails to filter correctly: the database enforces the boundary regardless.

For teams building embedded dashboards from PostgreSQL data, RLS is one of the most underused tools already in your stack.