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.