Row Level Security: Why Your Data Can't Leak

Multi-tenant SaaS has a dirty secret. Almost every platform you use stores your data in the same database as every other customer. That's fine. What matters is how they keep it separate.

The standard approach is a WHERE clause. Every database query includes a filter like WHERE organisation_id = $1 so you only see your own data. It works. Until a developer writes a query and forgets the filter. Then you see everyone’s data.

This weekend we shipped PostgreSQL Row Level Security across all 25 tenant-scoped tables in SpeyBooks. This article explains what that means for your data, how the implementation works under the hood, and the bugs we found and fixed along the way. We believe in transparency, so nothing here is redacted.

What This Means for Your Data

With RLS enabled, the database itself enforces tenant isolation. Even if there is a bug in our application code, even if a query is missing its WHERE clause entirely, PostgreSQL will only return rows that belong to your organisation. A broken query returns zero rows, not someone else’s invoices.

This is the difference between trusting code to do the right thing and making it structurally impossible to do the wrong thing. Your invoices, transactions, contacts, bank accounts, and reconciliation data are all protected at the storage layer. The application cannot override this.

Most accounting platforms handle tenant isolation purely in application code. That means every new feature, every database query, every API endpoint is a potential point of failure. One missed filter in one query is all it takes. Intuit faced a class action lawsuit in 2024 over a data breach. QuickBooks Online has active user reports of cross-company data contamination. These are not hypothetical risks.

SpeyBooks can now credibly claim database-level tenant isolation, and that is rare in the accounting software market.

How RLS Works

The mechanism is straightforward. On every inbound request, our Fastify middleware wraps the request in a database transaction and sets a session variable:

SET LOCAL app.current_org_id = 'your-organisation-uuid';

SET LOCAL scopes the variable to the current transaction. When the transaction ends, the variable resets automatically. On a pooled database connection, the next request starts clean. There is no risk of one customer’s context leaking to another.

Every tenant-scoped table has four RLS policies: one each for SELECT, INSERT, UPDATE, and DELETE. They all enforce the same rule:

CREATE POLICY tenant_isolation_select ON accounting.invoices
  FOR SELECT USING (organisation_id = current_setting('app.current_org_id')::uuid);

CREATE POLICY tenant_isolation_insert ON accounting.invoices
  FOR INSERT WITH CHECK (organisation_id = current_setting('app.current_org_id')::uuid);

CREATE POLICY tenant_isolation_update ON accounting.invoices
  FOR UPDATE USING (organisation_id = current_setting('app.current_org_id')::uuid)
  WITH CHECK (organisation_id = current_setting('app.current_org_id')::uuid);

CREATE POLICY tenant_isolation_delete ON accounting.invoices
  FOR DELETE USING (organisation_id = current_setting('app.current_org_id')::uuid);

If no context is set, current_setting returns NULL. NULL does not equal any organisation_id. Zero rows returned. That is the safe default.

The 25 Protected Tables

RLS covers everything that belongs to a specific organisation: invoices, invoice lines, transactions, transaction lines, accounts, contacts, bank accounts, bank imports, bank statement rows, attachments, settings, categorisation rules, financial periods, recurring invoices, recurring invoice lines, recurring transactions, recurring transaction lines, VAT returns, webhooks, webhook deliveries, year-end log entries, API keys, idempotency keys, audit log, and bug reports.

These are the tables that hold your financial data. All 25 are now locked down at the database level.

Tables We Intentionally Excluded

Thirteen tables are global by design. The reason is simple: they are queried before we know who you are.

When you log in, the system looks up your user record, validates your session, and resolves which organisation you belong to. All of that happens before tenant context exists. You cannot require an organisation context to look up which organisation someone belongs to. That is a circular dependency.

The global tables are: users, organisations, sessions, user_organisations, auth_audit_log, password_reset_tokens, email_verification_tokens, and system-level configuration. Each one is queried pre-authentication or serves a cross-tenant function.

Bugs Found and Fixed

Transparency means showing the problems, not just the successes. We found two bugs during implementation and fixed both before verification.

Bug 1: Auth Routes Writing to a Tenant-Scoped Audit Log (v3.5.2)

Our authentication routes (login, TOTP verification, password reset, email verification) were writing security events to the audit_log table. This table is tenant-scoped. Under RLS, writing to a tenant-scoped table requires an organisation context. Authentication happens before organisation context exists.

The result: every audit log entry on an auth route silently failed. No errors. No logs. Just missing data.

The fix was to create a separate auth_audit_log table that sits outside RLS scope, with a dedicated fastify.authAudit.log() decorator. We migrated 12 audit calls across 3 route files. Security events during authentication are now captured reliably.

Bug 2: Two Tables With Incorrect RLS Configuration (v3.5.3)

Post-RLS verification revealed two problems.

First, bug_reports had FORCE ROW LEVEL SECURITY set but ENABLE ROW LEVEL SECURITY was missing. Policies existed but were not being enforced. The table appeared protected but was not. This is a subtle PostgreSQL behaviour: FORCE without ENABLE is a no-op.

Second, user_organisations was incorrectly included in the RLS batch. This is the lookup table that the tenant middleware queries to figure out which organisation a user belongs to. Enabling RLS on it created a circular dependency: you need org context to read the table, but you need the table to establish org context. Every tenant route returned 403 errors until we removed it from RLS scope.

Both were caught by our verification suite before any customer data was at risk.

Verification

We run an 8-step automated test suite after every RLS change:

1. No context, zero rows. With no app.current_org_id set, every tenant-scoped table returns zero rows.
2. Tenant A sees Tenant A. Set context to Org A, count rows. Every table returns only Org A data.
3. Tenant B sees Tenant B. Switch context to Org B, count rows. Different data, zero overlap.
4. Cross-tenant INSERT rejected. Attempt to insert a row with a different organisation_id. PostgreSQL returns insufficient_privilege.
5. Cross-tenant UPDATE rejected. Attempt to change organisation_id on an existing row. Rejected.
6. Admin bypass works. The speybooks_admin role can see all data for operational purposes.
7. EXPLAIN shows index usage. Every RLS-filtered query uses an index condition on organisation_id, not a sequential scan.
8. Performance baseline. P&L aggregation across accounts and transaction lines: 0.335ms execution time with minimal buffer hits.

This suite runs against real tenant data, not mocked fixtures.

Performance Impact

RLS adds an implicit filter to every query plan. The concern is always whether this degrades performance. The answer depends on indexing.

Every tenant-scoped table has a composite index that includes organisation_id as the leading column. With proper indexes, the RLS filter resolves to an Index Condition, not a post-scan Filter. The query planner treats it the same as a manually written WHERE clause.

Our benchmark on the heaviest query in the system (Profit and Loss aggregation joining accounts and transaction_lines) showed 0.335ms execution time. For context, a typical API response time budget is 50ms. The RLS overhead is noise.

The Admin Role

SpeyBooks has an admin panel for operational tasks. This needs cross-tenant visibility: monitoring, support, debugging. We handle this with a separate PostgreSQL role called speybooks_admin that has the BYPASSRLS privilege:

CREATE ROLE speybooks_admin;
ALTER ROLE speybooks_admin BYPASSRLS;

Admin routes use this role under controlled conditions. The standard application role never bypasses RLS. There is no code path where a regular API request can escalate to admin-level database access.

Why This Matters to You

If you are evaluating accounting software, ask your provider a direct question: what happens when their application code has a bug? Does a missing WHERE clause expose your data to other customers?

With SpeyBooks, the answer is no. Not because our code is perfect (no code is), but because the database enforces isolation independently of the application. A bug in our code means a broken feature, not a data breach.

Combined with our existing security posture (Argon2 password hashing, HTTP-only secure cookies, TOTP two-factor authentication, Zod input validation, parameterised queries throughout, and zero tracking cookies), RLS adds another structural layer to protect your financial data.