Re: libpq: system-wide root.crt

Hello PostgreSQL developers,

I received a request to support system-wide root certificates in
libpq. Right now it only looks in ~/.postgresql/root.crt, but since
such certificates are usually set up system wide and be maintained by
the sysadmins, it would be very convenient if there was a fallback
lookup in /etc/postgresql/ or similar.

Would you accept a patch which adds a configure option for the path of
a system wide root certificate?

Thank you,

Martin

--
Martin Pitt | http://www.piware.de
Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org)

On Tue, Aug 17, 2010 at 6:49 AM, Martin Pitt <mpitt(at)debian(dot)org> wrote:
> Hello PostgreSQL developers,
>
> I received a request to support system-wide root certificates in
> libpq. Right now it only looks in ~/.postgresql/root.crt, but since
> such certificates are usually set up system wide and be maintained by
> the sysadmins, it would be very convenient if there was a fallback
> lookup in /etc/postgresql/ or similar.
>
> Would you accept a patch which adds a configure option for the path of
> a system wide root certificate?

That seems like a good feature to me. Obviously, it would have to come
with support for the CRL as well, since it goes together with the root
cert ;)

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Hello PostgreSQL developers,

Martin Pitt [2010-08-17 6:49 +0200]:
> I received a request to support system-wide root certificates in
> libpq. Right now it only looks in ~/.postgresql/root.crt, but since
> such certificates are usually set up system wide and be maintained by
> the sysadmins, it would be very convenient if there was a fallback
> lookup in /etc/postgresql/ or similar.

Patch attached against current git head. I tested it with both the
default case (new option not specified), as well as with
--with-ssl-root-cert-dir=/etc/postgresql-common, and confirm that in
the latter case root.crt gets picked up from
/etc/postgresql-common/ if it's not in ~/.postgresql/.

Thank you for considering,

Martin
--
Martin Pitt | http://www.piware.de
Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org)

On Thu, Aug 19, 2010 at 23:11, Martin Pitt <mpitt(at)debian(dot)org> wrote:
> Hello PostgreSQL developers,
>
> Martin Pitt [2010-08-17  6:49 +0200]:
>> I received a request to support system-wide root certificates in
>> libpq. Right now it only looks in ~/.postgresql/root.crt, but since
>> such certificates are usually set up system wide and be maintained by
>> the sysadmins, it would be very convenient if there was a fallback
>> lookup in /etc/postgresql/ or similar.
>
> Patch attached against current git head. I tested it with both the
> default case (new option not specified), as well as with
> --with-ssl-root-cert-dir=/etc/postgresql-common, and confirm that in
> the latter case root.crt gets picked up from
> /etc/postgresql-common/ if it's not in ~/.postgresql/.

Hi!

Please add this to https://commitfest.postgresql.org/action/commitfest_view/open

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Magnus Hagander [2010-08-20 15:07 +0200]:
> Please add this to https://commitfest.postgresql.org/action/commitfest_view/open

Done, thanks!

https://commitfest.postgresql.org/action/patch_view?id=364

Martin
--
Martin Pitt | http://www.piware.de
Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org)

On Thu, Aug 19, 2010 at 23:11, Martin Pitt <mpitt(at)debian(dot)org> wrote:
> Hello PostgreSQL developers,
>
> Martin Pitt [2010-08-17  6:49 +0200]:
>> I received a request to support system-wide root certificates in
>> libpq. Right now it only looks in ~/.postgresql/root.crt, but since
>> such certificates are usually set up system wide and be maintained by
>> the sysadmins, it would be very convenient if there was a fallback
>> lookup in /etc/postgresql/ or similar.
>
> Patch attached against current git head. I tested it with both the
> default case (new option not specified), as well as with
> --with-ssl-root-cert-dir=/etc/postgresql-common, and confirm that in
> the latter case root.crt gets picked up from
> /etc/postgresql-common/ if it's not in ~/.postgresql/.

I wonder if we want to have a default value for this rather than
disabling it when it's not specified by configure. But is there any
kind of reasonable default that's not going to be
platform/distribution specific?

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Magnus Hagander <magnus(at)hagander(dot)net> writes:
> On Thu, Aug 19, 2010 at 23:11, Martin Pitt <mpitt(at)debian(dot)org> wrote:
>>> I received a request to support system-wide root certificates in
>>> libpq.

> I wonder if we want to have a default value for this rather than
> disabling it when it's not specified by configure. But is there any
> kind of reasonable default that's not going to be
> platform/distribution specific?

Given the potential security issues, I would argue very strenuously
that this should NOT be enabled by default. It should happen only
if the option is requested at configure time, and configure should
be told the exact path where to look for certs.

regards, tom lane

On tor, 2010-09-23 at 08:36 +0200, Magnus Hagander wrote:
> I wonder if we want to have a default value for this rather than
> disabling it when it's not specified by configure. But is there any
> kind of reasonable default that's not going to be
> platform/distribution specific?

I would like for us to find a way to do it without configure options.
Compiling in stuff is always weird.

I'm thinking, libpq could really use a global (and, for that matter, a
per-user) configuration file, where you could set defaults for some of
the things that you currently have to use environment variables for,
e.g., sslmode. And then you can configure the system-wide root.crt
location there.

Alternatively, if you think that that is overkill, then using an
environment variable to configure this feature would be consistent with
the existing mechanisms.

On Fri, Sep 24, 2010 at 00:41, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> On tor, 2010-09-23 at 08:36 +0200, Magnus Hagander wrote:
>> I wonder if we want to have a default value for this rather than
>> disabling it when it's not specified by configure. But is there any
>> kind of reasonable default that's not going to be
>> platform/distribution specific?
>
> I would like for us to find a way to do it without configure options.
> Compiling in stuff is always weird.

I think the compile-time default really is designed for distributions
- in this case debian, right? I would assume debian would prefer a
combination of a compile-time default and the below, rather than just
the default?

> I'm thinking, libpq could really use a global (and, for that matter, a
> per-user) configuration file, where you could set defaults for some of
> the things that you currently have to use environment variables for,
> e.g., sslmode.  And then you can configure the system-wide root.crt
> location there.

We already have this - pg_service.conf - no?

> Alternatively, if you think that that is overkill, then using an
> environment variable to configure this feature would be consistent with
> the existing mechanisms.

So in this case, a "sslfallbackroot=/etc/somewhere" parameter, that
you could then stick in etc/pg_service.conf, or in any of the other
places you can specify it? The one thing I think would be really
needed for that to work is to support wildcard database names in
pg_service.conf?

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

On fre, 2010-09-24 at 09:50 +0200, Magnus Hagander wrote:
> > I'm thinking, libpq could really use a global (and, for that matter, a
> > per-user) configuration file, where you could set defaults for some of
> > the things that you currently have to use environment variables for,
> > e.g., sslmode. And then you can configure the system-wide root.crt
> > location there.
>
> We already have this - pg_service.conf - no?

Indeed.

> > Alternatively, if you think that that is overkill, then using an
> > environment variable to configure this feature would be consistent with
> > the existing mechanisms.
>
> So in this case, a "sslfallbackroot=/etc/somewhere" parameter, that
> you could then stick in etc/pg_service.conf, or in any of the other
> places you can specify it? The one thing I think would be really
> needed for that to work is to support wildcard database names in
> pg_service.conf?

Yes.

On Sat, Sep 25, 2010 at 3:38 PM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> On fre, 2010-09-24 at 09:50 +0200, Magnus Hagander wrote:
>> > I'm thinking, libpq could really use a global (and, for that matter, a
>> > per-user) configuration file, where you could set defaults for some of
>> > the things that you currently have to use environment variables for,
>> > e.g., sslmode.  And then you can configure the system-wide root.crt
>> > location there.
>>
>> We already have this - pg_service.conf - no?
>
> Indeed.
>
>> > Alternatively, if you think that that is overkill, then using an
>> > environment variable to configure this feature would be consistent with
>> > the existing mechanisms.
>>
>> So in this case, a "sslfallbackroot=/etc/somewhere" parameter, that
>> you could then stick in etc/pg_service.conf, or in any of the other
>> places you can specify it? The one thing I think would be really
>> needed for that to work is to support wildcard database names in
>> pg_service.conf?
>
> Yes.

What I gather from the discussion on this thread is that this patch
needs to be revised before it can be considered for commit, so I'm
going to mark it as Returned with Feedback in the CommitFest
application. Hopefully, it will be revised and resubmitted for the
then-current open CommitFest, which can always be found at:

https://commitfest.postgresql.org/action/commitfest_view/open

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company