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?
LinkedIn
Twitter
