I’m upgrading an app from Ash ~2.x to the latest (3.4) and… overall, not too hard until I tried to run the tests and got this…

** (Postgrex.Error) ERROR 42501 (insufficient_privilege) only superuser can define a leakproof function
    (ecto_sql 3.12.0) lib/ecto/adapters/sql.ex:1078: Ecto.Adapters.SQL.raise_sql_call_error/1
    (elixir 1.17.2) lib/enum.ex:1703: Enum."-map/2-lists^map/1-1-"/2
    (ecto_sql 3.12.0) lib/ecto/adapters/sql.ex:1185: Ecto.Adapters.SQL.execute_ddl/4
    (ecto_sql 3.12.0) lib/ecto/migration/runner.ex:348: Ecto.Migration.Runner.log_and_execute_ddl/3
    (elixir 1.17.2) lib/enum.ex:1703: Enum."-map/2-lists^map/1-1-"/2
    (elixir 1.17.2) lib/enum.ex:1703: Enum."-map/2-lists^map/1-1-"/2
    (ecto_sql 3.12.0) lib/ecto/migration/runner.ex:311: Ecto.Migration.Runner.perform_operation/3
    (stdlib 5.2.3) timer.erl:270: :timer.tc/2

Obviously, a Postgres permission problem of some sort, but I’ve never seen it before and have no clue how to fix it. Just doing grant all privileges on database X to ROLE doesn’t fix it (I tried).

Any ideas what’s going on here?

It seems to pop up after this bit runs in mix ash.reset:

11:31:58.261 [info] execute "CREATE OR REPLACE FUNCTION timestamp_from_uuid_v7(_uuid uuid)\nRETURNS TIMESTAMP WITHOUT TIME ZONE\nAS $$\n  SELECT to_timestamp(('x0000' || substr(_uuid::TEXT, 1, 8) || substr(_uuid::TEXT, 10, 4))::BIT(64)::BIGINT::NUMERIC / 1000);\n$$\nLANGUAGE SQL\nIMMUTABLE PARALLEL SAFE STRICT LEAKPROOF;\n"

But… very strange. If I run mix ecto.migrate --log-migrations-sql it eventually blows up on the last function it tries to create from the extensions.exs migration:

zac@Firefly boss_site % mix ecto.migrate --log-migrations-sql
...other stuff...
12:31:01.357 [debug] QUERY ERROR db=2.2ms
CREATE OR REPLACE FUNCTION timestamp_from_uuid_v7(_uuid uuid)
RETURNS TIMESTAMP WITHOUT TIME ZONE
AS $$
  SELECT to_timestamp(('x0000' || substr(_uuid::TEXT, 1, 8) || substr(_uuid::TEXT, 10, 4))::BIT(64)::BIGINT::NUMERIC / 1000);
$$
LANGUAGE SQL
IMMUTABLE PARALLEL SAFE STRICT LEAKPROOF;
 []
** (Postgrex.Error) ERROR 42501 (insufficient_privilege) only superuser can define a leakproof function
    (ecto_sql 3.12.0) lib/ecto/adapters/sql.ex:1078: Ecto.Adapters.SQL.raise_sql_call_error/1
    (elixir 1.17.2) lib/enum.ex:1703: Enum."-map/2-lists^map/1-1-"/2
    (ecto_sql 3.12.0) lib/ecto/adapters/sql.ex:1185: Ecto.Adapters.SQL.execute_ddl/4
    (ecto_sql 3.12.0) lib/ecto/migration/runner.ex:348: Ecto.Migration.Runner.log_and_execute_ddl/3
    (elixir 1.17.2) lib/enum.ex:1703: Enum."-map/2-lists^map/1-1-"/2
    (elixir 1.17.2) lib/enum.ex:1703: Enum."-map/2-lists^map/1-1-"/2
    (ecto_sql 3.12.0) lib/ecto/migration/runner.ex:311: Ecto.Migration.Runner.perform_operation/3
    (stdlib 5.2.3) timer.erl:270: :timer.tc/2

Yet if I connect to Postgres, I’ve no problem creating the function manually:

boss_site_test-# execute "CREATE OR REPLACE FUNCTION timestamp_from_uuid_v7(_uuid uuid)\nRETURNS TIMESTAMP WITHOUT TIME ZONE\nAS $$\n  SELECT to_timestamp(('x0000' || substr(_uuid::TEXT, 1, 8) || substr(_uuid::TEXT, 10, 4))::BIT(64)::BIGINT::NUMERIC / 1000);\n$$\nLANGUAGE SQL\nIMMUTABLE PARALLEL SAFE STRICT LEAKPROOF;\n"
boss_site_test-#

So… it looks like it’s blowing up after (successfully??) running the extensions.exs but before trying to create tables. And indeed, after connecting to the DB, there are no tables. So my best guess… the extensions migration ran, and all the functions have been created, but then it blows up as soon as it tries to create the database tables… or maybe it is actually blowing up on that last function, but for whatever reason I can’t reproduce the problem manually. Gah. Halp?