Skip to content

Username/Password Authentication with SSL Enabled to Google Cloud SQL

This document provides detailed guidance for securely connecting to Google Cloud SQL using username/password authentication with SSL/TLS encryption enabled.

Enabling SSL for Cloud SQL connections ensures that all data transmitted between your application and the database is encrypted, protecting sensitive information from interception and unauthorized access.

This guide applies to both MySQL and PostgreSQL databases hosted on Google Cloud SQL, focusing on secure integration with the Privacera Portal or other services that require encrypted database connectivity.

Several Privacera services rely on databases, and this guide will assist in securing those connections.

Service Supported Optional
Privacera Portal Yes No
Apache Ranger Admin Yes No
Privacera Discovery Yes Yes
Privacera Ops Server Yes Yes
Privacera Scheme Server Yes Yes

Google Cloud SQL supports multiple database engines, and Privacera provides a compatibility matrix.
For more details Click here

Engine Version Supported
PostgreSQL 16 Yes
MySQL 5.7, 8.0 Yes

Prerequisites

Prerequisite Description
Database An existing database instance or the ability to create a new one.
Management Console Access Access to the Google Cloud Console – Required for creating and managing Cloud SQL instances.
SSL/TLS Enabled Ensure that SSL/TLS is enabled on the database instance. For instructions on enabling SSL for Google Cloud SQL, refer to the respective cloud documentation.
SSL Certificates Download the appropriate SSL CA certificate from Google Cloud to verify the database server's identity.
Database User A valid database user with username/password authentication credentials to connect to the database.

Note

For detailed steps on enabling SSL, configuring database users, downloading certificates, and managing network access, please refer to the official documentation of your cloud provider: - Google Cloud SQL Documentation

Setup

Step 1: Enable SSL and Download Certificates

  1. Go to the Google Cloud Console.
  2. Navigate to SQL and select your Existing SQL Instance or Create a New SQL Instance.
  3. In the Left Menu, go to Connections and open the Security tab.
  4. Under SSL settings:
    • Ensure "Allow only SSL connections" is enabled to enforce SSL for all incoming connections.
  5. Under Manage server CA certificates:
    • Click DOWNLOAD CERTIFICATES to download the Server CA certificate.
    • If no certificate exists, create a new one by selecting MANAGE CERTIFICATESCreate new CA certificate.

Step 2: Create or Verify a Database User

  1. In the Users tab of your Cloud SQL instance, ensure that you have a valid database user with username/password authentication.
  2. If needed, create a new user:
    • Click ADD USER ACCOUNT.
    • Select Built-in authentication.
    • Enter the desired username and password.
    • For Host name, select Allow any host (%).
    • Click ADD.

Step 3: Verify Certificate and Database User

After completing the SSL setup and downloading the required certificates (server-ca.pem, client-cert.pem, client-key.pem), follow the steps below to verify the secure connection to your Google Cloud SQL instance.

Bash
# Connect to the Cloud SQL instance using the psql client
psql "sslmode=verify-ca sslrootcert=<path_to_downloaded_cert>/server-ca.pem hostaddr=<INSTANCE_IP> port=<PORT> dbname=<DB_NAME> user=<DB_USER>"
- Replace:

Tip

  • path_to_downloaded_cert - Path to the downloaded server-ca.pem certificate.
  • INSTANCE_IP - Public IP address of the Cloud SQL instance.
  • PORT - Port number of the Cloud SQL instance.
  • DB_NAME - Name of the database.
  • DB_USER - Database user with username/password authentication.

Bash
# Connect to the Cloud SQL instance using the mysql client
mysql -u <DB_USER> -p --host=<INSTANCE_IP> --port=<PORT> --ssl-ca=<path_to_downloaded_cert>/server-ca.pem
- Replace:

Tip

  • DB_USER - Database user with username/password authentication.
  • INSTANCE_IP - Public IP address of the Cloud SQL instance.
  • PORT - Port number of the Cloud SQL instance.
  • path_to_downloaded_cert - Path to the downloaded server-ca.pem certificate.

Step 4: Changes for Privacera-Manager

Uploads Certificates

  1. Upload the SSL CA certificate to the Privacera-Manager instance.
  2. Create a custom folder within the SSL directory and copy the certificate into this folder.

    Bash
    1
    2
    3
    4
    # Create a folder in the config directory
    cd privacera/privacera-manager
    mkdir -p config/ssl/custom_certificates
    cp <path_to_downloaded_cert>/server-ca.pem config/ssl/custom_certificates
    

Changes for External DB

  1. Open Terminal and SSH to the instance where Privacera-Manager is installed.
  2. Create external DB variables in the custom-config file and update the properties accordingly.
Bash
1
2
3
4
5
#Follow the steps below to copy the vars and update the variables. 
#If the file is already present, then file will not be overwritten.
cd privacera/privacera-manager
cp -n config/sample-vars/vars.external.db.postgres.yml config/custom-vars/
vi config/custom-vars/vars.external.db.postgres.yml 
  • Update the following variables:

YAML
1
2
3
 EXTERNAL_DB_HOST: "<PLEASE_CHANGE>"
 EXTERNAL_DB_NAME: "privacera_db"
 EXTERNAL_DB_USER: "privacera"
- Replace:

Tip

  • EXTERNAL_DB_HOST - Endpoint URL of the RDS Postgres.
  • EXTERNAL_DB_NAME - If you have created a different database, update the name here.
  • EXTERNAL_DB_USER - If you have created a different user, update the name here.
Bash
1
2
3
4
5
#Follow the steps below to copy the vars and update the variables. 
#If the file is already present, then file will not be overwritten.
cd privacera/privacera-manager
cp -n config/sample-vars/vars.external.db.mysql.yml config/custom-vars/
vi config/custom-vars/vars.external.db.mysql.yml 
  • Update the following variables:

YAML
1
2
3
4
5
 EXTERNAL_DB_HOST: "<PLEASE_CHANGE>"
 EXTERNAL_DB_NAME: "privacera_db"
 EXTERNAL_DB_USER: "privacera"

 DB_CONNECTION_EXTRA_PARAMETERS: "useSSL=true&requireSSL=true&verifyServerCertificate=true&disableSslHostnameVerification=true"
- Replace:

Tip

  • EXTERNAL_DB_HOST - Endpoint URL of the RDS Postgres.
  • EXTERNAL_DB_NAME - If you have created a different database, update the name here.
  • EXTERNAL_DB_USER - If you have created a different user, update the name here.

Step 5: Apply Changes

If you haven't completed the base installation setup, you can skip this step and continue with the base installation steps.

Step 1 - Setup which generates the helm charts. This step usually takes few minutes.

Bash
cd ~/privacera/privacera-manager
./privacera-manager.sh setup
Step 2 - Apply the Privacera Manager helm charts.
Bash
cd ~/privacera/privacera-manager
./pm_with_helm.sh upgrade
Step 3 - Post-installation step which generates Plugin tar ball, updates Route 53 DNS and so on.

Bash
cd ~/privacera/privacera-manager
./privacera-manager.sh post-install

By enabling SSL and configuring secure certificates for your Google Cloud SQL instance, you ensure encrypted, secure communication between your services and the database using username and password authentication. This setup helps protect sensitive data in transit and enforces best practices for secure database connectivity.

Comments