How to create a logical replication subscription with SSL connection?

Google Cloud Database
1

I want to set up logical replication from one database to another in a PostgreSQL instance. Creating the subscription requires some SSL certificates, which unfortunately caused some issues.

The following chunk of code is some zsh-script that I wrote in order to set up the logical replication.

HOST=?
PORT=?
DB_NAME=?

SSL_CERT=?/client-cert.pem
SSL_KEY=?/client-key.pem
SSL_ROOT_CERT=?/root.crt

SU_PASSWORD=
SU_USER="postgres"

R_USER=?
R_PASSWORD=?

PUBLICATION_NAME="stock_publication"
SUBSCRIPTION_NAME="stock_subscription"
# CONNECTION_INFO="host=$HOST port=$PORT dbname=$DB_NAME \
#     user=$R_USER password=$R_PASSWORD"
CONNECTION_INFO="host=$HOST port=$PORT dbname=$DB_NAME \
    sslmode=allow sslcert=$SSL_CERT sslkey=$SSL_KEY \
    user=$SU_USER password=`$SU_PASSWORD`"

exec_query() {
    # Execute a query with SSL connection

    PGPASSWORD=$R_PASSWORD \
    psql "sslmode=allow \
    sslcert=$SSL_CERT \
    sslkey=$SSL_KEY \
    hostaddr=$HOST \
    port=5432 user=$R_USER dbname=$DB_NAME" \
    --command=$1
}

# exec_query
exec_query "DROP PUBLICATION IF EXISTS $PUBLICATION_NAME;"
exec_query "CREATE PUBLICATION $PUBLICATION_NAME FOR TABLE stock.location, stock.mutation_type, stock.stock, stock.stock_view, stock.stock_view_mutation_type;"
exec_query "DROP SUBSCRIPTION IF EXISTS $SUBSCRIPTION_NAME;"
exec_query "CREATE SUBSCRIPTION $SUBSCRIPTION_NAME CONNECTION '$CONNECTION_INFO' PUBLICATION $PUBLICATION_NAME;"

The first three queries work fine, but creating the subscription fails.

DROP PUBLICATION
CREATE PUBLICATION
NOTICE: subscription “stock_subscription” does not exist, skipping
DROP SUBSCRIPTION
ERROR: certificate is not allowed
DETAIL: Non-superusers cannot use certificate in the connection setting.

The ‘certificate is not allowed’ error surprises me, since it is caused by not being a superuser. Cloud SQL does not fully support the Postgres superuser role and instead offers the ‘cloudsqlsuperuser’ role. How am I supposed to fix this issue if I cannot create the subscription as a Postgres superuser?

2

I found this answer 1 from Stack Overflow where a user uses gcloud sql connect while running the Cloud SQL Proxy. Google Cloud SQL manages SSL connections for you with gcloud sql connect, so that removes the need for passing certificates if set up properly.