A schema per tenant: the database owns the name

The claim

When each tenant's data lives in a schema of its own, the function from tenant identifier to schema name is not a naming convention. It is the isolation boundary, and it belongs to the database, so it moved there: a stored generated column computes it, a unique index keeps two tenants off one schema, and a plain CREATE SCHEMA is the claim that decides who owns a new name. The application keeps a copy of the function for building connection strings, and a test keeps the copy equal to the database's.

Context

Hexavera's cloud keeps shared tables in public and each tenant's rows in a schema named tenant_<key>. The connection's search_path selects the schema; each tenant has an Npgsql data source of its own with that search path baked in. The identifier is what callers send and what a token carries. The key is the identifier's ASCII letters, digits and underscores, lower-cased. The name is the prefix and the key, cut at 63 bytes, which is PostgreSQL's identifier limit: "By default, NAMEDATALEN is 64 so the maximum identifier length is 63 bytes", and longer names "will be truncated", with only a notice to say so.

The isolation model was decided before this record. This note is about the part of it that turned out to be the security boundary: the name.

The gap in the obvious design

Two identifiers with the same key select the same schema. global-mfg, GLOBAL.MFG and globalmfg are one tenant's data. A unique index on identifier compares identifiers, and it does not see that. In one sentence: a tenant registered under a look-alike identifier would have read and written another tenant's schema.

The fix is not a check in C#. Any host with a different idea of the mapping (a different prefix in its settings, a different lower-casing) would work in schemas the registry does not describe, and nothing would notice until two of them disagreed. The database has to compute the name, and the database has to refuse the second tenant.

The database computes the name

ALTER TABLE public.tenants
    ADD COLUMN schema_name text GENERATED ALWAYS AS (
        left('tenant_' || nullif(lower(regexp_replace(identifier, '[^A-Za-z0-9_]', '', 'g') COLLATE "C"), ''), 63)
    ) STORED;

CREATE UNIQUE INDEX ux_tenants_schema_name ON public.tenants (schema_name);

In EF Core the column is a shadow property with HasComputedColumnSql(..., stored: true) and a unique index; no entity has a setter for it. Four details carry the design:

• Stored, not virtual. PostgreSQL documents a stored generated column as one that "is computed when it is written (inserted or updated) and occupies storage as if it were a normal column". The unique index needs a stored value.
• COLLATE "C". The C and POSIX collations "sort by byte values rather than natural language order, and only the ASCII letters 'A' through 'Z' are treated as letters". With it, lower() lower-cases ASCII only, whatever the database's default collation. On a database with a Turkish locale, a plain lower() would turn I into a dotless letter, and the column would name a schema no host ever creates. There is a test for exactly that database.
• nullif(..., ''). An identifier with no ASCII letter, digit or underscore selects no schema. The column is null, nulls are distinct in a unique index, so any number of such rows may exist, and the API refuses to bind a request to any of them: a request bound to such a row would run with no tenant's data under it.
• The host's copy is validated at startup. The application still builds the name itself to open a connection, and a startup validator refuses any prefix other than tenant_, because the database computes with that literal.

The two implementations are kept equal by integration tests against a real PostgreSQL in a container: the database names a tenant's schema as the hosts do, for a set of identifier patterns; a second tenant on the same schema is refused by the database; a tenant renamed onto another tenant's schema is refused; the column is the database's to compute and nobody's to write.

The identity server that issues identifiers closes the same gap on its side: it accepts only lower-case letters, digits, hyphens and underscores, so it refuses GLOBAL.MFG outright, and a unique index on the key refuses globalmfg after global-mfg. The cloud does not rely on that: it parks a tenant whose schema another tenant holds instead of building it, under a named reason (schema-taken), so the tenant that came first keeps its data.

Claiming a new name

A new tenant's schema is created first and its row written after; when the row is not written, the same create takes its own schema away again, with care for a row that is still being written. The creation itself has four rules.

One at a time. A host builds one schema at a time, behind a gate that is a SemaphoreSlim(1, 1). The reason was the lock table. When the gate was added, a tenant's schema was built inside the API request that created the tenant, by a migration that creates every table and index of the tenant model in one transaction and holds a lock on each until it commits: 59 tables and 227 indexes in the first version. Nothing else bounded how many of those ran at once, so a caller allowed to create tenants could have started as many as the pool has connections, and twenty of them would have held several thousand locks, which by arithmetic from the migration's size (not measured) is the order of the whole server's lock table at PostgreSQL's defaults; a full lock table fails the statements of every tenant. Schemas are now built by the background projection of the identity directory, and the gate stays. Serial costs a second create a few seconds of waiting, and a caller who gives up while waiting has created nothing. The gate is the host's own, so several instances build one schema each; the claim below settles the race between them. Two schemas asked for at once are built one after the other, and a test says so.

The claim is a statement that can lose. The schema is created with a plain CREATE SCHEMA "tenant_x";, never with IF NOT EXISTS, which PostgreSQL documents as "Do nothing (except issuing a notice) if a schema with the same name already exists". A claim that cannot lose is not a claim: two callers would both be told they succeeded.

PostgreSQL has two answers for "taken". 42P06 duplicate_schema when the schema is there to be seen. And 23505 unique_violation, raised by the catalogue's own unique index, when another session created it in the same moment: this statement finds nothing, starts its own insert, waits for the other session's transaction, and is refused when that commits. Read as a fault, the second answer turns the loser of a race into a 500. Both are "taken".

private static async Task<bool> ClaimAsync(DbConnection connection, string schema)
{
    try
    {
        // The name is built from the sanitized key: letters, digits and underscores only. It never comes from a request.
        await ExecuteAsync(connection, $@"CREATE SCHEMA ""{schema}"";", CancellationToken.None);
        return true;
    }
    catch (PostgresException taken) when (taken.SqlState is PostgresErrorCodes.DuplicateSchema
                                                           or PostgresErrorCodes.UniqueViolation)
    {
        return false;
    }
}

The claim is not cancellable. It runs with CancellationToken.None. A statement cancelled just as it completes leaves its outcome unknown to the caller, and a schema nobody knows they created is never taken away. It is one catalogue insert; the longest it waits is for another session's create of the same name.

The claim's outcome is one of four: not asked, unknown, taken, made. When the build fails after the claim was made, the half-built schema is dropped, so the name is free again. When the claim itself failed without saying "taken" (a connection that broke), nothing is dropped, because another create of the same name may be the one that made it, and the log says so, so that a later refusal for that identifier has an explanation. A caller who gives up while the schema is being built leaves no schema and no error in the log; that is not a fault of the host, and nobody is paged.

All of the DDL runs on a connection of its own, from the data source that has no tenant. It used to run on the request's own schema context, which inside a request is bound to the caller's tenant: the new schema's DDL would have run on a connection from that tenant's pool, with its search path turned to another tenant's schema for the rest of the request.

The pooling detail

EF Core returns a pooled DbContext to its pool by resetting it. In the 10.0 branch, the synchronous RelationalConnection.ResetState clears the connection's open count unconditionally; the asynchronous ResetStateAsync clears it only when it also disposes the DbConnection, and the pool calls it with disposeDbConnection: false. So a context whose connection was opened explicitly and never closed goes back to the pool with its count intact, and the next scope that is handed the instance fails, when it swaps in its tenant's connection, with "The instance of DbConnection is currently in use".

The schema pass used to open its context's connection twice per schema and close it never. The host's startup scope is disposed synchronously, so startup was spared. A request's scope is disposed asynchronously, so a schema pass inside a request broke the next request that drew the same pooled context. The rule is now one open, one close, and the test that guards it runs a schema pass in a scope disposed the way a request's is, then uses the pooled contexts again.

What it cost

• A stored column and a unique index on the registry, and a startup refusal of any prefix but the one the database uses.
• Migrations per schema: an update pass brings each schema up to date inside itself, and a pass over every active tenant creates what is missing.
• A connection pool per tenant, with its own sizes in options validated at startup.
• A parked state in the tenant builder that operators have to understand, with a reason code and a log line.
• A 63-byte name that two long identifiers could share; the unique index catches the second, and it is parked.

What a team can take from this

Put the mapping where the data is, and let the database compute it; make uniqueness the database's job, not a check in code that every host has to agree on. Claim a new name with a statement that can lose, treat both error codes as "taken", and never cancel the claim. Test against the real engine, including the locale that lower-cases differently. And test pooled contexts in a scope disposed the way requests dispose them, because the synchronous path in your startup will not show you what the asynchronous path in production does.