3

My goal is to enable the ltree postgres extension in an automated way (GCP PostgreSQL extensions).

This is the query needed to create the extension CREATE EXTENSION IF NOT EXISTS ltree;. I verified that this command works when I manually connect to the cloudsql instance using the following method. When I do this, the migrations run fine for that instance.

gcloud sql connect MyBackendInstance --user=postgres
postgres=> \c my_database;
Password:
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
You are now connected to database "my_database" as user "postgres".
mch_staging=> CREATE EXTENSION IF NOT EXISTS ltree;
CREATE EXTENSION

Now I have the following django migration from a library called django-ltree, which works perfect on my local installation.

migrations.RunSQL(
    "CREATE EXTENSION IF NOT EXISTS ltree;",
    "DROP EXTENSION ltree;"
)

However, when I run that migration in my pipeline (without manually installing the ltree connection using the method above), which uses the cloud_sql_proxy to connect to the database, I get the following error:

Traceback (most recent call last):
  File "/usr/local/lib/python3.7/site-packages/django/db/backends/utils.py", line 82, in _execute
    return self.cursor.execute(sql)
psycopg2.errors.InsufficientPrivilege: permission denied to create extension "ltree"
HINT:  Must be superuser to create this extension.

The command in the pipeline is quite simple:

./cloud_sql_proxy -instances="$CLOUD_SQL_INSTANCES"=tcp:5432 -credential_file=gcloud-api-credentials.json &
python backend/manage.py migrate

The credentials to connect with the database are correctly defined in the django settings, we've been able to use these credentials to perform all kinds of migrations for ages.

I have tried creating a new user as described in this question, this did not solve the issue and thus seems unrelated.

UPDATE I tried running the cloud_sql_proxy locally, using the same authentication file to install a different extension, and that seems to work flawlessly.

Pinna_be
  • 4,517
  • 2
  • 18
  • 34
  • Does this answer your question? [Can't create postgis extension on Google Cloud SQL](https://stackoverflow.com/questions/55871400/cant-create-postgis-extension-on-google-cloud-sql) – Mike Organek May 20 '21 at 15:39
  • @MikeOrganek good find, it seems quite related. However I tried creating a new user already and using that user. I got the same permission error. – Pinna_be May 20 '21 at 15:40
  • Did you try to use ltree data structure as is, without create the extension? I have the feeling that is already deployed and installed on Cloud SQL. – guillaume blaquiere May 21 '21 at 07:15
  • Hi Guillaume, when I logged in using gcloud sql connect, I could see the extension was not installed by default. Using the same tool, I was able to create the extension manually now. I will specify in my question that it fails when I run the sql command using cloud_sql_proxy, after I'm able to confirm it is in fact installed – Pinna_be May 21 '21 at 07:45

0 Answers0