Skip to main content

Home

PostgreSQL database secure connection using Cloud SQL Auth proxy

For the Google Cloud Platform (GCP), you can enable a secure connection between Unravel server and an external PostgreSQL database. This can be done using the Cloud SQL Auth proxy, which is a Cloud SQL connector that provides secure access to your instances without needing Authorized networks for configuring SSL.

  1. Go to Cloud SQL instances and create a PostgreSQL instance. Refer to Creating a PostgreSQL instance.

  2. Create a database on the PostgreSQL instance. Refer to Create a database on the Cloud SQL instance.

  3. Add a user account for the PostgreSQL instance. Refer to Add a user account.

  4. Go to the GCP instance, IAM & Admin > Service Accounts, and create a service account. Refer to Create a Service account. Ensure to add the following permissions to that service account:

    • Cloud SQL Client

    • Cloud SQL Editor

    • Cloud SQL Admin

  5. Open the newly created Service Account, select a project, and create a new service account key. Refer to Create a service account key. Ensure to create a service account key with JSON key type. You can download the key file.

  6. Download the Cloud SQL Auth proxy and provide the executable permissions. Refer to Download the Cloud SQL Auth proxy.

  7. Use a command line to run the Cloud SQL Auth proxy in a separate process in the backend.

    ./cloud_sql_proxy -instances=INSTANCE_CONNECTION_NAME=tcp:PORT_NUMBER  -credential_file=PATH_TO_KEY_FILE

    In the INSTANCE_CONNECTION_NAME, enter the Connection name listed for the PostgreSQL instance created in Step 1. This is the connection string to identify a Cloud SQL instance.

    In the PORT_NUMBER, enter the default port number, i.e., 5432.

    In the PATH_TO_KEY_FILE, enter the path to the JSON key file.

    For example: 

    ./cloud_sql_proxy -instances=unravel-dataproc:us-central:xyz-doc-test=tcp:5432  -credential_file=/home/key/file/path

    For example, the following result is shown:

    Listening on 127.0.0.1:5432 for unravel-dataproc:us-centrall:xyz-doc-test
Ready for new connections
Generated RSA key in 287.212948ms

  8. Verify if the cloud proxy is running in the background:

    ps -ef | grep -i cloud

    The following output is shown:

    1776 1605 0 10:49 pts/0	00:00:00 ./cloud_sql_proxy -instances=unravel-dataproc:us-central1:xyz-doc-test=tcp:5432 -credential_file=/home/key/file/path/xyz_sql.json

  9. Run the following command to verify the connection to the external PostgreSQL database. This command connects to the external PostgreSQL instance for GCP via the local host. 

    psql -u unravel -p -host 127.0.0.1
In this section: