On Thu, Jun 16, 2016 at 10:39 AM, Jakob Egger <jakob@eggerapps.at> wrote:
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?
You would think so.
The default mode of "prefer" is ridiculous in a lot of ways. If you are using SSL in any shape or form you should simply not use "prefer". That's really the only answer at this point, unfortunately.
This behavior also causes sslmode=require to behave like sslmode=verify-ca when ~/.postgresql/root.crt exists.
Correct. That's mainly for really old backwards compatibility. We could have a "sslmode=verify-none" to reverse that, I guess. I'm not sure if this scenario is common enough to care about though?
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 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.
Agreed. It's basically backwards compatibility with something that was badly documented in the first place :) That's not a particularly strong argument for the way it is. Clarifying the documentation would definitely be a good improvement.
