Xata•3d ago

ERROR: permission denied for schema public

Hey! I was trying to deploy an Elixir - Phoenix application (Xata Direct Connection Beta) and I'm getting an error when running migrations. I'm using the Oban job library and it has a migration to setup the job table which runs the following SQL:

    DO $$
    BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type
                   WHERE typname = 'oban_job_state'
                     AND typnamespace = 'public'::regnamespace::oid) THEN
        CREATE TYPE public.oban_job_state AS ENUM (
          'available',
          'scheduled',
          'executing',
          'retryable',
          'completed',
          'discarded'
        );
      END IF;
    END$$;

    DO $$
    BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type
                   WHERE typname = 'oban_job_state'
                     AND typnamespace = 'public'::regnamespace::oid) THEN
        CREATE TYPE public.oban_job_state AS ENUM (
          'available',
          'scheduled',
          'executing',
          'retryable',
          'completed',
          'discarded'
        );
      END IF;
    END$$;

I get the following error:

Query 1 ERROR at Line 1: : ERROR:  permission denied for schema public
CONTEXT:  SQL statement "CREATE TYPE public.oban_job_state AS ENUM (
          'available',
          'scheduled',
          'executing',
          'retryable',
          'completed',
          'discarded'
        )"
PL/pgSQL function inline_code_block line 6 at SQL statement

Query 1 ERROR at Line 1: : ERROR:  permission denied for schema public
CONTEXT:  SQL statement "CREATE TYPE public.oban_job_state AS ENUM (
          'available',
          'scheduled',
          'executing',
          'retryable',
          'completed',
          'discarded'
        )"
PL/pgSQL function inline_code_block line 6 at SQL statement

I looked around the docs & limitations but could not figure out why this errors? I upgraded to Pro and provisioned a dedicated cluster thinking maybe it was because functions are only supported on a dedicated cluster, but I get the same error. Thanks in advance for your help!

GitHub

GitHub - oban-bg/oban: 💎 Robust job processing in Elixir, backed by...

💎 Robust job processing in Elixir, backed by modern PostgreSQL and SQLite3 - oban-bg/oban

2 Replies

tsg•3d ago

hi, the issue is the IF condition used by that pl/pgsql script. We have some basic support for pl/pgsql but that's not enough for your case. We are working on supporting pl/pgsql fully, but it's not quite ready yet. It should be ready in a few weeks (it's a bit of a complicated problem because we need to rewrite the pl/pgsql). Is there any way you could edit the resulting SQL script to remove the IF condition? I think it will work then.

Kramer•3d ago

@tsg Thanks for the response! Makes sense, unfortunately the migration is part of the library, and the same sort of expression appears in the Pro version I'm using which is closed source. But neat it's being worked on! I'll give it another go when support rolls out 🚀

Gaming

Programming

ERROR: permission denied for schema public