Exposing pgmq queues via migration with RLS
Hey everyone, I’m working with pgmq queues in Supabase. I have a cron job calling an Edge Function and I want to expose queues via PostgREST using a schema migration instead of the Supabase Dashboard.
I also need RLS on the queues so I can safely do this:
const { error: deleteError } = await supabase
.schema('pgmq_public')
.rpc('delete', {
queue_name: QUEUE_NAME,
msg_id: message.msg_id,
});
Has anyone done this before? Any examples of exposing pgmq queues via migration and setting up RLS would be really helpful.
3 Replies
I don't know if I would attempt to duplicate it but here is the Supabase code that sets up the pgmq_public schema...https://github.com/supabase/supabase/blob/1431ee8ff763f27e0ba480f4b737e01920b95842/apps/studio/data/database-queues/database-queues-toggle-postgrest-mutation.ts#L19
It is not pretty and you would risk changes breaking your code.
You could certainly implement the functions on your own as they are just shells around the pgmq "native" functions.
Also there is no SQL way to enable a schema on the API although there is a Management API call to do it.
Totally understand the concern...
However, doing it on the UI throws an error
Failed to toggle queue exposure via PostgREST: function pgmq.read(text, integer, integer) does not exist
There's a git issue with a temporary fix suggestion: https://github.com/supabase/supabase/issues/39656 which led me to consider just having it all on code instead.
But thank you.GitHub
Enabling "Expose Queues via PostgREST" fails on newest Postgres Ver...
Bug report [ x ] I confirm this is a bug with Supabase, not with my own application. [ x ] I confirm I have searched the Docs, GitHub Discussions, and Discord. Describe the bug Enabling "Expos...
I had it all in code. The upgrade to pgmq extension broke all that, too. All of the grants on the functions disappeared. I had to partially re-apply a migration to fix the grants, and it also tripped up on the change in the
read() function signature.
The code I got was from here https://github.com/orgs/supabase/discussions/32969#discussioncomment-13512379GitHub
Enabling exposure of Queues via PostgREST is temporarily disabled ...
How can I restore(using brew) the functionality of 'Expose Queues via PostgREST'? It seems to have broken in version 2.6.8