Views: 1
Multi-tenant SaaS solutions rely on a shared database to optimize costs and maintenance while serving multiple clients with growing privacy and compliance demands (GDPR, FADP). Without fine-grained isolation, any incident can lead to data leakage between tenants, loss of user trust, and legal penalties. Edana supports IT leadership in designing secure cloud architectures, implementing cybersecurity best practices, and optimizing PostgreSQL environments to ensure performance, scalability, and regulatory compliance.
Multi-Tenant Isolation Models
Each approach involves trade-offs between isolation, maintenance, and performance. The choice should be based on data volume, data criticality, and regulatory constraints.
Separate Schemas or Databases
Separating tenants by PostgreSQL schemas or instances provides maximum isolation. Each client has its own space, ensuring that no query can cross the imposed boundary.
In practice, this approach is favored for large enterprises or regulated sectors (banking, healthcare) where strict audit and compartmentalization requirements apply. It reassures auditors and minimizes cross-contamination risks.
However, multiplying schemas or instances complicates migration processes and patching operations. Version coordination, migration script synchronization, and DevOps teams’ velocity becomes heavier, which can slow down the overall workflow. This approach integrates into your web architecture.
Discriminator Column (tenant_id)
Adding a tenant_id column to each table seems easy to implement. The application code always includes a filter to isolate data, avoiding schema proliferation.
However, the responsibility for security rests entirely on the application. A missing WHERE clause can expose another client’s data, creating a technical debt that grows with the complexity of analytical queries and cross-reports.
Furthermore, business analytics becomes more expensive: each reporting query must handle multi-tenant aggregations, leading to variable response times depending on volume and indexing.
Row-Level Security (RLS)
Row-Level Security moves isolation into the database. SQL-defined policies systematically filter rows accessible to each tenant, eliminating intervention from application code.
By default, tables enabled for RLS block access if no policies are defined. This drastically reduces risks associated with implementation oversights in the API or backend. For further optimization, see our article on PostgreSQL performance tuning.
A client in the logistics sector observed a reduction in data exposure incidents immediately after implementing RLS. This example shows that built-in PostgreSQL security limits operational risk and strengthens user trust.
RLS Principles and Initial Configuration
Row-Level Security is enabled per table and relies on SELECT, INSERT, UPDATE, DELETE policies. Centralized implementation enhances robustness and simplifies maintenance.
Enabling RLS and Creating Policies
For each relevant table, run ALTER TABLE … ENABLE ROW LEVEL SECURITY. This activates PostgreSQL’s internal firewall, which blocks any query before checking the defined policies.
Policies are created with CREATE POLICY. For SELECT, specify a USING clause to determine visible rows. For INSERT or UPDATE, a WITH CHECK clause ensures new data meets tenant criteria.
Centralizing these rules in the database prevents filtering logic duplication across services or microservices, reducing technical debt and unifying access controls.
Understanding USING and WITH CHECK
The USING expression restricts rows returned in a SELECT. For example, USING (tenant_id = current_setting(‘app.current_tenant’)::uuid) automatically filters data based on session context.
The WITH CHECK clause applies to INSERT or UPDATE operations: it rejects any change if the inserted row does not match the current tenant. This dual lock ensures business integrity without application overhead.
A small software publisher reported that after adding WITH CHECK, no erroneous inserts were detected during integration testing. PostgreSQL’s native visibility quickly uncovered any context anomalies.
Database vs. Application Responsibility
Shifting isolation into the DBMS separates concerns: the database handles security, while the application focuses on business logic and UI. This improves code maintainability and readability.
During refactoring or new API development, no filtering logic adjustment is needed if tables and policies are already in place. The architecture gains agility, and human errors in coding filters are eliminated.
This also streamlines audits: internal or external audits review policies directly, without scanning every line of application code, speeding up compliance validation.
Prerequisites and PostgreSQL Versions
Row-Level Security has been available since PostgreSQL 9.5. It does not depend on proprietary extensions, remaining open source and compatible with managed cloud provider editions.
No external plugins are required. Only a recent PostgreSQL version is needed, ensuring smooth portability between clusters hosted on AKS, Azure Database for PostgreSQL, or Amazon RDS.
This broad compatibility prevents vendor lock-in and eases migration across environments, aligning with Edana’s vision of a flexible, scalable cloud infrastructure.
Edana: strategic digital partner in Switzerland
We support companies and organizations in their digital transformation
Passing Tenant Context via GUC Variables
Using a custom GUC allows passing the tenant ID to each session or transaction. SET LOCAL ensures automatic reset at transaction end.
Creating and Using a Custom GUC
PostgreSQL lets you define user parameters via ALTER DATABASE … SET custom_variable_classes or in postgresql.conf. Declare app.current_tenant to carry the client’s UUID.
On session or transaction start, a simple SET app.current_tenant = ‘…’ assigns the variable. RLS policies read it via current_setting(…) to compare tenant_id against this context.
Managing this parameter at the database level simplifies traceability and ensures no query runs without explicit tenant context.
Integration into Application Code
Before each transaction, the application executes SET LOCAL app.current_tenant = ‘client-uuid’. Using SET LOCAL limits the scope to the current transaction.
In practice, a middleware or hook on transaction start in SQLAlchemy or any other ORM injects this command, ensuring consistency regardless of execution path.
A financial services provider automated this step in its connection pool, reducing context-related failures and improving end-to-end reliability.
Importance of SET LOCAL and PgBouncer Integration
SET LOCAL resets the variable at transaction end, preventing context leaks between queries on the same connection.
With PgBouncer in transaction pooling mode, consider switching to session pooling or extending purge logic for each transaction. Some cleanup scripts can run in hooks to reset context.
This rigor ensures each query sees only the active tenant’s data, even under heavy connection reuse.
Technical Implementation and Metrics
Configuring an application role without the BYPASSRLS privilege ensures all queries are subject to policies. Indexing tenant_id systematically minimizes performance impact.
Creating a Secured Application Role
Create an app role with NOLOGIN and without the BYPASSRLS privilege. Only designated functions or users have extended rights via SECURITY DEFINER or specific grants.
The app role then performs standard operations: SELECT, INSERT, UPDATE, DELETE on RLS tables. Any bypass attempt requires explicit BYPASSRLS, simplifying permissions audit.
Thus, even if the application fails, the database rejects non-compliant queries.
Example Schema: assets Table
Consider a table assets(id UUID PRIMARY KEY, tenant_id UUID, name TEXT, metadata JSONB). After ALTER TABLE assets ENABLE ROW LEVEL SECURITY, create:
CREATE POLICY assets_select ON assets FOR SELECT USING (tenant_id = current_setting(‘app.current_tenant’)::uuid);
CREATE POLICY assets_insert ON assets FOR INSERT WITH CHECK (tenant_id = current_setting(‘app.current_tenant’)::uuid);
GRANT SELECT, INSERT, UPDATE, DELETE ON assets TO app;
Indexing and Optimization
A B-tree index on tenant_id drastically improves RLS filter performance. For very large volumes, a multicolumn index on (tenant_id, created_at) may be considered for paginated queries.
Load tests on a managed cluster showed that adding an index on tenant_id reduced query time by 30% during multi-tenant analyses on over 100 million rows.
This example demonstrates that simple indexing, combined with RLS, ensures scalability and smooth performance, meeting SaaS application needs.
Turn Data Isolation into a Strategic Asset
PostgreSQL RLS provides native, default-secure, and centralized data isolation. Offloading filter responsibility to the DBMS reduces technical debt and simplifies regulatory compliance. Using custom GUCs and restricted roles locks down access while preserving performance through appropriate indexing.
Cloud architectures in Kubernetes, Azure, or AWS RDS are ideal for this approach. Each project remains contextualized, open source, and modular, true to Edana’s ethos. Our experts support existing system audits, policy definitions, continuous integration of multi-tenant tests, and operational scaling.
