Row-Level Security for Multi-Tenant Cross-Border Order Data

Our platform serves many independent shop owners. Each one runs a cross-border sourcing operation — customers abroad order from Taobao and 1688, and our system handles purchasing and shipping. Taocarts is the name of that system. Early on, every tenant's data lived behind the same application queries, and we relied on the code to add WHERE tenant_id = ? everywhere.

That worked until it didn't. One forgotten clause in a reporting endpoint is all it takes for one shop owner to see another's orders. So we moved tenant isolation from "a convention in the codebase" to "a guarantee in the database." This is a short account of that move and what it cost us.

Why application-level checks were not enough

We had dozens of query paths. Most remembered the tenant filter. A few did not — usually the newer, hastily added admin endpoints. Code review catches some, but not all, and the failure mode is severe: cross-tenant data leakage.

The problem is structural. When isolation lives in application code, correctness depends on every developer, every endpoint, every refactor forever. We wanted a rule that held even if the application forgot.

PostgreSQL row-level security (RLS) let us put that rule in the database itself.

Setting the tenant context per request

The core idea: every request carries a tenant identity, and we set that identity as a session variable before any query runs.

SET LOCAL app.current_tenant = 'tenant_123'; 

SET LOCAL scopes the value to the current transaction, which matters because our connection pool reuses connections across requests. A plain SET would leak the value to the next request that borrowed the same connection.

In our Laravel services, a middleware opens the transaction and sets this variable at the start, and the transaction commits at the end. That gives us a clean boundary: inside the transaction, every query sees the tenant context.

The policy itself

On each tenant-scoped table, we enable RLS and add a policy:

ALTER TABLE orders ENABLE ROW LEVEL SECURITY; CREATE POLICY tenant_isolation ON orders USING (tenant_id = current_setting('app.current_tenant')::uuid); 

With that in place, SELECT * FROM orders returns only the current tenant's rows — even with no WHERE clause in the application. An endpoint that forgets to filter is now harmless instead of dangerous.

We apply the same policy to customers, shipments, stock records, and the fulfillment tables. Anything that belongs to a shop gets the same guard.

The part that surprised us: background jobs

The hard case was not web requests, it was jobs. A background worker processing a scheduled report has no incoming request, so there is no middleware to set the tenant context. If we ran such a job with RLS enabled and no context, it silently saw zero rows.

We fixed this by making tenant context explicit in every job payload. When a job is dispatched, it records the tenant_id. The worker wraps its work in the same transaction-plus-SET LOCAL pattern. A job with no tenant context now fails loudly in development instead of returning empty results in production.

That change surfaced several jobs that were quietly operating without proper scoping — a good kind of unpleasant surprise.

Admin access and the bypass question

Support staff occasionally need cross-tenant views. We did not create a blanket bypass role, because a bypass is exactly the hole RLS is meant to close. Instead, admin queries go through a separate, audited service that records who requested access and why. Broad access exists, but it leaves a trail.

The trade-off is real: RLS adds a bit of overhead per query, and it makes local debugging more fiddly — you must remember to set the context or you see nothing. We accepted both. The overhead is small next to a query planner doing real work, and the debugging friction is a fair price for a guarantee that does not depend on human memory.

What we learned

  • Put isolation in the database when the cost of getting it wrong is a data breach.
  • SET LOCAL and connection pooling go together; a plain SET will eventually leak context.
  • Background jobs need explicit tenant context — they have no request middleware to set it for them.
  • Avoid a silent superuser bypass; audited access beats an invisible hole.

A year in, tenant isolation is no longer something we review in every pull request. It is simply true, enforced below the application layer. That is the kind of correctness you stop thinking about — which is exactly the point.

Tags: #programming #webdev #database #ecommerce