search

processone / ejabberd

Robust, Ubiquitous and Massively Scalable Messaging Platform (XMPP, MQTT, SIP Server)
https://www.process-one.net/ejabberd/
Other
6.12k stars 1.51k forks source link

[BUG] Cannot connect to Postgres with `sql_ssl_verify: true` #4180

Open sando38 opened 8 months ago

sando38 commented 8 months ago

Environment

Configuration (only if needed): grep -Ev '^$|^\s*#' ejabberd.yml

sql_server: cnpg-ejabberd-testing-abc
sql_port: 5432
sql_database: ejabberd
sql_username: ejabberd
sql_password: ""
sql_type: pgsql

sql_ssl: true
sql_ssl_verify: true
sql_ssl_cafile: "/opt/ejabberd/certs/cnpg-tls/ca.crt"
sql_ssl_certfile: "/opt/ejabberd/certs/cnpg-tls/fullchain.pem"

Errors from error.log/crash.log

``` 2024-03-19 22:46:03.304686+00:00 [warning] <0.450.0>@ejabberd_sql:handle_reconnect/2:491 pgsql connection failed: ** Reason: {tls_alert, {handshake_failure, "TLS client: In state wait_cert at ssl_handshake.erl:2135 generated CLIENT ALERT: Fatal - Handshake Failure\n {bad_cert,hostname_check_failed}"}} ** Retry after: 3 seconds 2024-03-19 22:46:03.306544+00:00 [notice] <0.1509.0>@ssl_handshake:path_validation_alert/1:2135 TLS client: In state wait_cert at ssl_handshake.erl:2135 generated CLIENT ALERT: Fatal - Handshake Failure - {bad_cert,hostname_check_failed} 2024-03-19 22:46:03.306757+00:00 [warning] <0.443.0>@ejabberd_sql:handle_reconnect/2:491 pgsql connection failed: ** Reason: {tls_alert, {handshake_failure, "TLS client: In state wait_cert at ssl_handshake.erl:2135 generated CLIENT ALERT: Fatal - Handshake Failure\n {bad_cert,hostname_check_failed}"}} ** Retry after: 3 seconds 2024-03-19 22:46:03.311020+00:00 [debug] <0.1511.0>@supervisor:report_progress/2:1565 PROGRESS REPORT: supervisor: {<0.1511.0>,tls_dyn_connection_sup} started: [{pid,<0.1512.0>}, {id,sender}, {mfargs,{tls_sender,start_link,[[{spawn_opt,[]}]]}}, {restart_type,temporary}, {significant,false}, {shutdown,5000}, {child_type,worker}] 2024-03-19 22:46:03.311185+00:00 [debug] <0.1511.0>@supervisor:report_progress/2:1565 PROGRESS REPORT: supervisor: {<0.1511.0>,tls_dyn_connection_sup} started: [{pid,<0.1513.0>}, {id,receiver}, {mfargs, {ssl_gen_statem,start_link, [client,<0.1512.0>, {10,40,24,14}, 5432,#Port<0.239>, {#{signature_algs_cert => undefined, session_tickets => disabled,verify_fun => undefined, user_lookup_fun => undefined,protocol => tls, alpn_advertised_protocols => undefined, crl_check => false,cacerts => undefined, renegotiate_at => 268435456, signature_algs => [eddsa_ed25519,eddsa_ed448, ecdsa_secp521r1_sha512,ecdsa_secp384r1_sha384, ecdsa_secp256r1_sha256,rsa_pss_pss_sha512, rsa_pss_pss_sha384,rsa_pss_pss_sha256, rsa_pss_rsae_sha512,rsa_pss_rsae_sha384, rsa_pss_rsae_sha256,rsa_pkcs1_sha512, rsa_pkcs1_sha384,rsa_pkcs1_sha256, {sha512,ecdsa}, {sha384,ecdsa}, {sha256,ecdsa}], versions => [{3,4},{3,3}], max_handshake_size => 131072, secure_renegotiate => true,fallback => false, cacertfile => <<"/opt/ejabberd/certs/cnpg-tls/ca.crt">>, early_data => undefined,handshake => full, psk_identity => undefined, max_fragment_length => undefined, crl_cache => {ssl_crl_cache,{internal,[]}}, log_level => notice,key_update_at => 388736063997, supported_groups => {supported_groups, [x25519,x448,secp256r1,secp384r1]}, customize_hostname_check => [], server_name_indication => undefined, reuse_sessions => true, ciphers => [<<19,2>>, <<19,1>>, <<19,3>>, <<19,4>>, <<19,5>>, <<"À,">>,<<"À0">>,<<"À­">>,<<"À¯">>,<<"À$">>, <<"À(">>, <<204,169>>, <<204,168>>, <<"À+">>,<<"À/">>,<<"À¬">>,<<"À®">>,<<"À.">>, <<"À2">>,<<"À&">>,<<"À*">>,<<"À-">>,<<"À1">>, <<"À#">>,<<"À'">>,<<"À%">>,<<"À)">>, <<0,159>>, <<0,163>>, <<0,107>>, <<0,106>>, <<0,158>>, <<0,162>>, <<204,170>>, <<0,103>>, <<0,64>>, <<"À\n">>, <<192,20>>, <<192,5>>, <<192,15>>, <<"À\t">>, <<192,19>>, <<192,4>>, <<192,14>>, <<0,57>>, <<0,56>>, <<0,51>>, <<0,50>>], use_ticket => undefined,srp_identity => undefined, eccs => {elliptic_curves, [{1,3,132,0,39}, {1,3,132,0,38}, {1,3,132,0,35}, {1,3,36,3,3,2,8,1,1,13}, {1,3,132,0,36}, {1,3,132,0,37}, {1,3,36,3,3,2,8,1,1,11}, {1,3,132,0,34}, {1,3,132,0,16}, {1,3,132,0,17}, {1,3,36,3,3,2,8,1,1,7}, {1,3,132,0,10}, {1,2,840,10045,3,1,7}]}, verify => verify_peer, partial_chain => #Fun, reuse_session => undefined, certs_keys => [#{certfile => <<"/opt/ejabberd/certs/cnpg-tls/fullchain.pem">>, keyfile => <<"/opt/ejabberd/certs/cnpg-tls/fullchain.pem">>}]}, {socket_options,binary,0,0,0,once}, undefined}, <0.1501.0>, {gen_tcp,tcp,tcp_closed,tcp_error,tcp_passive}]}}, {restart_type,temporary}, {significant,true}, {shutdown,5000}, {child_type,worker}] 2024-03-19 22:46:03.315281+00:00 [notice] <0.1513.0>@ssl_handshake:path_validation_alert/1:2135 TLS client: In state wait_cert at ssl_handshake.erl:2135 generated CLIENT ALERT: Fatal - Handshake Failure - {bad_cert,hostname_check_failed} 2024-03-19 22:46:03.315457+00:00 [warning] <0.454.0>@ejabberd_sql:handle_reconnect/2:491 pgsql connection failed: ** Reason: {tls_alert, {handshake_failure, "TLS client: In state wait_cert at ssl_handshake.erl:2135 generated CLIENT ALERT: Fatal - Handshake Failure\n {bad_cert,hostname_check_failed}"}} ** Retry after: 3 seconds 2024-03-19 22:46:03.325665+00:00 [debug] <0.1520.0>@supervisor:report_progress/2:1565 PROGRESS REPORT: supervisor: {<0.1520.0>,tls_dyn_connection_sup} started: [{pid,<0.1521.0>}, {id,sender}, {mfargs,{tls_sender,start_link,[[{spawn_opt,[]}]]}}, {restart_type,temporary}, {significant,false}, {shutdown,5000}, {child_type,worker}] 2024-03-19 22:46:03.325881+00:00 [debug] <0.1520.0>@supervisor:report_progress/2:1565 PROGRESS REPORT: supervisor: {<0.1520.0>,tls_dyn_connection_sup} started: [{pid,<0.1522.0>}, {id,receiver}, {mfargs, {ssl_gen_statem,start_link, [client,<0.1521.0>, {10,40,24,14}, 5432,#Port<0.240>, {#{signature_algs_cert => undefined, session_tickets => disabled,verify_fun => undefined, user_lookup_fun => undefined,protocol => tls, alpn_advertised_protocols => undefined, crl_check => false,cacerts => undefined, renegotiate_at => 268435456, signature_algs => [eddsa_ed25519,eddsa_ed448, ecdsa_secp521r1_sha512,ecdsa_secp384r1_sha384, ecdsa_secp256r1_sha256,rsa_pss_pss_sha512, rsa_pss_pss_sha384,rsa_pss_pss_sha256, rsa_pss_rsae_sha512,rsa_pss_rsae_sha384, rsa_pss_rsae_sha256,rsa_pkcs1_sha512, rsa_pkcs1_sha384,rsa_pkcs1_sha256, {sha512,ecdsa}, {sha384,ecdsa}, {sha256,ecdsa}], versions => [{3,4},{3,3}], max_handshake_size => 131072, secure_renegotiate => true,fallback => false, cacertfile => <<"/opt/ejabberd/certs/cnpg-tls/ca.crt">>, early_data => undefined,handshake => full, psk_identity => undefined, max_fragment_length => undefined, crl_cache => {ssl_crl_cache,{internal,[]}}, log_level => notice,key_update_at => 388736063997, supported_groups => {supported_groups, [x25519,x448,secp256r1,secp384r1]}, customize_hostname_check => [], server_name_indication => undefined, reuse_sessions => true, ciphers => [<<19,2>>, <<19,1>>, <<19,3>>, <<19,4>>, <<19,5>>, <<"À,">>,<<"À0">>,<<"À­">>,<<"À¯">>,<<"À$">>, <<"À(">>, <<204,169>>, <<204,168>>, <<"À+">>,<<"À/">>,<<"À¬">>,<<"À®">>,<<"À.">>, <<"À2">>,<<"À&">>,<<"À*">>,<<"À-">>,<<"À1">>, <<"À#">>,<<"À'">>,<<"À%">>,<<"À)">>, <<0,159>>, <<0,163>>, <<0,107>>, <<0,106>>, <<0,158>>, <<0,162>>, <<204,170>>, <<0,103>>, <<0,64>>, <<"À\n">>, <<192,20>>, <<192,5>>, <<192,15>>, <<"À\t">>, <<192,19>>, <<192,4>>, <<192,14>>, <<0,57>>, <<0,56>>, <<0,51>>, <<0,50>>], use_ticket => undefined,srp_identity => undefined, eccs => {elliptic_curves, [{1,3,132,0,39}, {1,3,132,0,38}, {1,3,132,0,35}, {1,3,36,3,3,2,8,1,1,13}, {1,3,132,0,36}, {1,3,132,0,37}, {1,3,36,3,3,2,8,1,1,11}, {1,3,132,0,34}, {1,3,132,0,16}, {1,3,132,0,17}, {1,3,36,3,3,2,8,1,1,7}, {1,3,132,0,10}, {1,2,840,10045,3,1,7}]}, verify => verify_peer, partial_chain => #Fun, reuse_session => undefined, certs_keys => [#{certfile => <<"/opt/ejabberd/certs/cnpg-tls/fullchain.pem">>, keyfile => <<"/opt/ejabberd/certs/cnpg-tls/fullchain.pem">>}]}, {socket_options,binary,0,0,0,once}, undefined}, <0.1515.0>, {gen_tcp,tcp,tcp_closed,tcp_error,tcp_passive}]}}, {restart_type,temporary}, {significant,true}, {shutdown,5000}, {child_type,worker}] 2024-03-19 22:46:03.327646+00:00 [debug] <0.1524.0>@supervisor:report_progress/2:1565 PROGRESS REPORT: supervisor: {<0.1524.0>,tls_dyn_connection_sup} started: [{pid,<0.1525.0>}, {id,sender}, {mfargs,{tls_sender,start_link,[[{spawn_opt,[]}]]}}, {restart_type,temporary}, {significant,false}, {shutdown,5000}, {child_type,worker}] 2024-03-19 22:46:03.327868+00:00 [debug] <0.1524.0>@supervisor:report_progress/2:1565 PROGRESS REPORT: supervisor: {<0.1524.0>,tls_dyn_connection_sup} started: [{pid,<0.1526.0>}, {id,receiver}, {mfargs, {ssl_gen_statem,start_link, [client,<0.1525.0>, {10,40,24,14}, 5432,#Port<0.241>, {#{signature_algs_cert => undefined, session_tickets => disabled,verify_fun => undefined, user_lookup_fun => undefined,protocol => tls, alpn_advertised_protocols => undefined, crl_check => false,cacerts => undefined, renegotiate_at => 268435456, signature_algs => [eddsa_ed25519,eddsa_ed448, ecdsa_secp521r1_sha512,ecdsa_secp384r1_sha384, ecdsa_secp256r1_sha256,rsa_pss_pss_sha512, rsa_pss_pss_sha384,rsa_pss_pss_sha256, rsa_pss_rsae_sha512,rsa_pss_rsae_sha384, rsa_pss_rsae_sha256,rsa_pkcs1_sha512, rsa_pkcs1_sha384,rsa_pkcs1_sha256, {sha512,ecdsa}, {sha384,ecdsa}, {sha256,ecdsa}], versions => [{3,4},{3,3}], max_handshake_size => 131072, secure_renegotiate => true,fallback => false, cacertfile => <<"/opt/ejabberd/certs/cnpg-tls/ca.crt">>, early_data => undefined,handshake => full, psk_identity => undefined, max_fragment_length => undefined, crl_cache => {ssl_crl_cache,{internal,[]}}, log_level => notice,key_update_at => 388736063997, supported_groups => {supported_groups, [x25519,x448,secp256r1,secp384r1]}, customize_hostname_check => [], server_name_indication => undefined, reuse_sessions => true, ciphers => [<<19,2>>, <<19,1>>, <<19,3>>, <<19,4>>, <<19,5>>, <<"À,">>,<<"À0">>,<<"À­">>,<<"À¯">>,<<"À$">>, <<"À(">>, <<204,169>>, <<204,168>>, <<"À+">>,<<"À/">>,<<"À¬">>,<<"À®">>,<<"À.">>, <<"À2">>,<<"À&">>,<<"À*">>,<<"À-">>,<<"À1">>, <<"À#">>,<<"À'">>,<<"À%">>,<<"À)">>, <<0,159>>, <<0,163>>, <<0,107>>, <<0,106>>, <<0,158>>, <<0,162>>, <<204,170>>, <<0,103>>, <<0,64>>, <<"À\n">>, <<192,20>>, <<192,5>>, <<192,15>>, <<"À\t">>, <<192,19>>, <<192,4>>, <<192,14>>, <<0,57>>, <<0,56>>, <<0,51>>, <<0,50>>], use_ticket => undefined,srp_identity => undefined, eccs => {elliptic_curves, [{1,3,132,0,39}, {1,3,132,0,38}, {1,3,132,0,35}, {1,3,36,3,3,2,8,1,1,13}, {1,3,132,0,36}, {1,3,132,0,37}, {1,3,36,3,3,2,8,1,1,11}, {1,3,132,0,34}, {1,3,132,0,16}, {1,3,132,0,17}, {1,3,36,3,3,2,8,1,1,7}, {1,3,132,0,10}, {1,2,840,10045,3,1,7}]}, verify => verify_peer, partial_chain => #Fun, reuse_session => undefined, certs_keys => [#{certfile => <<"/opt/ejabberd/certs/cnpg-tls/fullchain.pem">>, keyfile => <<"/opt/ejabberd/certs/cnpg-tls/fullchain.pem">>}]}, {socket_options,binary,0,0,0,once}, undefined}, <0.1514.0>, {gen_tcp,tcp,tcp_closed,tcp_error,tcp_passive}]}}, {restart_type,temporary}, {significant,true}, {shutdown,5000}, {child_type,worker}] 2024-03-19 22:46:03.328378+00:00 [debug] <0.1528.0>@supervisor:report_progress/2:1565 PROGRESS REPORT: supervisor: {<0.1528.0>,tls_dyn_connection_sup} started: [{pid,<0.1529.0>}, {id,sender}, {mfargs,{tls_sender,start_link,[[{spawn_opt,[]}]]}}, {restart_type,temporary}, {significant,false}, {shutdown,5000}, {child_type,worker}] 2024-03-19 22:46:03.328566+00:00 [debug] <0.1528.0>@supervisor:report_progress/2:1565 PROGRESS REPORT: supervisor: {<0.1528.0>,tls_dyn_connection_sup} started: [{pid,<0.1530.0>}, {id,receiver}, {mfargs, {ssl_gen_statem,start_link, [client,<0.1529.0>, {10,40,24,14}, 5432,#Port<0.242>, {#{signature_algs_cert => undefined, session_tickets => disabled,verify_fun => undefined, user_lookup_fun => undefined,protocol => tls, alpn_advertised_protocols => undefined, crl_check => false,cacerts => undefined, renegotiate_at => 268435456, signature_algs => [eddsa_ed25519,eddsa_ed448, ecdsa_secp521r1_sha512,ecdsa_secp384r1_sha384, ecdsa_secp256r1_sha256,rsa_pss_pss_sha512, rsa_pss_pss_sha384,rsa_pss_pss_sha256, rsa_pss_rsae_sha512,rsa_pss_rsae_sha384, rsa_pss_rsae_sha256,rsa_pkcs1_sha512, rsa_pkcs1_sha384,rsa_pkcs1_sha256, {sha512,ecdsa}, {sha384,ecdsa}, {sha256,ecdsa}], versions => [{3,4},{3,3}], max_handshake_size => 131072, secure_renegotiate => true,fallback => false, cacertfile => <<"/opt/ejabberd/certs/cnpg-tls/ca.crt">>, early_data => undefined,handshake => full, psk_identity => undefined, max_fragment_length => undefined, crl_cache => {ssl_crl_cache,{internal,[]}}, log_level => notice,key_update_at => 388736063997, supported_groups => {supported_groups, [x25519,x448,secp256r1,secp384r1]}, customize_hostname_check => [], server_name_indication => undefined, reuse_sessions => true, ciphers => [<<19,2>>, <<19,1>>, <<19,3>>, <<19,4>>, <<19,5>>, <<"À,">>,<<"À0">>,<<"À­">>,<<"À¯">>,<<"À$">>, <<"À(">>, <<204,169>>, <<204,168>>, <<"À+">>,<<"À/">>,<<"À¬">>,<<"À®">>,<<"À.">>, <<"À2">>,<<"À&">>,<<"À*">>,<<"À-">>,<<"À1">>, <<"À#">>,<<"À'">>,<<"À%">>,<<"À)">>, <<0,159>>, <<0,163>>, <<0,107>>, <<0,106>>, <<0,158>>, <<0,162>>, <<204,170>>, <<0,103>>, <<0,64>>, <<"À\n">>, <<192,20>>, <<192,5>>, <<192,15>>, <<"À\t">>, <<192,19>>, <<192,4>>, <<192,14>>, <<0,57>>, <<0,56>>, <<0,51>>, <<0,50>>], use_ticket => undefined,srp_identity => undefined, eccs => {elliptic_curves, [{1,3,132,0,39}, {1,3,132,0,38}, {1,3,132,0,35}, {1,3,36,3,3,2,8,1,1,13}, {1,3,132,0,36}, {1,3,132,0,37}, {1,3,36,3,3,2,8,1,1,11}, {1,3,132,0,34}, {1,3,132,0,16}, {1,3,132,0,17}, {1,3,36,3,3,2,8,1,1,7}, {1,3,132,0,10}, {1,2,840,10045,3,1,7}]}, verify => verify_peer, partial_chain => #Fun, reuse_session => undefined, certs_keys => [#{certfile => <<"/opt/ejabberd/certs/cnpg-tls/fullchain.pem">>, keyfile => <<"/opt/ejabberd/certs/cnpg-tls/fullchain.pem">>}]}, {socket_options,binary,0,0,0,once}, undefined}, <0.1516.0>, {gen_tcp,tcp,tcp_closed,tcp_error,tcp_passive}]}}, {restart_type,temporary}, {significant,true}, {shutdown,5000}, {child_type,worker}] 2024-03-19 22:46:03.330446+00:00 [debug] <0.1532.0>@supervisor:report_progress/2:1565 PROGRESS REPORT: supervisor: {<0.1532.0>,tls_dyn_connection_sup} started: [{pid,<0.1533.0>}, {id,sender}, {mfargs,{tls_sender,start_link,[[{spawn_opt,[]}]]}}, {restart_type,temporary}, {significant,false}, {shutdown,5000}, {child_type,worker}] 2024-03-19 22:46:03.330715+00:00 [debug] <0.1532.0>@supervisor:report_progress/2:1565 PROGRESS REPORT: supervisor: {<0.1532.0>,tls_dyn_connection_sup} started: [{pid,<0.1534.0>}, {id,receiver}, {mfargs, {ssl_gen_statem,start_link, [client,<0.1533.0>, {10,40,24,14}, 5432,#Port<0.243>, {#{signature_algs_cert => undefined, session_tickets => disabled,verify_fun => undefined, user_lookup_fun => undefined,protocol => tls, alpn_advertised_protocols => undefined, crl_check => false,cacerts => undefined, renegotiate_at => 268435456, signature_algs => [eddsa_ed25519,eddsa_ed448, ecdsa_secp521r1_sha512,ecdsa_secp384r1_sha384, ecdsa_secp256r1_sha256,rsa_pss_pss_sha512, rsa_pss_pss_sha384,rsa_pss_pss_sha256, rsa_pss_rsae_sha512,rsa_pss_rsae_sha384, rsa_pss_rsae_sha256,rsa_pkcs1_sha512, rsa_pkcs1_sha384,rsa_pkcs1_sha256, {sha512,ecdsa}, {sha384,ecdsa}, {sha256,ecdsa}], versions => [{3,4},{3,3}], max_handshake_size => 131072, secure_renegotiate => true,fallback => false, cacertfile => <<"/opt/ejabberd/certs/cnpg-tls/ca.crt">>, early_data => undefined,handshake => full, psk_identity => undefined, max_fragment_length => undefined, crl_cache => {ssl_crl_cache,{internal,[]}}, log_level => notice,key_update_at => 388736063997, supported_groups => {supported_groups, [x25519,x448,secp256r1,secp384r1]}, customize_hostname_check => [], server_name_indication => undefined, reuse_sessions => true, ciphers => [<<19,2>>, <<19,1>>, <<19,3>>, <<19,4>>, <<19,5>>, <<"À,">>,<<"À0">>,<<"À­">>,<<"À¯">>,<<"À$">>, <<"À(">>, <<204,169>>, <<204,168>>, <<"À+">>,<<"À/">>,<<"À¬">>,<<"À®">>,<<"À.">>, <<"À2">>,<<"À&">>,<<"À*">>,<<"À-">>,<<"À1">>, <<"À#">>,<<"À'">>,<<"À%">>,<<"À)">>, <<0,159>>, <<0,163>>, <<0,107>>, <<0,106>>, <<0,158>>, <<0,162>>, <<204,170>>, <<0,103>>, <<0,64>>, <<"À\n">>, <<192,20>>, <<192,5>>, <<192,15>>, <<"À\t">>, <<192,19>>, <<192,4>>, <<192,14>>, <<0,57>>, <<0,56>>, <<0,51>>, <<0,50>>], use_ticket => undefined,srp_identity => undefined, eccs => {elliptic_curves, [{1,3,132,0,39}, {1,3,132,0,38}, {1,3,132,0,35}, {1,3,36,3,3,2,8,1,1,13}, {1,3,132,0,36}, {1,3,132,0,37}, {1,3,36,3,3,2,8,1,1,11}, {1,3,132,0,34}, {1,3,132,0,16}, {1,3,132,0,17}, {1,3,36,3,3,2,8,1,1,7}, {1,3,132,0,10}, {1,2,840,10045,3,1,7}]}, verify => verify_peer, partial_chain => #Fun, reuse_session => undefined, certs_keys => [#{certfile => <<"/opt/ejabberd/certs/cnpg-tls/fullchain.pem">>, keyfile => <<"/opt/ejabberd/certs/cnpg-tls/fullchain.pem">>}]}, {socket_options,binary,0,0,0,once}, undefined}, <0.1517.0>, {gen_tcp,tcp,tcp_closed,tcp_error,tcp_passive}]}}, {restart_type,temporary}, {significant,true}, {shutdown,5000}, {child_type,worker}] 2024-03-19 22:46:03.331255+00:00 [notice] <0.1522.0>@ssl_handshake:path_validation_alert/1:2135 TLS client: In state wait_cert at ssl_handshake.erl:2135 generated CLIENT ALERT: Fatal - Handshake Failure - {bad_cert,hostname_check_failed} 2024-03-19 22:46:03.331473+00:00 [warning] <0.446.0>@ejabberd_sql:handle_reconnect/2:491 pgsql connection failed: ** Reason: {tls_alert, {handshake_failure, "TLS client: In state wait_cert at ssl_handshake.erl:2135 generated CLIENT ALERT: Fatal - Handshake Failure\n {bad_cert,hostname_check_failed}"}} ** Retry after: 3 seconds ```

Bug description

I cannot connect to Postgres with sql_ssl_verify: true. I have the above error messages. When I use a simple psql client using the same certificates, it works:

~ $ psql "sslmode=verify-full sslrootcert=/opt/ejabberd/certs/cnpg-tls/ca.crt sslcert=/opt/ejabberd/certs/cnpg-tls/tls.crt sslkey=/opt/ejabberd/certs/cnpg-tls/tls.key host=cnpg-ejabberd-testing-abc port=5432 u
ser=ejabberd dbname=ejabberd"
psql (15.6, server 16.2 (Debian 16.2-1.pgdg110+2))
WARNING: psql major version 15, server major version 16.
         Some psql features might not work.
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

ejabberd=>

Here is the corresponding Postgres error message:

{"level":"info","ts":"2024-03-19T22:49:58Z","logger":"postgres","msg":"record","logging_pod":"cnpg-ejabberd-testing-abc-1","record":{"log_time":"2024-03-19 22:49:58.861 UTC","process_id":"4953","connection_from":"127.0.0.6:47863","session_id":"65fa1696.1359","session_line_num":"1","session_start_time":"2024-03-19 22:49:58 UTC","transaction_id":"0","error_severity":"LOG","sql_state_code":"08P01","message":"could not accept SSL connection: sslv3 alert handshake failure","backend_type":"not initialized","query_id":"0"}}

The CA certificate is PEM encoded. Without the sql_ssl_verify: true option, only presenting the client certificate, it works.

I am not sure if this is a problem on my side with ejabberd or Postgres. I am happy for any advice.

intranetlabs commented 3 months ago

+1

I am using client certificate authentication between ejabberd and postgres. I can only get a successful connection when sql_ssl_verify is set to false.

Environment

Relevant ejabberd configuration

loglevel: debug
[...]
new_sql_schema: true
default_db: sql
sql_server: 1.2.3.4
sql_port: 5432
sql_database: ejabberd
sql_type: pgsql
# no username/password defined, postgres configured with client cert auth
sql_ssl: true
sql_ssl_verify: true
sql_ssl_cafile: "/path/to/ca.crt"
sql_ssl_certfile: "/path/to/user@certfile.pem"

Relevant postgres configuration

postgresql.conf

ssl = on
ssl_ca_file = '/path/to/ca.crt'
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'

pg_hba.conf

#TYPE       DATABASE     USER     ADDRESS       METHOD
hostssl     ejabberd     user     1.2.3.4/5     cert clientcert=verify-full

Errors from error.log

22:20:57.330 [notice] TLS :client: In state :wait_cert at ssl_handshake.erl:2127 generated CLIENT ALERT: Fatal - Handshake Failure
 - {:bad_cert, :hostname_check_failed}
22:20:57.333 [warning] :pgsql connection failed:
** Reason: {:tls_alert,
 {:handshake_failure,
  ~c"TLS client: In state wait_cert at ssl_handshake.erl:2127 generated CLIENT ALERT: Fatal - Handshake Failure\n {bad_cert,hostname_check_failed}"}}
** Retry after: 30 seconds

Postgres Error Log

[32081] LOG:  08P01: could not accept SSL connection: sslv3 alert handshake failure
[32081] LOCATION:  be_tls_open_server, be-secure-openssl.c:508
prefiks commented 3 months ago

If you are using ip address to connect to sql server, there is no chance that cert domain validation will pass - there is domain used. You would need to make sql_server point to domain from cert to get pass that.

intranetlabs commented 3 months ago

If you are using ip address to connect to sql server, there is no chance that cert domain validation will pass - there is domain used. You would need to make sql_server point to domain from cert to get pass that.

Ah, fair point. That being said, the issue persists when setting sql_server to the fqdn as well.

It may be of note that postgres and ejabberd are both running on the same machine, I plan on testing further with them on separate hosts.

Edit:

Tested with fqdn using postgres hosted on separate host, and the issue continues to persist.

intranetlabs commented 2 months ago

Is there a specific format ejabberd needs the certificate files to be in?

e.g. does sql_ssl_cafile need to contain the full chain between the ca public key to the postgres server's public key?

FWIW the server certificates were issued via acme with an internal certificate authority. Additionally, connecting to postgres using client-certificate authentication succeeds both via the command line using psql and when defined in the ejabberd configuration file.

All systems were configured to include the appropriate ca chain in the trust store accordingly, and are able to utilize other services requiring SSL under the same certificate authority.