postgresql – synkriaops

When building a SaaS that hosts the accounting of multiple companies on the same PostgreSQL schema, data isolation is not a detail. One cross-tenant leak and that is the end of the product. Yet many teams still rely on application-layer filtering alone.

Here is why this is insufficient and how SynkriaOps does it differently.

Application-layer filtering and its trap

Classic pattern: each entity carries a tenant_id, and the repository interleaves this filter in every query.

// Naive pattern — INSUFFICIENT
const invoices = await repo.find({
  where: { tenantId: user.tenantId, status: "validated" },
});

The trap: a single bug — a missing where, a wrongly-typed join, a queryBuilder that forgets the scope, a raw SQL written too quickly — and a query returns all tenants merged together.

And this bug can remain latent in production for months without any alert firing. From the user’s side, nothing signals that they just glimpsed a competitor’s invoices.

Engine-layer filtering (Row Level Security)

PostgreSQL has offered since version 9.5 a native mechanism: Row Level Security (RLS). Filtering is enforced at the engine level, not the application level. Every query — whether from an ORM, raw SQL, or psql console — is filtered automatically before rows leave the database.

-- Enable RLS on the table
ALTER TABLE factures ENABLE ROW LEVEL SECURITY;

-- Read policy
CREATE POLICY factures_tenant_isolation ON factures
  FOR ALL
  USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

From that point, any query that does not explicitly set app.current_tenant_id receives zero rows. This is a fail-safe, not a best-effort.

The SynkriaOps architecture

SynkriaOps uses three-layer defense:

TenantMiddleware (NestJS) — extracts the tenant_id from the JWT, verifies membership, sets set_config('app.current_tenant_id', <uuid>, false) on the DB connection for the duration of the HTTP request.
TenantAccessGuard — verifies at the controller level that the user is authorized to access the current tenant.
PostgreSQL RLS — ultimate safety net: if the two layers above fail, the DB still returns 0 rows.

The code:

// apps/api/src/common/middleware/tenant.middleware.ts
async use(req: Request, res: Response, next: NextFunction) {
  const tenantId = this.extractTenantId(req);
  if (!tenantId) {
    throw new UnauthorizedException('Tenant context missing');
  }

  await this.dataSource.query(
    `SELECT set_config('app.current_tenant_id', $1, false)`,
    [tenantId],
  );
  next();
}

The connection pool trap

Subtle catch: PostgreSQL set_config(..., false) fixes the variable for the session, not for the transaction. With a connection pool (pgBouncer, TypeORM connection pool), the same connection serves several successive requests. If one query badly resets the context, the next inherits the previous tenant_id.

Best practices:

Always reset set_config at the start of every request (middleware pattern).
For critical mutations (UPDATE / DELETE / INSERT), wrap in an explicit transaction with set_config(..., true) which scopes the variable to the transaction.
SynkriaOps reference pattern: TenantContextService.setForTransaction().

Why it is non-negotiable for accounting

An SME’s accounting includes:

Customer invoices (amounts, identities, margins)
Supplier contracts (terms, rebates)
Salaries (HR filings, payslips)
Bank flows (statements, balances)

A cross-tenant leak on this data = end of the founder’s career. Regulators, press, customers, everyone piles on. This is an existential risk for an accounting SaaS.

PostgreSQL RLS converts this existential risk into a PostgreSQL risk — that is, a risk managed by a team of hundreds of engineers in open-source for 30 years.

Regression test

SynkriaOps includes a dedicated E2E test: test/rls-fail-safe.e2e-spec.ts. It seeds two distinct tenants, makes a query with a wrong tenant_id set, and asserts that 0 rows are returned.

If someone accidentally disables an RLS policy on a table, the test fails before merge, not in production.

The full pattern is documented in apps/api/src/common/middleware/tenant.middleware.ts and apps/api/src/common/services/tenant-context.service.ts. See also the LOT-MOMO-C retrospective in docs/audit-correction/ for a real-world RLS silent fail case detected in review.

Tag: postgresql