Hacktoberfest 2026: los issues que los mantenedores marcaron para octubre, abiertos y aptos para principiantes. Explorar issues de Hacktoberfest

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

Cerrado
  • ¿Algo no está bien?
#6,860 1 comentario 0 reacciones 1 asignado Ver en GitHub

Los mantenedores suelen responder en 1 día

@7ttp ya está trabajando en esto.

Desde el 28/9/2026.

Evaluación

Este issue todavía no se ha evaluado.

Descripción

🐛 Bug supabase/cli

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 #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])
Lenguaje dominante
TypeScript
Estrellas
2.4k
Forks
526
Merge medio
1 d 2 h
PR fusionados (30 d)
307

Preparar el entorno

Primeros pasos

  1. Lee el issue completo y luego la guía de contribución del proyecto.
  2. Comenta en el issue que vas a ocuparte — evita que dos personas hagan lo mismo.
  3. Haz un fork del repositorio y trabaja en una rama.
  4. Abre un pull request que haga referencia al número del issue.

Más de supabase/cli

Todos los issues de supabase/cli

Issues similares

Más issues de TypeScript

Recibe los nuevos issues en tu correo

Un resumen breve de issues de GitHub para principiantes.