Tenant isolation means one customer organisation cannot read or change another's records through any supported data path. In PostgreSQL, the design needs clear ownership relationships before query filters or row-security policies can enforce them.
Start with the product's resource model. Identify which tables belong to a tenant, which are genuinely shared, and which link tenants to users or external systems. Ambiguous ownership becomes ambiguous enforcement.
Make ownership explicit in the data model
Represent the tenant for customer records, attachments, exports, jobs, and audit events where applicable. Review relationships so a record in one tenant cannot accidentally reference a private parent in another.
Application-level filtering and database row security can work together. The official PostgreSQL row-security guide explains policy behaviour, including privileged-role exceptions. Check the role actually used by the application before relying on those policies.
Test a fictional request database
Create organisations Alder and Fir, each with customers, service requests, and files. Give a user access to Alder only. Use distinct fixture values that make a mixed result obvious.
Test direct record reads, customer-to-request joins, search, exports, updates, deletes, and attachment metadata. Attempt to create an Alder request linked to a Fir customer. Confirm the system rejects the cross-tenant relationship under the chosen model rather than merely hiding it in the interface.
Run the checks as the real application database role. Add a worker processing an export because background code may use a different role or context. Add two overlapping requests for different tenants to expose stale connection state.
Review privileged work as a separate boundary
Maintenance, migrations, and support may need broader access. Name those paths and restrict them rather than quietly allowing ordinary request handling to use an unrestricted role. Keep diagnostic records sufficient to investigate a failed boundary without logging private customer content.
Define how current membership becomes trusted database context and how that context is scoped to the operation. A tenant ID supplied by the browser is not evidence of membership, even when a query consistently filters by it.
The tradeoff is defence depth versus operational complexity. Row security can reduce the impact of a missed filter, but it introduces role, policy, and connection behaviour to understand. A small team should prefer a model it can test and operate over a sophisticated configuration nobody can explain.
The StackFlight case study provides PostgreSQL-backed product context. Alder and Fir are fictional fixtures, and this article does not claim a tested schema or security assessment of that product.
Questions about PostgreSQL tenant isolation
Is one tenant column enough?
It is a start. Check related tables, joins, constraints, files, and jobs so ownership stays consistent throughout the workflow.
Can policies replace every application check?
No. The application must still establish trusted context and enforce action-specific business rules. Review the layers together.
What should be tested first?
Use the production-style runtime role to attempt a cross-tenant read and relationship write, then verify a legitimate operation still works. Bring the matrix to a database review.