Thread: Making sslrootcert=system work on Windows psql

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
bypsql installations, perhaps because people compiling Postgres haven’t always been aware of the requirement to point
OpenSSLin 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
isneeded. 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

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
bypsql installations, perhaps because people compiling Postgres haven’t always been aware of the requirement to point
OpenSSLin 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

> 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

Daniel, Jacob: thanks. My feeling is that it would be a bit odd to prioritise the preservation of a secondary behaviour
(userscan 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
environmentvariables 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



> On 2 Apr 2025, at 08:32, Daniel Gustafsson <daniel@yesql.se> wrote:
>
>> 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
>

On 2 Apr 2025, at 14:39, George MacKerron <george@mackerron.co.uk> 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
environmentvariables is set? 
>
> Thinking about this a little more, I guess the remaining concern is about people on Windows compiling their own psql
fromsource, 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
calledsslrootcert=system and it’s documented as using “the system’s trusted CA roots” (not sslrootcert=openssldir or
documentedas 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

> 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
environmentvariables 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
fromsource, 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
calledsslrootcert=system and it’s documented as using “the system’s trusted CA roots” (not sslrootcert=openssldir or
documentedas 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

> 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-timeoption, 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
likeit 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:
connectingsecurely 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
problemin the Postgres community. I strongly suspect many users don’t realise that it offers no protection _at all_
againstMITM attacks. I know it took me a while to figure that point out, because sslmode=require just _sounds_
reassuringlysecure. 

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
connectionstrings, and Postgres providers aren’t going to put it in their connection strings until it has a damn good
chanceof 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
suspecta _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
toremain 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
securityon 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
buildprocess, so as to use the Windows store in this one case. Personally, I think that would be a clear improvement;
butI 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
thereany 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
(whichis what I would have liked sslrootcert=system to mean). Some possible alternatives might be
sslrootcert=public-casor 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

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

> 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
willdo. 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’dcertainly support that. But I would say that’s a much bigger backwards-incompatible change than the one I was asking
for.:) 

--
George MacKerron

> 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
thereany 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
(whichis what I would have liked sslrootcert=system to mean). Some possible alternatives might be
sslrootcert=public-casor 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
fewlines 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

> 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