Introduction
To setup the GCP PostgreSQL with Vault we must have the following setup at a minimum:
Prerequisites
- A PostgreSQL setup in GCP
- A running HashiCorp Enterprise Vault. Vault v1.15.4+ent is used at the time of writing this article.
Expected Outcome
PostgreSQL configured successfully with Vault over SSL.
How to enforce SSL/
You can use the SSL mode setting to enforce SSL encryption in the following ways:
-
Allow both non-SSL/non-TLS and SSL/TLS connections. The client certificate isn't verified for SSL/TLS connections. This is the default.
-
Only allow connections encrypted with SSL/TLS. The client certificate isn't verified for SSL connections.
-
Only allow connections encrypted with SSL/TLS and with valid client certificates.
If you select Allow non-SSL/non-TLS and SSL/TLS connections for your Cloud SQL instance, SSL/TLS connections are accepted, as well as unencrypted and unsecured connections. If you do not require SSL/TLS for all connections, unencrypted connections are still allowed. For this reason, if you are accessing your instance using public IP, we strongly recommend that you enforce SSL for all connections.
To enable requiring SSL/TLS, do the following from GCP Console :
In the Google Cloud console, go to the Cloud SQL Instances page.
- To open the Overview page of an instance, click the instance name.
- Click Connections from the SQL navigation menu.
- Select the Security tab.
- Select one of the following:
- Allow unencrypted network traffic (not recommended)
- Allow only SSL connections. This option only allows connections using SSL/TLS encryption. Certificates aren't validated.
- Require trusted client certificates. This option only allows connections from clients that use a valid client certificate and are SSL encrypted.
Note: If your Cloud SQL instance is using a public IP address, you need to add the IP addresses of your PostgreSQL clients as authorized networks when configuring SSL/TLS.
# Enable the database secrets engine if it is not already enabled:
vault secrets enable database
Success! Enabled the database secrets engine at: database/
To enable the secrets engine at a different path, use the -path argument.
Use Case 1: Allow unencrypted network traffic (not recommended)
When PostgreSQL is configured with SSLmode as disable. With this mode, it only tries a non-SSL connection:
vault write database/config/postgresql plugin_name=postgresql-database-plugin allowed_roles="*" connection_url="postgresql://{{username}}:{{password}}@35.238.62.10:5432/postgres?sslmode=disable" username="postgres" password="postgres"
Success! Data written to: database/config/postgresqlPlease update the details on the above command based on your settings and values from the console.
Use Case 2: Allow only SSL connections
When PostgreSQL is configured with SSLmode as verify-ca. With this mode, it only tries an SSL connection and verifies that the server certificate is issued by a trusted certificate authority (CA)
vault write database/config/postgresql plugin_name=postgresql-database-plugin allowed_roles="*" connection_url="postgresql://{{username}}:{{password}}@35.238.62.10:5432/postgres?sslmode=verify-ca&sslrootcert=/root/certstrap/postgres_ssl/certs/out/certstrap/server-ca.pem" username="postgres" password="postgres"
Success! Data written to: database/config/postgresqlPlease update the details on the above command based on your settings and values from the console.
Use Case 3: Require trusted client certificates
When PostgreSQL is configured with SSLmode as verify-full. With this mode, it only tries an SSL connection, and verifies that the server certificate is issued by a trusted CA and that the requested server host name matches that in the certificate.
The challenge with this mode i that the certificate issued by GCP doesn't contain any IP address, They contain the email address of the issuer. Certificate details can be viewed using the below openssl command:
openssl x509 -in <crt_file_name> -text -noout
While trying to configure with trusted certificates issued by GCP :
vault write database/config/my-postgresql-database plugin_name="postgresql-database-plugin" allowed_roles="my-role" connection_url="postgresql://{{username}}:{{password}}@35.238.62.10:5432/postgres?sslmode=verify-full&sslrootcert=/root/certstrap/postgres_ssl/certs/out/certstrap/Auth.crt&sslcert=/root/certstrap/postgres_ssl/certs/out/certstrap/postgres.crt&sslkey=/root/certstrap/postgres_ssl/certs/out/certstrap/postgres.key" username="postgres" password="postgres"
Error : failed to write startup message (tls: failed to verify certificate: x509: cannot validate certificate for 35.238.62.10 because it doesn't contain any IP SANs)
Please update the details on the above command based on your settings and values from the console.
As of writing this article, there is no workaround for this error. This needs to be discussed with GCP if they can issue certs with valid IP addresses of PostgreSQL.
Possible Issues during Setup:
1.) Permission Denied.
error creating database object: error verifying connection: cannot parse `postgresql://postgres:xxxxx@100.98.134.16:5432/postgres?sslmode=verify-full&sslrootcert=/home/CCD/[password]_instance/server-ca.pem&timezone=UTC&application_name=vault`: failed to configure TLS (unable to read CA file
: open /home/CCD/[password]_instance/server-ca.pem: permission denied)
Solution
- Update the permissions of certificates and folder in which certificates are present and changed owner to the user Vault is running with.
- Copy the certs to the location where Vault configuration is present.
2.) No such file or directory.
failed to configure TLS(unable to read CA file : open <path of cert> : no such file or directory.
Solution
Update the File system with certificates on all vault servers. Also for any configuration changes that require some file i.e. certs etc have to be available on all nodes. Please verify if certificates are available on both active and standby nodes.