Re: sslmode=secure by default (Re: Making sslrootcert=system work on Windows psql)

> On Linux/*ix, there would be 3 things that are all the same.
>
> If the Windows Openssl store is that bad, wouldn't the smarter thing
> to do for PG19 to use winstore by default? The Openssl one would still
> be available when requested explicitly. This would avoid the
> proliferation of default values.

I agree ... but I think that looks rather like my most recent (rejected) patch?

However, perhaps we could extend that patch for greater backwards-compatibility, checking not only that the SSL_CERT_DIR and SSL_CERT_FILE environment variables are not set, but *also* that there is no cert.pem file and no certs/ directory inside OPENSSLDIR.

I think that should make the behaviour backwards-compatible for all scenarios *except* those that would otherwise be guaranteed to fail certificate verification because we are on Windows and there are no OpenSSL certificates configured on the system. It seems fairly safe to assume that people who are using sslrootcert=system on Windows and without any configured OpenSSL certs are not doing so with the deliberate intention that all connections should fail!

I attach a patch that would do this (side-by-side view at https://github.com/postgres/postgres/compare/master...jawj:postgres:jawj-sslrootcert-system-windows)

An advantage of this approach would be that people building Postgres who want this behaviour sooner than next year could also patch it into versions 16 – 18 without much trouble.

>> BIGGER IDEA

>> In summary, you end up with these as sslmode values:
>>
>> * disabled
>> * insecure (formerly known as require)
>> * verify-ca
>> * verify-full
>> * secure (the new default, meaning sslmode=verify-full + sslrootcert=os)
>>
>> Obviously this would need to be well-trailed ahead of time, as some people would need to make changes to how they use psql/libpq. But it would peg the default security of a Postgres connection at the same level as the security of any random blog page (which I think is a bare minimum one might aspire to).
>
> I agree that this would be a good change for SSL users, and also one
> that people would likely be willing to buy.
>
> The big problem here is that a lot of installations are not using SSL
> at all (default on RPM), and another big chunk is using SSL, but
> relying on the default snakeoil certificates to just work (default on
> Debian), so this would not be "some people" but more like "everyone
> except the few who have already configured certificates properly".
>
> These people would have to change every single connection string to
> include "sslmode=disabled" or the like. This will likely not be
> received well.
>
> Before we can make this change, I think we would have to improve the
> UX. psql does not even have any --switch for it. PostgreSQL serving
> non-SSL and SSL on the same port doesn't make the UX better... :-/

How do you think the UX could be improved? Maybe by using a psql switch and/or an env var to opt out of (or initially even to opt into) the new sslmode treatment?