fix(server): apply custom database schema via SET search_path

What changed

When TUIST_DATABASE_SCHEMA is set to a non-public schema, the server now issues an explicit SET search_path TO "<schema>" on every database connection (via Ecto :after_connect in config/runtime.exs), in addition to the existing startup-packet search_path parameter. The self-host guide also documents a proxy-proof, role-level fallback.

Why

The custom-schema feature (#11416, released in server@1.219.0) routed every query to the configured schema through a single mechanism: the session search_path, delivered only as a Postgrex startup-packet connection parameter. Connection poolers (e.g. PgBouncer) and managed-Postgres proxies routinely drop unknown startup parameters, so the session silently stays on the default "$user", public path.

A self-hoster hit this: with TUIST_DATABASE_SCHEMA set, Tuist.Release.migrate failed with:

** (Postgrex.Error) ERROR 42501 (insufficient_privilege) permission denied for schema public

Root cause

The feature had no schema-qualification fallback — everything depended on the session search_path being applied:

• release.ex runs Ecto.Migrator.run(repo, :up, all: true) with no prefix:, so CREATE TABLE schema_migrations resolves purely through search_path.
• The baseline migration (20240301000000_create_legacy_baseline.exs) keys off SELECT current_schema() — also search_path-dependent.
• Postgrex sends parameters: [search_path: ...] exclusively in the startup packet; it is never re-issued as a SET.

The tell-tale in the trace: the failure is at the migrator, after ensure_database_schema (which created the schema) succeeded. The schema exists, yet the next CREATE TABLE targets public — which is only possible if the session search_path is the default. So the startup parameter was not applied on that connection, and the locked-down role (no CREATE on public, the policy that motivates a custom schema in the first place) was denied.

Why this approach

A plain SET search_path statement is forwarded by proxies that drop startup parameters, and Ecto.Migrator.with_repo applies :after_connect to the migrator’s own connections — so the schema resolves on exactly the connections that were failing. The public path is left completely untouched: no after_connect is added when the schema is public, so existing deployments behave identically.

Alternatives considered:

• Passing prefix: to the migrator would conflict with the baseline migration’s current_schema()-based string substitution and only fixes migrations, not runtime queries.
• A purely server-side ALTER ROLE ... SET search_path is the most robust option for transaction-mode poolers (it survives DISCARD ALL/RESET between transactions, where even after_connect SET is wiped), but requires a manual DBA step. It’s documented as the fallback rather than forced, so the common (direct / session-pooled / startup-param-dropping-proxy) case works out of the box.

Impact

• Self-hosters using a custom schema behind a pooler/proxy can now migrate and run without the permission denied for schema public failure.
• No behavior change for the default public schema.

Validation

Traced the full mechanism (runtime config → Postgrex startup message → ecto_sql SchemaMigration.ensure_schema_migrations_table! → migrator), then reproduced the failure and confirmed the fix against a local Postgres, using the exact library versions the release ships (ecto_sql 3.13.5, postgrex 0.22.0) and the exact after_connect statement this PR generates (SET search_path TO "tuist").

Test setup faithfully mirrors the reported environment: a role with CREATE on the database (so ensure_database_schema succeeds) but no CREATE on public. Both runs omit the startup-packet search_path parameter, simulating a pooler/proxy that drops it — so the test isolates after_connect as the only schema signal (a direct local connection would mask the bug, since the startup parameter works there).

Bug reproduced (no schema routing — old behavior with the startup param dropped):

current_schema():         "public"
MIGRATE_RESULT: FAILED  -> permission denied for schema public
tuist.schema_migrations:  nil
public.schema_migrations: nil   (nothing created)

With the fix (after_connect SET as the only schema signal):

current_schema():           "tuist"
MIGRATE_RESULT: OK
tuist.schema_migrations:    "schema_migrations"   (created in tuist)
public.schema_migrations:   nil                   (nothing in public)
tuist.accounts:             "accounts"            (migration DDL also in tuist)
runtime unqualified SELECT FROM accounts -> count=0 (resolved via search_path)

So with only the after_connect SET, Ecto.Migrator creates schema_migrations and migration tables in the custom schema, nothing lands in public, and an unqualified runtime read resolves through the schema too (the read path the running server uses).

Scope note: this validated the migration + query-routing mechanism end to end (the same connection path every Tuist.Repo connection uses), not a full MIX_ENV=prod Phoenix boot. The custom-schema config is prod-only (gated on env in [:prod, :stag, :can]), so mise run dev does not exercise it.

Also: mix format applied to config/runtime.exs; the diff is a minimal two-file change, and the public path adds no after_connect (unchanged behavior).

No GitHub comments yet.