Summary

The Cloud SQL (Google) documentation provides a guide on how to connect to PostgreSQL via SSLand provides instructions of how to download the Certificates and Key that are needed for the client to connect. 

• server-ca.pem
• client-cert.pem 
• client-key.pem

You can find it in the below documentation:

• Configuring SSL/TLS certificates (Google Cloud SQL)

The following command is an example that would allow the PostgreSQL client to connect to the Cloud SQL database bamboo at IP address 10.1.1.1 as the user bamboouser.

psql "sslmode=verify-ca sslrootcert=server-ca.pem \
sslcert=client-cert.pem sslkey=client-key.pem \
hostaddr=10.1.1.1 \
port=5432 \
user=bamboouser dbname=bamboo"

Bamboo will use a JDBC driver to connect to the external Postgres databases. To configure the JDBC driver there are a few gotcha's that are not immediately obvious. If the JDBC connection string is not correct then you may see some error messages similar to the following:

Error accessing database: org.postgresql.util.PSQLException: FATAL: connection requires a valid client certificate

Error accessing database: org.postgresql.util.PSQLException: The hostname 10.1.1.1 could not be verified by hostnameverifier PgjdbcHostnameVerifier.

Solution

The JDBC driver by default will attempt to verify that the certificate to use is a valid. This would generally fail because the IP target would not have a corresponding Hostname. To work around this we must specify sslmode=verify-ca. This will then verify the certificate with the CA that had been supplied server-ca.pem but not verify that the IP matches a host.

The JDBC driver does also not accept keys in PEM encoding. The client-key.pem that has been downloaded from Cloud SQL would need to be converted to the PKCS8 (DER) format that the JDBC driver recognises. The can be achieved using openssl as below.

openssl pkcs8 -topk8 -inform PEM -in client-key.pem -outform DER -out client-key.pk8 -nocrypt

• The nocrypt flag is to tell the JDBC driver that your key has no password. If your key has a password, provide it using the sslpassword connection parameter on the JDBC connection string and omit nocrypt when converting it.

• For more information, please see the sslkey section of the PostgreSQL JDBC driver documentation: https://jdbc.postgresql.org/documentation/head/connect.html

Putting this all together we get that the JDBC URL needed for Bamboo to connect to Cloud SQL - Postgres using SSL would be as per the below example:

jdbc:postgresql://10.1.1.1:5432/bamboo?ssl=true&sslmode=verify-ca&sslrootcert=/path/to/server-ca.pem&sslcert=/path/to/client-cert.pem&sslkey=/path/to/client-key.pk8

This can be configured within the $BAMBOO_HOME/bamboo.cfg.xml within <property name="hibernate.connection.url">..</property>