Confused about SSL support (can't authenticate to Crunchy Data Postgres Operator cluster)

[paul-snively]

I'll try to describe my setup with sufficient information, but please let me know if this is inadequate.

I have:

1. Minikube
2. The Operator Lifecycle Manager installed via the operator-sdk.
3. The Service Binding Operator installed via the Operator Hub.
4. Odo installed.

Odo supports binding projects to backing services with the Service Binding Operator. For a Proof of Concept, I've created a branch on the Practical FP in Scala Shopping Cart that takes key configuration, e.g. for connecting to Postgres, from environment variables provided by the Service Binding. I've created a Postgres cluster in Minikube via the Kustomize subdirectory of the Postgres Operator Examples. The only change I made was to the Service spec's type, which I made a NodePort type so I can easily access Postgres from outside Minikube just by using its IP and the Service's NodePort. Since the shopping cart also uses Redis, I use the redis-standalone operator and create a Redis instance also.

When I run odo dev in the project directory, it runs, but I get this in the container logs:

tools: [error] 🔥  Startup negotiation failed.
tools: [error] 🔥  
tools: [error] 🔥  
tools: [error] 🔥  Postgres FATAL 28000 raised in ClientAuthentication (auth.c:552)
tools: [error] 🔥  
tools: [error] 🔥    Problem: No pg_hba.conf entry for host "172.17.0.1", user "hippo", database
tools: [error] 🔥             "hippo", no encryption.
tools: [error] 🔥  
tools: [error] 🔥  Startup properties were:
tools: [error] 🔥  
tools: [error] 🔥    user      =  hippo
tools: [error] 🔥    database  =  hippo
tools: [error] 🔥  
tools: [error] 🔥  If this is an error you wish to trap and handle in your application, you can do
tools: [error] 🔥  so with a SqlState extractor. For example:
tools: [error] 🔥  
tools: [error] 🔥    doSomething.recoverWith { case SqlState.InvalidAuthorizationSpecification(ex) =>  ...}
tools: [error] 🔥  
tools: [error] 
tools: [error] skunk.exception.StartupException: No pg_hba.conf entry for host "172.17.0.1", user "hippo", database "hippo", no encryption.

The user and database are correct, but the "no encryption" makes me wonder.

So I figure I should try good ol' psql. To do this, I need the password that was generated in a secret for the cluster:

psnively@oryx-pro:~/axoni/pfps-shopping-cart|axoni ⇒  kubectl get -n postgres-operator secret/hippo-pguser-hippo -o json | jq -c '.data | map_values(@base64d)'
{"dbname":"hippo","host":"hippo-primary.postgres-operator.svc","jdbc-uri":"jdbc:postgresql://hippo-primary.postgres-operator.svc:5432/hippo?password=2DOHt%2A%292%3C%2BeirdG%5DmYv%3DFggZ&user=hippo","password":"2DOHt*)2<+eirdG]mYv=FggZ","port":"5432","uri":"postgresql://hippo:2DOHt%2A%292%3C+eirdG%[email protected]:5432/hippo","user":"hippo","verifier":"SCRAM-SHA-256$4096:O0b3hyKdariyucLb7zJqFQ==$C0rncfncHkiPe+7y2D+zIKw2sOL+cDyqzdkoHz8RfbE=:V/322Vowzhm78+B/jGDOWGgTkTCa5JAqqQyUFLbVKHk="}

Hence:

psnively@oryx-pro:~/axoni/pfps-shopping-cart|axoni ⇒  psql -h 192.168.39.160 -p 32000 -U hippo
Password for user hippo: 
psql (14.7 (Ubuntu 14.7-1.pgdg22.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

hippo=> 

using the password from the secret. Of course, I notice that TLS negotiation has taken place. A bit of digging in the Postgres documentation, and I learn how to suppress that:

psnively@oryx-pro:~/axoni/pfps-shopping-cart|axoni ⇒  psql -h 192.168.39.160 -p 32000 -U hippo sslmode=disable
psql: error: connection to server at "192.168.39.160", port 32000 failed: FATAL:  no pg_hba.conf entry for host "172.17.0.1", user "hippo", database "hippo", no encryption

Boom! I've reproduced the error from the logs.

OK, so I need to pass the appropriate ssl parameter to the Session at construction, right? And that should be SSL.System, right? But then I get:

tools: [error] javax.net.ssl.SSLHandshakeException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target

and at this point I give up, because life is too short to muck with TLS, certificates, the JVM, Kubernetes...

By the way, I did also try connecting with a JDBC CLI and the standard Postgres JDBC driver, and was also successful with it.

So on the assumption psql and the JDBC driver don't have some sort of root cert embedded in them, I'm pretty confused as to what's going on here.

Attaching my branch of the project for reference, in case it helps.
pfps-shopping-cart.zip