PostgreSQL row-level security for multi-tenant telemetry

In a multi-tenant monitoring service, the worst possible bug is not downtime. It is one customer seeing another customer's accounts. Application-level filters (WHERE tenant_id = ? in every query) prevent that until the day someone forgets one. PostgreSQL row-level security moves the rule into the database, where a forgotten filter returns nothing instead of everything. This is how we apply it to MT5 telemetry, with the SQL.

The model in one paragraph

Every table that holds tenant data carries a tenant_id. Row-level security is enabled and forced on those tables. A policy allows a row only when its tenant_id equals a session setting. The application connects with a role that has no way around the policy, and sets that session value at the start of every transaction from the authenticated credential. Migrations run under a different role. If the setting is missing, the comparison is against NULL and the query returns zero rows: fail-closed by construction.

Schema and policy

CREATE TABLE ea_telemetry_latest (
  tenant_id     uuid        NOT NULL,
  account_key   text        NOT NULL,   -- 'server::login'
  magic         bigint      NOT NULL,
  symbol        text        NOT NULL,
  final_lot     numeric     NOT NULL,
  allow_trading boolean     NOT NULL,
  last_seen_utc timestamptz NOT NULL,
  PRIMARY KEY (tenant_id, account_key, magic)
);

ALTER TABLE ea_telemetry_latest ENABLE ROW LEVEL SECURITY;
ALTER TABLE ea_telemetry_latest FORCE  ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON ea_telemetry_latest
  USING      (tenant_id = current_setting('app.tenant_id', true)::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::uuid);

Three details carry the weight. FORCE makes the policy apply to the table owner too; without it, a role that owns the table bypasses RLS silently. The second argument true in current_setting returns NULL instead of raising when the setting is absent. And WITH CHECK covers writes: without it a tenant could insert rows labelled as someone else's.

Two roles, never one

CREATE ROLE telemetry_migrator LOGIN;             -- owns schema, runs migrations
CREATE ROLE telemetry_app      LOGIN NOBYPASSRLS; -- used by the API at runtime

GRANT SELECT, INSERT, UPDATE ON ea_telemetry_latest TO telemetry_app;

The runtime role owns nothing, cannot alter policies and is not a superuser (superusers and roles with BYPASSRLS ignore policies entirely). The migrator's credentials exist only in the deployment pipeline. Sharing one connection string between both jobs quietly removes the whole protection.

Setting the tenant per transaction

BEGIN;
SELECT set_config('app.tenant_id', $1, true);   -- true = local to this transaction
SELECT account_key, magic, last_seen_utc FROM ea_telemetry_latest;
COMMIT;

Use the transaction-local form. With a connection pool, a session-level SET survives after the request ends and the next request on that connection inherits the previous tenant. The local form disappears at commit or rollback. In an ORM, do this in one place: a connection interceptor or middleware that opens the transaction, sets the value from the authenticated principal and refuses to continue if there is none.

What RLS does not cover

• Policies on new tables. RLS is per table. A table added next sprint is unprotected until it gets its policy. Add a test that lists tables with a tenant_id column and fails if relrowsecurity or relforcerowsecurity is false.
• Views and functions. Views run with their owner's rights unless created with security_invoker (PostgreSQL 15+); SECURITY DEFINER functions run as their owner. Both can become a way around the policy.
• Uniqueness leaks. A unique index that does not start with tenant_id lets a tenant learn that a value exists elsewhere from the constraint error. Prefix tenant-scoped unique keys with tenant_id.
• Performance. The policy is an extra predicate on every query. With tenant_id as the leading index column the cost is negligible; without it, every query scans.

The test that matters

Create two tenants with fixture data. As the runtime role: with tenant A set, assert that B's rows are invisible and that inserting a row labelled B fails; with no tenant set, assert that every table returns zero rows. Run it in CI against a real PostgreSQL instance, not a mock: the behaviour being verified lives in the database. It is a small test and it is the one that lets you tell a customer, truthfully, that isolation does not depend on every developer remembering a WHERE clause.