Multi-Tenant SaaS Architecture With Postgres RLS

If you are building a SaaS product as a one- to three-person team in Indonesia, your database design decides how much you pay per customer and how badly a single bug can hurt you. Multi-tenancy is the pattern that lets one running instance of your app serve every customer at once, instead of standing up a separate deployment for each.

The hard question is not whether to be multi-tenant, but how strictly to isolate each tenant's data. This post walks through the three classic isolation models, explains why a shared schema with a tenant_id column plus PostgreSQL Row-Level Security is the pragmatic default for a small team, and shows how to set it up without leaking data between customers.

What Multi-Tenancy Means for Cost

A tenant is one customer organization and all of its users. In a multi-tenant SaaS, a single application instance and, usually, a single database serve many tenants at the same time. The alternative, giving every customer their own copy of the app and database, is called single-tenant, and it multiplies your hosting bill and your maintenance work by the number of customers you have.

For a small team, density is everything. The more tenants you pack onto shared infrastructure, the lower your cost per customer, which is exactly what lets a lean SaaS stay profitable at a low price point. The catch is that shared infrastructure moves the burden of keeping tenants apart onto your design, so isolation has to be deliberate rather than accidental.

• One deployment, one database, and one schema to migrate keeps operational overhead flat as you add customers.
• Cost per tenant falls as density rises, which matters most when you charge a small monthly fee.
• Shared infrastructure makes the noisy-neighbor problem and cross-tenant data leaks real risks you must design against.

The Three Isolation Models

There are three well-known ways to store many tenants' data, and they trade isolation against cost and operational effort. A separate database per tenant gives the strongest isolation and the simplest per-customer backup and restore, but it is the most expensive and the hardest to migrate, because every schema change must be applied across every database. A schema per tenant, one set of tables per customer inside a shared database, sits in the middle: decent isolation, but the number of tables grows without bound and querying across tenants becomes awkward.

The third model is a shared schema: all tenants live in the same tables, and every row carries a tenant_id column that says which customer owns it. This gives the highest density and the lowest cost, and only one schema to migrate, but it puts the responsibility for isolation on correct filtering. Miss a tenant_id filter on one query and you leak another customer's data. That single weakness is exactly what Row-Level Security exists to remove.

How Postgres RLS Enforces Isolation

Row-Level Security is a PostgreSQL feature that filters which rows a query can see or change, evaluated inside the database on every statement. Once you enable it on a table and write a policy, the database itself refuses to return rows that do not belong to the current tenant, no matter what the application code asks for. That is the key shift: isolation stops depending on your application remembering to add a filter.

• A policy is a rule attached to a table, using an expression that must be true for a row to be visible or writable; for tenancy the rule compares the row's tenant_id to the current tenant.
• The current tenant is supplied per request as a session setting, for example by running SET app.current_tenant to the tenant id and reading it back inside the policy with current_setting.
• Enabling row security is not enough on its own; the table owner and any role that created the table bypass policies until you also run ALTER TABLE with FORCE ROW LEVEL SECURITY.
• With a connection pool, a pooled connection can carry a leftover tenant setting from a previous request, so the tenant context must be set at the start of every transaction and reset when the connection returns to the pool.

Practical Setup and Indexing

The setup is small. Add a tenant_id column to every tenant-owned table, enable row security on each, and add a policy that only allows rows whose tenant_id equals the value read from the session setting. Because your application connects as a normal role rather than a superuser, the policy applies to every query it runs. At the start of each request, after checking who the user is, set the session variable to their tenant id inside the same transaction that does the work.

Indexing matters because RLS effectively adds a tenant_id equality condition to every query. Put tenant_id first in the indexes that back your common lookups, so a composite index on tenant_id together with the column you filter or sort by keeps queries fast. Without it, the database may scan across all tenants' rows before discarding the ones the policy hides, which gets slower as your busiest tenants grow.

Pitfalls That Cause Leaks

RLS is only as strong as the way you deploy it. Most cross-tenant leaks come from a handful of predictable mistakes, and every one of them is easy to check for before you ship.

• Connecting as a superuser or a role with the BYPASSRLS attribute skips policies entirely, so your application must run as an ordinary, non-bypassing role.
• Forgetting FORCE ROW LEVEL SECURITY means the table owner still sees every row, which silently defeats isolation if your app connects as that owner.
• Leaving a tenant setting on a pooled connection between requests can serve one customer another customer's data, so always scope the tenant context to a single transaction.

For a small Indonesian SaaS team, a shared schema with a tenant_id column and PostgreSQL Row-Level Security gives you enterprise-grade isolation at the lowest running cost. Enable RLS, force it on owners, run as a non-superuser role, and scope the tenant setting to each transaction. Do that, and no single application bug can leak one customer's data into another's.