Horton

Thread

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. doc: Clarify the system value for sslrootcert

  1. Making sslrootcert=system work on Windows psql

    George MacKerron <george@mackerron.co.uk> — 2025-04-01T21:05:08Z

    I was very pleased to see the sslrootcert=system connection option added in Postgres 16 (I even blogged about it: https://neon.tech/blog/avoid-mitm-attacks-with-psql-postgres-16). But sslrootcert=system has not been widely supported by psql installations, perhaps because people compiling Postgres haven’t always been aware of the requirement to point OpenSSL in the direction of the system’s root CA certificates.

I’ve recently been trying to get it more widely supported, with some success (details at end of this message).

However, psql via the EnterpriseDB Windows installer still doesn’t support sslrootcert=system, and I think a tiny patch is needed. The diff is attached, and can be seen in context here: https://github.com/postgres/postgres/compare/master...jawj:postgres:jawj-sslrootcert-system-windows

Essentially, on Windows with OpenSSL 3.2+, it replaces SSL_CTX_set_default_verify_paths(SSL_context) with SSL_CTX_load_verify_store(SSL_context, "org.openssl.winstore:”).

I’m not a Windows or OpenSSL expert, but so far the patched code seems to work in theory and in practice (sources below, and I’ve compiled and tested it working on Windows 11 x64).


# Sources

https://stackoverflow.com/a/79461864/338196
https://docs.openssl.org/master/man7/OSSL_STORE-winstore/
https://docs.openssl.org/master/man3/SSL_CTX_load_verify_locations/


# Status of sslrootcert=system in various packages providing psql

## Mac
Postgres.app — now fixed (https://github.com/PostgresApp/PostgresApp/issues/801)
MacPorts — now fixed (https://trac.macports.org/ticket/72080)
EDB installer — now fixed (https://github.com/EnterpriseDB/edb-installers/issues/264)
homebrew — was working already

## Linux
Debian/Ubuntu — now Recommends ca-certificates (https://salsa.debian.org/postgresql/postgresql/-/commit/96077ad61c36386646cdd9b5ce0e423a357ce73b)

## Windows
EDB installer — in progress
WSL1, WSL2 (Ubuntu, openSUSE) — was working already

  2. Re: Making sslrootcert=system work on Windows psql

    Jacob Champion <jacob.champion@enterprisedb.com> — 2025-04-01T21:46:56Z

    On Tue, Apr 1, 2025 at 2:05 PM George MacKerron <george@mackerron.co.uk> wrote:
>
> I was very pleased to see the sslrootcert=system connection option added in Postgres 16 (I even blogged about it: https://neon.tech/blog/avoid-mitm-attacks-with-psql-postgres-16). But sslrootcert=system has not been widely supported by psql installations, perhaps because people compiling Postgres haven’t always been aware of the requirement to point OpenSSL in the direction of the system’s root CA certificates.
>
> I’ve recently been trying to get it more widely supported, with some success (details at end of this message).

(Thank you!)

> However, psql via the EnterpriseDB Windows installer still doesn’t support sslrootcert=system,

Hm. I've been in contact with Kritika recently for the EDB macOS
fixes; hopefully we can get something figured out for Windows too.

> and I think a tiny patch is needed. The diff is attached, and can be seen in context here: https://github.com/postgres/postgres/compare/master...jawj:postgres:jawj-sslrootcert-system-windows
>
> Essentially, on Windows with OpenSSL 3.2+, it replaces SSL_CTX_set_default_verify_paths(SSL_context) with SSL_CTX_load_verify_store(SSL_context, "org.openssl.winstore:”).
>
> I’m not a Windows or OpenSSL expert, but so far the patched code seems to work in theory and in practice (sources below, and I’ve compiled and tested it working on Windows 11 x64).

While this will get things working -- if you plan to use the Windows
store! -- I worry that it's an incompatible change, and anyone who is
actually happy with the way things currently work (i.e. not using the
EDB installers) will be broken. The meaning of `sslrootcert=system` is
"do whatever OpenSSL wants to do by default." That includes
modification by the OpenSSL environment variables, which (I think)
this patch disables.

The winstore is new to me. Is there no way to get OpenSSL to switch
its default store without code changes?

--Jacob

  3. Re: Making sslrootcert=system work on Windows psql

    Daniel Gustafsson <daniel@yesql.se> — 2025-04-02T07:32:55Z

    > On 1 Apr 2025, at 23:46, Jacob Champion <jacob.champion@enterprisedb.com> wrote:
> On Tue, Apr 1, 2025 at 2:05 PM George MacKerron <george@mackerron.co.uk> wrote:

>> I’ve recently been trying to get it more widely supported, with some success (details at end of this message).
> 
> (Thank you!)

+many

>> I’m not a Windows or OpenSSL expert, but so far the patched code seems to work in theory and in practice (sources below, and I’ve compiled and tested it working on Windows 11 x64).
> 
> While this will get things working -- if you plan to use the Windows
> store! -- I worry that it's an incompatible change, and anyone who is
> actually happy with the way things currently work (i.e. not using the
> EDB installers) will be broken. The meaning of `sslrootcert=system` is
> "do whatever OpenSSL wants to do by default." That includes
> modification by the OpenSSL environment variables, which (I think)
> this patch disables.

Correct, this patch changes from using the defaults (directory, file and store)
and thus the env variable overrides, to hardcoding the new winstore which came
in 3.2 as the only option.  While I agree that we probably should allow
winstore (and other such stores for other platforms when/if they happen) I
don't think making it the only option is the right way.

> The winstore is new to me. Is there no way to get OpenSSL to switch
> its default store without code changes?

AFAIK one cannot change the default store in OpenSSL short of recompiling
OpenSSL.

--
Daniel Gustafsson

  4. Re: Making sslrootcert=system work on Windows psql

    George MacKerron <george@mackerron.co.uk> — 2025-04-02T13:39:06Z

    Daniel, Jacob: thanks. My feeling is that it would be a bit odd to prioritise the preservation of a secondary behaviour (users can customise what cert store is used via environment variables) over fixing the feature’s basic reason for existing (certificates will be validated against the system CA cert store), even in the name of backward-compatibility.

But happily, I don’t think we need to choose. Can’t we just use the Windows system store if neither of the relevant environment variables is set?

I’ve updated my patch to do that. It’s attached, and also still here: https://github.com/postgres/postgres/compare/master...jawj:postgres:jawj-sslrootcert-system-windows

  5. Re: Making sslrootcert=system work on Windows psql

    George MacKerron <george@mackerron.co.uk> — 2025-04-02T14:15:24Z

    > On 2 Apr 2025, at 14:39, George MacKerron <george@mackerron.co.uk> wrote:
> But happily, I don’t think we need to choose. Can’t we just use the Windows system store if neither of the relevant environment variables is set?


Thinking about this a little more, I guess the remaining concern is about people on Windows compiling their own psql from source, using an OpenSSL build that has a meaningful OPENSSLDIR baked in.

I guess that might suggest we should make the "org.openssl.winstore:" code path something users can opt out of (or even, for maximum backwards-compatibility, opt in to) at compile-time.

My preference would be for "org.openssl.winstore:" to be the compile-time default, though, because the option is called sslrootcert=system and it’s documented as using “the system’s trusted CA roots” (not sslrootcert=openssldir or documented as using OpenSSL’s default CA roots).

  6. Re: Making sslrootcert=system work on Windows psql

    Jacob Champion <jacob.champion@enterprisedb.com> — 2025-04-03T01:21:13Z

    On Wed, Apr 2, 2025 at 7:15 AM George MacKerron <george@mackerron.co.uk> wrote:
> > But happily, I don’t think we need to choose. Can’t we just use the Windows system store if neither of the relevant environment variables is set?
>
> Thinking about this a little more, I guess the remaining concern is about people on Windows compiling their own psql from source, using an OpenSSL build that has a meaningful OPENSSLDIR baked in.

Right. In a past life I shipped client stacks on Windows that looked
kind of like that; I would have been less than happy if a client
suddenly stopped using the certificate bundle I'd set up.

> My preference would be for "org.openssl.winstore:" to be the compile-time default, though, because the option is called sslrootcert=system and it’s documented as using “the system’s trusted CA roots” (not sslrootcert=openssldir or documented as using OpenSSL’s default CA roots).

If we'd decided to do that from the beginning, maybe... but it looks
like the winstore URI wasn't released yet when we designed that, so
"the system" couldn't have meant anything except for OpenSSL. Maybe
the documentation needs to be more specific now that OpenSSL is
supporting more stuff.

Even if we want to change that definition sometime in the future, we'd
still have to wait at least until OpenSSL 3.1 was no longer supported;
I don't think it would be very helpful for our definition of "system"
to change abruptly when upgrading OpenSSL past the 3.2 boundary. All
this to say, I'd like to support the winstore, but I'm not convinced
it should take over the existing meaning of "system". Even just adding
it as a fallback has some risk to any packagers who have gotten it
working.

On Wed, Apr 2, 2025 at 12:33 AM Daniel Gustafsson <daniel@yesql.se> wrote:
> AFAIK one cannot change the default store in OpenSSL short of recompiling
> OpenSSL.

I had hoped that a `system_default` entry in openssl.cnf would be able
to override it, but no luck -- VerifyCAStore is explicitly forbidden
in the default section :(

Thanks,
--Jacob

  7. Re: Making sslrootcert=system work on Windows psql

    Daniel Gustafsson <daniel@yesql.se> — 2025-04-03T10:41:35Z

    > On 3 Apr 2025, at 03:21, Jacob Champion <jacob.champion@enterprisedb.com> wrote:
> 
> On Wed, Apr 2, 2025 at 7:15 AM George MacKerron <george@mackerron.co.uk> wrote:
>>> But happily, I don’t think we need to choose. Can’t we just use the Windows system store if neither of the relevant environment variables is set?

The env vars are only overrides for the default, they are not required to be
set, so their presence (or lack thereof) cannot be used to base any decision on
really.

>> Thinking about this a little more, I guess the remaining concern is about people on Windows compiling their own psql from source, using an OpenSSL build that has a meaningful OPENSSLDIR baked in.
> 
> Right. In a past life I shipped client stacks on Windows that looked
> kind of like that; I would have been less than happy if a client
> suddenly stopped using the certificate bundle I'd set up.

Agreed.  I don't think it's productive to assume that OPENSSLDIR is bogus as a
general rule, OpenSSL sure doesn't.  Also, remember that the API we use sets
OpenSSL to use the default dir, file or *store*, not just file/dir:

    SSL_CTX_set_default_verify_paths() specifies that the default locations
    from which CA certificates are loaded should be used.  There is one default
    directory, one default file and one default store.  The default CA
    certificates directory is called certs in the default OpenSSL directory,
    and this is also the default store.

org.openssl.winstore isn't by OpenSSL defined as the default even on Windows,
but a future version might change that.

>> My preference would be for "org.openssl.winstore:" to be the compile-time default, though, because the option is called sslrootcert=system and it’s documented as using “the system’s trusted CA roots” (not sslrootcert=openssldir or documented as using OpenSSL’s default CA roots).
> 
> If we'd decided to do that from the beginning, maybe... but it looks
> like the winstore URI wasn't released yet when we designed that, so
> "the system" couldn't have meant anything except for OpenSSL. Maybe
> the documentation needs to be more specific now that OpenSSL is
> supporting more stuff.

I don't think we need to be more specific regarding what OpenSSL support, but
in hindsight I wonder if we should be more specific around that "system"
actually means.  The attached (untested) small diff tries to make that more
clear. (Line reflow omitted for review ease.)

> Even if we want to change that definition sometime in the future, we'd
> still have to wait at least until OpenSSL 3.1 was no longer supported;
> I don't think it would be very helpful for our definition of "system"
> to change abruptly when upgrading OpenSSL past the 3.2 boundary. All
> this to say, I'd like to support the winstore, but I'm not convinced
> it should take over the existing meaning of "system". Even just adding
> it as a fallback has some risk to any packagers who have gotten it
> working.

Right now sslrootcert can have two different values, a filename or "system".  I
don't think altering what "system" means is a good idea, but I also don't think
limiting ourselves to those two values is helpful.  We either need to make a
new param.  to over time replace sslrootcert with, which can handle multiple
different values; or we need to retrofit a DSL/syntax to sslrootcert for
differentiating.  Both have in common that the coding task is magnitudes easier
than figuring out the user experience.

Something to consider for v19 work.

--
Daniel Gustafsson

  8. Re: Making sslrootcert=system work on Windows psql

    Sandeep Thakkar <sandeep.thakkar@enterprisedb.com> — 2025-04-03T11:02:25Z

    On Wed, Apr 2, 2025 at 2:35 AM George MacKerron <george@mackerron.co.uk>
wrote:

> I was very pleased to see the sslrootcert=system connection option added
> in Postgres 16 (I even blogged about it:
> https://neon.tech/blog/avoid-mitm-attacks-with-psql-postgres-16). But
> sslrootcert=system has not been widely supported by psql installations,
> perhaps because people compiling Postgres haven’t always been aware of the
> requirement to point OpenSSL in the direction of the system’s root CA
> certificates.
>
> I’ve recently been trying to get it more widely supported, with some
> success (details at end of this message).
>
> However, psql via the EnterpriseDB Windows installer still doesn’t support
> sslrootcert=system, and I think a tiny patch is needed. The diff is
> attached, and can be seen in context here:
> https://github.com/postgres/postgres/compare/master...jawj:postgres:jawj-sslrootcert-system-windows
>
> Essentially, on Windows with OpenSSL 3.2+, it replaces
> SSL_CTX_set_default_verify_paths(SSL_context) with
> SSL_CTX_load_verify_store(SSL_context, "org.openssl.winstore:”).
>
> Please note the EDB Windows installers for PostgreSQL versions (upto v17)
use OpenSSL v3.0, which is an LTS version. PostgreSQL 18 installer may use
OpenSSL v3.5 depending on the release timeframe.


> I’m not a Windows or OpenSSL expert, but so far the patched code seems to
> work in theory and in practice (sources below, and I’ve compiled and tested
> it working on Windows 11 x64).
>
>
> # Sources
>
> https://stackoverflow.com/a/79461864/338196
> https://docs.openssl.org/master/man7/OSSL_STORE-winstore/
> https://docs.openssl.org/master/man3/SSL_CTX_load_verify_locations/
>
>
> # Status of sslrootcert=system in various packages providing psql
>
> ## Mac
> Postgres.app — now fixed (
> https://github.com/PostgresApp/PostgresApp/issues/801)
> MacPorts — now fixed (https://trac.macports.org/ticket/72080)
> EDB installer — now fixed (
> https://github.com/EnterpriseDB/edb-installers/issues/264)
> homebrew — was working already
>
> ## Linux
> Debian/Ubuntu — now Recommends ca-certificates (
> https://salsa.debian.org/postgresql/postgresql/-/commit/96077ad61c36386646cdd9b5ce0e423a357ce73b
> )
>
> ## Windows
> EDB installer — in progress
> WSL1, WSL2 (Ubuntu, openSUSE) — was working already
>
>

-- 
Sandeep Thakkar

  9. Re: Making sslrootcert=system work on Windows psql

    George MacKerron <george@mackerron.co.uk> — 2025-04-03T12:41:00Z

    > On 3 Apr 2025, at 11:41, Daniel Gustafsson <daniel@yesql.se> wrote:
> 
> org.openssl.winstore isn't by OpenSSL defined as the default even on Windows,
> but a future version might change that.

Right — there’s definitely an argument that OpenSSL should in future make it possible to have this be the default via a compile-time option, at least.


> I don't think we need to be more specific regarding what OpenSSL support, but
> in hindsight I wonder if we should be more specific around that "system"
> actually means.  The attached (untested) small diff tries to make that more
> clear. (Line reflow omitted for review ease.)

I guess my issue here was twofold: 

(1) sslrootcert=system on Windows doesn’t do what it says on the tin. In other words, it doesn’t do (a) what it sounds like it does or (b) what it says it does in the docs.

(2) sslrootcert=system on Windows doesn’t do a thing that would be extremely useful in some common situations. Namely: connecting securely to servers that present a certificate signed by a public CA.

Your diff certainly fixes (1b), so it’s definitely an improvement. But of course it does nothing for (2). :(


> Right now sslrootcert can have two different values, a filename or "system".  I
> don't think altering what "system" means is a good idea, but I also don't think
> limiting ourselves to those two values is helpful.  We either need to make a
> new param.  to over time replace sslrootcert with, which can handle multiple
> different values; or we need to retrofit a DSL/syntax to sslrootcert for
> differentiating.  Both have in common that the coding task is magnitudes easier
> than figuring out the user experience.
> 
> Something to consider for v19 work.

To give a bit of context here, my feeling is that the widespread use of sslmode=require is a pretty serious security problem in the Postgres community. I strongly suspect many users don’t realise that it offers no protection _at all_ against MITM attacks. I know it took me a while to figure that point out, because sslmode=require just _sounds_ reassuringly secure.

That’s why I was so pleased to read about sslrootcert=system in Postgres 16: I thought it was going to improve this situation. But sslrootcert=system (or similar) isn’t going to be widely used until Postgres providers put it in their connection strings, and Postgres providers aren’t going to put it in their connection strings until it has a damn good chance of just working.

On Linux and Mac, I would say the ‘damn good chance of just working’ bar has now been reached. But on Windows, I suspect a _lot_ of devs are using psql as installed by the EDB Installer (it’s the only option listed at https://www.postgresql.org/download/windows/, after all). So until that works, sslrootcert=system (or similar) is going to remain a no-go.

What I am saying is: it would be _really_ nice not to have to wait another whole release cycle to get a level of security on many people’s Postgres connections that’s simply on par with the security of visiting some random web page.

So: what can be done?

(1) I could ask the EDB installer guys if they’re willing to apply my patch to the Postgres source as part of their build process, so as to use the Windows store in this one case. Personally, I think that would be a clear improvement; but I don’t know if they’ll like the idea. Based on Sandeep’s comment, it seems this is also dependent on OpenSSL 3.5 (LTS) becoming available prior to the Postgres 18 release.

(2) Your idea of a new parameter, or a new value of sslrootcert, is what I was also starting to mull this morning. Is there any chance at all this could be done for Postgres 18 or, failing that, 18.1?

I quite like sslrootcert=os: it’s snappy, and it implies that the Operating System root certs are going to be used (which is what I would have liked sslrootcert=system to mean). Some possible alternatives might be sslrootcert=public-cas or sslrootcert=os-default.

The key thing is that it should work out-of-the-box basically everywhere, so my preferred behaviour for it would be:

* On Windows, use the Windows built-in cert store (per my original patch).

* On Mac and Linux, just do the exact same thing sslrootcert=system currently does.

Is there any realistic prospect of this? I appreciate that it’s not the result of a lengthy, thorough and principled UX evaluation. On the other hand, it’s a few lines of code that could enable a pretty big improvement in security for many users’ Postgres connections much sooner.

(3) Any other ideas?

--
George MacKerron

  10. Re: Making sslrootcert=system work on Windows psql

    Christoph Berg <myon@debian.org> — 2025-04-03T13:28:12Z

    Re: George MacKerron
> (3) Any other ideas?

I'm not a fan of "security by adding more connection parameters".

What are the chances of making "use the system/os default CA store"
the default? "sslmode=require" would then already actually "require" a
certificate if I'm reading the docs right. This would match user
expectation for POLA.

This default could then be pointed at the correct locations (plural)
on all operating systems. (sslrootcert=system:wincert:otherlocation?)

The "default default" would still be sslmode=prefer so it wouldn't
break today's normal case. Users of sslmode=require will understand
that supplying a CA certificate is no longer optional.

Perhaps add a sslmode=require-weak could be added as a workaround.

Christoph

  11. Re: Making sslrootcert=system work on Windows psql

    George MacKerron <george@mackerron.co.uk> — 2025-04-03T13:46:42Z

    > On 3 Apr 2025, at 14:28, Christoph Berg <myon@debian.org> wrote:
> 
> What are the chances of making "use the system/os default CA store"
> the default? "sslmode=require" would then already actually "require" a
> certificate if I'm reading the docs right. This would match user
> expectation for POLA.

Right: the issue at present is that sslmode=require does require a certificate, but IIRC basically any old certificate will do. It doesn’t need to be signed by any particular CA. It doesn’t even need to have the server’s name on it.


> This default could then be pointed at the correct locations (plural)
> on all operating systems. (sslrootcert=system:wincert:otherlocation?)
> 
> The "default default" would still be sslmode=prefer so it wouldn't
> break today's normal case. Users of sslmode=require will understand
> that supplying a CA certificate is no longer optional.
> 
> Perhaps add a sslmode=require-weak could be added as a workaround.

I would love it if sslmode=require started verifying against OS cert stores and so became secure against MITM attacks. I’d certainly support that. But I would say that’s a much bigger backwards-incompatible change than the one I was asking for. :)

--
George MacKerron

  12. Re: Making sslrootcert=system work on Windows psql

    Daniel Gustafsson <daniel@yesql.se> — 2025-04-03T14:26:44Z

    > On 3 Apr 2025, at 14:41, George MacKerron <george@mackerron.co.uk> wrote:

> (2) sslrootcert=system on Windows doesn’t do a thing that would be extremely useful in some common situations. Namely: connecting securely to servers that present a certificate signed by a public CA.

Just to be clear, does (2) happens when the OpenSSL installation has a bogus
OPENSSLDIR value, or does it happen regardless?

> Your diff certainly fixes (1b), so it’s definitely an improvement.

Thanks, unless Jacob objects I propose to apply that backpatched down to when
sslrootcert=system went in.

> (2) Your idea of a new parameter, or a new value of sslrootcert, is what I was also starting to mull this morning. Is there any chance at all this could be done for Postgres 18

Not really, a net new patch mere days before the feature freeze is not really
how we do development.  Especially for security related work on a platform most
developers aren't intimately familiar with.

> or, failing that, 18.1?

Minor revisions only ever get bugfixes, never features or changed behaviour of
features.

> I quite like sslrootcert=os: it’s snappy, and it implies that the Operating System root certs are going to be used (which is what I would have liked sslrootcert=system to mean). Some possible alternatives might be sslrootcert=public-cas or sslrootcert=os-default.

The thing is, we don't know that sslrootcert=os will mean the operating system
root certs.  We are limited to what the OpenSSL API provides, and what can ask
OpenSSL to do is use its defaults.

> The key thing is that it should work out-of-the-box basically everywhere, so my preferred behaviour for it would be:
> 
> * On Windows, use the Windows built-in cert store (per my original patch).
> 
> * On Mac and Linux, just do the exact same thing sslrootcert=system currently does.
> 
> Is there any realistic prospect of this?

IMV there isn't.  I can't see it being an improvement to switch the meaning of
a value based on the underlying OpenSSL version, especially since the current
meaning might be useful for some installations who would then lose that
ability.  I am convinced we need to do be able to use the defaults (as we do
now) *and* use winstore and whatever new stores come, not that one replaces the
other.

> I appreciate that it’s not the result of a lengthy, thorough and principled UX evaluation. On the other hand, it’s a few lines of code that could enable a pretty big improvement in security for many users’ Postgres connections much sooner.

To be clear, wanting to make postgres more secure is a Good Thing, and your
efforts are much appreciated!  Don't take no's in this thread as an objection
to your idea and mission.  Most likely we will support winstore in some way in
v19, we just need to make sure we develop features in a way which is
sustainable wrt our available resources and our development process.

--
Daniel Gustafsson

  13. Re: Making sslrootcert=system work on Windows psql

    Daniel Gustafsson <daniel@yesql.se> — 2025-04-04T08:39:44Z

    > On 3 Apr 2025, at 16:26, Daniel Gustafsson <daniel@yesql.se> wrote:
>> On 3 Apr 2025, at 14:41, George MacKerron <george@mackerron.co.uk> wrote:

>> Your diff certainly fixes (1b), so it’s definitely an improvement.
> 
> Thanks, unless Jacob objects I propose to apply that backpatched down to when
> sslrootcert=system went in.

Hearing no objections I've committed this with a backpatch down to v16.

--
Daniel Gustafsson

  14. Re: Making sslrootcert=system work on Windows psql

    George MacKerron <george@mackerron.co.uk> — 2025-04-23T15:46:59Z

    > On 3 Apr 2025, at 15:26, Daniel Gustafsson <daniel@yesql.se> wrote:
> 
>> I quite like sslrootcert=os: it’s snappy, and it implies that the Operating System root certs are going to be used (which is what I would have liked sslrootcert=system to mean). Some possible alternatives might be sslrootcert=public-cas or sslrootcert=os-default.
> 
> The thing is, we don't know that sslrootcert=os will mean the operating system
> root certs.  We are limited to what the OpenSSL API provides, and what can ask
> OpenSSL to do is use its defaults.
> 
>> The key thing is that it should work out-of-the-box basically everywhere, so my preferred behaviour for it would be:
>> 
>> * On Windows, use the Windows built-in cert store (per my original patch).
>> 
>> * On Mac and Linux, just do the exact same thing sslrootcert=system currently does.
>> 
>> Is there any realistic prospect of this?
> 
> IMV there isn't.  I can't see it being an improvement to switch the meaning of
> a value based on the underlying OpenSSL version, especially since the current
> meaning might be useful for some installations who would then lose that
> ability.  I am convinced we need to do be able to use the defaults (as we do
> now) *and* use winstore and whatever new stores come, not that one replaces the
> other.
> 
>> I appreciate that it’s not the result of a lengthy, thorough and principled UX evaluation. On the other hand, it’s a few lines of code that could enable a pretty big improvement in security for many users’ Postgres connections much sooner.
> 
> To be clear, wanting to make postgres more secure is a Good Thing, and your
> efforts are much appreciated!  Don't take no's in this thread as an objection
> to your idea and mission.  Most likely we will support winstore in some way in
> v19, we just need to make sure we develop features in a way which is
> sustainable wrt our available resources and our development process.


Thanks for your appreciation! It might be good to start thinking about how things might look in v19, then?

Perhaps I can start things off with one smaller idea and one bigger one.


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.


BIGGER IDEA

A much bigger, backwards-incompatible shake-up of libpq security parameters might incorporate the above changes, and then proceed something like this:

* 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”.

* Rename sslmode=require to sslmode=insecure, because it’s vulnerable to MITM attacks, and ideally people would get a sense of that without reading the relevant page of the docs. Make asking for sslmode=require an error (with a helpful explanation pointing out the rename to sslmode=insecure).

* Retain sslmode=verify-ca and sslmode=verify-full.

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

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).


Please do all suggest better ideas!

All the best,
George

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

    Christoph Berg <myon@debian.org> — 2025-04-23T16:24:46Z

    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

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

    George MacKerron <george@mackerron.co.uk> — 2025-04-24T10:31:09Z

    > 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?

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

    Christoph Berg <myon@debian.org> — 2025-04-24T10:53:30Z

    Re: George MacKerron
> > 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?

The env var is already there (PGSSLMODE).

Now you can say `psql -h db.example.com -p 5433 dbfoo`, but for
specifying the sslmode, you have to rewrite at least the last argument
to use connection string syntax, `psql "dbname=dbfoo sslmode=verify-full`.
This needs be be less cumbersome. (And the names of the options make
me want to stay away from them, require/verify-ca/verify-full/verify-confusing.
Your sslmode=secure idea is really good.)

It should be as simple as
psql --ssl (= sslmode=secure)
psql --insecure (the old sslmode=require)
psql --no-ssl (= sslmode=disable)

psql -s and -S are unfortunately already taken :-/

For connection strings, perhaps the best action is to tell people that
always including "sslmode=something" is best practise. For libpq-style
key=value connection strings, that wouldn't even be ugly. For
postgresql://-style strings, we would ideally have something like http://
vs https://, but I am not sure how to squeeze that into the syntax.
(Appending ?sslmode= works, but meh.)

Christoph

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

    Peter Eisentraut <peter@eisentraut.org> — 2025-04-24T12:00:25Z

    On 24.04.25 12:53, Christoph Berg wrote:
> Now you can say `psql -h db.example.com -p 5433 dbfoo`, but for
> specifying the sslmode, you have to rewrite at least the last argument
> to use connection string syntax, `psql "dbname=dbfoo sslmode=verify-full`.
> This needs be be less cumbersome. (And the names of the options make
> me want to stay away from them, require/verify-ca/verify-full/verify-confusing.
> Your sslmode=secure idea is really good.)

I'm generally in favor of making sslmode=verify-full the effective 
default somehow.

Another detail to think about is how this affects psql -h localhost.  In 
principle, this should require full SSL, but you're probably not going 
to have certificates that allow "localhost".  And connections to 
localhost are the default on Windows.  We could also switch the Windows 
default to Unix-domain sockets.  But there are probably still other 
reasons why connections to TCP/IP localhost are made.  Some things to 
think about.

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

    Jacob Champion <jacob.champion@enterprisedb.com> — 2025-04-24T16:45:50Z

    On Thu, Apr 24, 2025 at 5:00 AM Peter Eisentraut <peter@eisentraut.org> wrote:
> I'm generally in favor of making sslmode=verify-full the effective
> default somehow.

+many

On Thu, Apr 24, 2025 at 3:53 AM Christoph Berg <myon@debian.org> wrote:
> For
> postgresql://-style strings, we would ideally have something like http://
> vs https://, but I am not sure how to squeeze that into the syntax.

Not to derail things too much, but I'd also like a postgress://
scheme, and I've put a little bit of idle thought into it. I think
we'd want it to imply sslnegotiation=direct and sslrootcert=system
(modulo the Windows discussion already in progress), and potentially
make a bunch of stricter decisions about TLS settings to better match
modern practice. The intent would be to have a "browser-strength"
scheme for people who care more about security than about raw
compatibility with older systems, because they're connecting to
someone else's servers on the open Web.

The hardest part, in my opinion, is that we'd have to start following
the RFC concept of "authority". A URL of
"postgress://example.com/db?host=evil.com&hostaddr=..." is outright
dangerous, as is "postgress://example.com/db?sslmode=disable". So if
there's interest in that scheme, I think it should remain a separate
feature from "verify-full by default", because there's a lot more to
figure out.

--Jacob

  20. Re: Making sslrootcert=system work on Windows psql

    Jacob Champion <jacob.champion@enterprisedb.com> — 2025-04-24T17:45:29Z

    On Wed, Apr 23, 2025 at 8:47 AM George MacKerron <george@mackerron.co.uk> wrote:
> 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.

Stealing the word "system" from the existing sslrootcert domain had at
least two hazards: a) existing users might have a file named "system"
that would now be ignored, and b) users might accidentally use
sslrootcert=system on older versions of libpq, picking up an
unexpected file named "system" and doing the Wrong Thing. Problem (a)
can be worked around by saying "./system" instead, so honestly I
wasn't too concerned about that, and I considered (b) to be more of a
theoretical problem that was outweighed by the benefit of getting
OpenSSL to just Do The Thing people wanted it to do.

A couple years on, I think (b) is less theoretical than I had
originally hoped. As evidence I point to Stack Overflow questions like
[1], where both the asker and the answerer are a bit confused about
how connection string versioning works. If we steal more words, I
think that problem is going to get worse. So I'm leaning toward's
Daniel's earlier position that sslrootcert has kind of run its course,
and if you want to select OpenSSL stores, we need a more fully
featured syntax and probably a completely new option to be able to
pass that through safely.

> (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.

Falling back to standard OpenSSL introduces the same hazard we're
running into today, though -- what if someone creates a macstore [2]
for OpenSSL, so that its behavior matches Safari's or whatever, and
then everyone wonders why sslrootcert=os doesn't use that? If the
abstraction must leak the details anyway, I think we should expose
them directly instead.

(As a small soapbox, I think "application-level" fallback for a trust
chain is frequently going to lead to regret. You should ideally tell
us what you want, and either get it or fail.)

Thanks,
--Jacob

[1] https://stackoverflow.com/questions/77989772/psql-root-certificate-file-system-does-not-exist-why-sslrootcert-system-do
[2] https://github.com/openssl/openssl/issues/23460

  21. Re: Making sslrootcert=system work on Windows psql

    Jelte Fennema-Nio <postgres@jeltef.nl> — 2025-04-24T21:52:53Z

    On Wed, 23 Apr 2025 at 17:47, George MacKerron <george@mackerron.co.uk> wrote:
> 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

Okay I have some time to respond to this thread now. My main thought is this:

I think we should try as hard as possible for sslrootcert=system to do
"the right thing" on all platforms. "the right thing" being: allowing
users to connect to a Postgres server if the cert of that server. It's
a huge shame that MANY users connect to their production Postgres
databases over unauthenticated TLS. I believe the only way to fix that
is by having a *standard* & *secure* connection string that people can
copy paste from their database service provider's portal. Adding new
options that users need to choose between makes it impossible for
database providers to provide such a *standard* connection string
because the exact string will depend on the platform. Which means
they'll continue to only provide sslmode=require in their provided
connstrings. And even if we can somehow avoid that, it will reset the
clock on when most clients will actually support that *standard*
connection string. Thus increasing the time (by at least two years)
before database providers dare to put these options in their default
connection strings, in fear of their customers not being able to
connect and opening support requests or closing their accounts.

But yeah, the discussed situation is problematic for this: Windows
machines have multiple cert stores that could be reasonably considered
the system store.

So I'd like to propose a different way around that problem: Instead
adding more connection options. How about we add a *compile time*
option that allows the person that compiles libpq to choose which cert
store it should use if sslrootcert=system is provided. Something like
--system-cert-store=openssl and --system-cert-store=winstore flags for
./configure. This way users don't have to choose between the various
system stores to get behaviour that is sensible. Which one should be
the default, requires discussion, and maybe we'd want to let that
depend on the OpenSSL version or change it in the future. We could
even make it required for people compiling libpq on Windows (with an
OpenSSl version that supports winstore) to choose between these two
options, by making it a required flag.

Note: This doesn't mean we couldn't allow people to override the
compile time systemstore at runtime with e.g. sslsystemstore=winstore,
but the default would be the one that was chosen at compile time.

> BIGGER IDEA

I would really like to get to a point where libpq by default fails to
connect if you're not connecting to Postgres in a secure way: i.e. one
where you're not both encrypting traffic and authenticating the host
(unless you're connecting over unix sockets and maybe local loopback).
I think there's no point in actually working on/proposing that until
we have a secure connection string that works on all systems (i.e.
what sslrootcert=system is supposed to do)

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

    Jelte Fennema-Nio <postgres@jeltef.nl> — 2025-04-24T22:16:03Z

    On Thu, 24 Apr 2025 at 18:46, Jacob Champion
<jacob.champion@enterprisedb.com> wrote:
>
> On Thu, Apr 24, 2025 at 5:00 AM Peter Eisentraut <peter@eisentraut.org> wrote:
> > I'm generally in favor of making sslmode=verify-full the effective
> > default somehow.
>
> +many

Yes, +many

> Not to derail things too much, but I'd also like a postgress://
> scheme

Sounds great.

Let me derail some more, while we're at it I think it would be good to
add tls-prefixed aliases for all our ssl options. Like tlscert/tlskey.
Since such a new postgress:// scheme would be totally new, maybe we
can even disallow the ssl prefixed ones there.

> The hardest part, in my opinion, is that we'd have to start following
> the RFC concept of "authority". A URL of
> "postgress://example.com/db?host=evil.com&hostaddr=..." is outright
> dangerous

Why is this dangerous? As long as we'd validate that the provided cert
by the server is for example.com, I don't see any security problem in
having DNS resolution happen for evil.com, nor in having the IP
addresses hardcoded using hostaddr.

> as is "postgress://example.com/db?sslmode=disable"

Yeah that should be addressed, but seems like we mainly need to
disallow specifying sslmode completely there (or error if it's not
verify-full). And maybe there's some other options that we'd want to
disallow.

On Thu, 24 Apr 2025 at 18:46, Jacob Champion
<jacob.champion@enterprisedb.com> wrote:
>
> On Thu, Apr 24, 2025 at 5:00 AM Peter Eisentraut <peter@eisentraut.org> wrote:
> > I'm generally in favor of making sslmode=verify-full the effective
> > default somehow.
>
> +many
>
> On Thu, Apr 24, 2025 at 3:53 AM Christoph Berg <myon@debian.org> wrote:
> > For
> > postgresql://-style strings, we would ideally have something like http://
> > vs https://, but I am not sure how to squeeze that into the syntax.
>
> Not to derail things too much, but I'd also like a postgress://
> scheme, and I've put a little bit of idle thought into it. I think
> we'd want it to imply sslnegotiation=direct and sslrootcert=system
> (modulo the Windows discussion already in progress), and potentially
> make a bunch of stricter decisions about TLS settings to better match
> modern practice. The intent would be to have a "browser-strength"
> scheme for people who care more about security than about raw
> compatibility with older systems, because they're connecting to
> someone else's servers on the open Web.
>
> The hardest part, in my opinion, is that we'd have to start following
> the RFC concept of "authority". A URL of
> "postgress://example.com/db?host=evil.com&hostaddr=..." is outright
> dangerous, as is "postgress://example.com/db?sslmode=disable". So if
> there's interest in that scheme, I think it should remain a separate
> feature from "verify-full by default", because there's a lot more to
> figure out.
>
> --Jacob
>
>

  23. Re: Making sslrootcert=system work on Windows psql

    Jelte Fennema-Nio <postgres@jeltef.nl> — 2025-04-24T22:19:15Z

    On Thu, 24 Apr 2025 at 23:52, Jelte Fennema-Nio <postgres@jeltef.nl> wrote:
> How about we add a *compile time*
> option that allows the person that compiles libpq to choose which cert
> store it should use if sslrootcert=system is provided. Something like
> --system-cert-store=openssl and --system-cert-store=winstore flags for
> ./configure.

@George So basically my suggestion is to make the behaviour that your
patch introduces configurable at compile time. FWIW my vote would
probably be to default to --system-cert-store=winstore if it's
available. And then --system-cert-store=openssl would be a way out for
people that took the effort to configure openssl correctly on Windows.

  24. Re: Making sslrootcert=system work on Windows psql

    George MacKerron <george@mackerron.co.uk> — 2025-04-25T10:20:55Z

    > On Thu, 24 Apr 2025 at 23:52, Jelte Fennema-Nio <postgres@jeltef.nl> wrote:
> 
>> How about we add a *compile time*
>> option that allows the person that compiles libpq to choose which cert
>> store it should use if sslrootcert=system is provided. Something like
>> --system-cert-store=openssl and --system-cert-store=winstore flags for
>> ./configure.
> 
> @George So basically my suggestion is to make the behaviour that your
> patch introduces configurable at compile time. FWIW my vote would
> probably be to default to --system-cert-store=winstore if it's
> available. And then --system-cert-store=openssl would be a way out for
> people that took the effort to configure openssl correctly on Windows.

👍 I think that’s a pretty nice idea.

On the other hand, what are the specific objections to doing it dynamically, the way my patch does? I think that has backwards-compatibility quite well covered.

Is the main concern that users may be surprised that the behaviour of psql changes if they later set one of the OpenSSL environment variables or put cert files in OPENSSLDIR? I feel like that would be quite rare and also a pretty safe failure mode.

  25. Re: Making sslrootcert=system work on Windows psql

    George MacKerron <george@mackerron.co.uk> — 2025-04-25T10:22:06Z

    
> On 24 Apr 2025, at 18:45, Jacob Champion <jacob.champion@enterprisedb.com> wrote:
> 
> On Wed, Apr 23, 2025 at 8:47 AM George MacKerron <george@mackerron.co.uk> wrote:
>> 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.
> 
> Stealing the word "system" from the existing sslrootcert domain had at
> least two hazards: a) existing users might have a file named "system"
> that would now be ignored, and b) users might accidentally use
> sslrootcert=system on older versions of libpq, picking up an
> unexpected file named "system" and doing the Wrong Thing. Problem (a)
> can be worked around by saying "./system" instead, so honestly I
> wasn't too concerned about that, and I considered (b) to be more of a
> theoretical problem that was outweighed by the benefit of getting
> OpenSSL to just Do The Thing people wanted it to do.
> 
> A couple years on, I think (b) is less theoretical than I had
> originally hoped. As evidence I point to Stack Overflow questions like
> [1], where both the asker and the answerer are a bit confused about
> how connection string versioning works. If we steal more words, I
> think that problem is going to get worse. So I'm leaning toward's
> Daniel's earlier position that sslrootcert has kind of run its course,
> and if you want to select OpenSSL stores, we need a more fully
> featured syntax and probably a completely new option to be able to
> pass that through safely.

If we stick to ‘system’ as the only special value, then (b) gets more theoretical with every passing day, as more people upgrade their Postgres installs.

But it’s true that adding a new special value makes it day 0 again. So I guess I’m persuaded that adding new special values is probably not a great idea. That makes me all the keener to get sslrootcert=system working for average Windows users!


> You should ideally tell us what you want, and either get it or fail.

The key thing I want (I am a stuck record on this point!) is a reliably cross-platform way to use the operating system’s trust store when evaluating the credentials of the Postgres server I’m connecting to.

This is what sslrootcert=system promised to be, and sounded like it would be, but turned out not to be on Windows, because for ordinary Windows users (i.e. those who don’t maintain an OpenSSL cert store on their machines) it always fails.

I know the documentation has now been changed to reflect that ‘system’ actually means OpenSSL. But I still think it would be better for it to really mean the operating system. On Windows, that’s the winstore. 

Which is why I still think my patch (or perhaps Jelte’s suggestion of a compile-time option, as an alternative) is an improvement on the status quo … ?

  26. Re: Making sslrootcert=system work on Windows psql

    Jelte Fennema-Nio <postgres@jeltef.nl> — 2025-04-25T12:22:35Z

    On Fri, 25 Apr 2025 at 12:22, George MacKerron <george@mackerron.co.uk> wrote:
> I know the documentation has now been changed to reflect that ‘system’ actually means OpenSSL.

I didn't realize that. I'm definitely not in favor of that doc change.
It's describing behaviour that I believe is incorrect, as if it's
actually intended.

  27. Re: Making sslrootcert=system work on Windows psql

    George MacKerron <george@mackerron.co.uk> — 2025-04-25T12:34:18Z

    > On Fri, 25 Apr 2025 at 12:22, George MacKerron <george@mackerron.co.uk> wrote:
>> I know the documentation has now been changed to reflect that ‘system’ actually means OpenSSL.
> 
> I didn't realize that. I'm definitely not in favor of that doc change.
> It's describing behaviour that I believe is incorrect, as if it's
> actually intended.

The change was described in Daniel’s message on 3 April. It’s actually a bit subtler than I suggested. The diff is:

         The special value <literal>system</literal> may be specified instead, in
-        which case the system's trusted CA roots will be loaded.
+        which case the trusted CA roots from the SSL implementation will be loaded.

I agree with you here: the change makes the docs more correct, but the correctly-documented behaviour itself still seems incorrect to me.

I think a clue is that the word ‘system’ no longer appears in the updated version of text explaining what sslrootcert=system does!

  28. Re: Making sslrootcert=system work on Windows psql

    Daniel Gustafsson <daniel@yesql.se> — 2025-04-25T12:53:04Z

    > On 3 Apr 2025, at 16:26, Daniel Gustafsson <daniel@yesql.se> wrote:
> 
>> On 3 Apr 2025, at 14:41, George MacKerron <george@mackerron.co.uk> wrote:
> 
>> (2) sslrootcert=system on Windows doesn’t do a thing that would be extremely useful in some common situations. Namely: connecting securely to servers that present a certificate signed by a public CA.
> 
> Just to be clear, does (2) happens when the OpenSSL installation has a bogus
> OPENSSLDIR value, or does it happen regardless?

I would still like to get clarity on this, do you have any insights here?

--
Daniel Gustafsson

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

    Daniel Gustafsson <daniel@yesql.se> — 2025-04-25T13:04:25Z

    > On 25 Apr 2025, at 00:16, Jelte Fennema-Nio <postgres@jeltef.nl> wrote:

> Let me derail some more, while we're at it I think it would be good to
> add tls-prefixed aliases for all our ssl options. Like tlscert/tlskey.
> Since such a new postgress:// scheme would be totally new, maybe we
> can even disallow the ssl prefixed ones there.

I think that would be a mistake, 'SSL' has long lost its original meaning and
is now interpreted to be an umbrella term for "secure connections with
certificates and things".  Sticking to ssl_* will most likely be the least
confusing for our users.

--
Daniel Gustafsson

  30. Re: Making sslrootcert=system work on Windows psql

    George MacKerron <george@mackerron.co.uk> — 2025-04-25T13:40:41Z

    > On 25 Apr 2025, at 13:53, Daniel Gustafsson <daniel@yesql.se> wrote:
>> 
>>> (2) sslrootcert=system on Windows doesn’t do a thing that would be extremely useful in some common situations. Namely: connecting securely to servers that present a certificate signed by a public CA.
>> 
>> Just to be clear, does (2) happens when the OpenSSL installation has a bogus
>> OPENSSLDIR value, or does it happen regardless?
> 
> I would still like to get clarity on this, do you have any insights here?

I can tell you what happens on my Windows 11 system with Postgres 17 via the EDB installer, which has a non-bogus OPENSSLDIR.

OpenSSL appears to have been built with OPENSSLDIR="C:\Program Files\Common Files\SSL".

This is a valid path, the directory exists, and it contains a few *.cnf files. I’m pretty sure the EDB installer created and populated this directory.

However, the directory contains no certificates, and its location (or existence) are not advertised anywhere. You would have to know it must exist somewhere, and then hunt about for it.

(1) In this original state, attempting a connection produces this error:

c:\Program Files\PostgreSQL\17>.\bin\psql.exe "postgresql://.../neondb?sslrootcert=system"
psql: error: connection to server at "…" (...), port 5432 failed: SSL error: unregistered scheme

(2) If I create either an empty folder "C:\Program Files\Common Files\SSL\certs" or an empty file "C:\Program Files\Common Files\SSL\cert.pem", the error changes:

c:\Program Files\PostgreSQL\17>.\bin\psql.exe "postgresql://.../neondb?sslrootcert=system"
psql: error: connection to server at "…" (...), port 5432 failed: SSL error: certificate verify failed

(3) Or if I download the curl/Mozilla certificates list to "C:\Program Files\Common Files\SSL\cert.pem", the connection then works as expected:

c:\Program Files\PostgreSQL\17>.\bin\psql.exe "postgresql://.../neondb?sslrootcert=system"
psql (17.3, server 16.8)
WARNING: Console code page (850) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off, ALPN: postgresql)
Type "help" for help.

neondb=>

(But sslrootcert=system is obviously not very useful in this case: I might as well just point sslrootcert directly at that cert.pem file in whatever location I like).

  31. Re: Making sslrootcert=system work on Windows psql

    Daniel Gustafsson <daniel@yesql.se> — 2025-04-25T14:04:29Z

    > On 25 Apr 2025, at 15:40, George MacKerron <george@mackerron.co.uk> wrote:
> 
>> On 25 Apr 2025, at 13:53, Daniel Gustafsson <daniel@yesql.se> wrote:
>>> 
>>>> (2) sslrootcert=system on Windows doesn’t do a thing that would be extremely useful in some common situations. Namely: connecting securely to servers that present a certificate signed by a public CA.
>>> 
>>> Just to be clear, does (2) happens when the OpenSSL installation has a bogus
>>> OPENSSLDIR value, or does it happen regardless?
>> 
>> I would still like to get clarity on this, do you have any insights here?
> 
> I can tell you what happens on my Windows 11 system with Postgres 17 via the EDB installer, which has a non-bogus OPENSSLDIR.

Thanks for confirming.

> OpenSSL appears to have been built with OPENSSLDIR="C:\Program Files\Common Files\SSL".
> 
> This is a valid path, the directory exists, and it contains a few *.cnf files. I’m pretty sure the EDB installer created..

It did, CVE-2019-10211 has more details.

> ..and populated this directory.

The contents most likely come from building OpenSSL, by the sounds of it that's
the stock OPENSSLDIR setup.

--
Daniel Gustafsson

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

    Jacob Champion <jacob.champion@enterprisedb.com> — 2025-04-25T18:55:40Z

    On Thu, Apr 24, 2025 at 3:16 PM Jelte Fennema-Nio <postgres@jeltef.nl> wrote:
> Why is this dangerous? As long as we'd validate that the provided cert
> by the server is for example.com

I can't help but read this as "as long as everyone mitigates the
danger, what's the danger?" We won't be the only implementers of any
URL schemes we introduce.

> I don't see any security problem in
> having DNS resolution happen for evil.com, nor in having the IP
> addresses hardcoded using hostaddr.

I think if we introduce a new scheme with the idea that it's "HTTPS
mode", it needs to behave very similarly to HTTPS, so people reason
about it correctly in worst-case corner cases.

To attack an https:// connection, you need to both steal the server
key _and_ get the client to talk to you instead of the real server.
And for HTTPS, that second part generally requires hijacking DNS or
mounting a successful MITM, not modifying the query.

The idea of a query string overriding the //authority is... weird. It
breaks the conventions of generic parsers (and I will include "humans"
in that group). We're "allowed" to do it, I guess -- it's our scheme,
we do it with our existing schemes today, and the IETF isn't going to
send spec police to our doors -- but I don't think we should.

Thanks,
--Jacob

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

    Jacob Champion <jacob.champion@enterprisedb.com> — 2025-04-25T18:56:38Z

    On Thu, Apr 24, 2025 at 5:00 AM Peter Eisentraut <peter@eisentraut.org> wrote:
> Another detail to think about is how this affects psql -h localhost.  In
> principle, this should require full SSL, but you're probably not going
> to have certificates that allow "localhost".  And connections to
> localhost are the default on Windows.  We could also switch the Windows
> default to Unix-domain sockets.  But there are probably still other
> reasons why connections to TCP/IP localhost are made.  Some things to
> think about.

Yeah, we pretty quickly get to the boring-but-hard part. Is there a
group of users we feel comfortable breaking? What ways is it
acceptable to break them? How hard should it be for them to unbreak
themselves once it happens?

It'd be kind of nice if there were a better way than environment
variables to configure defaults for the client. I've been looking at
openssl.cnf for the Windows certificate problem, and I wish we had
that knob available for conversations like this... If we had a global
client config, then we could declare that we're going to change the
defaults in that config far in advance, and anyone who absolutely
hates it can proceed to undo it globally and move on. The service file
is IMO not enough for this.

--Jacob