One destructive doctor test needs superuser, so the CREATEDB-less role model config/test.exs supports cannot run the suite green

[timujinne]

config/test.exs deliberately supports running the suite as a role without CREATEDB against a pre-provisioned database — the comment there says so explicitly, describing it as the case a shared or managed Postgres imposes. Under exactly that model, one test cannot pass: it needs superuser, and table ownership does not substitute.

Observed

Role pk_test, rolsuper = f, rolcreatedb = f, owning the tables in a database provisioned for it. No CREATE DATABASE was performed.

mix test test/mix/tasks/phoenix_kit_doctor_orphaned_fk_destructive_test.exs

1) test check_orphaned_fk_refs/1 — destructive: a genuinely orphaned row
   outside the old 4 pairs is caught
   test/mix/tasks/phoenix_kit_doctor_orphaned_fk_destructive_test.exs:52
   ** (Postgrex.Error) ERROR 42501 (insufficient_privilege) permission denied:
      "RI_ConstraintTrigger_c_1168323" is a system trigger
   code: Repo.query!("ALTER TABLE phoenix_kit_user_oauth_providers DISABLE TRIGGER ALL")

Finished in 2.4 seconds
4 tests, 1 failure

Postgres reserves internal RI constraint triggers for superusers regardless of who owns the table, so this is a structural requirement rather than a missing grant.

It is one statement, not a broken setup

Worth stating plainly, because the natural first reading is that our role configuration is simply wrong. In the same run, under the same role and database:

• the full migration chain applied — V135 through V181, 47 migrations, completing normally;
• three of the four tests in that file passed.

Only DISABLE TRIGGER ALL fails. The model your config supports works for everything else.

A fix that does not weaken the test

Verified under the same unprivileged role:

ALTER TABLE ... DROP CONSTRAINT <fk>         -- ok
INSERT ... (row referencing a missing parent) -- ok
ALTER TABLE ... ADD CONSTRAINT <fk> ... NOT VALID  -- ok
SELECT count(*) -> 1                          -- the orphan is really there

The orphan is genuine and the constraint is restored, so the test still proves what it set out to prove — it simply reaches the state without asking for superuser.

We have this change ready and will send it as a PR if you would like it.

Not verified by us

Only this file was run under the unprivileged role. Whether other tests elsewhere need superuser for their own reasons, we did not survey.