Thread: Usage of the system truststore for SSL certificate validation
Hi, currently, libpq does SSL cerificate validation only against the defined `PGSSLROOTCERT` file. Is there any specific reason, why the system truststore ( at least under unixoid systems) is not considered for the validation? We would like to contribute a patch to allow certificate validation against the system truststore. Are there any opinions against it? A little bit background for this: Internally we sign the certificates for our systems with our own CA. The CA root certificates and revocation lists are distributed via puppet and/or packages on all of our internal systems. Validating the certificate against this CA requires to either override the PGSSLROOTCERT location via the environment or provide a copy of the file for each user that connects with libpq or libpq-like connectors. We would like to simplify this. -- Thomas Berger PostgreSQL DBA Database Operations 1&1 Telecommunication SE | Ernst-Frey-Straße 10 | 76135 Karlsruhe | Germany
If we're going to open this up, can we add an option to say "this key is allowed to log in to this account", SSH style?
I like the idea of using keys rather than .pgpass, but I like the ~/.ssh/authorized_keys model and don't like the "set up an entire certificate infrastructure" approach.
On Thu, 19 Sep 2019 at 10:54, Thomas Berger <thomas.berger@1und1.de> wrote:
Hi,
currently, libpq does SSL cerificate validation only against the defined
`PGSSLROOTCERT` file.
Is there any specific reason, why the system truststore ( at least under
unixoid systems) is not considered for the validation?
We would like to contribute a patch to allow certificate validation against
the system truststore. Are there any opinions against it?
A little bit background for this:
Internally we sign the certificates for our systems with our own CA. The CA
root certificates and revocation lists are distributed via puppet and/or
packages on all of our internal systems.
Validating the certificate against this CA requires to either override the
PGSSLROOTCERT location via the environment or provide a copy of the file for
each user that connects with libpq or libpq-like connectors.
We would like to simplify this.
--
Thomas Berger
PostgreSQL DBA
Database Operations
1&1 Telecommunication SE | Ernst-Frey-Straße 10 | 76135 Karlsruhe | Germany
This certainly looks like a good addition to me that can be implemented on both client and server side. It is always good to have a common location where the list of all the certificates from various CA's can be placed for validation. -- With Regards, Ashutosh Sharma EnterpriseDB:http://www.enterprisedb.com On Thu, Sep 19, 2019 at 8:24 PM Thomas Berger <thomas.berger@1und1.de> wrote: > > Hi, > > currently, libpq does SSL cerificate validation only against the defined > `PGSSLROOTCERT` file. > > Is there any specific reason, why the system truststore ( at least under > unixoid systems) is not considered for the validation? > > We would like to contribute a patch to allow certificate validation against > the system truststore. Are there any opinions against it? > > > A little bit background for this: > > Internally we sign the certificates for our systems with our own CA. The CA > root certificates and revocation lists are distributed via puppet and/or > packages on all of our internal systems. > > Validating the certificate against this CA requires to either override the > PGSSLROOTCERT location via the environment or provide a copy of the file for > each user that connects with libpq or libpq-like connectors. > > We would like to simplify this. > > > -- > Thomas Berger > > PostgreSQL DBA > Database Operations > > 1&1 Telecommunication SE | Ernst-Frey-Straße 10 | 76135 Karlsruhe | Germany > >
On Thu, 19 Sep 2019 at 12:26, Isaac Morland <isaac.morland@gmail.com> wrote:
If we're going to open this up, can we add an option to say "this key is allowed to log in to this account", SSH style?I like the idea of using keys rather than .pgpass, but I like the ~/.ssh/authorized_keys model and don't like the "set up an entire certificate infrastructure" approach.
Sorry for the top-post.
On Thu, Sep 19, 2019 at 12:26:27PM -0400, Isaac Morland wrote: > If we're going to open this up, can we add an option to say "this key is > allowed to log in to this account", SSH style? > > I like the idea of using keys rather than .pgpass, but I like the ~/.ssh/ > authorized_keys model and don't like the "set up an entire certificate > infrastructure" approach. This is actually a good question --- why does ssh do it that way and Postgres does it another, more like a web server/client. Maybe it is because ssh allows the user to create one key pair, and use it for several independent servers, while Postgres assumes the client will only connect to multiple related servers controlled by the same CA. With the Postgres approach, you can change the client certificate with no changes on the server, while with the ssh model, changing the client certificate requires sending the public key to the ssh server to be added to ~/.ssh/authorized_keys. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription +
On Sat, Sep 28, 2019 at 9:59 PM Bruce Momjian <bruce@momjian.us> wrote:
On Thu, Sep 19, 2019 at 12:26:27PM -0400, Isaac Morland wrote:
> If we're going to open this up, can we add an option to say "this key is
> allowed to log in to this account", SSH style?
>
> I like the idea of using keys rather than .pgpass, but I like the ~/.ssh/
> authorized_keys model and don't like the "set up an entire certificate
> infrastructure" approach.
This is actually a good question --- why does ssh do it that way and
Postgres does it another, more like a web server/client. Maybe it is
because ssh allows the user to create one key pair, and use it for
several independent servers, while Postgres assumes the client will only
connect to multiple related servers controlled by the same CA. With the
Postgres approach, you can change the client certificate with no changes
on the server, while with the ssh model, changing the client certificate
requires sending the public key to the ssh server to be added to
~/.ssh/authorized_keys.
The big difference between the two methods in general is the CA yes. In the SSL based method, you have a central authority that says "these keys are OK" by means of certificates. In the ssh key model, there's an individual keypair.
It would make no sense to extend the cert model of authentication to support ssh style keys, IMO. However, it might make perfect sense to add a separate pure key based login method. And re-using the way ssh handles keys there would make sense. But the question is, would you really want to re-use the ssh *keys*? You couldn't do it server-side anyway (PostgreSQL won't have access to authorized_keys files for other users than itself, as unlike ssh it doesn't run as root), and since you need a separate keyspace you probably wouldn't want to use .ssh/identity either.