Security: Row-Level Security Now Enforced Across All Tenant Tables

Version: 1.0.42
Severity: Critical security fix
Affected file: src/db/rls.ts

---

Background

This platform stores sensitive financial and HMRC data on behalf of UK landlords, including HMRC OAuth tokens, Making Tax Digital (MTD) submission records, property portfolios, bank connection credentials, and AgentOS transaction imports. Each organisation's data must be strictly isolated at every layer of the stack.

PostgreSQL Row-Level Security (RLS) is one of the most important of those layers — it enforces tenant isolation directly inside the database engine, meaning even a compromised query cannot return rows belonging to a different organisation.

---

The Problem

Prior to v1.0.42, the RLS_TABLES list in src/db/rls.ts only included 5 tables:

// Before v1.0.42
const RLS_TABLES = [
  'org_members',
  'subscriptions',
  'usage_events',
  'api_keys',
  'audit_log',
];

This meant approximately 20 additional tables with an org_id column had no database-level RLS policy. Tables affected included:

• hmrc_credentials and hmrc_tokens — HMRC API access credentials and OAuth tokens
• transactions, quarterly_summaries — core MTD financial data
• properties, bank_connections, bank_accounts, bank_transactions — property and Open Banking data
• agentos_connections, agentos_properties, agentos_tenancies, agentos_transactions — AgentOS integration data
• hmrc_businesses, hmrc_annual_adjustments, landlord_links, notifications, onboarding_steps, feedback

For these tables, tenant isolation depended entirely on application-layer filtering (e.g. ORM WHERE org_id = ? clauses). A SQL injection vulnerability, an ORM misconfiguration, or a missing filter clause in any of these code paths could have resulted in cross-tenant data leakage — including exposure of another organisation's HMRC tokens or financial records.

---

The Fix

RLS_TABLES has been extended to include every table that carries an org_id column:

// After v1.0.42
const RLS_TABLES = [
  // Previously covered
  'org_members',
  'subscriptions',
  'usage_events',
  'api_keys',
  'audit_log',

  // Newly added
  'hmrc_credentials',
  'hmrc_tokens',
  'hmrc_businesses',
  'hmrc_annual_adjustments',
  'transactions',
  'quarterly_summaries',
  'properties',
  'bank_connections',
  'bank_accounts',
  'bank_transactions',
  'agentos_connections',
  'agentos_properties',
  'agentos_tenancies',
  'agentos_transactions',
  'landlord_links',
  'notifications',
  'onboarding_steps',
  'feedback',
];

Additionally, setOrgContext — which sets the app.current_org_id PostgreSQL session variable that RLS policies read — is now called consistently across all transaction-using code paths, not just the subset that was previously covered.

---

How RLS Works in This Codebase

Every protected table has a PostgreSQL RLS policy of the form:

CREATE POLICY tenant_isolation ON <table>
  USING (org_id = current_setting('app.current_org_id')::uuid);

Before any query runs, the application calls setOrgContext(orgId), which executes:

SELECT set_config('app.current_org_id', $1, true);

PostgreSQL then transparently appends the RLS condition to every SELECT, UPDATE, and DELETE on that table for the duration of the transaction. Even if application-layer filtering is absent or bypassed, the database will not return rows from other tenants.

---

What This Means for Operators

• No action is required from landlords or end users.
• The RLS policies are applied at the database migration level. Ensure the migration included in v1.0.42 has been run against your PostgreSQL instance before deploying the updated application code.
• If you manage your own database instance, verify that row_security = on is set for the application database user and that the migration completed without errors.

---

Defence-in-Depth Posture After This Fix

Layer	Mechanism	Status after v1.0.42
Database	PostgreSQL RLS policies on all org_id tables	✅ Complete
ORM / query layer	WHERE org_id = ? clauses in application queries	✅ Existing
API layer	JWT-bound orgId extracted from authenticated session	✅ Existing
Audit	audit_log table (RLS-protected) records data access events	✅ Existing

With this release, tenant data isolation is enforced at all four layers for all tables.