Hello! I’ve been having a lot of troubles trying to set up ssl with postgresql and I tried looking also on the discord before asking but turns out there was another guy who was also having issues and made a post without response, I’ve already tried looking everywhere online but I can’t really find a solution, also given I’m not too experienced with elixir, ssl, postgresql and all these programming things

So the current system:

So I create the certs like this (I’ve tried 2 ways)

openssl req -new -x509 -days 365 -nodes -out ca.crt -keyout ca.key -subj "/CN=root" &&

openssl genrsa -des3 -out server.key 2048 &&
openssl rsa -in server.key -out server.key && 
openssl req -new -nodes -key server.key -out server.csr -subj "/CN=root" && 
openssl x509 -req -in server.csr -days 365 -CA ca.crt -CAkey ca.key -CAcreateserial -out server.crt && 

openssl genrsa -des3 -out client.key 2048 &&
openssl rsa -in client.key -out client.key && 
openssl req -new -nodes -key client.key -out client.csr -subj "/CN=root" &&
openssl x509 -req -in client.csr -days 365 -CA ca.crt -CAkey ca.key -CAcreateserial -out client.crt &&

sudo chown 70:70 server.key &&
sudo chmod 600 server.key

and later

# https://www.howtoforge.com/postgresql-ssl-certificates
openssl genrsa -des3 -out server.key 4096 &&
openssl rsa -in server.key -out server.key
sudo chown 70:70 server.key &&
sudo chmod 600 server.key &&
openssl req -new -key server.key -days 3650 -out server.crt -x509 -subj '/C=CA/ST=British Columbia/L=Comox/O=localhost/CN=root/emailAddress=example@xyz.com' &&
cp server.crt root.crt &&
openssl genrsa -des3 -out ./client_certs/client.key 4096 &&
openssl rsa -in ./client_certs/client.key -out ./client_certs/client.key &&
openssl req -new -key ./client_certs/client.key -out ./client_certs/client.csr -subj '/C=CA/ST=British Columbia/L=Comox/O=localhost/CN=root' &&
openssl x509 -req -in ./client_certs/client.csr -CA root.crt -CAkey server.key -out ./client_certs/client.crt -CAcreateserial 
cp root.crt client_certs

The configurations look something like this

listen_addresses = '*'
ssl = on
ssl_cert_file = '/etc/postgresql/certs/server.crt'
ssl_key_file = '/etc/postgresql/certs/server.key'
ssl_ca_file = '/etc/postgresql/certs/client_certs/root.crt'

port=5432
ssl = on

hostnossl   all   all   0.0.0.0/0   reject
hostnossl   all   all   ::/0        reject
hostssl     all   all   0.0.0.0/0   cert clientcert=verify-full
hostssl     all   all   ::/0        cert clientcert=verify-full

I am using docker compose and before SSL the containers were able to communicate just fine, I am copying over the certs properly

db has
        - ./postgres/postgres_config/pg_hba.conf:/etc/postgresql/pg_hba.conf
        - ./postgres/postgres_config/postgresql.conf:/etc/postgresql.conf
        - ./postgres/certs:/etc/postgresql/certs/:ro
backend has      
- ./postgres/certs/client_certs:/app/certs:ro

In regards to the configurations within my code

config :chat_app, ecto_repos: [ChatApp.Repo]
config :chat_app, ChatApp.Repo,

  # fetches the DATABASE_URL environment variable to set database connection
  url: System.fetch_env!("DATABASE_URL"),
  # Connection pool size defaults to ten if not set by POOL_SIZE variable
  pool_size: String.to_integer(System.get_env("POOL_SIZE") || "10"),
  migration_primary_key: [type: :uuid],
  migration_foreign_key: [type: :uuid],

  # enable ssl connection
  ssl: [
  verify: :verify_peer,
  cacertfile: "/app/certs/root.crt",
  keyfile: "/app/certs/client.key",
  certfile: "/app/certs/client.crt",
  server_name_indication: to_charlist("localhost"),
]

(I do have extra db configs elsewhere (it’s when I used ai (which I solemnly regret) and I’m trying to figure stuff out and fix it)) but in general that’s the important ssl config things and also the other guy having the issues on the elixir server Discord gives his own different way and config!

Now I think I have included enough details, I can give more if needed but I think it’s good for now, I’ve had several errors while trying to fix the issue in different ways such as

{utf8String,<<"localhost">>}}]]}}}} - {:tls_alert, {:handshake_failure, ~c"TLS client: In state wait_cert at ssl_handshake.erl:2186 generated CLIENT ALERT: Fatal - Handshake Failure\n {bad_cert,\n     {hostname_check_failed,\n         {requested,\"postgres\"},\n         {received,\n             {rdnSequence,\n                 [[{'AttributeTypeAndValue',\n                       {2,5,4,3},\n                       {utf8String,<<\"localhost\">>}}]]}}}}"}}

or

22:41:14.559 [error] Postgrex.Protocol (#PID<0.322.0>) failed to connect: ** (DBConnection.ConnectionError) ssl connect: TLS client: In state wait_cert at ssl_handshake.erl:2169 generated CLIENT ALERT: Fatal - Bad Certificate
 invalid_signature - {:tls_alert, {:bad_certificate, ~c"TLS client: In state wait_cert at ssl_handshake.erl:2169 generated CLIENT ALERT: Fatal - Bad Certificate\n invalid_signature"}}

or

23:58:39.739 [error] Postgrex.Protocol (#PID<0.623.0>) failed to connect: ** (DBConnection.ConnectionError) ssl connect: TLS client: In state wait_cert at ssl_handshake.erl:2183 generated CLIENT ALERT: Fatal - Unknown CA
 - {:tls_alert, {:unknown_ca, ~c"TLS client: In state wait_cert at ssl_handshake.erl:2183 generated CLIENT ALERT: Fatal - Unknown CA\n"}}

But at this point most recently I have

 selfsigned_peer - {:tls_alert, {:bad_certificate, ~c"TLS client: In state wait_cert at ssl_handshake.erl:2181 generated CLIENT ALERT: Fatal - Bad Certificate\n selfsigned_peer"}}

And the other guy in his discord post got

[my-app] [notice] TLS :client: In state :wait_cert_cr at ssl_handshake.erl generated CLIENT ALERT: Fatal - Internal Error
[my-app] - {:unexpected_error, :undef}
[my-app] [error] Postgrex.Protocol failed to connect: ** (DBConnection.ConnectionError) ssl connect: TLS client: In state wait_cert_cr at ssl_handshake.erl

Also to clarify those previous errors were with other configurations in general. Generally though it appears to me that the fundamental issue is that Elixir does not like the certs themselves, how can I properly set up and deal with Ssl and certs to actually get this working because it really is a pain ;-; All the errors as far as my limited knowledge goes lead back to rome (bad certs)

If anyone knows what to do, how to solve this that would be greatly appreciated! take care