ERROR 42501 (insufficient_privilege) only superuser can define a leakproof function

Ash Forum Ash Questions / Help
, ,
1

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?

2

Remove LEAKPROOF

1 Like
3

I’ll give that a try… but I feel compelled to point out this is generated by Ash, when I add in “ash_functions” (to shut up the associated warning)…

def installed_extensions do
  ["uuid-ossp", "citext", "ash-functions"]
end
4

Yep, manually editing out LEAKPROOF fixes it.

5

Yeah, the main problem is that some users don’t have super user access to their database. I’ve been hesitant to remove it because it is in fact an optimization for those that do have super user access to their database. I’ll remove it though because people keep getting bitten by it :laughing: