Failing to Understand ssl_opts in Elixir Phoenix Ecto's Postgres Adapter

Questions / Help
,
1

So I have installed the Percona Postgres Operator and Distribution on Kubernetes. Everything works fine, I can do the check mentioned on their installation site by using the ssl certificate generated in the helm install process.
In my runtime.exs, I have this configuration for postgres:

config :core, Repo,
  adapter: Ecto.Adapters.Postgres,
  username: System.get_env("POSTGRES_USERNAME"),
  password: System.get_env("POSTGRES_PASSWORD"),
  database: System.get_env("POSTGRES_DB"),
  hostname: System.get_env("POSTGRES_HOST"),
  ssl_opts: [verify: :verify_peer, cacertfile: "./ca.crt",versions: [:"tlsv1.3"]],
  ssl: true,
  pool_size: 10

When I try to do mix ecto.create, I get error:

08:47:41.422 [error] Postgrex.Protocol (#PID<0.2436.0>) failed to connect: ** (DBConnection.ConnectionError) ssl connect: TLS client: In state wait_cert at ssl_handshake.erl:2071 generated CLIENT ALERT: Fatal - Bad Certificate

Obviously, if I do verify: :verify_none, it works like a charm, but then I can put anything as the cacert and it works. I don’t understand this as this certificate works with the psql protocol. Can anyone explain this behavior and maybe think of a solution?

P.S.: I tried to add the ca.crt to the trusted certificates, but to no avail.

2

I haven’t connected to Percona directly but I remembered PlanetScale does require additional options:

https://sourdough.dev/connecting-to-planetscale-with-phoenix-elixir

It is MySQL but the ssl stack is the same. Maybe worth trying some of those too?

3

I think that the question is more about the generation of the certificates. Actually, the way percona manages the certificates is through generating self-signed certificates with the help of cert-manager. So, I guess, my question would be how to make the ecto Postgres Adapter trust this certificate.

4

If the server certificate is self-signed you’re going to have to use the verify_fun ssl option to substitute a new verification logic: the default one in the ssl application does not handle those.

You could try using https://hex.pm/packages/ssl_verify_fun: it includes two verify_fun implementations for certificate pinning, one based on the fingerprint and one based on the public key. Examples (in Erlang syntax) are in the project’s README.

2 Likes
5

Thanks. Yes, I also found this site proposing a verifying function for self-sgined certificates. From the looks of it, it seems to do what I would want, that is to check whether the certificate is signed by a known CA or corresponds to the certificate I shall provide by environment variable (that part remains to be implemented, but seems doable). My question would be if the system still does the testing of the certificate being issued by a universally trusted authority because I read on a page that if verify_fun is used, the default verifying function is not applied. That seems impossible because the functions on the site still look at the error that cannot come but from that standard function or am I mistaken?

6

Yes, I also found this site proposing a verifying function for self-sgined certificates.

That verify_fun allows any self-signed peer certificate, which means it does not actually verify anything. What you need is some verification that the certificate from the server (in your case) can be trusted.

There are a couple of ways to do that, intuitively you might think it involves verifying the signature over the server’s certificate using the public key of the copy you have in your trust store. That would work, but in practice it is easier to just pin the certificate, basically saying: I trust the server if they present this specific certificate (using a fingerprint) or if they use this specific public key. The rest of the handshake then verifies that the server actually holds the private key associated with that certificate, and if they do it must mean you are connected to the right server.

My question would be if the system still does the testing of the certificate being issued by a universally trusted authority

If the certificate is self-signed then the certificate is not issues by a universally trusted authority. For these 1:1 trust relationships with 1 server and 1 client the (pinned) self-signed certificate is often enough. Certificates issued by trusted CAs, even private ones, are only really necessary when you have many clients, many servers or both.

Edit: to be be fair, the verify_fun in apiac_auth_mtls is presumably ok for their use-case of client certificates that are then, at a later stage, checked against the provisioned public keys:

authentication with a self-signed, self-issued certificate which is called Self-Signed Certificate Mutual-TLS Method. In this case, the certificate is checked against the subject public key info of the registered certificates of the client_id

So here the second stage of certificate verification happens at a later point, in application code, rather than in the verify_fun during the handshake

2 Likes
7

In that context, can you tell me how to retrieve the self-signed certificate sent by the server to compare it with the certificate in the trust-store? All I have right now is the otp_cert with four entries.

8

Normally you could use openssl s_client -connect server:port -showcerts, which will show the certificate(s) sent by the server in PEM format. But for Postgres the wire protocol requires some additional back-and-forth between the client and the server to start the TLS handshake, so this won’t work.

All I have right now is the otp_cert with four entries.

If by that you mean an :OTPCertificate record then you could convert it to PEM format using:

cert = {:OTPCertificate, ...}
pem = :public_key.pem_encode([{:Certificate, :public_key.pkix_encode(:OTPCertificate, cert, :otp), :not_encrypted}])
File.write!("cert.pem", pem)
4 Likes