Security Boundaries & Access Control for TimescaleDB Hypertables

Enforcing access control on a TimescaleDB deployment is harder than on a plain PostgreSQL schema because the objects a query touches are not the objects you granted privileges on. A hypertable is a thin routing layer over hundreds of physical chunks, a continuous aggregate is a materialized view backed by its own internal hypertable, and retention and compression run inside background workers that execute as the object owner rather than the caller. Any one of these can quietly widen the effective access scope of tenant data. This guide solves one focused problem for IoT platform developers, DevOps engineers, and Python automation builders: how to draw a security boundary that holds across chunks, continuous aggregates, and automated maintenance jobs so that a tenant only ever reads its own rows and no background job leaks data across the boundary. It builds directly on the Core Hypertable Architecture & Partitioning Strategy, because access control must be aligned with the chunk model that governs how data is stored, materialized, and dropped.

The diagram captures the central trap: row-level security (RLS) filters interactive tenant queries, but the role that owns and refreshes a continuous aggregate bypasses those same policies. A correct design treats the base table, the aggregate, and every background job as three separate surfaces that each need their own boundary.

# Prerequisites & Schema Assumptions

Before applying any of the SQL below, confirm the environment and schema meet these assumptions. This page targets TimescaleDB 2.10+ on PostgreSQL 14+.

• timescaledb extension installed and loaded via shared_preload_libraries; verify with SELECT extversion FROM pg_extension WHERE extname = 'timescaledb';
• A dedicated, non-superuser application role (for example app_reader) that connections use at query time — never the object owner
• A separate owner role that creates hypertables, continuous aggregates, and policies, so ownership and query access are distinct identities
• Every tenant-scoped table carries an explicit tenant_id column (UUID or bigint) that the RLS predicate can key on
• A session-level tenant context passed through a custom GUC such as app.current_tenant_id, set with set_config() at the start of each connection checkout
• max_worker_processes and timescaledb.max_background_workers sized to run refresh, compression, and retention jobs without starving application connections

The required TimescaleDB functions are the standard lifecycle set — add_continuous_aggregate_policy, add_retention_policy, add_compression_policy, and add_job — plus the PostgreSQL RLS primitives ENABLE/FORCE ROW LEVEL SECURITY and CREATE POLICY. If you run tenants across separate physical partitions, this page assumes you have already read space partitioning for multi-tenant IoT, since physical segregation and RLS are complementary layers, not substitutes.

# Step-by-Step Implementation

The steps below map to the three surfaces in the diagram: secure the base hypertable, secure the continuous aggregate, then constrain the background jobs that cross both.

# Step 1 — Enable and force RLS on the base hypertable

RLS must be enabled to activate policy evaluation, and it must be forced if the same role that owns the table ever runs interactive queries. Without FORCE ROW LEVEL SECURITY, the owner silently bypasses every policy. Wrap the DDL in idempotent guards so the migration is safe to re-run.

-- Idempotent RLS activation on the raw telemetry hypertable
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_tables
    WHERE tablename = 'sensor_readings' AND rowsecurity = true
  ) THEN
    ALTER TABLE sensor_readings ENABLE ROW LEVEL SECURITY;
    ALTER TABLE sensor_readings FORCE ROW LEVEL SECURITY;
  END IF;
END $$;

Enabling RLS on the parent hypertable is sufficient — TimescaleDB applies the policy to every existing and future chunk automatically, so you never grant on or attach policies to individual chunks.

# Step 2 — Create a tenant-isolation policy

PostgreSQL has no CREATE POLICY IF NOT EXISTS, so guard creation with a pg_policies existence check. Include both a USING clause (which rows are visible on read) and a WITH CHECK clause (which rows an insert or update may produce) so a tenant cannot write outside its own scope.

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_policies
    WHERE tablename = 'sensor_readings' AND policyname = 'tenant_isolation_policy'
  ) THEN
    CREATE POLICY tenant_isolation_policy ON sensor_readings
      FOR ALL
      USING (current_setting('app.current_tenant_id', true)::uuid = tenant_id)
      WITH CHECK (current_setting('app.current_tenant_id', true)::uuid = tenant_id);
  END IF;
END $$;

The second argument true to current_setting makes an unset GUC return NULL instead of raising, so an unauthenticated session sees zero rows rather than erroring — fail-closed by construction.

# Step 3 — Materialize the continuous aggregate and understand what it does not inherit

Define the rollup as usual. The critical fact is that RLS policies on sensor_readings are not inherited by the aggregate: querying sensor_readings_1h never evaluates the base table’s policies, because the aggregate reads from its own materialized internal hypertable. This is the same materialization model described under materialized view architecture and syntax.

CREATE MATERIALIZED VIEW IF NOT EXISTS sensor_readings_1h
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 hour', time) AS bucket,
  device_id,
  tenant_id,
  AVG(temperature) AS avg_temp,
  COUNT(*)         AS readings_count
FROM sensor_readings
GROUP BY 1, 2, 3
WITH NO DATA;

Always keep tenant_id in the GROUP BY — if it is dropped, the aggregate becomes physically un-isolable and no downstream policy can reconstruct tenant scope.

# Step 4 — Apply a second RLS policy on the aggregate itself

Because inheritance does not happen, the aggregate needs its own policy for tenant-scoped reads. Enable and force RLS on the continuous aggregate view exactly as you did on the base table.

ALTER MATERIALIZED VIEW sensor_readings_1h ENABLE ROW LEVEL SECURITY;
ALTER MATERIALIZED VIEW sensor_readings_1h FORCE ROW LEVEL SECURITY;

CREATE POLICY cagg_tenant_isolation ON sensor_readings_1h
  FOR SELECT
  USING (current_setting('app.current_tenant_id', true)::uuid = tenant_id);

If your TimescaleDB version rejects policies directly on the view object, expose the aggregate through a per-tenant security-barrier view (CREATE VIEW ... WITH (security_barrier)) and grant SELECT on the view instead of the aggregate.

# Step 5 — Attach the refresh policy under a constrained owner

The refresh job runs as the aggregate’s owner and bypasses RLS by design — that is intentional, because materialization must see all tenants’ rows to build the rollup. The boundary you control is who owns the aggregate: make it the dedicated owner role, never a superuser and never the application role. Attach the continuous aggregate refresh policy idempotently.

SELECT add_continuous_aggregate_policy('sensor_readings_1h',
  start_offset      => INTERVAL '3 hours',
  end_offset        => INTERVAL '1 hour',
  schedule_interval => INTERVAL '1 hour',
  if_not_exists     => TRUE);

# Step 6 — Grant read access to the application role only

Finish by granting SELECT on the aggregate (and the base table, if direct access is needed) to the application role, and nothing more. The application role owns no objects, so it can never bypass the policies you just wrote.

GRANT SELECT ON sensor_readings     TO app_reader;
GRANT SELECT ON sensor_readings_1h  TO app_reader;

# Configuration Parameters Reference

Treat the owner role as a privileged identity: anything it owns is exempt from that object’s RLS unless FORCE is set, so keep its footprint small and never connect application traffic through it.

# Integration With Adjacent Lifecycle Features

Access control cannot be reasoned about in isolation from the rest of the lifecycle, because compression and retention both run as background jobs that cross the tenant boundary.

Compression is a per-chunk operation. When a chunk converts to columnar compression models for high-frequency telemetry, the RLS policy on the parent hypertable still applies to reads of that chunk — compression changes the physical layout, not the policy surface — so no additional access rule is needed for compressed data. The compression policy job, however, runs as the owner and must retain ownership of every chunk it touches; revoking owner privileges to “lock down” a table will stall the compression job.

Retention behaves differently and is the sharper edge. drop_chunks and TTL policy mapping and enforcement operate on chunk metadata in the TimescaleDB catalog, not on rows, so they do not evaluate RLS at all. A retention job drops an entire chunk regardless of tenant. This is safe only because chunks are time-aligned rather than tenant-aligned — but it means you cannot express “retain tenant A for 90 days, tenant B for 30 days” through RLS. True per-tenant retention requires physically separating tenants into their own chunks, which is exactly what configuring space partitions for multi-tenant time series enables, so retention can target a tenant’s chunks without scanning the others. Index design on those partitions, covered in best practices for chunk indexing on high-cardinality tags, also affects how efficiently the RLS predicate can prune, since the policy adds a tenant_id filter to every plan.

For Python automation builders, enforcement should be idempotent and set the tenant context explicitly on every connection. The snippet below verifies RLS state, sets the session tenant GUC safely, and applies a retention window without blocking application threads.

import logging
from datetime import timedelta

import psycopg
from psycopg.rows import dict_row

logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)


def enforce_boundary(
    conn_str: str,
    tenant_id: str,
    retention_days: int = 30,
    hypertable: str = "sensor_readings",
) -> None:
    """Idempotently verify RLS and apply a retention window for one hypertable."""
    try:
        with psycopg.connect(conn_str, row_factory=dict_row) as conn:
            # 1. Set tenant context. SET does not accept bind parameters, so use
            #    set_config() to parameterize the value safely against injection.
            with conn.cursor() as cur:
                cur.execute(
                    "SELECT set_config('app.current_tenant_id', %s, false)",
                    (tenant_id,),
                )

            # 2. Confirm RLS is active on the target hypertable.
            with conn.cursor() as cur:
                cur.execute(
                    """
                    SELECT relrowsecurity, relforcerowsecurity
                    FROM pg_class
                    WHERE oid = %s::regclass
                    """,
                    (hypertable,),
                )
                row = cur.fetchone()
                if row and not (row["relrowsecurity"] and row["relforcerowsecurity"]):
                    logger.warning("RLS not fully enforced on %s", hypertable)

            # 3. Apply an idempotent retention policy. Pass the interval as a real
            #    parameter; %s cannot be substituted inside a quoted SQL literal.
            with conn.cursor() as cur:
                cur.execute(
                    """
                    SELECT add_retention_policy(
                        %s,
                        drop_after   => %s,
                        if_not_exists => TRUE
                    )
                    """,
                    (hypertable, timedelta(days=retention_days)),
                )
            conn.commit()
            logger.info("Boundary verified for tenant %s", tenant_id)
    except psycopg.Error:
        logger.exception("Database error during boundary enforcement")
        raise

# Performance Validation

Confirm the boundary is actually in force by querying the catalog rather than trusting the migration ran. First, verify that both RLS flags are set on every tenant object.

-- relrowsecurity = RLS enabled; relforcerowsecurity = applies to owner too
SELECT c.relname,
       c.relrowsecurity      AS rls_enabled,
       c.relforcerowsecurity AS rls_forced
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public'
  AND c.relname IN ('sensor_readings', 'sensor_readings_1h');

Next, list the active policies and their predicates so a review can confirm the tenant key is present in both USING and WITH CHECK.

SELECT tablename, policyname, cmd, qual, with_check
FROM pg_policies
WHERE tablename IN ('sensor_readings', 'sensor_readings_1h');

Finally, confirm which role owns the refresh and retention jobs — this is the identity that bypasses RLS, and it should be your dedicated owner, never a superuser.

SELECT job_id, application_name, proc_name, owner, hypertable_name
FROM timescaledb_information.jobs
WHERE proc_name IN ('policy_refresh_continuous_aggregate', 'policy_retention');

To prove isolation empirically, set the GUC to a known tenant and check that a count only ever returns that tenant’s rows: SELECT set_config('app.current_tenant_id', '...', false); SELECT count(DISTINCT tenant_id) FROM sensor_readings; should return 1 for any authenticated tenant and 0 when the GUC is unset.

# Troubleshooting

new row violates row-level security policy for table "sensor_readings" — An INSERT or UPDATE produced a row whose tenant_id does not match app.current_tenant_id, or the GUC is unset so the WITH CHECK predicate evaluated to NULL. Set the tenant context with set_config('app.current_tenant_id', <uuid>, false) before writing, and confirm the payload’s tenant_id matches.

Owner still sees all tenants’ rows — RLS is enabled but not forced. The object owner bypasses policies until you run ALTER TABLE ... FORCE ROW LEVEL SECURITY. Verify with the relforcerowsecurity column from the validation query above.

Querying the continuous aggregate returns cross-tenant rows — Base-table policies are not inherited by the aggregate. Apply a separate policy to sensor_readings_1h (Step 4) or front it with a per-tenant security-barrier view.

ERROR: must be owner of table sensor_readings when creating a policy or job — The connected role is not the object owner. Run policy and add_continuous_aggregate_policy DDL as the dedicated owner role, and reserve the application role for SELECT only.

unrecognized configuration parameter "app.current_tenant_id" — The GUC was read with the strict single-argument form of current_setting before it was ever set. Always use the two-argument form current_setting('app.current_tenant_id', true) in policy predicates so a missing setting returns NULL and fails closed.

Frequently Asked Questions

# Related & Navigation

← Back to Core Hypertable Architecture & Partitioning Strategy

• Space Partitioning for Multi-Tenant IoT — physically segregate tenants into their own chunks
• Time-Based Chunk Partitioning Strategies — how chunk boundaries govern what background jobs can target
• Compression Models for High-Frequency Telemetry — access implications of columnar chunk conversion
• Refresh Policy Design & Scheduling — configure the owner-run refresh job that bypasses RLS
• TTL Policy Mapping & Enforcement — chunk-level retention that ignores row policies