To connect Java app with Postgres on GCP with ssl enabled using .DER

[Anchellani]

I have a Java app and need to connect it with Postgres on GCP (SSL Enabled).

As I see from post https://www.googlecloudcommunity.com/gc/Databases/To-connect-with-postgres-on-gcp-with-ssl-enabled/t...

I should use cert .DER format instead. When I use .DER, the application starting and up for some period and works, but in console I see errors from Cloud SQL

{
  "textPayload": "2023-10-18 08:42:56.879 UTC [819484]: [1-1] db=squashtm,user=test FATAL:  connection requires a valid client certificate",
.....
  "receiveTimestamp": "2023-10-18T08:43:02.651455949Z"
}

The app is up and works (and connected to Postgres) for several days with this error log from Cloud SQL present from time to time. Then it crashes, and the fix is to generate the new one cert.

It looks like there is a deadline for running with 'wrong' cert and then the Java app connection is blocked

Could you advice on this

[ms4446]

The error message "connection requires a valid client certificate" indicates that the Cloud SQL instance is configured to require client certificates for SSL/TLS connections. The Java application is attempting to connect using a certificate that is not valid for the instance.

There are a few possible reasons why the certificate may not be valid:

• The certificate may have expired.
• The certificate may have been revoked.
• The certificate may not be trusted by the Cloud SQL instance.

To troubleshoot the issue, you can try the following:

Verify Certificate Validity

Use the following command to view the certificate information:

openssl x509 -inform der -in certificate.der -noout -text

The "Not After" field indicates the expiration date of the certificate.

Check for Revocation

Use the following command to check the revocation status of the certificate:

openssl ocsp -inform der -issuer issuer.pem -cert certificate.der -CAfile ca.pem

Test Certificate Trust

Use a PostgreSQL client such as psql to test the connection. For example:

psql -h hostname -U username -p password --sslmode=require --sslcert=your_cert_location --sslkey=your_key_location --sslrootcert=server_ca_location

If the connection is successful, then the certificate is trusted by the Cloud SQL instance.

If the certificate is valid and trusted, the issue may be with how the Java application is loading the certificate. Ensure the following:

• The certificate is being loaded from the correct location.
• The certificate is being loaded in the correct format.
• The certificate is being loaded with the correct truststore.

If you continue to face problems, consider reaching out to Google Cloud support.

In the meantime, you can generate a new certificate for the Cloud SQL instance, which will have a new expiration date. The Java application should then be able to connect using this new certificate. However, this is a temporary solution, as the new certificate will also eventually expire. To prevent recurrence, generate a new certificate before the current one expires.

When generating a new certificate, ensure the associated private key is kept secure and add the new certificate to the Cloud SQL instance's list of allowed client certificates.

You might also consider using the Cloud SQL Auth Proxy. It provides a secure way to connect without the need for SSL/TLS certificates, simplifying the connection process. The data transmitted is encrypted, but the authentication is managed by the proxy.

Lastly, if the Java application uses a connection pool, stale or old connections might still utilize the expired certificate. Restarting the application or the connection pool can help address this.