Multi-Tenancy with postgres_fdw: How Fluxbase Isolates Tenant Data

Motivation

Fluxbase started as a single-tenant backend, and that worked well for my own projects. But I kept thinking about a feature gap: self-hosted Supabase doesn’t support multi-tenancy, and I wanted Fluxbase to offer it. Teams and organizations sharing one instance with strong data isolation seemed too valuable to ignore.

The key requirement was isolation at the PostgreSQL level, not just application-layer filtering that could be bypassed by a bug. And I didn’t want to break the existing single-tenant experience that people were already using in production.

The answer turned out to be a hybrid database architecture [6] built on PostgreSQL’s postgres_fdw [1] "But postgres_fdw provides more transparent and standards-compliant syntax for accessing remote tables, and can give better performance in many cases." View source ↗ and Row Level Security [3] "When row security is enabled on a table (with ALTER TABLE ... ENABLE ROW LEVEL SECURITY), all normal access to the table for selecting rows or modifying rows must be allowed by a row security policy." View source ↗ .

Architecture Overview

Fluxbase uses a hybrid model. A default tenant shares the main database: no separate database, no FDW, no changes. Named tenants each get their own PostgreSQL database, but on the same PostgreSQL instance. This is important: the foreign data wrapper connects databases within one cluster, not across separate servers. There’s no network hop, no extra authentication, and transactions stay lightweight.

Each tenant database has its own public schema for user data, completely isolated. The tables Fluxbase itself manages (authentication, storage, edge functions, and so on) live once in the main database. Tenant databases access them through foreign tables, with Row Level Security enforcing that each tenant only sees its own data.

Backward Compatibility

The default tenant is the key to backward compatibility. It uses the main database pool with no extra moving parts. Existing single-tenant deployments continue working identically. No FDW, no extra databases, no configuration changes.

Multi-tenancy is opt-in. You only pay the overhead when you create a named tenant. For deployments that never need it, nothing changes.

How a Request Is Routed

When a request identifies a tenant, a connection pool router handles routing transparently [7] "Connection pool router that routes queries to the correct database based on tenant context." View source ↗ :

For the default tenant, everything goes to the main database directly. FDW is not involved at all.

postgres_fdw as the Glue

Each named tenant database uses the postgres_fdw extension to access shared infrastructure tables from the main database. From the application’s perspective, these foreign tables look and behave like local tables [1] "Now you need only SELECT from a foreign table to access the data stored in its underlying remote table. You can also modify the remote table using INSERT, UPDATE, DELETE, COPY, or TRUNCATE." View source ↗ .

The setup happens automatically when a tenant is created [7] "Tenant manager handles database creation, FDW setup, role provisioning, and schema imports in a single transaction." View source ↗ :

1. Create the tenant database: a fresh PostgreSQL database within the same instance
2. Create a per-tenant FDW role on the main database. This role is created with NOBYPASSRLS [4] "These clauses determine whether a role bypasses every row-level security (RLS) policy. NOBYPASSRLS is the default." View source ↗ , meaning it cannot bypass Row Level Security
3. Import foreign schemas [2] "IMPORT FOREIGN SCHEMA creates foreign tables that represent tables existing on a foreign server." View source ↗ : the infrastructure schemas are imported as foreign tables pointing at the main database
4. Configure the user mapping. The application connects through the per-tenant FDW role, which has a default tenant identifier set at the role level [5] "ALTER ROLE can set configuration defaults that apply whenever the role starts a session, including custom settings like app.current_tenant_id." View source ↗

The result: infrastructure tables exist once in the main database, but every tenant database sees them as local tables. The FDW role’s NOBYPASSRLS attribute ensures that RLS policies are enforced even through foreign table access.

WHERE Clauses Are Pushed Down

A reasonable concern with FDW is performance. Fortunately, postgres_fdw pushes WHERE clauses down to the remote server [9] "postgres_fdw attempts to optimize remote queries to reduce the amount of data transferred from foreign servers. This is done by sending query WHERE clauses to the remote server for execution." View source ↗ . When a tenant queries users with a filter, the filtering happens on the main database rather than fetching all rows and filtering locally.

Transaction Consistency

postgres_fdw manages remote transactions transparently [8] "The remote transaction is committed or aborted when the local transaction commits or aborts." View source ↗ . A transaction that touches both the tenant’s own tables and foreign tables commits or rolls back atomically. Since everything runs on the same PostgreSQL instance, this stays fast because there’s no cross-server coordination.

Row Level Security

FDW gives access to shared tables. RLS gives isolation.

Every infrastructure table has Row Level Security enabled with policies that filter by a tenant identifier [3] "When row security is enabled on a table (with ALTER TABLE ... ENABLE ROW LEVEL SECURITY), all normal access to the table for selecting rows or modifying rows must be allowed by a row security policy." View source ↗ . The per-tenant FDW role has this identifier set as a default, so queries through FDW are automatically scoped. No application code needed.

The defense-in-depth model:

• Superusers and BYPASSRLS roles skip RLS [3] "Superusers and roles with the BYPASSRLS attribute always bypass the row security system when accessing a table." View source ↗ , reserved for internal operations like migrations
• Service roles respect RLS and are used for normal request processing
• FDW roles are created with NOBYPASSRLS [4] "NOBYPASSRLS is the default. The role cannot bypass any row-level security policy defined on tables it accesses." View source ↗ , so foreign table queries are always filtered

If a bug in the application sends the wrong tenant context, RLS still prevents cross-tenant data access. The isolation is enforced at the database level, not in application code.

Benefits

This architecture has some practical advantages beyond isolation:

Schema migrations run once. Since all infrastructure tables live in the main database and are accessed via FDW, I only need to migrate them once when upgrading Fluxbase. Every tenant automatically sees the updated schema through their foreign tables. No need to run migrations against each tenant database for infrastructure changes.

Shared infrastructure maintenance. One set of auth, storage, and function tables to monitor, backup, and optimize. The operational surface area doesn’t grow with the number of tenants.

Same-cluster performance. Because all databases run on the same PostgreSQL instance, FDW queries have no network overhead. The foreign data wrapper is just a way to cross database boundaries within one cluster.

Transparent for application code. Queries look the same whether they hit a local table or a foreign table. The router handles the connection, RLS handles the filtering, and the application just writes SQL.

The Result

The combination of postgres_fdw and Row Level Security gives Fluxbase strong tenant isolation on a single PostgreSQL instance:

• Data isolation: each tenant’s user data lives in a separate database
• Shared infrastructure: auth, storage, functions accessed via FDW, filtered by RLS
• Backward compatible: existing deployments work unchanged
• Single migration: infrastructure schema changes propagate automatically through FDW

All of this runs on PostgreSQL alone. No Redis, no message queues, no additional services. Just PostgreSQL doing what it does best.