How to create a logical replication subscription with SSL certificates in Google Cloud SQL (PostgreSQL)?

Question

I would like to use CDC with PostgreSQL's logical replication feature to gather data from different databases in the same instance for a materialized view. However, I cannot get this to work with SSL.

To test the logical replication, I wrote a zsh script that takes care of the publication and subscription.

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=$R_USER password=$R_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 t1, t2, ...;"
exec_query "DROP SUBSCRIPTION IF EXISTS $SUBSCRIPTION_NAME;"
exec_query "CREATE SUBSCRIPTION $SUBSCRIPTION_NAME CONNECTION '$CONNECTION_INFO' PUBLICATION $PUBLICATION_NAME;"

However, creating the subscription fails due to SSL restrictions. So I consequently added the required SSL mode and certificates, but that raised another error.

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 error surprises me, since Google Cloud SQL does not offer support for the super user role. Instead, the 'cloudsqlsuperuser' role exists. This role is not the same as a traditional PostgreSQL super user, so how am I supposed to mitigate this issue?

For as far as I know, there is not an alternative way to pass the SSL certificates to the 'CREATE SUBSCRIPTION' statement.

I guess that I can try Apache Debezium for CDC instead, but using Postgres' built-in CDC support seems much simpler to me. This is, for streaming data from one Postgres instance to another.

Answer 1

I was using psql to connect to my Postgres instance. Instead, I should have used 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 setup properly.