Network is a cloud VPN owned by neteng. They have allocated subnets from their host project to our service projects.
The shared VPC also have Private services access setup to allow us to provision cloudsql instances on neteng owned network
which is accessible from the datacenter
Aim of this spike is to verify server and client config to enforce TLS1.2, TLS1.3 with the appropriate ciphers
generate client key/cert
openssl genrsa -out client.key 2048
openssl req -new -key client.key -out client.csr -subj "/CN=postgres"
openssl x509 -req -in client.csr -CA ca.crt -CAkey ca.key -CAcreateserial -out client.crt -days 3650 -sha256
openssl pkcs8 -topk8 -inform PEM -outform PEM -in client.key -out client_pkcs8.key -nocrypt
#verify connection from psql
psql -U postgres -h 0.0.0.0 -d "sslmode=verify-ca dbname=postgres sslcert=client.crt sslkey=client.key sslrootcert=ca.crt"
#this `should` work but does not - client cert
java -cp ./target/verify-cloudsql-postgres-mtls-1.0-SNAPSHOT-jar-with-dependencies.jar org.example.PostgresConnectTest \
"jdbc:postgresql://0.0.0.0:5432/default?ssl=true&sslmode=verify-ca&sslrootcert=/tmp/ca.crt&sslcert=/tmp/client.crt&sslkey=/tmp/client_pkcs8.key&user=postgres&sslfactory=org.postgresql.ssl.DefaultJavaSSLFactory"
#import postgres ca.crt into client truststore
keytool -import -alias postgresCA -file /tmp/ca.crt -keystore /tmp/truststore.jks -storepass postgres -noprompt
#convert client pkcs8 file into p12 format and import into client keystore
openssl pkcs12 -export -in client.crt -inkey client_pkcs8.key -out client.p12 -name client_key -passout pass:postgres
keytool -importcert -file client.crt -keystore client_keystore.jks -storepass postgres -alias client_cert -noprompt
keytool -importkeystore -srckeystore client.p12 -srcstoretype PKCS12 -srcstorepass postgres -destkeystore client_keystore.jks -deststoretype JKS -deststorepass postgres
#check the 3 TLSv1.3 ciphers
for cipher in TLS_AES_256_GCM_SHA384 TLS_AES_256_GCM_SHA384 TLS_CHACHA20_POLY1305_SHA256
do
echo verify TLSv1.3 and cipher $cipher is supported
java \
-Djdk.tls.client.protocols=TLSv1.3 \
-Djdk.tls.client.cipherSuites=$cipher \
-Djavax.net.debug=ssl \
-Djavax.net.ssl.trustStore=/tmp/truststore.jks \
-Djavax.net.ssl.trustStorePassword=postgres \
-Djavax.net.ssl.trustStoreType=JKS \
-Djavax.net.ssl.keyStore=/tmp/client_keystore.jks \
-Djavax.net.ssl.keyStorePassword=postgres \
-Djavax.net.ssl.keyStoreType=JKS \
-cp ./target/verify-cloudsql-postgres-mtls-1.0-SNAPSHOT-jar-with-dependencies.jar org.example.PostgresConnectTest \
"jdbc:postgresql://0.0.0.0:5432/testdb?ssl=true&sslmode=verify-ca&user=postgres&password=postgres&sslfactory=org.postgresql.ssl.DefaultJavaSSLFactory" 600
done
#verify all of TLSv1.1,TLSv1 is not supported
for cipher in TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA256 TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA384 TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256 TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384 TLS_ECDHE_ECDSA_WITH_AES_128_GCM_SHA256 TLS_ECDHE_ECDSA_WITH_AES_256_GCM_SHA384 TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 TLS_ECDHE_RSA_WITH_AES_256_GCM
do
java \
-Djdk.tls.client.protocols=TLSv1.1,TLSv1 \
-Djdk.tls.client.cipherSuites=$cipher \
-Djavax.net.debug=ssl \
-Djavax.net.ssl.trustStore=/tmp/truststore.jks \
-Djavax.net.ssl.trustStorePassword=postgres \
-Djavax.net.ssl.trustStoreType=JKS \
-Djavax.net.ssl.keyStore=/tmp/client_keystore.jks \
-Djavax.net.ssl.keyStorePassword=postgres \
-Djavax.net.ssl.keyStoreType=JKS \
-cp ./target/verify-cloudsql-postgres-mtls-1.0-SNAPSHOT-jar-with-dependencies.jar org.example.PostgresConnectTest \
"jdbc:postgresql://0.0.0.0:5432/testdb?ssl=true&sslmode=verify-ca&user=postgres&password=postgres&sslfactory=org.postgresql.ssl.DefaultJavaSSLFactory" 600
if [ "$?" != "0" ]
then
echo Expcted exception $cipher
break
fi
done
# create cloudsql postgres instance with terraform
db_instance=demo
gcloud sql ssl client-certs create $app-client-pki --instance=$db_instance
gcloud sql ssl client-certs describe CERT_NAME --instance=INSTANCE_NAME --format="value(cert)" > client-cert.pem
gcloud sql instances describe $db_instance --format="value(serverCaCert.cert)" > server-ca.pem
jdbc:postgresql://[PUBLIC_IP_or_PRIVATE_IP]/[DATABASE_NAME]?sslmode=verify-full&ssl=true&sslrootcert=/path/to/server-ca.pem&sslcert=/path/to/client-cert.pem&sslkey=/path/to/client-key.pk8
psql -U <db-user> -h <instance-ip> -d "sslmode=verify-ca dbname=<DBNAME> sslcert=/path/to/client-cert.pem sslkey=/path/to/client-key.pem sslrootcert=/path/to/server-ca.pem"
# we can configure the sinks with mTLS auth
CloudSQL by default handles TLS 1.2 or higher. Can use mTLS auth between client ans swerver as described here https://cloud.google.com/sql/docs/postgres/configure-ssl-instance#gcloud_1
and set jvm properties ot ensure the jvm does not try otherTLS protocols/ciphers
e.g
-Djdk.tls.client.protocols=TLSv1.2,TLSv1.3
-Djdk.tls.client.ciphers=TLSv1.2,TLSv1.3
-Dhttps.protocols=TLSv1.2,TLSv1.3
-Djdk.tls.disabledAlgorithms=SSLv3, TLSv1, TLSv1.1, RC4, DES, 3DES, MD5, SHA1, ...
TLS_AES_256_GCM_SHA384 TLS_AES_256_GCM_SHA384 TLS_CHACHA20_POLY1305_SHA256
TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA256 TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA384 TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256 TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384 TLS_ECDHE_ECDSA_WITH_AES_128_GCM_SHA256 TLS_ECDHE_ECDSA_WITH_AES_256_GCM_SHA384 TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 TLS_DHE_RSA_WITH_AES_128_CBC_SHA256 TLS_DHE_RSA_WITH_AES_256_CBC_SHA256 TLS_DHE_DSS_WITH_AES_128_CBC_SHA256 TLS_DHE_DSS_WITH_AES_256_CBC_SHA256 TLS_DHE_RSA_WITH_AES_128_GCM_SHA256 TLS_DHE_RSA_WITH_AES_256_GCM_SHA384 TLS_RSA_WITH_AES_128_CBC_SHA256 TLS_RSA_WITH_AES_256_CBC_SHA256 TLS_RSA_WITH_AES_128_GCM_SHA256 TLS_RSA_WITH_AES_256_GCM_SHA384 TLS_PSK_WITH_AES_128_CBC_SHA256 TLS_PSK_WITH_AES_256_CBC_SHA384 TLS_DHE_PSK_WITH_AES_128_CBC_SHA256 TLS_DHE_PSK_WITH_AES_256_CBC_SHA384 TLS_RSA_PSK_WITH_AES_128_CBC_SHA256 TLS_RSA_PSK_WITH_AES_256_CBC_SHA384 TLS_ECDHE_PSK_WITH_AES_128_CBC_SHA256 TLS_ECDHE_PSK_WITH_AES_256_CBC_SHA384