Enforcing Row-Level Security in PostGIS for Tenants

This guide implements tenant isolation in the database itself, so that the guarantee survives a misconfigured application, a direct connection, or an OGC request that reached the engine by an unexpected path. It belongs to Implementing RBAC for Multi-Tenant GIS Portals, within the Core Portal Architecture & Security Boundaries framework.

Prerequisites #

• PostgreSQL 14 or newer with PostGIS, and rights to alter tables and create policies.
• A tenant column on every table in scope, populated and non-null — adding one is part of this work, not a prerequisite you can skip.
• An application connection path that can set a session variable per transaction, per the pooling constraints in configuring PostGIS connection pooling with PgBouncer.
• A staging database with representative multi-tenant data.

Why the guarantee belongs here #

Application-level filtering is correct until something reaches the data another way — and in a geospatial portal there are more such paths than in a typical web application. The rendering engine issues its own SQL. Analysts connect with desktop GIS. Reporting tools use a pooled service account. A restore into a test environment carries every tenant’s rows. Row-level security is the only mechanism that applies to all of them, because it lives below the layer they all pass through.

Step-by-step implementation #

1. Add and enforce the tenant column #

-- Every table in scope carries the tenant, non-null, indexed alongside geometry.
ALTER TABLE parcels ADD COLUMN IF NOT EXISTS tenant_id text;
UPDATE parcels SET tenant_id = 'tenant-highways' WHERE tenant_id IS NULL;
ALTER TABLE parcels ALTER COLUMN tenant_id SET NOT NULL;

-- A composite index: tenant first, then the spatial predicate.
CREATE INDEX IF NOT EXISTS parcels_tenant_geom_idx
  ON parcels USING gist (geom) INCLUDE (tenant_id);
CREATE INDEX IF NOT EXISTS parcels_tenant_idx ON parcels (tenant_id);

2. Define the policy and turn it on #

-- The application role must NOT be the table owner, or the policy is bypassed:
-- owners are exempt unless FORCE is set.
ALTER TABLE parcels ENABLE ROW LEVEL SECURITY;
ALTER TABLE parcels FORCE ROW LEVEL SECURITY;

-- Read policy: rows whose tenant matches the session's declared tenant.
CREATE POLICY parcels_tenant_read ON parcels
  FOR SELECT
  USING (tenant_id = current_setting('app.tenant_id', true));

-- Write policy: the same, and new rows may not be attributed to another tenant.
CREATE POLICY parcels_tenant_write ON parcels
  FOR ALL
  USING      (tenant_id = current_setting('app.tenant_id', true))
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true));

GRANT SELECT, INSERT, UPDATE, DELETE ON parcels TO geonode_app;

The true second argument to current_setting makes an unset variable return NULL rather than raising. That is the safe direction: tenant_id = NULL is never true, so an unset session sees zero rows rather than an error — and zero rows is a much better failure than an exception that a caller might catch and ignore.

3. Set the variable where it cannot be forgotten #

Under transaction pooling the variable must be set inside each transaction, and it must be set by the connection layer rather than by each query author.

# db.py — every transaction begins by declaring its tenant, from the verified
# request context. Nothing else in the codebase is trusted to remember.
from contextlib import contextmanager

@contextmanager
def tenant_transaction(pool, tenant_id: str):
    if not tenant_id:
        raise ValueError("no tenant in request context")   # fail closed
    conn = pool.getconn()
    try:
        with conn:
            with conn.cursor() as cur:
                # set_config with is_local=true scopes it to this transaction,
                # so a pooled connection cannot leak it to the next borrower.
                cur.execute("SELECT set_config('app.tenant_id', %s, true)", (tenant_id,))
            yield conn
    finally:
        pool.putconn(conn)

4. Give the rendering engine its own scoped path #

The OGC engine issues SQL through its own datastore and does not know about the request context. Two workable arrangements exist, and they differ in where the tenant comes from.

Performance: the part that decides whether this survives #

A policy is a predicate added to every query, and on a spatial table the interaction between that predicate and the spatial index decides whether the portal stays usable. The common failure is a policy expression the planner cannot push down — a subquery over a mapping table, or a function call that is not marked stable — which turns an index scan into a filter over every candidate row.

Check the plans rather than assuming. EXPLAIN (ANALYZE, BUFFERS) on a representative tile query, before and after enabling the policy, tells you immediately whether the predicate is being pushed down — and it is a two-minute check that prevents a portal-wide slowdown discovered at the next busy afternoon.

Verification #

-- 1. With no tenant set, the table returns nothing (fails closed)
RESET app.tenant_id;
SELECT count(*) FROM parcels;              -- expect: 0

-- 2. With a tenant set, only that tenant's rows are visible
SELECT set_config('app.tenant_id', 'tenant-highways', false);
SELECT count(*) FROM parcels;              -- expect: that tenant's count
SELECT count(DISTINCT tenant_id) FROM parcels;  -- expect: 1

-- 3. A write cannot be attributed to another tenant
INSERT INTO parcels (tenant_id, geom) VALUES ('tenant-planning', ST_Point(0,0));
--   expect: ERROR: new row violates row-level security policy

-- 4. The owner is not exempt (FORCE is in effect)
SET ROLE parcels_owner;
SELECT count(*) FROM parcels;              -- expect: 0 with no tenant set
RESET ROLE;

-- 5. No role in production carries BYPASSRLS
SELECT rolname FROM pg_roles WHERE rolbypassrls;
--   expect: only the superuser used for maintenance, never an application role

# 6. And the plan still uses the spatial index
psql -c "EXPLAIN (ANALYZE, BUFFERS) SELECT id FROM parcels
         WHERE geom && ST_MakeEnvelope(-1.31,50.90,-1.28,50.92,4326);" \
  | grep -E 'Index Scan|Seq Scan|Filter'
#   expect: Index Scan, not Seq Scan

Troubleshooting matrix #

FAQ #

Does row-level security replace the application’s own checks? #

No — it backs them. The application still needs to reject requests early, produce sensible errors, and enforce role-level permissions that have nothing to do with tenancy. What the database adds is that a bug or an unexpected path in that layer results in zero rows rather than another tenant’s data.

What is the performance cost in practice? #

Close to zero when the predicate is a direct comparison against a session setting and the tenant column is indexed alongside the geometry, because the planner folds it into the index scan. It becomes significant when the policy contains a subquery or a function the planner cannot push down — which is why the plan check belongs in the rollout rather than after it.

How does this work with connection pooling? #

Only with transaction-scoped settings. Under transaction pooling a connection is handed to another caller between transactions, so a session-scoped variable leaks the previous tenant to the next borrower. Set it with set_config(..., true) inside each transaction, and treat any code path that sets it outside a transaction as a bug.

Should each tenant get its own schema or database instead? #

For a small number of large, long-lived tenants that is a defensible alternative, and it makes isolation structural. It scales badly past a few dozen — migrations multiply, connection pools multiply, and cross-tenant reporting becomes an integration problem. Row-level security keeps one schema and one migration path, at the cost of a predicate that must be right.

What about the superuser? #

A superuser bypasses everything, which is correct and is why no application ever connects as one. Keep superuser access for maintenance, perform it deliberately, and log it — the audit approach in auditing secret access for agency compliance applies to database credentials as much as to any other.

Related #

• How to Configure GeoNode User Roles for Agency Teams — the application-level half of the same model.
• Optimizing PostgreSQL/PostGIS Connection Limits — the pooling behaviour this depends on.
• Security Boundary Mapping for OGC Services — where the data boundary sits among the others.

Up one level: Implementing RBAC for Multi-Tenant GIS Portals.