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
There are a few possible reasons why the certificate may not be valid:
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:
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.