> 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_DIRand 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
guaranteedto fail certificate verification because we are on Windows and there are no OpenSSL certificates configured
onthe system. It seems fairly safe to assume that people who are using sslrootcert=system on Windows and without any
configuredOpenSSL 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
couldalso 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
usepsql/libpq. But it would peg the default security of a Postgres connection at the same level as the security of any
randomblog 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
evento opt into) the new sslmode treatment?
