db diff (migra): session search_path/role applied once per pool, lost on idle reconnect → spurious drop+create and REVOKE floods

[lexomat-ai]

Describe the bug

supabase db diff --linked (legacy migra engine) is non-deterministic. On identical inputs, it sometimes emits spurious drop policy / create policy and drop trigger / create trigger pairs for objects that are byte-identical on both sides. On a less lucky run it floods the output with thousands of statements, mostly revoke … from … plus differences that only concern a schema prefix.

Root cause

apps/cli-go/internal/db/diff/templates/migra.ts applies the session settings once through each pool, not on every connection:

await clientHead.query(sql`set role postgres`);
await clientHead.query(sql`set search_path = ''`);
await clientBase.query(sql`set search_path = ''`);

clientHead and clientBase come from @pgkit/client's createClient, which wraps a pg-promise pool, so each query() runs on whichever physical connection the pool hands out. pg-pool's default idleTimeoutMillis is 10 s. During a slow inspection (for example the CPU-bound schema work between the non-managed and managed-schema steps), the other side's pool sits idle for more than 10 s and its connection is closed. The replacement connection keeps the server defaults:

• search_path goes back to "$user", public, so object definitions that reference unqualified public functions or tables deparse without the public. prefix on one side only. migra then sees a difference and emits drop+create. Definitions that reference nothing search_path-dependent render the same either way, which is why only some policies and triggers are affected.
• On the head side, set role postgres is lost as well. When the reconnect happens earlier in the run, every object's ACLs and ownership are inspected as the login role, which produces the large revoke flood.

The same code is present in v2.109.1, v2.118.0 and main.

To Reproduce

This is a read-only reproduction that mirrors migra.ts, but diffs one database against itself, so correct output is empty by construction:

// package.json deps: "@pgkit/client": "^0.6.1", "@pgkit/migra": "^0.6.1"
import { createClient, sql } from "@pgkit/client";
import { Migration } from "@pgkit/migra";
const url = process.env.DB_URL; // any Supabase project; session pooler or direct
const mk = (l) => createClient(`${url}?application_name=migra_repro_${l}`, {
  pgpOptions: { connect: { options: "-c default_transaction_read_only=on" } },
});
const base = mk("base"), head = mk("head");
await head.query(sql`set search_path = ''`);
await base.query(sql`set search_path = ''`);
const out = [];
const nonManaged = await Migration.create(base, head, {
  exclude_schema: ["auth", "realtime", "storage", "pg_catalog", "extensions"],
  ignore_extension_versions: true,
});
nonManaged.set_safety(false); nonManaged.add_all_changes(true); out.push(nonManaged.sql);
// Simulate the CPU-bound gap: block the event loop past pg-pool's 10 s idle timeout.
const gap = Number(process.env.GAP_SECS ?? 0);
const until = Date.now() + gap * 1000; while (Date.now() < until) {}
for (const schema of ["auth", "storage"]) {
  const s = await Migration.create(base, head, { schema, ignore_extension_versions: true });
  s.set_safety(false);
  s.add(s.changes.triggers({ drops_only: true }));
  s.add(s.changes.rlspolicies({ drops_only: true }));
  s.add(s.changes.rlspolicies({ creations_only: true }));
  s.add(s.changes.triggers({ creations_only: true }));
  out.push(s.sql);
}
console.log(out.join(""));
await Promise.all([head.end(), base.end()]);

Steps:

1. Have an auth.users trigger or a storage.objects policy whose definition calls an unqualified public function, for example using (bucket_id = 'x' and my_check(split_part(name, '/', 1))).
2. Run the script with GAP_SECS=0: the output is empty (correct).
3. Run it with GAP_SECS=12: the output contains drop + create for every such object, although both sides are the same database. Logging pool connects (pg-promise's initialize.connect) shows the pool that sat idle opening a new physical connection whose current_setting('search_path') is "$user", public.

In real supabase db diff --linked runs, the gap comes from the shadow-side inspection. We saw the artifact in 3 of 4 runs on one machine, and a whole-schema flood (4,132 statements) in 2 of 4 polled runs.

Expected behavior

Identical schemas diff to nothing, on every run.

Suggested fix

Apply the settings to every connection rather than once per pool. Any one of these would work:

• pass them as startup parameters (options: "-c search_path= -c role=postgres" in each client's connect config);
• run them from pg-promise's initialize.connect hook;
• or pin each pool to a single, never-idled connection (max: 1, idleTimeoutMillis: 0).

Notes

• This is not the same as supabase db diff emits spurious REVOKE statements on all existing tables when adding a single new table (related: #4107) #5601. That flood was closed as intended behaviour of the v2.106.0 auto_expose_new_tables change. This one comes from set role postgres being lost on a reconnect, and it happens with no schema change at all.
• The experimental pg-delta engine (SUPABASE_EXPERIMENTAL_PG_DELTA=true) doesn't hit this, but on v2.109.1 it reported "No schema changes found" even when a migration added a table, a storage policy and an auth trigger. So it isn't a workaround at the moment.

System information

• Supabase CLI: 2.109.1 (the code is unchanged in 2.118.0 and main)
• OS: macOS (darwin, arm64)
• Engine: migra (default; no [experimental.pgdelta])

[avinash-git-24]

Hi team! I'd love to fix this non-deterministic supabase db diff issue in apps/cli-go/internal/db/diff/templates/migra.ts.

Root Cause

In apps/cli-go/internal/db/diff/templates/migra.ts, clientHead and clientBase are initialized with @pgkit/client's createClient(), which uses pg-pool's default 10s idleTimeoutMillis. Because set role postgres and set search_path = '' are executed only once as top-level query() calls rather than per-connection lifecycle hooks, any >10s gap during schema diffing causes the idle pool to reconnect with server defaults (search_path = "$user", public and login role), producing asymmetric public. qualification and ACL REVOKE floods.

Proposed Fix Plan

1. In apps/cli-go/internal/db/diff/templates/migra.ts, pass pgpOptions to createClient for both clientBase and clientHead with idleTimeoutMillis: 0, max: 1 and an on-connect hook / connection startup options ensuring set search_path = '' (and set role postgres on clientHead) is guaranteed on every physical connection handed out by the pool.
2. Add a test verifying that connection-level search_path and role remain intact across idle intervals during multi-stage Migration.create runs.

Could I take this on and submit a PR?