Hacktoberfest 2026: những issue maintainer đã đánh dấu cho tháng Mười, đang mở và phù hợp người mới. Xem issue Hacktoberfest

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

Đã đóng
  • Có gì không ổn?
#6,860 1 bình luận 0 reaction 1 người được giao Xem trên GitHub

Maintainer thường phản hồi trong vòng 1 ngày

@7ttp đang làm issue này rồi.

Từ ngày 28/9/2026.

Đánh giá

Issue này chưa được đánh giá.

Mô tả

🐛 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])
Ngôn ngữ chính
TypeScript
Star
2.4k
Fork
526
Merge trung bình
1 ngày 2 giờ
Pull request đã merge (30 ngày)
307

Chuẩn bị môi trường

Bắt đầu từ đâu

  1. Đọc hết issue, rồi đọc hướng dẫn đóng góp của dự án.
  2. Bình luận trên issue rằng bạn sẽ nhận — tránh hai người làm cùng một việc.
  3. Fork repository và làm thay đổi trên một nhánh.
  4. Mở pull request có tham chiếu số hiệu của issue.

Issue khác của supabase/cli

Tất cả issue của supabase/cli

Issue tương tự

Thêm issue về TypeScript

Nhận issue mới trong hộp thư của bạn

Bản tóm tắt ngắn những issue GitHub phù hợp với người mới.