S
Supabase•9mo ago
Bubu

Permission denied for sequence q_conversation_generation_msg_id_seq

Hi, My code is returning this error since a few days while it was working before and continue to works locally. Did something change?
SELECT
schemaname,
sequencename,
sequenceowner
FROM
pg_sequences
WHERE
sequencename = 'q_conversation_generation_msg_id_seq';
---
| schemaname | sequencename | sequenceowner |
| ---------- | ------------------------------------ | ------------- |
| pgmq | q_conversation_generation_msg_id_seq | postgres |
SELECT
schemaname,
sequencename,
sequenceowner
FROM
pg_sequences
WHERE
sequencename = 'q_conversation_generation_msg_id_seq';
---
| schemaname | sequencename | sequenceowner |
| ---------- | ------------------------------------ | ------------- |
| pgmq | q_conversation_generation_msg_id_seq | postgres |
os.getenv("SUPABASE_URL"),
os.getenv("SUPABASE_KEY"),
options=ClientOptions(schema="pgmq_public")
)

message = {
'type': 'conversation_generation',
'email': user.user.email,
'model_instance_id': model_instance_response.data[0]['instance_id']
}

generation_task_response = supabase_queue_client.rpc('send', { 'queue_name': QUEUE_CONVERSATION_GENERATION, 'message': message}).execute()
if not generation_task_response.data:
return jsonify({'error': 'Error creating the generation task'}), 500
os.getenv("SUPABASE_URL"),
os.getenv("SUPABASE_KEY"),
options=ClientOptions(schema="pgmq_public")
)

message = {
'type': 'conversation_generation',
'email': user.user.email,
'model_instance_id': model_instance_response.data[0]['instance_id']
}

generation_task_response = supabase_queue_client.rpc('send', { 'queue_name': QUEUE_CONVERSATION_GENERATION, 'message': message}).execute()
if not generation_task_response.data:
return jsonify({'error': 'Error creating the generation task'}), 500
56 Replies
Bubu
BubuOP•9mo ago
My code is actually:
supabase_queue_client: Client = create_client(
os.getenv("SUPABASE_URL"),
os.getenv("SUPABASE_KEY"),
options=ClientOptions(schema="pgmq_public")
)
try:
supabase_queue_client.rpc('send', { 'queue_name': QUEUE_CONVERSATION_GENERATION, 'message': message}).execute()
except PostgrestAPIError as e:
logger.exception(f"Error when creating the generation task: {e}")
return jsonify({'error': 'Error creating the generation task'}), 500
supabase_queue_client: Client = create_client(
os.getenv("SUPABASE_URL"),
os.getenv("SUPABASE_KEY"),
options=ClientOptions(schema="pgmq_public")
)
try:
supabase_queue_client.rpc('send', { 'queue_name': QUEUE_CONVERSATION_GENERATION, 'message': message}).execute()
except PostgrestAPIError as e:
logger.exception(f"Error when creating the generation task: {e}")
return jsonify({'error': 'Error creating the generation task'}), 500
garyaustin
garyaustin•9mo ago
There have been issues with the REST access to the queue and permissions. They shut off any one adding those in the UI a week or so ago and have a fix on the way.
Bubu
BubuOP•9mo ago
Ok good to know that I haven't changed something by mistake in my code! Thanks for letting me know. Is there a way to track this issue?
garyaustin
garyaustin•9mo ago
GitHub
Revert "Temporarily disable enabling of queues exposure via postgre...
Re-enables exposing pgmq over APIs Reverts Temporarily disable enabling of queues exposure via postgrest #32699 Resolves the underlying permissions issue
garyaustin
garyaustin•9mo ago
That is the PR. I can't find the threads here but I think people were creating a new queue to keep going for the moment.
Bubu
BubuOP•9mo ago
Thanks So new queues are not impacted by the issue?
garyaustin
garyaustin•9mo ago
I think they might still fail eventually until the fix gets put in place. I do not know the details of what is involved. If just studio then hard refresh of browser after it is released usually gets it. Seems like doing something breaks them and they had to fix that. It is not a new issue that just started breaking them.
Bubu
BubuOP•9mo ago
Ah ok I see, something happens behind the scene causing the mismatch
garyaustin
garyaustin•9mo ago
That is what I gather. It has been happening for maybe 2 weeks to a user every couple of days and I think they shut it off for new users knowing something was wrong, but not what until that PR. But that is all from watching things.
Bubu
BubuOP•9mo ago
thanks, super helpful!
garyaustin
garyaustin•9mo ago
This had a warning on it and does not now. I could not turn in on a couple of days ago...
No description
tomaspozo
tomaspozo•9mo ago
Yeah, I had a couple of issues too (twice the error you shared), I wrote to support and they helped me out with the following: - Drop pgmq_public schema - Run the same code that Studio runs to enable queues over APIs (substitute pgmq_public for ${QUEUES_SCHEMA}) - Make sure pgmq_public is in the API exposed_schemas setting for your project It worked out for me, but tbh, I decided to use a proxy like RPC function that takes charge of invoking pgmq.send instead of calling from the client. Until queues API is stable. Hope this help out! If not sure of running this code, reach out too support 😉
GitHub
supabase/apps/studio/data/database-queues/database-queues-toggle-po...
The open source Firebase alternative. Supabase gives you a dedicated Postgres database to build your web, mobile, and AI applications. - supabase/supabase
Bubu
BubuOP•9mo ago
@Tomás P. awesome, it seems it worked!
naveedehmad
naveedehmad•9mo ago
oh, my issue seems related. i'll check. thanks folks!
Aaron Farr
Aaron Farr•9mo ago
I think this may be the root of my problems as well!
Nahro
Nahro•9mo ago
I have the same issue! Looks like i will use another MQ option instead of Supabase, shame that the feature isnt stable Gonna use RabbitMQ instead
tomaspozo
tomaspozo•9mo ago
Btw, is just the http wrapper around PGMQ that is presenting issues, meaning the part that allows calling pgmq functions from the client side... so all the actual MQ functionality is working great! is Tembo's PGMQ... At the end, if you use another MQ, you wouldn't have direct client calls using Supabase SDK's so, you don't loose much is staying... I would still encourage to use it, because of all the benefits you will get of having everything under Supabase... I am doing that and as I mentioned, I am using an RPC in case I need calls from the client side...
Nahro
Nahro•9mo ago
But the HTTP wrapper is what is being used by our apps.. So if the HTTP wrapper isnt working, then it is just not working at all
tomaspozo
tomaspozo•9mo ago
Honest question... what difference would it make moving to RabbitMQ?
Nahro
Nahro•9mo ago
It.. works? not getting weird permission denied error, like this post mentioned
tomaspozo
tomaspozo•9mo ago
Sorry I'm lost... the "HTTP wrapper" that I was talking about is Supabase specifics to make the calls using the Supabase sdks... If you use RabbitMQ, you don't have that neither... so wondering what do you mean with HTTP wrapper?
Nahro
Nahro•9mo ago
How are you going to talk to supabase if the HTTP wrapper is broken
garyaustin
garyaustin•9mo ago
The permission denied was supposed to be fixed. Several instances already using had to be patched. If this is not working (you lose permission after 24 hours) for you then you should put in a support request. Also you can do an rpc call to access the queues with a security definer function.
tomaspozo
tomaspozo•9mo ago
Because you still have access to all the pgmq schema from Postgres functions (RPC from the client) so you can still call all pgmq functions like pgmq.send that is not HTTP, that is pure postgres 🙂 This are just workarounds until the team solves this 100%, which will happen very soon... as Gary states, probably is already solved
garyaustin
garyaustin•9mo ago
From an SB employee last week.
No description
garyaustin
garyaustin•9mo ago
The option has been turned back on in my instance so I assume they believe it is fixed.
Nahro
Nahro•9mo ago
Unfortuantely I need this to work right now. If i send a support ticket, I dont know how long it will take I am only using queues on the backend, so its allright Using rabbitmq i can more easily have a seperate local setup. otherwise i had to spin up supabase locally, which would have been more effort probably This didnt fix it for me. It looks like after some time the queue corrupts or something
tomaspozo
tomaspozo•9mo ago
So you applied the fixed, it worked, but after a while it stopped working again? can you sare what error you are having?
Nahro
Nahro•9mo ago
Same error as the original post mentioned
tomaspozo
tomaspozo•9mo ago
got it, it looks like it hasn't been solved yet... in my case I use a db function (RPC) to bypass the http_client... and haven't had any errors doing that way... @Nahro , did you recently upgraded your project and got the error after that? does re aplying the above solution works?
Nahro
Nahro•9mo ago
Im doing the same now too. It only seems to apply to the send rpc function btw. ardhive/delete/read all still work with pgmq_public rpc
garyaustin
garyaustin•9mo ago
@Nahro maybe you should be clear on exactly what the issue is as far as an error. I think you just said I have the same issue when you came on to the thread. Maybe it is something else as your other rpc calls work.
Nahro
Nahro•9mo ago
No description
Nahro
Nahro•9mo ago
Exactly the same error as the title of this thread (Different queue name of course)
garyaustin
garyaustin•9mo ago
Does it work for awhile on a new queue and then error after a day? Seems odd it would only impact send.
Nahro
Nahro•9mo ago
Yes It works And then after some time, it doesnt work But less than 24 hours
tomaspozo
tomaspozo•9mo ago
probably because it involves the record creation?
Nahro
Nahro•9mo ago
But after reading this, probably when the backup job gets executed, which I dont know when the time is (the backup hook the superbase dev was talking about)
garyaustin
garyaustin•9mo ago
Yeah, that is what I meant. Sounds like the 2nd...
No description
garyaustin
garyaustin•9mo ago
So yes that seems it would only impact send.
Nahro
Nahro•9mo ago
you sure? because im sending it with service role auth key so service_role, not authenticated/anon
garyaustin
garyaustin•9mo ago
Well the first would impact everything I would think.... Although the sequence may not need accessing except on writes. But the main thing is you still have the reset after a time so it is related to this bug. Can you DM your instance id with permission for SB to look at it.
Nahro
Nahro•9mo ago
You mean my Project ID?
garyaustin
garyaustin•9mo ago
Yes
Nahro
Nahro•9mo ago
Sure
garyaustin
garyaustin•9mo ago
I'll send it to the dev who was looking at this. Even if you have moved on it might help others.
Nahro
Nahro•9mo ago
Yes For now, just used the same function definition mentioned earlier here for recreating the pgmq_public function, but hanged it under public and security definer
tomaspozo
tomaspozo•9mo ago
Yeah, it was the same I was experiencing weeks ago... probably your project hasn't been patched, have you write to support?
Nahro
Nahro•9mo ago
Yes, yesterday
garyaustin
garyaustin•9mo ago
Are you also posting on Github on this?
Nahro
Nahro•9mo ago
Didn't plan to
garyaustin
garyaustin•9mo ago
OK. Just checking as I bumped this again based on you and another user there. Otherwise support was the plan for any missed, but with 3 reports that is quite a few missed.
Nahro
Nahro•9mo ago
where are the other 2 reports?
garyaustin
garyaustin•9mo ago
GitHub
pgmq lost permission · supabase · Discussion #32717
im sending messages to pgmq through edge functions with the following code: const params = await req.json(); const supabaseUrl = Deno.env.get('SUPABASE_URL'); const supabaseKey = Deno.env.g...
Bubu
BubuOP•9mo ago
Is the fix released? After resetting my local instance today, I had to execute the studio code
garyaustin
garyaustin•9mo ago
@Bubu , You should start a new question. This was for RLS and using anon when it was off. Just took awhile for me (not having used) to sort it myself. There was an issue on hosted instances when a backup occurred of losing queue permissions. Not sure any of this to do with executing studio code or local specifically.

Did you find this page helpful?