Can't create postgis extension on Google Cloud SQL

Question

I've got a Postgres database and user I've created on Google Cloud SQL.

I'm trying to install the postgis extension for that user:

myuser=> CREATE EXTENSION postgis;
ERROR:  permission denied to create extension "postgis"
HINT:  Must be superuser to create this extension.

as you can see, it won't allow me to create the extension for this user, so I attempted to make this user a superuser from the postgres role:

postgres=> ALTER USER myuser WITH SUPERUSER;
ERROR:  must be superuser to alter superusers

and I get the following error. This is because Google Cloud SQL does not allow the SUPERUSER role for any postgres accounts according to: https://cloud.google.com/sql/docs/postgres/users.

So I'm in this weird state of purgatory that I need to add this extension, but can't.

Any tips on how to proceed?

score 4 · Accepted Answer · answered Apr 26 '19 at 16:35

4

It looks like creating a new user through the API (or the console) will give it the proper permissions, at least according to the prompt when trying to add additional users through the console:

answered Apr 26 '19 at 16:35

Jofre

3,718
1
23
31

2

The link on the screenshot points to here: https://cloud.google.com/sql/docs/postgres/users – Jofre Apr 26 '19 at 16:35
Wow! Thank you!! – bob_cobb Apr 26 '19 at 16:47
1

I am having a similar problem, but creating a new user did not seem to solve the issue (although I do get the same message) – Pinna_be May 20 '21 at 15:48

Can't create postgis extension on Google Cloud SQL

1 Answers1

Linked