One ERP, Many Companies: Modeling Multi-Entity Data in PostgreSQL Without Data Leaks | Blog

# One ERP, Many Companies: Modeling Multi-Entity Data in PostgreSQL Without Data Leaks

TL;DR: Separate database, separate schema, or a shared company_id column enforced by Row Level Security — each option has a real and different operational cost, and an isolation test that only checks "no rows from company B showed up" can pass even when isolation never worked at all.

The scenario: one group, several legal entities, one ERP

A group holding stakes across several blocks, or an EPC contractor running subsidiaries against different contracts, tends to arrive at the same decision point: multiple legal entities, one ERP. The holding company in Luanda wants consolidated group reporting. Each operating company or JV partner needs its own accounting, its own SAF-T file, and its own tax filing — without a user from company A ever being able to see, by mistake or curiosity, an invoice, a supplier contract, or a production sheet belonging to company B.

This isn't an abstract "security" requirement. It's a concrete legal one in Angola: starting January 1, 2026, Presidential Decree No. 71/25 makes electronic invoicing mandatory for large taxpayers and state suppliers, with the accounting SAF-T (AO) file due by April 10 each year, always generated through software certified or validated by AGT (EY Angola). If one ERP serves three group companies, all three SAF-T files have to come out clean, each one containing only the data of its own fiscal entity. A company-A SAF-T with one line from company B isn't a cosmetic bug — it's a problem with AGT.

In the oil and gas sector there's a second layer on top of that: entities providing services to operators and the national concessionaire need to register and be certified with ANPG under the local content regime established by Presidential Decree No. 271/20 of October 20, 2020 (CMS Law). Each certified entity reports its own local content plan to ANPG — one more reason the data of each legal company needs to stay distinct inside the same system.

The engineering question is always the same: how do we model this in PostgreSQL without duplicating the whole application per company, and without risking a data leak between entities?

Three real approaches — and the honest cost of each

1. A separate database per company

Each group company gets its own PostgreSQL database. This is the strongest isolation there is at the data layer: no shared table, no policy to forget, no WHERE clause that can fail.

The cost shows up in operations, not in code. Every new company is a new database to migrate, monitor, back up, and restore. An ALTER TABLE in the application becomes a script that runs N times, once per database — and if it fails partway through one of them, the group's schema drifts apart. Consolidated group reporting (the holding company summing revenue across three operating companies) stops being a single query and becomes dblink, postgres_fdw, or a separate ETL pipeline. Connection pooling suffers too: each database typically wants its own pool of connections, and that adds up quickly in memory and open files on the Postgres server as the group grows. A Crunchy Data engineering post on designing Postgres for multi-tenancy sums this up well: maintaining different schema versions across separate databases "becomes painful," and Crunchy Data writes that at around 10 you are probably fine, but that from 50 or more you should steer clear of this pattern (Crunchy Data).

For a group with three to eight companies, this is perfectly manageable. For a group that plans to keep spinning up new SPVs per project, it starts getting expensive in operational engineering.

2. A separate schema per company, one database

A single PostgreSQL cluster, one database, but each company gets its own schema (company_a.invoices, company_b.invoices). This is a genuine middle ground: there's still a real physical boundary between entities — one schema can't see another unless explicitly queried across — but the engine, memory, and pg_stat_statements are all shared.

The cost here is migration fan-out and noisy-neighbor risk. A schema change still has to run N times, once per schema, though within the same connection — cheaper than N databases, but still N executions and N points of failure. A company running a heavy report can consume working memory and I/O that slows down the others, because they all share the same shared_buffers and global work_mem settings. Consolidated group reporting becomes possible again within the same database — UNION ALL across schemas is a normal query — but only while the number of schemas stays manageable; with dozens of companies, managing per-schema migrations and permissions starts to weigh on the team. Crunchy Data also notes that newer tooling, such as Citus 12 combined with PgBouncer, has made this pattern more viable by easing the historical connection-pooling problem with per-schema setups (Crunchy Data).

This is usually the right call when a group has around a dozen companies, each with meaningful data volume, and the team wants real physical isolation without multiplying clusters.

3. A discriminator column (`company_id`) enforced by Row Level Security

One invoices table, shared across every company, with a company_id column. Isolation isn't physical anymore — it's enforced by a security policy the database engine applies on every read and write.

This is the cheapest of the three to operate: one migration, one database, one schema, group reports are just a query with no filter applied. It's also the approach directly supported by PostgreSQL's own documentation through Row Security Policies, available since version 9.5. But it's the only one of the three where isolation lives entirely in application and database logic — there is no physical wall protecting against human error.

RLS is powerful, but it isn't automatic

PostgreSQL's official documentation is explicit about the limits of Row Level Security (PostgreSQL Docs — Row Security Policies):

ALTER TABLE ... ENABLE ROW LEVEL SECURITY only starts filtering rows once at least one policy exists via CREATE POLICY. Without a policy, the table falls into a default-deny state — nobody sees anything, which is itself an easy mistake to introduce and hard to notice.
Superusers and roles with the `BYPASSRLS` attribute always bypass the policy, on any table. If the application's database connection uses a role with administrative privileges — common in maintenance scripts, seed jobs, or a role accidentally reused in production — RLS simply doesn't run, with no warning at all.
Table owners also bypass RLS by default. ALTER TABLE ... FORCE ROW LEVEL SECURITY is required to make even the owner respect the policies — a step that's easy to skip because most administrative connections use exactly that role.
A policy needs to exist on every table holding per-company sensitive data. Forgetting one new table — an audit table, an attachments table, a log table — leaves that table with zero isolation, even if every other table is set up correctly.

In practice, the typical per-company isolation policy combines the discriminator column with a session variable, following the pattern documented in Crunchy Data's engineering post on RLS for tenants: the application runs SET app.current_company_id = '...' at the start of every request, and the policy reads that value with current_setting():

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

CREATE POLICY company_isolation ON invoices
  USING (company_id = current_setting('app.current_company_id', true)::uuid);

(Crunchy Data — Row Level Security for Tenants in Postgres). This works well with connection pooling (transaction-mode PgBouncer, for instance) precisely because it doesn't require a database role per company — but it demands discipline: if any code path (a background job, a migration, an admin endpoint) forgets to set the session variable before querying, or uses a role with BYPASSRLS, every company's data becomes visible on that path, with nothing visibly breaking.

The core point: a test that looks for "nothing" can pass without testing anything

This is where the most common — and most silent — mistake shows up when teams implement multi-company isolation. Someone writes a test like this:

test: "company A user cannot see company B invoices"
  authenticate as a company A user
  query invoices
  assert: no invoice belongs to company B

This test passes. And it proves nothing.

An absence-only assertion is satisfied by any condition that produces zero rows — not just by isolation actually working. If company B's test data was never inserted in the first place, the test passes. If there's a typo in the filter (company_id = 'compnay_a' instead of 'company_a') that matches nothing, the test passes. If the JOIN is written incorrectly and simply returns no rows at all, the test passes. If the RLS policy was never even applied because the test connection uses a role with BYPASSRLS, and the query happens to return nothing because the table is empty in the test environment, the test passes — without RLS ever having been exercised once.

This isn't a rare edge case. It's the natural result of writing only the "negative" half of the test. An absence assertion doesn't distinguish between "isolation worked" and "there was nothing to find either way."

How to write a test that proves it looked at something real

An isolation test worth trusting has to satisfy four conditions:

1. Seed real data for at least two companies. Not an empty row — an actual company-A invoice and an actual company-B invoice, with distinct, known identifiers.

2. Assert presence, not just absence. The test has to confirm that company A's invoice does show up (with the right ID, the right amounts), and only then confirm that company B's does not. If the positive assertion fails, the team knows immediately the problem is in the query or the test data, not in isolation.

3. Run through the same code path and the same role a real request would use. Never through a superuser connection or an admin/seed role carrying BYPASSRLS — that tests the query, not the policy.

4. Deliberately break the policy once, and confirm the test fails. Temporarily comment out the USING clause, or swap the test role for one with BYPASSRLS, and run the same test. If it still passes after that, the test was never testing isolation — it was testing something else while wearing the costume of a security test. Only after watching it fail when isolation is broken can the team trust that it will fail when isolation breaks in production.

That last step — the manual "mutation test" — is the only one that reliably distinguishes a real isolation test from one that merely looks like one.

What this means for an ERP serving several companies in Angola

Wise Hustlers builds and operates its own ERP for oil and gas operations, from production and contracts through to finance and compliance, and the same decision resurfaces at several layers of the product: wherever the boundary between group companies is legal and fiscal (SAF-T, AGT-certified invoicing), stronger isolation tends to pay off — a separate schema, or even a separate database, for accounting — even when the rest of the application (supplier catalogs, asset data, maintenance plans) works perfectly well in a shared table with RLS. It isn't a single choice for the whole system; it's a choice made per domain, weighed against the real cost of each option, not a matter of aesthetic preference.

When the requirement is to model this solidly — with isolation tests that actually prove something instead of merely passing — this kind of data architecture work is what we do in custom software development engagements.

FAQ

Is RLS enough on its own, with no other controls?

It shouldn't be the only layer. RLS protects against poorly filtered queries at the application layer, but it still depends on the application's connection never carrying BYPASSRLS and on FORCE ROW LEVEL SECURITY being enabled on every relevant table. Access auditing and role review remain necessary.

Can the three approaches be mixed in the same ERP?

Yes, and it's often the most sensible decision: fiscal and accounting data gets stronger physical isolation (separate schema or database), while shared operational data (assets, suppliers, maintenance) runs on RLS.

Does RLS have a performance cost?

PostgreSQL's own documentation notes that the simplest and best-performing case is when a policy only evaluates values from the row itself (like company_id = current_setting(...)), without complex sub-queries — which is exactly the discriminator-column pattern. Policies with sub-selects into other tables cost more.

How do you validate that each company's SAF-T comes out clean from shared data?

The test needs to generate the file through the same production code path, with data seeded for at least two companies, and compare the output line by line — not just confirm the file was generated without an error.