Row-Level Security for Multi-Tenant Hypertables

Sharing one hypertable across many tenants keeps your schema flat and your compression ratios high, but it also means a single missing WHERE tenant_id = ... clause can leak one customer’s telemetry to another — so the isolation belongs in the database, not in every query. PostgreSQL row-level security (RLS) pushes a per-tenant predicate onto the table itself, and because a TimescaleDB hypertable is a partitioned parent, that predicate propagates automatically to every chunk beneath it. This page shows how to enable RLS on a shared hypertable, drive it from a per-connection session variable, and reason about how the policy interacts with chunk exclusion, compression, and continuous aggregates.

# Input Profiling: What to Establish First

RLS is only as trustworthy as the tenant identity it keys off, so pin down four things before writing a single policy. This design builds directly on the space-partitioned multi-tenant hypertable pattern, where tenant_id is already the space-partition dimension.

• The tenant key column — a single, non-null, indexed column present on every row (tenant_id UUID or TEXT). If it is already your space-partition key, chunk placement and the security predicate share the same column, which is ideal.
• The connection identity model — decide whether tenant context arrives as a session variable (one shared application role, tenant set per checkout) or as a PostgreSQL role per tenant. Session variables scale to thousands of tenants without role sprawl; per-role isolation suits a handful of large tenants that already have distinct credentials.
• The context transport — with the session-variable model, current_setting('app.tenant_id') reads a custom, namespaced GUC. Namespaced settings (anything with a dot) require no postgresql.conf change; any role can set them.
• The privilege inventory — enumerate which roles hold BYPASSRLS, who owns the table, and whether the application connects as a superuser. Every one of those silently defeats a policy, and each is a common cause of “RLS isn’t filtering” incidents.

The load-bearing rule: RLS is enforced for a query’s current role, but it is not enforced for the table owner, for BYPASSRLS roles, or for superusers unless you explicitly opt in with FORCE ROW LEVEL SECURITY. The application must therefore connect as an ordinary, non-owner role.

# Enabling RLS and Writing the Policy

Two statements arm the table: one enables the feature, one defines the predicate. Because the hypertable is the partition parent, both apply to existing and future chunks — you never touch chunk tables directly.

-- 1. Turn RLS on for the shared hypertable.
ALTER TABLE sensor_data ENABLE ROW LEVEL SECURITY;

-- 2. Also force it for the owner, so an accidental app-as-owner
--    connection cannot read across tenants.
ALTER TABLE sensor_data FORCE ROW LEVEL SECURITY;

-- 3. One policy covering reads and writes, keyed off a session GUC.
--    The second arg to current_setting (true) returns NULL instead of
--    erroring when the variable is unset, so an un-scoped connection
--    matches zero rows rather than throwing.
CREATE POLICY tenant_isolation ON sensor_data
  USING       ( tenant_id = current_setting('app.tenant_id', true)::uuid )
  WITH CHECK  ( tenant_id = current_setting('app.tenant_id', true)::uuid );

USING filters rows the tenant may read (and the visible rows an UPDATE/DELETE may touch); WITH CHECK constrains rows an INSERT/UPDATE may write, so a tenant cannot insert a row stamped with someone else’s id. The application connects as a login role that has SELECT/INSERT on the table but is neither the owner nor BYPASSRLS:

GRANT SELECT, INSERT ON sensor_data TO app_tenant;
-- app_tenant must NOT own the table and must NOT have BYPASSRLS.

# Setting tenant context per connection with psycopg v3

The linchpin is scoping the GUC to a transaction with SET LOCAL, so tenant context never bleeds across a pooled connection’s next checkout. In psycopg v3, open a transaction block, set the variable, and run the tenant’s work inside it:

import psycopg
from psycopg.rows import dict_row


def read_tenant_series(conn_str: str, tenant_id: str, since: str) -> list[dict]:
    """Read one tenant's rows; RLS enforces isolation server-side."""
    with psycopg.connect(conn_str, row_factory=dict_row) as conn:
        # A transaction block scopes SET LOCAL to exactly this unit of work.
        with conn.transaction():
            with conn.cursor() as cur:
                # set_config(..., is_local => true) is the parameterizable
                # equivalent of SET LOCAL — never string-format the tenant id.
                cur.execute(
                    "SELECT set_config('app.tenant_id', %s, true);",
                    (tenant_id,),
                )
                cur.execute(
                    """
                    SELECT time, device_id, value
                    FROM sensor_data
                    WHERE time > %s
                    ORDER BY time DESC
                    LIMIT 500;
                    """,
                    (since,),
                )
                return cur.fetchall()

Two details matter. First, use set_config(name, value, true) rather than interpolating into a literal SET LOCAL string — the third argument true makes it transaction-local, and the value is bound as a parameter, closing an injection vector on the very identity you are trying to protect. Second, because the setting is LOCAL, a connection returned to a psycopg_pool pool carries no residual tenant context; the next borrower must set its own before it can see any rows.

# Worked Example: Two Tenants, One Hypertable

Seed the shared hypertable with rows for two tenants and watch identical queries return disjoint results. Assume sensor_data(tenant_id UUID, time TIMESTAMPTZ, device_id UUID, value DOUBLE PRECISION) is a hypertable space-partitioned on tenant_id.

INSERT INTO sensor_data VALUES
  ('11111111-1111-1111-1111-111111111111', now(), gen_random_uuid(), 21.4),
  ('11111111-1111-1111-1111-111111111111', now(), gen_random_uuid(), 22.9),
  ('22222222-2222-2222-2222-222222222222', now(), gen_random_uuid(), 18.0);

Now run the same query in two sessions connected as app_tenant, differing only in the GUC:

-- Session for tenant A
SET LOCAL app.tenant_id = '11111111-1111-1111-1111-111111111111';
SELECT count(*) FROM sensor_data;   -- returns 2

-- Session for tenant B
SET LOCAL app.tenant_id = '22222222-2222-2222-2222-222222222222';
SELECT count(*) FROM sensor_data;   -- returns 1

Neither query names tenant_id, yet each sees only its own rows. An INSERT proves the write side too: tenant A attempting to insert a row stamped for tenant B is rejected by WITH CHECK with new row violates row-level security policy. The isolation is a property of the table, not a discipline the query author must remember.

# How RLS Fits Among Isolation Strategies

RLS is one of several ways to separate tenants; the table below places it against the alternatives so you deviate deliberately, not by default.

The shared-hypertable-plus-RLS row is the sweet spot for IoT fleets: you keep one compression policy, one retention policy, and one continuous-aggregate definition, while the database — not application code — guarantees isolation.

# Edge Cases & When to Deviate

• Continuous aggregates run as their owner. A continuous aggregate’s refresh executes as the view owner, which is not subject to RLS, so materialization correctly sees all tenants’ raw rows. That is only safe if tenant_id is in the aggregate’s GROUP BY, keeping each tenant’s buckets on separate rows. Then enable RLS on the aggregate itself so readers are still filtered. Plan the tenant column into every rollup you build on top of the patterns in continuous aggregate creation and refresh management.

  CREATE MATERIALIZED VIEW sensor_hourly
  WITH (timescaledb.continuous) AS
  SELECT tenant_id,                          -- MUST be grouped, or tenants merge
         time_bucket('1 hour', time) AS bucket,
         avg(value) AS avg_value
  FROM sensor_data
  GROUP BY tenant_id, time_bucket('1 hour', time);
  
  ALTER MATERIALIZED VIEW sensor_hourly ENABLE ROW LEVEL SECURITY;
  CREATE POLICY tenant_isolation_hourly ON sensor_hourly
    USING ( tenant_id = current_setting('app.tenant_id', true)::uuid );
  

• The RLS predicate is not a substitute for chunk exclusion. RLS adds tenant_id = ... to the query; it does nothing for time pruning. Keep an explicit time range in every query so the planner can still exclude chunks by their range_start/range_end. When tenant_id is also the space-partition key, the predicate additionally prunes space partitions — a bonus, not a replacement for the time filter.

• BYPASSRLS roles see everything. Analytics jobs, replication, and pg_dump often run as roles with BYPASSRLS or as the owner. That is intended for maintenance, but never point the tenant-facing application at such a role. Audit rolbypassrls regularly.

• Compressed chunks enforce the policy identically. RLS filters logical rows, so it applies whether a chunk is row-store or columnar-compressed; the columnar compression models do not weaken isolation. For maximum effect, list tenant_id first in the compression segmentby so decompression touches only the querying tenant’s segments.

• Superuser bypasses silently. Migrations and psql sessions run as superuser read across all tenants with no error. Restrict superuser access and route every tenant path through the non-privileged app_tenant role. Broader controls belong with the security boundaries and access control guidance.

• Unset context matches zero rows, by design. Because the policy uses current_setting('app.tenant_id', true), a connection that forgot to set the GUC sees nothing rather than erroring — fail-closed. If you would rather fail loud, drop the true so an unset variable raises instead.

# Verification

Prove isolation empirically and confirm the policy has not broken time pruning. First, isolation:

-- Run identically in each tenant's session; the counts must be disjoint
-- and must sum to the true total seen by a BYPASSRLS role.
SELECT current_setting('app.tenant_id', true) AS tenant, count(*)
FROM sensor_data;

Then confirm the planner still prunes chunks on the time column despite the RLS predicate — the plan should scan only the chunks overlapping the time range, not all of them:

EXPLAIN (ANALYZE, BUFFERS)
SELECT avg(value)
FROM sensor_data
WHERE time > now() - INTERVAL '1 day';
-- Look for a small number of chunk scans, each carrying an implicit
-- "Filter: tenant_id = current_setting(...)" — chunk exclusion and the
-- RLS predicate coexist rather than compete.

Finally, audit the policy surface from the catalog so you catch a stray BYPASSRLS grant or a table where RLS was enabled but never forced:

-- Which tables have RLS enabled vs forced, and which roles bypass it.
SELECT relname, relrowsecurity, relforcerowsecurity
FROM pg_class WHERE relname = 'sensor_data';

SELECT rolname FROM pg_roles WHERE rolbypassrls;   -- should be a short, known list

-- Inspect the policy expressions themselves.
SELECT polname, pg_get_expr(polqual, polrelid)      AS using_expr,
               pg_get_expr(polwithcheck, polrelid)  AS check_expr
FROM pg_policy WHERE polrelid = 'sensor_data'::regclass;

If the two per-tenant counts are disjoint, the EXPLAIN output prunes chunks, and rolbypassrls lists only maintenance roles, the isolation boundary is sound.

# Related

• Space Partitioning for Multi-Tenant IoT — the parent design where tenant_id becomes the space-partition dimension
• Configuring Space Partitions for Multi-Tenant Time Series — choosing the partition column RLS keys off
• Security Boundaries & Access Control — roles, grants, and privilege hygiene around hypertables
• Compression Models for High-Frequency Telemetry — how RLS and columnar segmentby interact on compressed chunks
• Continuous Aggregate Creation & Refresh Management — grouping by tenant so rollups stay isolatable

← Back to Space Partitioning for Multi-Tenant IoT