[PATCH] Add `verify-system` sslmode to use system CA pool for server cert

Thomas Habets <thomas@habets.se> writes:

With Letsencrypt now protecting web servers left and right, and it makes
sense to me to just re-use the cert that the server may already have
installed.

I'm confused by your description of this patch. AFAIK, OpenSSL verifies
against the system-wide CA pool by default. Why do we need to do
anything?

regards, tom lane

On Mon, 6 Sep 2021 20:47:37 +0100, Tom Lane <tgl@sss.pgh.pa.us> said:

I'm confused by your description of this patch. AFAIK, OpenSSL verifies
against the system-wide CA pool by default. Why do we need to do
anything?

Experimentally, no it doesn't. Or if it does, then it doesn't verify
the CN/altnames of the cert.

sslmode=require allows self-signed and name mismatch.

verify-ca errors out if there is no ~/.postgresql/root.crt. verify-full too.

It seems that currently postgresql verifies the name if and only if
verify-full is used, and then only against ~/.postgresql/root.crt CA file.

But could be that I missed a config option?

--
typedef struct me_s {
char name[] = { "Thomas Habets" };
char email[] = { "thomas@habets.se" };
char kernel[] = { "Linux" };
char *pgpKey[] = { "http://www.habets.pp.se/pubkey.txt&quot; };
char pgp[] = { "9907 8698 8A24 F52F 1C2E 87F6 39A4 9EEA 460A 0169" };
char coolcmd[] = { "echo '. ./_&. ./_'>_;. ./_" };
} me_t;

On 9/6/21 6:21 PM, thomas@habets.se wrote:

On Mon, 6 Sep 2021 20:47:37 +0100, Tom Lane <tgl@sss.pgh.pa.us> said:

I'm confused by your description of this patch. AFAIK, OpenSSL verifies
against the system-wide CA pool by default. Why do we need to do
anything?

Experimentally, no it doesn't. Or if it does, then it doesn't verify
the CN/altnames of the cert.

sslmode=require allows self-signed and name mismatch.

verify-ca errors out if there is no ~/.postgresql/root.crt. verify-full too.

It seems that currently postgresql verifies the name if and only if
verify-full is used, and then only against ~/.postgresql/root.crt CA file.

But could be that I missed a config option?

That's my understanding. But can't you specify a CA cert in the system's
CA store if necessary? e.g. on my Fedora system I think it's
/etc/pki/ca-trust/extracted/openssl/ca-bundle.trust.crt

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com

On Tue, 7 Sep 2021 15:16:51 +0100, Andrew Dunstan <andrew@dunslane.net> said:

can't you specify a CA cert in the system's
CA store if necessary? e.g. on my Fedora system I think it's
/etc/pki/ca-trust/extracted/openssl/ca-bundle.trust.crt

I could, but that seems more like a workaround, where I have to change
things around as LetsEncrypt switches to another root (I believe they
have in the past, but I'm not sure), or the server decides to switch
from LetsEncrypt to something else. Then all clients need to update.

Such a decision could actually be made by whoever runs the webserver,
not the database, and the database just reuses the cert and gets a
free ride for cert renewals.

So in other words postgresql currently doesn't use the system database
at all, and the workaround is to find and copy from the system
database. I agree that is a workaround.

If you think this is enough of a corner case that the workaround is
acceptable, or the added complexity of another sslmode setting isn't
worth fixing this edge case, then I assume you have more knowledge
about postgres is used in the field than I do.

But it's not just about today. I would hope that now with LE that
every user of SSL starts using "real" certs. Postgres default settings
imply that most people who even enable SSL will not verify the CA nor
the name, which is a shame.

--
typedef struct me_s {
char name[] = { "Thomas Habets" };
char email[] = { "thomas@habets.se" };
char kernel[] = { "Linux" };
char *pgpKey[] = { "http://www.habets.pp.se/pubkey.txt&quot; };
char pgp[] = { "9907 8698 8A24 F52F 1C2E 87F6 39A4 9EEA 460A 0169" };
char coolcmd[] = { "echo '. ./_&. ./_'>_;. ./_" };
} me_t;

thomas@habets.se writes:

On Tue, 7 Sep 2021 15:16:51 +0100, Andrew Dunstan <andrew@dunslane.net> said:

can't you specify a CA cert in the system's
CA store if necessary? e.g. on my Fedora system I think it's
/etc/pki/ca-trust/extracted/openssl/ca-bundle.trust.crt

I could, but that seems more like a workaround, where I have to change
things around as LetsEncrypt switches to another root (I believe they
have in the past, but I'm not sure), or the server decides to switch
from LetsEncrypt to something else. Then all clients need to update.

I experimented with loading a real (not self-signed, not from a private
CA) cert into the server, and I can confirm these results when trying
to use sslmode=verify-ca or sslmode=verify-full:

* libpq fails the connection if ~/.postgresql/root.crt is missing
or empty.

* If I put an irrelevant cert into ~/.postgresql/root.crt, then
libpq reports "SSL error: certificate verify failed". So the
verification insists that the server's cert chain to whatever
is in root.crt.

This does seem to make it unreasonably painful to use a real SSL cert
for a PG server. If you've gone to the trouble and expense of getting
a real cert, it should not be on you to persuade the clients that
it's valid. I agree with Thomas that copying the system trust store
into users' home directories is a completely horrid idea, from both
the ease-of-use and maintenance standpoints.

This is not how I supposed it worked, so I'm coming around to the idea
that we need to do something. I don't like the details of Thomas'
proposal though; specifically I don't see a need to invent a new sslmode
value. I think it should just be "if ~/.postgresql/root.crt doesn't
exist, use the system's default trust store".

regards, tom lane

On 9/7/21 10:57 AM, thomas@habets.se wrote:

On Tue, 7 Sep 2021 15:16:51 +0100, Andrew Dunstan <andrew@dunslane.net> said:

can't you specify a CA cert in the system's
CA store if necessary? e.g. on my Fedora system I think it's
/etc/pki/ca-trust/extracted/openssl/ca-bundle.trust.crt

I could, but that seems more like a workaround, where I have to change
things around as LetsEncrypt switches to another root (I believe they
have in the past, but I'm not sure), or the server decides to switch
from LetsEncrypt to something else. Then all clients need to update.

Such a decision could actually be made by whoever runs the webserver,
not the database, and the database just reuses the cert and gets a
free ride for cert renewals.

So in other words postgresql currently doesn't use the system database
at all, and the workaround is to find and copy from the system
database. I agree that is a workaround.

If you think this is enough of a corner case that the workaround is
acceptable, or the added complexity of another sslmode setting isn't
worth fixing this edge case, then I assume you have more knowledge
about postgres is used in the field than I do.

But it's not just about today. I would hope that now with LE that
every user of SSL starts using "real" certs. Postgres default settings
imply that most people who even enable SSL will not verify the CA nor
the name, which is a shame.

It would be if it were true, but it's not. In talks I give on
PostgreSQL+SSL I highly recommend people use verify-full. And the CA
doesn't have to be one that's publicly known. We cater for both public
and private CAs.

You don't have to copy anything to achieve what you want. Just set the
sslrootcert parameter of your connection to point to the system file. e.g.

psql "sslmode=verify-full sslrootcert=/etc/pki/ca-trust/extracted/openssl/ca-bundle.trust.crt ..."

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com

Andrew Dunstan <andrew@dunslane.net> writes:

You don't have to copy anything to achieve what you want. Just set the
sslrootcert parameter of your connection to point to the system file. e.g.

psql "sslmode=verify-full sslrootcert=/etc/pki/ca-trust/extracted/openssl/ca-bundle.trust.crt ..."

While that does work for me, it seems pretty OS-specific and
user-unfriendly. Why should ordinary users need to know that
much about their platform's OpenSSL installation?

regards, tom lane

On 9/7/21 11:47 AM, Tom Lane wrote:

This is not how I supposed it worked,

That happens to me more than I usually admit -)

so I'm coming around to the idea
that we need to do something. I don't like the details of Thomas'
proposal though; specifically I don't see a need to invent a new sslmode
value. I think it should just be "if ~/.postgresql/root.crt doesn't
exist, use the system's default trust store".

I agree sslmode is the wrong vehicle.

An alternative might be to allow a magic value for sslrootcert, say
"system" which would make it go and look in the system's store wherever
that is, without the user having to know exactly where. OTOH it would
require that the user knows that the system's store is being used, which
might not be a bad thing.

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com

Andrew Dunstan <andrew@dunslane.net> writes:

On 9/7/21 11:47 AM, Tom Lane wrote:

so I'm coming around to the idea
that we need to do something. I don't like the details of Thomas'
proposal though; specifically I don't see a need to invent a new sslmode
value. I think it should just be "if ~/.postgresql/root.crt doesn't
exist, use the system's default trust store".

An alternative might be to allow a magic value for sslrootcert, say
"system" which would make it go and look in the system's store wherever
that is, without the user having to know exactly where. OTOH it would
require that the user knows that the system's store is being used, which
might not be a bad thing.

Yeah, that would mostly fix the usability concern. I guess what it
comes down to is whether you think that public or private certs are
likely to be the majority use-case in the long run. The shortage of
previous requests for this feature says that right now, just about
everyone is using self-signed or private-CA certs for Postgres
servers. So it would likely be a long time, if ever, before public-CA
certs become the majority use-case.

On the other hand, even if I'm using a private CA, there's a lot
to be said for adding its root cert to system-level trust stores
rather than copying it into individual users' home directories.
So I still feel like there's a pretty good case for allowing use
of the system store to happen by default. (As I said, I'd always
thought that was *already* what would happen.)

regards, tom lane

On Tue, 7 Sept 2021 at 12:59, Tom Lane <tgl@sss.pgh.pa.us> wrote:

I guess what it
comes down to is whether you think that public or private certs are
likely to be the majority use-case in the long run. The shortage of
previous requests for this feature says that right now, just about
everyone is using self-signed or private-CA certs for Postgres
servers. So it would likely be a long time, if ever, before public-CA
certs become the majority use-case.

Well the main thing making public CA certs a pain is precisely tools
that are a pain to configure to use public CA certs so it's a bit of a
chicken and egg problem. Projects like LetsEncrypt are all about
making public CA certs work easily without any additional effort.

However I have a different question. Are the system certificates
intended or general purpose certificates? Do they have their intended
uses annotated on the certificates? Does SSL Verification have any
logic deciding which certificates are appropriate for signing servers?

I ask because the only authority I'm personally aware of is the web
browser consortium that approves signers for web site domains. That's
what web browsers need but I'm not sure those are the same authorities
that are appropriate for internal services like databases.

--
greg

Greg Stark <stark@mit.edu> writes:

However I have a different question. Are the system certificates
intended or general purpose certificates? Do they have their intended
uses annotated on the certificates? Does SSL Verification have any
logic deciding which certificates are appropriate for signing servers?

AFAIK, once you've stuck a certificate into the system store, it
will be trusted by every service on your machine. Most distros
ship system-store contents that are basically just designed for
web browers, because the web is the only widely-applicable use
case. Like you said, chicken and egg problem.

regards, tom lane

Hm. Let's Encrypt's FAQ tells me I'm on the right track with that
question but the distinctinos are far more coarse than I was worried
about:

Does Let’s Encrypt issue certificates for anything other than SSL/TLS
for websites?

Let’s Encrypt certificates are standard Domain Validation
certificates, so you can use them for any server that uses a domain
name, like web servers, mail servers, FTP servers, and many more.

Email encryption and code signing require a different type of
certificate that Let’s Encrypt does not issue.

So it sounds like, at least for SSL connections, we should use the
same certificate authorities used to authenticate web sites. If ever
we implemented signed extensions, for example, it might require
different certificates -- I don't know what that means for the SSL
validation rules and the storage for them.

Hi,

I manage a bunch of Postgres servers at Oslo University and we use real ssl
certs on all our servers.

I was actually really surprised to discover that the libpq default is
sslmode=require and that the root cert defaults to a file under the user’s
home directory. I have been planning to use our management system
(CFEngine) to globally change the client settings to verify-ca and to use
the system trust store.

So that’s a +1 to use the system cert store for client connections.

I also agree that the proposed patch is not the right way to go as it is
essentially the same as verify-full, and I think that the correct fix would
be to change the default.

Thanks
C

On Sat, 18 Sept 2021 at 00:10, Cameron Murdoch <cam@macaroon.net> wrote:

I also agree that the proposed patch is not the right way to go as it is
essentially the same as verify-full, and I think that the correct fix would
be to change the default.

But these are two changes:
1. Actually verify against a CA
2. Actually check the CN/altnames

Anything short of "verify-full" is in my view "not checking". Even with a
private CA this allows for a lot of lateral movement in an org, as if you
have one cert you have them all, for impersonation purposes.

Changing such a default is a big change. Maybe long term it's worth the
short term pain, though. Long term it'd be the config of least surprise, in
my opinion.
But note that one has to think about all the settings, such that the
default is not more checking than "require", which might also be surprising.

A magic setting of the file to be "system" sounds good for my use cases, at
least.

--
typedef struct me_s {
char name[] = { "Thomas Habets" };
char email[] = { "thomas@habets.se <thomas@habets.pp.se>" };
char kernel[] = { "Linux" };
char *pgpKey[] = { "http://www.habets.pp.se/pubkey.txt&quot; };
char pgp[] = { "9907 8698 8A24 F52F 1C2E 87F6 39A4 9EEA 460A 0169" };
char coolcmd[] = { "echo '. ./_&. ./_'>_;. ./_" };
} me_t;

On Sat, 18 Sep 2021 at 12:57, Thomas Habets <thomas@habets.se> wrote:

But these are two changes:
1. Actually verify against a CA
2. Actually check the CN/altnames

Anything short of "verify-full" is in my view "not checking". Even with a
private CA this allows for a lot of lateral movement in an org, as if you
have one cert you have them all, for impersonation purposes.

100% agree. I suspect that many postgres users are not completely aware
that by default their ssl connections do not check the CA or CN/altnames.

Changing such a default is a big change.

Agreed. It is going to break existing installs that rely on the current
behaviour.

There are two defaults to worry about here:

sslmode=prefer
sslrootcert=~/.postgresql/root.crt

Having sslrootcert use the system trust store if ~/.postgresql/root.crt
doesn’t exist would seem like a good change.

Changing sslmode to default to something else would mostly likely break a
ton of existing installations, and there are plenty of use cases were ssl
isn’t used. Trying ssl first and without afterwards probably is still a
sensible default. However…

I haven’t completely through this through, but what if the sslmode=prefer
logic was:

1. Try ssl first, with both CA and CN checking (ie same as verify-full)
2. Print warnings appropriate to what type of ssl connection can be made
3. If all else fails, try without ssl.

In other words start with verify-full and downgrade gracefully to prefer,
but actually tell the user that this has happen.

Essentially sslmode=prefer is a type of opportunistic encryption. I’m
suggesting making it try stronger levels of ssl opportunistically. Require,
verify-ca and verify-full can keep their semantics, or rather, they should
all try verify-full first and then downgrade (with warnings logged) to the
level they actually enforce.

Thanks
C

On 9/17/21 5:35 PM, Greg Stark wrote:

Hm. Let's Encrypt's FAQ tells me I'm on the right track with that
question but the distinctinos are far more coarse than I was worried
about:

Does Let’s Encrypt issue certificates for anything other than SSL/TLS
for websites?

Let’s Encrypt certificates are standard Domain Validation
certificates, so you can use them for any server that uses a domain
name, like web servers, mail servers, FTP servers, and many more.

Email encryption and code signing require a different type of
certificate that Let’s Encrypt does not issue.

Presumably this should be a certificate something like our client certs,
where the subject designates a user id or similar (e.g. an email
address) rather than a domain name.

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com

On Sat, 2021-09-18 at 14:20 +0200, Cameron Murdoch wrote:

Having sslrootcert use the system trust store if
~/.postgresql/root.crt doesn’t exist would seem like a good change.

Fallback behavior can almost always be exploited given the right
circumstances. IMO, if I've told psql to use a root cert, it really
needs to do that and not trust anything else.

Changing sslmode to default to something else would mostly likely
break a ton of existing installations, and there are plenty of use
cases were ssl isn’t used. Trying ssl first and without afterwards
probably is still a sensible default. However…

The discussion on changing the sslmode default behavior seems like it
can be separated from the use of system certificates. Not to shut down
that branch of the conversation, but is there enough tentative support
for an "sslrootcert=system" option to move forward with that, while
also discussing potential changes to the sslmode defaults?

The NSS patchset [1]/messages/by-id/FAB21FC8-0F62-434F-AA78-6BD9336D630A@yesql.se also deals with this problem. FWIW, it currently
treats an empty ssldatabase setting as "use the system's (Mozilla's)
trusted roots".

--Jacob

[1]: /messages/by-id/FAB21FC8-0F62-434F-AA78-6BD9336D630A@yesql.se

On 9/22/21 2:36 PM, Jacob Champion wrote:

On Sat, 2021-09-18 at 14:20 +0200, Cameron Murdoch wrote:

Having sslrootcert use the system trust store if
~/.postgresql/root.crt doesn’t exist would seem like a good change.

Fallback behavior can almost always be exploited given the right
circumstances. IMO, if I've told psql to use a root cert, it really
needs to do that and not trust anything else.

Changing sslmode to default to something else would mostly likely
break a ton of existing installations, and there are plenty of use
cases were ssl isn’t used. Trying ssl first and without afterwards
probably is still a sensible default. However…

The discussion on changing the sslmode default behavior seems like it
can be separated from the use of system certificates. Not to shut down
that branch of the conversation, but is there enough tentative support
for an "sslrootcert=system" option to move forward with that, while
also discussing potential changes to the sslmode defaults?

The NSS patchset [1] also deals with this problem. FWIW, it currently
treats an empty ssldatabase setting as "use the system's (Mozilla's)
trusted roots".

I think we need to be consistent on this. NSS builds and OpenSSL builds
should act the same, mutatis mutandis.

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com

On 22 Sep 2021, at 20:59, Andrew Dunstan <andrew@dunslane.net> wrote:

I think we need to be consistent on this. NSS builds and OpenSSL builds
should act the same, mutatis mutandis.

I 100% agree. Different TLS backends should be able use different truststores
etc but once the server is running they must be identical in terms of how they
interact with a connecting client. I've tried hard to match our OpenSSL
implementation when hacking on the NSS support, but no doubt I've slipped up
somewhere so indepth reviews like what Jacob et.al have done is needed (and
very welcome).

--
Daniel Gustafsson https://vmware.com/