Hi! I've looked at the way libpq handles TLS certificates and plaintext fallback, and I am somewhat surprised.
The default ssmode is prefer. According to the documentation, this will make libpq use an SSL connection if possible, but will use a plain text connection as a fallback. The certificate will not be verified. If, however, there is a root certificate in ~/.postgresql/root.crt, libpq will check if the server cert matches this certificate, and refuse any certfificates that don't match. This means that libpq will fall back to a plain text connection! This is very unexpected behavior! Shouldn't libpq prefer an *unauthenticated but encrypted* connection over an *unauthenticated and unencrypted* connection? This behavior also causes sslmode=require to behave like sslmode=verify-ca when ~/.postgresql/root.crt exists. From my limited understanding, it seems the way to fix this would be in fe-secure-openssl.c, to change initialize_SSL() to only read the root certificate file when sslmode=verify_* However, if this is the expected behavior, the documentation at https://www.postgresql.org/docs/current/static/libpq-ssl.html <https://www.postgresql.org/docs/current/static/libpq-ssl.html> should be updated to make this more clear. It should be made clear that the existence of the file ~/.postgresql/root.crt changes the behavior of sslmode=require and sslmode=prefer. Best regards, Jakob
