Can't connect to PostgreSQL 14 with SSL

[benbro]

I'm using Erlang OTP-24.3.3 and PostgreSQL 12 with SSL authentication successfully. I can't connect to a new database server running PostgreSQL 14. I don't see any error in the database log file. On the client I only see the error {error,none_available} when trying to use pgo:query/2.

I'm using self signed ssl cert:

[
 {pgo, [{pools, [{default, #{pool_size => 5,
                          host => "<db-ip>",
                          database => "mydb",
                          user => "myuser",
                          password => "mypassword",
                          ssl => true,
                          ssl_options => [{cacertfile, "/path/to/ca.crt"},
                                                   {certfile,   "/path/to/db.crt"},
                                                   {keyfile,    "/path/to/db.key"},
                                                   {verify,     verify_peer},
                                                   {server_name_indication, "db.myserver.com"}]
                            }
  }]}]}
].

postgresql.conf
ssl = on
ssl_ca_file = '/path/to/ca.crt'
ssl_cert_file = '/path/to/db.crt'
ssl_key_file = '/path/to/db.key'

and:

pg_hba.conf
hostssl all all 0.0.0.0/0 md5 clientcert=verify-ca

I've also tried to change password_encryption to md5 in postgresql.conf because of the missing scram support in pgo:

password_encryption = md5

Any idea what's missing?

[tsloughter]

Is there an error logged by the connection pool about the SSL issue?

(scram support is in the works)

[benbro]

I don't see an error in the log. Anything I need to turn on?

[benbro]

Once the password is encrypted with scram-sha-256 changing password_encryption = md5 will have no effect. I'll try setting the password after changing password_encryption. I don't know why I don't see errors on either the server side and the client side.

https://www.postgresql.org/docs/current/auth-password.html

The availability of the different password-based authentication methods depends on how a user's password on the server is encrypted (or hashed, more accurately). This is controlled by the configuration parameter password_encryption at the time the password is set.

[tsloughter]

So you mean it is still trying to use scram and that is the issue?

And you are right, looks like there isn't proper logging. I can probably get that added before finishing scram support.

[benbro]

I'll be able to verify it later but yes that's what I think. In postgres 14 I need to first set password_encryption to md5 and only then set the user password.

[benbro]

This works:

1. Set password_encryption = md5 in postgresql.conf.
2. Restart postgresql service.
3. Create a user with a password.
4. Connect with pgo.

scarm support will make it work by default. Thank you for working on it.