A shared SaaS database can serve several customers, but every path to their records must preserve the customer boundary. Put tenant membership checks in the API, consider database policies as another layer, and carry verified scope through caches and background work. Hiding another workspace in the interface does not isolate its data.
Define the tenant before choosing a database layout
A tenant might be a gym, a coaching business or a company workspace. A person can belong to more than one tenant and hold different roles in each. Keep identity, membership and business records separate in the model. Signing in proves identity; choosing a workspace selects a context that the server still needs to authorize.
Write a concrete access example: a coach may view an assigned client while the assignment is active, a workspace administrator may manage membership, and neither may read an unrelated gym’s records. Then list the exceptions, such as a support operation with a separate approval trail. If the exceptions are unclear, the database layout cannot resolve them.
Compare shared tables, schemas and separate databases
- Shared tables: economical operationally, but every tenant-owned row and query needs a consistent scope.
- Separate schemas: useful organizational separation, with additional migration and connection-management work.
- Separate databases: a stronger deployment boundary for some requirements, with greater backup, rollout and fleet-management complexity.
Choose from isolation needs and operating capacity rather than the number of tenants alone. Estimate how you will migrate all customers, restore one customer, investigate an incident and delete an account. A design that makes the first request simple can still make recovery expensive. Document the reasoning so a later enterprise requirement can be evaluated against it.
Use PostgreSQL row security deliberately
PostgreSQL row-level security can filter reads and constrain writes. Enabling it without an applicable policy produces default-deny behaviour for normal access. Table owners normally bypass policies; superusers and roles with BYPASSRLS also bypass them. Therefore a test performed only with an administrator connection does not establish what the application role can access.
Treat database policies as a second layer alongside API authorization. If the application supplies tenant context to the database, set it from verified membership and scope it to the transaction. With connection pooling, test that a reused connection cannot retain a prior request’s tenant state. Review privileged maintenance paths separately.
Follow the boundary beyond HTTP requests
A tenant-aware endpoint can still leak data through a cache key that omits the tenant. Include the scope in reusable result keys and clear account-specific browser data when switching identities. Apply the same review to object storage, search indexes, analytics extracts and generated download links. Authorization at the start of a request does not automatically cover a later shared artifact.
Background jobs need an explicit tenant and operation identity. A worker should not infer scope from whichever user happened to trigger it most recently. Recheck permissions when appropriate, especially for exports queued before a member was removed. Bound the job’s output and make its delivery destination part of the authorized operation.
Test a matrix of operations and identities
- Create two unrelated workspaces, two ordinary members and one revoked membership.
- Exercise list, detail, create, update, delete and export paths under the real application database role.
- Replace path IDs, query IDs and nested body IDs with records from the other workspace.
- Repeat through bulk operations, cached responses and queued exports.
- Verify both the returned response and the stored data; a denied response must not hide a completed write.
Apply the lesson without claiming an unverified deployment
UltraFit360 provides relevant product context: its documented stack separates Firebase identity, FastAPI rules and PostgreSQL records, while the coach portal introduces working relationships between operators and clients. This guide does not claim that a particular row-security configuration is deployed there. It explains the decisions another SaaS team should verify in its own system.
For an architecture review, bring the membership model, representative API routes, database roles and one export workflow. AptixLabs can map those paths and define a small, repeatable isolation suite before a team expands the portal or adds an AI client.
PostgreSQL 18: row security policies and role bypasses
Is a tenant_id column enough to isolate SaaS customers?
No. Queries, writes, memberships, caches and asynchronous jobs must enforce that scope. A column stores the relationship; it does not enforce every access path.
Does PostgreSQL row-level security replace API authorization?
No. It can provide another enforcement layer, but application roles, privileged bypasses and business relationships still require explicit design and tests.
Should every SaaS customer have a separate database?
It depends on isolation, recovery and operating requirements. Compare shared tables, schemas and separate databases against those requirements before choosing.