How to setup the logical replication in Google cloud SQL (PostgreSQL DB)

[akhilesh_arya2]

@All
I, need to set up a logical replication in the Cloud SQL DB (PostgreSQL) to my DWH which is also an GCP managed PgSQL Cloud SQL instance but the source database has SSL configurations and needs to pass the certificates for creating the Subscription, to connect to the publication server, but the Cloud SQL doesn't let me grant the cloudsqlSuperUser permissions to the Postgres User and thus I am encountering errors during Subscription creation as only Superuser can take the certificates for connection to the source DB.
please provide any suggestions to create a logical replication.

[ms4446]

Setting up logical replication with SSL connections in Google Cloud SQL for PostgreSQL requires careful consideration due to certain limitations and security implications. Here are some refined suggestions to help you create the replication:

1. Use the pglogical Extension:

   • The pglogical extension is a practical solution for setting up logical replication in PostgreSQL, especially within the constraints of Google Cloud SQL. It allows replication without superuser privileges and is compatible with SSL connections.
   • For detailed guidance on setting up pglogical in Cloud SQL, refer to the Google Cloud documentation: Configure Logical Replication in Cloud SQL.

2. Understand the Limitations of Superuser Roles:

   • In Google Cloud SQL, true superuser access is not provided for security reasons. This limitation means that some native PostgreSQL replication features requiring superuser privileges might not be feasible.
   • While users can have elevated privileges, they do not equate to full superuser capabilities found in self-managed PostgreSQL instances.

3. Consider Alternative Authentication Methods:

   • If SSL certificate management poses challenges, exploring other authentication methods like password authentication or IAM database authentication could be beneficial. However, it's essential to balance ease of setup with the security requirements of your data.
   • Keep in mind that SSL/TLS is generally recommended for secure connections, particularly when handling sensitive information.

4. Explore Third-Party Tools for Replication:

   • Various third-party tools offer advanced features and capabilities for logical replication with Google Cloud SQL. One notable options is Striim.
   • Be aware that these tools might add complexity to your setup and have their own dependencies and considerations.

5. Additional Resources:

   • For more information on replication options and best practices in Google Cloud SQL, the following resources can be helpful:
     • Google Cloud SQL Replication
     • pglogical GitHub Repository

6. Security and Performance Considerations:

   • It's crucial to prioritize security and performance needs when implementing your chosen solution. Understand the trade-offs of each method and ensure they align with your organizational policies and requirements.

While setting up logical replication in Google Cloud SQL with SSL connections has its challenges, there are several approaches you can take. Each has its advantages and limitations, so it's important to choose the one that best fits your technical and security needs.

[akhilesh_arya2]

Sir, I am facing this error.

ERROR: Non-superusers cannot use certificate in the connection setting.certificate is not allowed

ERROR: certificate is not allowed
SQL state: 2F003
Detail: Non-superusers cannot use certificate in the connection setting.

while creating subscription from the Cloud SQL PostgreSQL instance

[ms4446]

Here are some potential solutions to address this error:

1. Use the pglogical Extension:

   • The pglogical extension is a suitable alternative for setting up logical replication in Cloud SQL. It's designed to work within the constraints of Cloud SQL, supporting logical replication without requiring superuser privileges. This extension is compatible with SSL connections and can be a practical solution in your scenario.

2. Configure IAM Database Authentication:

   • Consider using Google Cloud's IAM database authentication as an alternative to SSL certificates. This method simplifies the setup by eliminating the need for certificate-based authentication, though it may not be suitable for all replication scenarios that specifically require SSL configurations.

3. Explore Alternative Replication Tools:

   • Investigate third-party tools like Striim, which offer more flexibility and capabilities for logical replication with Cloud SQL. These tools often provide solutions for managing certificates and authentication without requiring superuser privileges.

4. Contact Google Cloud Support:

   • If the above methods do not resolve the error, it's advisable to contact Google Cloud Support for expert guidance. They can assist with troubleshooting specific issues related to your Cloud SQL instance and replication configuration.

[josh_hawn]

---

@ms4446 wrote:

1. Use the pglogical Extension:

   • The pglogical extension ... is compatible with SSL connections and can be a practical solution in your scenario.

---

Yet when I try to set up a subscription and specify ssl options in the dsn, I also get the same error:

=> select pglogical.create_subscription(
subscription_name := 'subscription',
replication_sets := array['replication_set'],
provider_dsn := 'host=source-host port=5432 dbname=sourcedb user=replication_user password=${PGPASSWORD} sslmode=verify-ca sslrootcert=/certs/server-ca.pem sslcert=/certs/client-cert.pem sslkey=/certs/client-key.pem'
);
ERROR: certificate is not allowed
DETAIL: Non-superusers cannot use certificate in the connection setting.

So it seems like it is not compatible with SSL connections with Cloud SQL?

[josh_hawn]

Additional context, I am attempting to migrate data to a new Cloud SQL instance which uses less storage space (because it's not possible to reduce storage on an existing instance). Both instances are on the same VPC and have a private IP only. Is it recommended that I set ssl-mode to allow unencrypted network traffic while I perform the logical replication and migration?

[ms4446]

The error message you're encountering, "certificate is not allowed DETAIL: Non-superusers cannot use certificate in the connection setting," indicates a specific restriction within Cloud SQL's implementation of PostgreSQL permissions, particularly regarding SSL client certificates.

Understanding the Issue

• Cloud SQL Permissions: Cloud SQL does not fully mirror traditional PostgreSQL roles. The cloudsqlsuperuser role lacks some privileges of a true superuser, including the ability to use client certificates for authentication in replication.

• SSL Security: Setting sslmode=allow would permit connections to fall back to unencrypted traffic if SSL is not available. While your instances are on the same VPC with private IPs, this still poses a risk if there are vulnerabilities in your network infrastructure.

Recommended Solutions

1. pglogical Node as Proxy:

   • Consider setting up an external pglogical node (e.g., on a Compute Engine instance) that acts as a proxy. This node can establish the SSL connection to your Cloud SQL source instance using the necessary certificates. Your Cloud SQL target instance can then connect to this proxy node without needing to handle client certificates directly.

2. Temporary Superuser Account:

   • If the replication is a one-time migration, you could create a temporary superuser account on your source Cloud SQL instance. Use this account only for the duration of the replication process. Be extremely cautious with this approach and revoke the superuser privileges immediately after the migration.

3. Alternative Replication Tools:

   • Explore other logical replication tools designed for Cloud SQL environments. Some may have built-in mechanisms to handle the client certificate restrictions.

Example: pglogical Proxy

-- pglogical node acting as a proxy (e.g., on Compute Engine)
SELECT pglogical.create_subscription(
    subscription_name := 'proxy_subscription',
    provider_dsn := 'host=source-cloud-sql-ip port=5432 dbname=sourcedb user=replication_user password=${PGPASSWORD} sslmode=verify-ca sslrootcert=/certs/server-ca.pem sslcert=/certs/client-cert.pem sslkey=/certs/client-key.pem'
);

-- pglogical on target Cloud SQL instance
SELECT pglogical.create_subscription(
    subscription_name := 'cloud_sql_subscription',
    provider_dsn := 'host=proxy-node-ip port=5432 dbname=sourcedb user=replication_user password=${PGPASSWORD}'  -- No SSL options here
);

Important Considerations

Always prioritize security. Even if you use a temporary superuser or a proxy node, ensure that all connections are over secure channels (private IPs, firewalls, etc.).

If you're unsure, reach out to Google Cloud Support. They might have specific guidance or workarounds for this issue.

[josh_hawn]

Thanks for the suggestion. I have a follow-up question about the proxy option... I considered using the cloud sql auth proxy from a separate GCE instance but then it occurred to me that the connections between the subscriber to the proxy would not be encrypted/authenticated. Am I understanding that correctly?

I was also wondering if I could use sslmode=require which would at least encrypt the connections and rely on the replication user password for authentication. Does that sound reasonable? I'm not sure whether I could at least get server certificate verification to work.

As for the superuser role suggestion, I didn't even realize this was possible. It was my understanding that it was not possible for end-users to grant the superuser  role in cloud SQL. I'll give it a try though because I would prefer to have full end-to-end mutual TLS.

- Josh

[ms4446]

Your understanding and concerns are valid regarding the use of the Cloud SQL Auth Proxy and the potential encryption/authentication challenges. Please note: 

Connection Security:

• GCE instance to Cloud SQL: Encrypted by the proxy using SSL/TLS.
• Subscriber to GCE instance: Potentially vulnerable if not explicitly configured with SSL/TLS.

Solutions:

• Configure SSL/TLS on the GCE instance.
• Use direct VPC peering for added security.

Using sslmode=require:

• Encryption: Ensures all connections are encrypted.
• Authentication: Relies on replication user password (secure with strong password policies).
• Server Certificate Verification:
  • sslmode=require does not verify the server certificate (only encrypts the connection).
  • Consider sslmode=verify-ca or sslmode=verify-full for enhanced security (if manageable).

Steps to Use sslmode=require:

1. Enable pglogical Extension:

   CREATE EXTENSION pglogical;
   

2. Create Replication User:

   CREATE ROLE replication_user WITH LOGIN REPLICATION PASSWORD 'password';
   

3. Configure Source and Target Nodes:

   SELECT pglogical.create_node(
     node_name := 'source_node',
     dsn := 'host=source-host port=5432 dbname=sourcedb user=replication_user password=password sslmode=require'
   );
   
   SELECT pglogical.create_node(
     node_name := 'target_node',
     dsn := 'host=target-host port=5432 dbname=targetdb user=replication_user password=password sslmode=require'
   );
   

4. Create Subscription:

   SELECT pglogical.create_subscription(
     subscription_name := 'subscription_name',
     provider_dsn := 'host=source-host port=5432 dbname=sourcedb user=replication_user password=password sslmode=require'
   );
   

Superuser Role in Cloud SQL:

• True superuser (CREATE USER ... SUPERUSER) not possible.
• cloudsqlsuperuser role provides some elevated privileges, but not full superuser capabilities (e.g., managing SSL certificates directly).

Example Configurations:

• Proxy GCE Setup:
  • Use Cloud SQL Auth Proxy for secure connection to Cloud SQL.
  • Ensure SSL/TLS between GCE proxy and subscriber.
• Subscriber to Proxy: Secure connection setup.
• Example subscription:

  SELECT pglogical.create_subscription(
    subscription_name := 'cloud_sql_subscription',
    provider_dsn := 'host=proxy-node-ip port=5432 dbname=sourcedb user=replication_user password=${PGPASSWORD} sslmode=require'
  );
  

This approach ensures secure connections while working within Cloud SQL constraints.

[DavoLinares]

Hi @ms4446 ! 

So is there a way to setup CDC using IAM service accounts? how do I run the command to create a node, for example, if I need a password and IAM SA are passwordless?

Thanks in advance!

David Linares