permission denied to create extension "postgis" #2472
Comments
|
The issue is not immediately reproducible on a brand new supabase.com project. However, I can still reproduce it shortly thereafter. I can't nail down the exact minimal reproduction yet. One would expect to recover from this errored state by running |
|
I added the minimal reproduction steps to the original post. |
|
It turns out this is solved by waiting a bit after running |
|
Thanks for the repro. I believe drop extension should succeed without requiring db reset. This looks like a bug in hosted project or supautils. |
|
Were you able to reproduce the issue? I initially thought I was doing something wrong, but it seems this is broken for any project on supabase.com (see minimal repro). I'm not sure what I can do un-break my project; I'd like to reset it with And when when attempting to disable postgis through the supabase.com dashboard gui: # taken from browser devtools on supabase.com
curl 'https://api.supabase.com/platform/pg-meta/abc123/extensions?id=postgis' -X DELETE
# {"message":"failed to delete pg.extensions with the given ID: must be owner of extension postgis"}Assuming it can be reproduced, would it be possible to get more eyes on this as it seems like a critical bug to me (project is left in an un-recoverable state as result of enabling an extension). |
It's good to know I can do that, but that would not be necessary at the moment as my hosted project is still fresh where I might as well create a new one. The blocker is that this issue happens on every new project I create, preventing me from properly setting up a production project on supabase.com. For context: I've spent the last several weeks preping things using the local supabase stack and it's been great. I'm currently trying to set up the hosted project for production but I keep facing this issue. |
|
@thenbe I just tried reproducing this on a new project but I'm getting a different error at step 3 Have you tried using psql to execute step 2 instead of kysely? I'm not sure how kysely manages migration but it might be that it's trying to alter some default permissions or entities managed by Supabase. |
|
You're right, those minimal repro steps do not lead to that error ( Afterwards, when I tried to run my migration with The most painful thing about is that creating a minimal reproduction is very time-consuming because, by definition, it involves reaching a state where the hosted database is "bricked" (can longer be reset with |
|
I managed to reproduce the issue on a VPS and narrow it down to the "mode" Note that:
# errors with ERROR: must be owner of extension postgis (SQLSTATE 42501)
supabase db reset --db-url 'postgresql://postgres:[email protected]:54322/postgres'
# errors with ERROR: must be owner of extension postgis (SQLSTATE 42501)
supabase db reset --db-url 'postgresql://postgres:postgres@$EXTERNAL_IP_OF_THIS_VPS:54322/postgres'
# ok, does not error
supabase db reset --local |
|
Here is the output when running with output of good commandoutput of bad command |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
When I attempt to enable the postgis extension, as per the docs, I get the following error:
What is the proper way to manage extensions using a single source of truth (i.e. not the GUI). Can I not manage extensions using vanilla SQL migrations?
For reference, I'm using kysely's migration cli. Could that be the issue? Do I need to use supabase's own migration tooling? Or does any standard sql tool work?
Minimal reproduction
Create new project on supabase.com
Connect to the db as user
postgresand run this seed script:supabase db reset --linkedERROR: permission denied to create extension "postgis" HINT: Must be superuser to create this extension.The text was updated successfully, but these errors were encountered: