MPG SuperUser Permissions for Extensions

Questions / Help
,
1

Trying to get our CI and Review apps working correctly and have hit this road block:

ERROR: permission denied to create extension “postgis”
HINT: Must be superuser to create this extension.

I have checked that our user is schema admin, but still doesn’t work.

I did read this: Enabling pgvector extension on Managed Postgres cluster
And I understand the UI button toggle.

However, we let any dev spin up review apps for testing and sharing per PR at their discretion. The only way to turn this on for a review app (ephemeral db) is to let them into fly, so they can click a toggle box. That seems crazy to me. Am I missing something here on how this can be possible?

3

Are you creating these review app clusters via CI? If so you can update your CI’s creation flow to enable Postgis at cluster creation time by using fly mpg create --enable-postgis-support.
This will create the cluster with PostGis enabled out of the box, and doesn’t require using the UI to enable it. Does that work for your use case?

Granting full superuser permissions to all users isn’t something we’re planning at this time, though with MPG v2 we might be able to offer better access to features that otherwise require SU perms.

4

The cluster is already created, and the enable postgis support is already on. The review apps are created by CI and create MPG databases that are specific for the review app. So even thought the support is on, it is not enabled until I go to the MPG db and toggle the switch.

5

This is causing some serious gymnastics when it comes to getting or process back on track, while starting to implement spatial features. Is there maybe a support ticket/DM where I could get this enabled on just a single cluster or something? @Sam-Fly

6

Oh, that’s not intentional then! Creating it with the flag set should get it set up for postgis without having to do the manual toggle. It looks like that is not passing through correctly. We’re taking a look

7

@Sam-Fly I appreciate you taking a look, but want to make sure I actually correctly communicated the issue:

Cluster: AppName-Staging

  • DB: staging
  • DB: reviewapp-pr-1
  • DB: reviewapp-pr-2

Enable PostGIS support (before you can toggle it on) - seems to apply at the cluster level, then you have the option to toggle it on at the db level:

CI/CD - create db reviewapp-pr-3 > good
Enable extention postgis > insufficient privelages

**Addition: The process builds a full stack app from top to bottom. And while we can dance around this issue to get it up and running, as soon as we start using spatial columns, migrations fail, seeds fail.

* Tested: created a fresh MPG cluster with --enable-postgis-support, then created a new database inside it and checked pg_extension. PostGIS binaries are available but the extension is NOT pre-installed in new databases (SELECT extname FROM pg_extension WHERE extname = 'postgis' returns 0 rows). The flag just makes CREATE EXTENSION postgis possible a superuser still has to run it per-database. The dashboard toggle is the only way to do that now.

8

Thank you for those extra details, that does help. At a minimum I expect we should be able to expose the CREATE EXTENSION on the db level via flyctl for doing it programatically.

9

The best news this week. Can’t wait, I would love to rip out all this monkeypatching!

10

@Sam-Fly was curious if there was a time frame. I am holding off on some other extension for the team , and they asked me for a target to check back in. I failed to ask last week, but is this a (next couple of weeks fix) or some future release on a roadmap.

Thanks again!

11

Hi @wdenny3885! I want to drop a note that we haven’t forgotten about this, and we’ll have a more concrete update on the progress and timeline within the next couple of days!

12

Sounds great! I also saw the update on the “Drop a Database”… +1 for that as well. It was on the list, but glad someone else already got to it!

13

@wdenny3885 Thanks for your patience here!

The next version of flyctl (v0.4.68, coming later today) includes the fly mpg database extension list | enable | disable family of commands, which allows for enabling/disabling cluster extensions in the same manner as the dashboard.

This should let you script enabling any supported extension on any database as needed. For postGIS you can enable it on any clusters created with postgis enabled, either in the dashboard or via fly mpg create --enable-postgis-support

Here’s an example of that in action:

# create a new cluster w/ postgis support
fly mpg create --enable-postgis-support 

# create a new db within the cluster
fly mpg db create -n new-db  new-cluster

# confirm postgis isn't installed in new database:
fly mpg connect new-cluster -d new-db
 new-db=>  SELECT extname FROM pg_extension WHERE extname = 'postgis'; 
 extname 
---------
(0 rows)

#enable postgis on db
flyctl mpg db extensions enable postgis -c new-cluster -d new-db

new-db=>  SELECT extname FROM pg_extension WHERE extname = 'postgis';
 extname 
---------
 postgis
(1 row)