Tenant Schemas, Access Permissions and Migration Boundaries in PostgreSQL
An anonymized Ampity account of PostgreSQL tenant-schema selection and staged migration, separating recorded scope from unverified security and migration results.
[Overview] Moving tenant selection into the database access design
This anonymized Ampity engagement concerned a multi-tenant application using PostgreSQL. The original account describes shared tables with tenant identifiers and a schema-per-tenant approach intended to reduce reliance on developers adding a tenant filter to each query.
Historical incidents, migration completion and resulting security or business outcomes remain unverified. The account describes the intended approach, not proven isolation or a successful migration.
[Context] A tenant filter was part of application behaviour
The recorded starting point used shared tables containing a tenant identifier. Application queries were expected to restrict access to the relevant tenant. The concern was that an omitted or incorrect restriction could expose another tenant's data.
The account does not identify the queries, access paths or permissions involved in reported events. Shared-table designs need not rely only on application filters.
The design question was how much of tenant selection could be moved out of repeated query predicates and into the database access arrangement. Authentication, tenant authorization and administrative access still needed their own decisions.
[Decisions] Why separate schemas were considered
The account records schema-per-tenant as the chosen approach and more rigorous code review and testing as an alternative considered. The intended benefit was to give each tenant a separate namespace rather than depend on a tenant predicate in every query.
Code review and testing still need to cover tenant selection, database authorization and migration checks. The narrative does not establish that stronger review was tried and failed, or that schemas removed the need for application controls.
There are other database-side approaches to evaluate. PostgreSQL row-level security can constrain row access, but its role and policy semantics need review, including owner and privileged-role bypass. It is an alternative for a comparable system, not work recorded in this engagement. See the PostgreSQL row-security documentation.
[Design] Namespace selection and access permission are separate
The recorded design places tenant data in separate PostgreSQL schemas and sets the connection's search path when authenticating a tenant. Queries would then use unqualified table names rather than repeat a tenant identifier.
A search path selects how unqualified object names resolve. It does not prohibit a qualified reference to another schema when the executing role has the necessary privileges. PostgreSQL explicitly describes schemas as namespaces rather than rigidly separated databases. Its guidance also warns about trusting schemas writable by other users in the search path. See PostgreSQL schemas and search paths.
Actual access therefore needs a privilege design, not just a selected schema name. The PostgreSQL privilege documentation distinguishes schema access, object permissions and ownership. A reviewer should inspect the effective application role and its access to other tenants' objects, including any privileged routines it can invoke.
The account supplies no role grants, ownership rules, tenant-to-schema mapping or connection-management configuration. It therefore does not establish an enforced cross-tenant denial boundary or a deployed topology.
[Boundaries] Continuity and data preservation were requirements
Keeping the application available and preserving data were migration requirements. The stated rollout approach used parallel writes, a customer pilot, cohort migration and cleanup. Its duration, pilot size, completion and operational results are not established.
For parallel writes, a comparable migration needs an authoritative copy, divergence detection and repair, and read-cutover criteria. These checks are proposed, not demonstrated here.
Removing an old write path, deleting old data and removing access rights are different cleanup operations. Their sequence should follow reconciliation and recovery needs rather than calendar milestones.
[Operating checks] Inspect the effective tenant context
The following review is proposed reader guidance, not an executed test record for this engagement.
Run these checks only in an explicitly authorized, isolated test environment using synthetic or approved sanitized data and test tenants. Use non-delivering receivers for downstream effects and disable live business integrations. Do not access data or tenants outside the approved test scope, even when testing an expected denial. Simulate failures non-destructively in disposable fixtures. Live fault injection, destructive changes and production-data access are not authorized by these instructions.
Begin with the authenticated actor and the tenant that actor is allowed to access. Check how the application derives the schema choice from that authorization. A schema name supplied with a request should not, by itself, establish permission to use it.
Then inspect the actual database context used for the operation. If connections are reused, test transitions between different tenants and failure paths rather than checking only a fresh login.
PostgreSQL distinguishes session settings from transaction-local settings. A committed session-level change can persist for later work on that session; a transaction-local setting ends with its transaction. Those semantics alone do not select a safe pooling strategy. Check the application's transaction and connection lifecycle against PostgreSQL SET behaviour.
Within that authorized test scope, use the following record. Expected denial conditions are requirements to define and test, not observed results.
| Review boundary | Proposed check | Record to retain |
|---|---|---|
| Tenant authorization | Use an approved test tenant outside the fixture actor's permitted scope and expect denial. | Test actor, permitted tenant, requested test tenant and intended denial point. |
| Database privileges | Evaluate unqualified access and an explicit reference to another approved test tenant's object under the fixture application role. | Role, relevant grants, object ownership and expected allowed or denied operations. |
| Reused connection | Sequence operations for approved test tenants, including a simulated failed transaction or interrupted request. | Context setup and cleanup, transaction outcome and which test tenant's data was accessed. |
| Migration reconciliation | Simulate a one-sided write failure in a disposable fixture and inspect discrepancy detection before read cutover. | Authoritative copy, reconciliation decision, stop condition and recovery owner. |
Keep administrative or support access separate from the ordinary tenant path. Any permitted cross-tenant operation needs a documented purpose and owner; its existence must not be hidden by a blanket statement that cross-tenant queries are impossible.
[Results boundary] No security or migration outcome is established
This account supports discussion of the recorded starting arrangement, selected approach, migration requirements and rollout rationale. It does not substantiate an incident-free observation period, achieved isolation, zero downtime, complete data preservation or restored engineering and customer confidence.
A security result would need a defined threat model, effective permissions, relevant access paths and test or incident records over a stated scope. A migration result would also need reconciliation and cutover evidence. An absence of reported incidents alone would not establish that unauthorized access was impossible.
No regulatory or contractual consequence is inferred from the account. Those depend on the actual obligations, events and affected data.
[Applicability] Choose the boundary before choosing the schema layout
Evaluate schema-per-tenant against the application's data model, schema-change process, permissions, tenant lifecycle and migration needs. These are selection considerations, not measured costs or delivered capabilities from this engagement.
Start with one ordinary tenant operation and write down its actor, authorized tenant, schema choice, effective database role and permitted objects. Define the forbidden operation and the control expected to reject it. That record gives the team a concrete isolation boundary to test before drawing conclusions from the schema layout.
Technical reference
PostgreSQL 18 documentation was read on 17 October 2026, and the version-pinned pages were verified against the relevant current-page sections: schemas and search paths, privileges, SET behaviour and row security. Links accompany the general explanations. These sources do not establish the engagement's PostgreSQL version, configuration, execution, security tests or results.