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

Re: George MacKerron
> SMALLER IDEA
>
> I’d suggest two new special sslrootcert values:
>
> (1) sslrootcert=openssl
>
> This does exactly what sslrootcert=system does now, but is less confusingly named for Windows users. sslrootcert=system becomes a deprecated synonym for this option.
>
> (2) sslrootcert=os
>
> This does what I was proposing in my patch: it uses winstore on Windows and behaves the same as sslrootcert=openssl elsewhere, where openssl *is* the operating system SSL provider.
>
> These changes would be fully backwards-compatible.

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.

> BIGGER IDEA
>
> * Entirely remove the current default, sslmode=prefer, and make explicitly asking for sslmode=prefer an error. After all, as the docs themselves point out for sslmode=prefer: “this makes no sense from a security point of view”.

(It's not really secure, but opportunistic "use SSL when available" is
still better than nothing.)

> * Create a new option, sslmode=secure, which means sslmode=verify-full + sslrootcert=os. Make this the default!

I like the name.

> 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... :-/

Christoph