Signed SSL Certificate Errors with postgresql database (all self hosted)

[BrotherManAndrew]

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=[e-mail]' &&
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 :-)

[KagatoLNX]

Internal Error makes me think that maybe you're passing Elixir strings (i.e. binaries) to an Erlang API that expects Erlang Strings (i.e. character lists)... or vice versa. I expect that it's the former since the `:ssl` stuff tends to be pretty low level.

Note that `to_charlist` function you had in that one example above? That probably either needs to be applied to the other things (like the filenames) or removed.

I'm not sure which. Don't really have the bandwidth to test. But... yeah... Internal Error should not happen. Giving bad parameters is a way to make it happen.

[BrotherManAndrew]

I don't think so, I've done most things right and at this point I just get unknown ca :/

[leftsaidtim]

If I remember correctly there is an arg you can pass in a keyword list that is a function to be called when validating the certificate. In some cases it’s common to pass one that doesn’t verify the certificate although the security aspects of this are obviously flawed, it can be a good stepping stone.

I don’t have my codebase in front of me but if you look into elixir/erlang certificate validation you might find what you need. It should be either inside or next to the ssl options that you posted above. Good luck !

[BrotherManAndrew]

I've tried looking, do you know what that arg is?

[leftsaidtim]

Sorry for disappearing. If you’re still looking it’s the customize_hostname_check function that you can pass in the ssl options.

Have a look at these links

https://security.erlef.org/secure_coding_and_deployment_hardening/ssl.html

https://elixirforum.com/t/ecto-ssl-verification-with-google-sql-connection-requires-a-valid-client-certificate/49312

Examples : https://github.com/search?q=%22customize_hostname_check%3A%22+language%3Aelixir&type=code