Setting session_replication_role for GCP Cloud SQL

Question

I am trying to run SET session_replication_role = 'replica'; in a GCP Cloud SQL Postgres 9.6 instance, however I'm encountering this error ERROR: permission denied to set parameter "session_replication_role" even if the postgres user is a cloudsql admin user. Do I have to spin up my own self managed instance to solve the problem or is there a way around it?

Answer 1

Unfortunately, it is not connected with the service is in Beta or not, you can't set session_replication_role in GCP Cloud SQL.

You need to have superuser privileges to do that operation, but GCP Cloud only allows to cloudsqlsuperuser privileges. It's features as follows:

When you create a new Cloud SQL for PostgreSQL instance, the default postgres user is already created for you, though you must set its password.

The postgres user is part of the cloudsqlsuperuser role, and has the following attributes (privileges): CREATEROLE, CREATEDB, and LOGIN. It does not have the SUPERUSER or REPLICATION attributes.

You can find much more information in this blog post.