DEV Community

Kholipha Ahmmad Al-Amin
Kholipha Ahmmad Al-Amin

Posted on

Multi-Tenant Database Architecture for SaaS: Row-Level Security vs Schema-Per-Tenant

#architecture #database #postgres #security

Multi-Tenant Database Architecture for SaaS: Row-Level Security vs Schema-Per-Tenant

When architecting a multi-tenant business platform like the EquiSaaS BD ERP & POS Platform, one of the foundational decisions is how to partition tenant data.

Isolating business data across hundreds of independent retail merchants requires balancing three competing forces:

  1. Security and Data Isolation: Zero risk of tenant data leakage.
  2. Infrastructure Cost and Operational Overhead: Minimizing database connection pools, migrations, and memory consumption.
  3. Query Performance and Analytics: Fast indexed lookups across large transactional tables.

The Contenders: RLS vs Separate Schemas

Strategy 1: Schema-per-Tenant

In a schema-per-tenant architecture, every customer gets an isolated PostgreSQL schema within the same database:

  • Pros: Natural isolation boundary; easy per-tenant backups and drops.
  • Cons: Schema migrations must run hundreds of times; table cache bloat; connection pool exhaustion; complex cross-tenant reporting.

Strategy 2: Shared Tables with Row-Level Security (RLS)

In this pattern, all tenants share identical tables containing a tenant_id column. PostgreSQL Row-Level Security policies automatically filter rows at the database engine level based on session variables:

-- Enable RLS on core tables
ALTER TABLE pos_transactions ENABLE ROW LEVEL SECURITY;

-- Enforce tenant isolation via app session variable
CREATE POLICY tenant_isolation_policy ON pos_transactions
    AS RESTRICTIVE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
Enter fullscreen mode Exit fullscreen mode

Before running queries, the application sets the tenant context:

SET LOCAL app.current_tenant_id = 'a7d8c154-8e56-4299-bbcb-7fb62f3a6102';
Enter fullscreen mode Exit fullscreen mode

Any query executed during this transaction automatically filters to the assigned tenant, preventing accidental data access even if developers forget a WHERE clause.

Auditing and Cybersecurity

For mission-critical accounting and point of sale workloads, our cybersecurity guidelines (developed in coordination with our Agency Cybersecurity Unit) require immutable append-only audit tables.

Trigger-based CDC logs store previous and updated states as JSONB, capturing IP addresses, cashier IDs, and HMAC integrity hashes.

Exploring the Ecosystem

To learn more about cooperative engineering and retail software development:

Top comments (0)