Unable to use pgmq extension installed with dbdev

[ljagiello]

Hi,

I’m experiencing an issue with the pgmq extension, which I installed using dbdev a few months ago in my Supabase instance. Everything was working fine until today, and now I’m encountering problems like:

permission denied for sequence q_entity_event_msg_id_seqs

In psql console I see lack of permissions for pgmq.q_entity_event_msg_id_seq:

postgres=> \z pgmq.q_entity_event_msg_id_seq;
                            Access privileges
 Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+------+-------------------+-------------------+----------
(0 rows)

On localhost / previously in Supabase this specific permission was:

postgres=> \z pgmq.q_entity_event_msg_id_seq;
                                          Access privileges
 Schema |           Name            |   Type   |   Access privileges   | Column privileges | Policies
--------+---------------------------+----------+-----------------------+-------------------+----------
 pgmq   | q_entity_event_msg_id_seq | sequence | pg_monitor=r/postgres+|                   |
        |                           |          | postgres=rwU/postgres |                   |
(1 row)

When I try to grant permissions:

ostgres=> GRANT USAGE, SELECT, UPDATE ON SEQUENCE pgmq.q_entity_event_msg_id_seq TO postgres;
WARNING:  no privileges were granted for "q_entity_event_msg_id_seq"
GRANT
postgres=> \z pgmq.q_entity_event_msg_id_seq;
                                                Access privileges
 Schema |           Name            |   Type   |         Access privileges         | Column privileges | Policies
--------+---------------------------+----------+-----------------------------------+-------------------+----------
 pgmq   | q_entity_event_msg_id_seq | sequence | supabase_admin=rwU/supabase_admin |                   |
(1 row)

I cannot grant permissions to postgres user and instead user supabase_admin shows up.

I cannot uninstall my extension:

postgres=> DROP EXTENSION IF EXISTS "plpgsql@pgmq";
ERROR:  must be owner of extension plpgsql@pgmq

I'm looking for a help how to fix this issue.

[GaryAustin1]

I would contact support or file an issue in supabase/postgres.

[ljagiello]

Yes, we did. There are compatibility issues between the official pgmq extension and the TLE version we previously installed before Supabase officially supported the extension. I was informed that the Supabase Postgres team is currently working on resolving this.

[GaryAustin1]

Thanks for replying back.

[CallumBoase]

I'm also seeing this issue from pgmq previously installed via dbdev. Watching

[ljagiello]

The solution that unblocked us was to drop and recreate the queue.

postgres=> SELECT pgmq.drop_queue('entity_event');
 drop_queue
------------
 t
(1 row)

postgres=> SELECT pgmq.create('entity_event');
 create
--------

(1 row)

In our case, the queue was empty, so it was a relatively easy decision. A new queue was created with a different default value for id.

postgres=> \d pgmq.q_entity_event
                                 Table "pgmq.q_entity_event"
   Column    |           Type           | Collation | Nullable |           Default
-------------+--------------------------+-----------+----------+------------------------------
 msg_id      | bigint                   |           | not null | generated always as identity
[…]

[CallumBoase]

Thanks @ljagiello
When I run that I get this error and I can't seem to change the permissions for that table which is owned by supabase_admin rather than postgres

ERROR:  42501: permission denied for table meta
CONTEXT:  SQL statement "
    DELETE FROM pgmq.meta
    WHERE queue_name = 'txn_emails' "
PL/pgSQL function pgmq.drop_queue(text,boolean) line 14 at EXECUTE

Did you find a workaround for being able to drop the queue?

[ljagiello]

@CallumBoase that was not a case for us, I was able to just drop the queue without any additional issues.

[tomaspozo]

I am having same sequence like errors with queues created with the new Queues integration.

Initially everything worked fine, but next day, we started getting the error.

Already filed a ticket support. Hopefully this gets fixed soon.

[CallumBoase]

For anyone it may help...

To fix this I ended up migrating my database and all schema, data and storage uploads to a new supabase project.

This time, when setting up the schema I used the supabase UI to install the pgmq extension instead of installing via supabase-dbdev.

This was a pain but not out of the question for me because I already had a process in place for migrating between projects due to my development workflow, but would be difficult for someone who doesn't already have that set up.

[fellos-nl]

We ran into the same permission issue. We had installed the extension through the queues integration in Supabase Studio. We solved it by creating a new queue, which now works fine.

We're unable to drop (or use) the old queue.

[tomaspozo]

Same for me, but now my new queue has issues... Already filed a support ticket, lets hope this gets solved.

[olirice]

Apologies for the interruption. The user-uploaded version of pgmq on dbdev plpgsql@pgmq had an unexpected interaction with the permissions for the native pgmq that now ships with Supabase. A fix has been rolled out for impacted projects + new queues will be created with the original/expected set of permissions.

If you aren't tightly tied to the dbdev version I'd recommend dropping it in favor of the native extension as it'll behave more predictably

If you continue to have any trouble with plpgsql@pgmq please open a support ticket and we'll take a look directly at your project's permissions

[tomaspozo]

Hi, I did not used any other version previously only the new integration. Already filed a support ticket. Based on what you say, I can create a new queue and it will work fine? that works for me... and later with the help of the support team delete the others (I also get an error).

Is this new update already available for cloud projects?

[CallumBoase]

My original database where pgmq was installed via supabase-dbdev is now working again.

I had since recreated my database in a new project where I installed queues via the official supabase UI. This started experiencing a very similar issue to queues installed via supabase-dbdev! I was unable to send messages with pgmq due to lack of permissions on the sequence, and was unable to drop and recreate existing queues to fix it.

So whatever fix Supabase rolled out for the original issue broke my project where I installed queues via the official method.

I did a reset of my new database to fix it and am operational again. Hopefully this doesn't happen again if new fixes are rolled out.