Re: sslmode=require fallback

Hi!

I've looked at the way libpq handles TLS certificates and plaintext fallback, and I am somewhat surprised.

The default ssmode is prefer. According to the documentation, this will make libpq use an SSL connection if possible, but will use a plain text connection as a fallback. The certificate will not be verified.

If, however, there is a root certificate in ~/.postgresql/root.crt, libpq will check if the server cert matches this certificate, and refuse any certfificates that don't match. This means that libpq will fall back to a plain text connection!

This is very unexpected behavior! Shouldn't libpq prefer an *unauthenticated but encrypted* connection over an *unauthenticated and unencrypted* connection?

This behavior also causes sslmode=require to behave like sslmode=verify-ca when ~/.postgresql/root.crt exists.

From my limited understanding, it seems the way to fix this would be in fe-secure-openssl.c, to change initialize_SSL() to only read the root certificate file when sslmode=verify_*

However, if this is the expected behavior, the documentation at https://www.postgresql.org/docs/current/static/libpq-ssl.html <https://www.postgresql.org/docs/current/static/libpq-ssl.html> should be updated to make this more clear. It should be made clear that the existence of the file ~/.postgresql/root.crt changes the behavior of sslmode=require and sslmode=prefer.

Best regards,
Jakob

On Thu, Jun 16, 2016 at 10:39 AM, Jakob Egger <jakob(at)eggerapps(dot)at> wrote:

> Hi!
>
> I've looked at the way libpq handles TLS certificates and plaintext
> fallback, and I am somewhat surprised.
>
> The default ssmode is prefer. According to the documentation, this will
> make libpq use an SSL connection if possible, but will use a plain text
> connection as a fallback. The certificate will not be verified.
>
> If, however, there is a root certificate in ~/.postgresql/root.crt, libpq
> will check if the server cert matches this certificate, and refuse any
> certfificates that don't match. This means that libpq will fall back to a
> plain text connection!
>
> This is very unexpected behavior! Shouldn't libpq prefer an
> *unauthenticated but encrypted* connection over an *unauthenticated and
> unencrypted* connection?
>

You would think so.

The default mode of "prefer" is ridiculous in a lot of ways. If you are
using SSL in any shape or form you should simply not use "prefer". That's
really the only answer at this point, unfortunately.

This behavior also causes sslmode=require to behave like sslmode=verify-ca
> when ~/.postgresql/root.crt exists.
>

Correct. That's mainly for really old backwards compatibility. We could
have a "sslmode=verify-none" to reverse that, I guess. I'm not sure if this
scenario is common enough to care about though?

From my limited understanding, it seems the way to fix this would be in
> fe-secure-openssl.c, to change initialize_SSL() to only read the root
> certificate file when sslmode=verify_*
>
> However, if this is the expected behavior, the documentation at
> https://www.postgresql.org/docs/current/static/libpq-ssl.html should be
> updated to make this more clear. It should be made clear that the existence
> of the file ~/.postgresql/root.crt changes the behavior of sslmode=require
> and sslmode=prefer.
>

Agreed. It's basically backwards compatibility with something that was
badly documented in the first place :) That's not a particularly strong
argument for the way it is. Clarifying the documentation would definitely
be a good improvement.

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

On Thu, Jun 16, 2016 at 10:42:56AM +0200, Magnus Hagander wrote:
> However, if this is the expected behavior, the documentation at https://
> www.postgresql.org/docs/current/static/libpq-ssl.html should be updated to
> make this more clear. It should be made clear that the existence of the
> file ~/.postgresql/root.crt changes the behavior of sslmode=require and
> sslmode=prefer.
>
>
>
> Agreed. It's basically backwards compatibility with something that was badly
> documented in the first place :) That's not a particularly strong argument for
> the way it is. Clarifying the documentation would definitely be a good
> improvement.

Does this have to remain backward-compatible forever?

--
Bruce Momjian <bruce(at)momjian(dot)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 Thu, Jun 23, 2016 at 1:50 AM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> On Thu, Jun 16, 2016 at 10:42:56AM +0200, Magnus Hagander wrote:
> > However, if this is the expected behavior, the documentation
> at https://
> > www.postgresql.org/docs/current/static/libpq-ssl.html should be
> updated to
> > make this more clear. It should be made clear that the existence of
> the
> > file ~/.postgresql/root.crt changes the behavior of sslmode=require
> and
> > sslmode=prefer.
> >
> >
> >
> > Agreed. It's basically backwards compatibility with something that was
> badly
> > documented in the first place :) That's not a particularly strong
> argument for
> > the way it is. Clarifying the documentation would definitely be a good
> > improvement.
>
> Does this have to remain backward-compatible forever?
>

In general no. But I think the problem here is that if somebody misses the
removal of something backwards compatible, it turns off their security.
Which is not good...

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

On Thu, Jun 16, 2016 at 3:42 AM, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
> You would think so.
>
> The default mode of "prefer" is ridiculous in a lot of ways. If you are
> using SSL in any shape or form you should simply not use "prefer". That's
> really the only answer at this point, unfortunately.

Suppose we changed the default to "require". How crazy would that be?

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

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Thu, Jun 16, 2016 at 3:42 AM, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
>> The default mode of "prefer" is ridiculous in a lot of ways. If you are
>> using SSL in any shape or form you should simply not use "prefer". That's
>> really the only answer at this point, unfortunately.

> Suppose we changed the default to "require". How crazy would that be?

You mean, aside from the fact that it breaks every single installation
that hasn't configured with SSL?

regards, tom lane

On Wed, Jul 13, 2016 at 3:16 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Thu, Jun 16, 2016 at 3:42 AM, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
>>> The default mode of "prefer" is ridiculous in a lot of ways. If you are
>>> using SSL in any shape or form you should simply not use "prefer". That's
>>> really the only answer at this point, unfortunately.
>
>> Suppose we changed the default to "require". How crazy would that be?
>
> You mean, aside from the fact that it breaks every single installation
> that hasn't configured with SSL?

No, including that.

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

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Wed, Jul 13, 2016 at 3:16 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>>> Suppose we changed the default to "require". How crazy would that be?

>> You mean, aside from the fact that it breaks every single installation
>> that hasn't configured with SSL?

> No, including that.

I cannot imagine such a plan surviving the first wave of villagers with
torches and pitchforks.

regards, tom lane

On 13 Jul 2016 9:28 pm, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> > On Wed, Jul 13, 2016 at 3:16 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> >>> Suppose we changed the default to "require". How crazy would that be?
>
> >> You mean, aside from the fact that it breaks every single installation
> >> that hasn't configured with SSL?
>
> > No, including that.

Well what's required to "configure SSL" anyways? If you don't have
verify-ca set or a root canal cert present then the server just needs a
certificate -- any certificate. Can the server just cons one up on demand
(or server startup or initdb)?

Yes, that would not help with active MITM attacks but at least removes any
chance that people are unknowingly using an unencrypted connection
vulnerable to passive sniffers.

Greg Stark <stark(at)mit(dot)edu> writes:
> Well what's required to "configure SSL" anyways? If you don't have
> verify-ca set or a root canal cert present then the server just needs a
> certificate -- any certificate. Can the server just cons one up on demand
> (or server startup or initdb)?

Hmm, good old "snake oil certificate" approach. Yeah, we could probably
have initdb create a cert all the time. I had memories of this taking
an undue amount of time, but it seems pretty fast on a modern server.
Also, we could offer a switch to turn it off if necessary, with the
understanding that non-Unix-socket connections can be expected to fail
if user doesn't install a cert.

regards, tom lane

On Thu, Jul 14, 2016 at 11:27 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Greg Stark <stark(at)mit(dot)edu> writes:
> > Well what's required to "configure SSL" anyways? If you don't have
> > verify-ca set or a root canal cert present then the server just needs a
> > certificate -- any certificate. Can the server just cons one up on demand
> > (or server startup or initdb)?
>
> Hmm, good old "snake oil certificate" approach. Yeah, we could probably
> have initdb create a cert all the time. I had memories of this taking
> an undue amount of time, but it seems pretty fast on a modern server.
>

It can still take a very significant amount of time in some virtual
environments, due to lack of entropy. And virtual environments aren't
exactly uncommon these days...

> Also, we could offer a switch to turn it off if necessary, with the
> understanding that non-Unix-socket connections can be expected to fail
> if user doesn't install a cert.
>

If we do it we should also ensure it's not enabled on localhost by default.
Though that's a nice "consultant switch" -- more than once just turning
that off (since it's on by default on debian/ubuntu) has fixed a customers
entire performance issue and I could go back home again...

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

Magnus Hagander <magnus(at)hagander(dot)net> writes:
> On Thu, Jul 14, 2016 at 11:27 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Also, we could offer a switch to turn it off if necessary, with the
>> understanding that non-Unix-socket connections can be expected to fail
>> if user doesn't install a cert.

> If we do it we should also ensure it's not enabled on localhost by default.

If we could make sure that both Unix-socket and localhost connections do
not do SSL by default, that would make it possible to skip cert generation
in "make check" and buildfarm scenarios, which would be awfully nice for
slower buildfarm critters. I'm not sure though whether libpq should be
given that sort of hardwired knowledge about "localhost".

regards, tom lane

On 7/13/16 4:11 PM, Robert Haas wrote:
> On Thu, Jun 16, 2016 at 3:42 AM, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
>> You would think so.
>>
>> The default mode of "prefer" is ridiculous in a lot of ways. If you are
>> using SSL in any shape or form you should simply not use "prefer". That's
>> really the only answer at this point, unfortunately.
>
> Suppose we changed the default to "require". How crazy would that be?

If we think that that is appropriate, should we not also change the
default pg_hba.conf to hostssl lines?

I'm not convinced either of these would go over well.

The original complaint was not actually that "prefer" is a bad default,
but that in the presence of a root certificate on the client, a
certificate validation failure falls back to plain text. That seems
like a design flaw of the "prefer" mode, no matter whether it is the
default or not.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Fri, Jul 15, 2016 at 5:10 AM, Peter Eisentraut <
peter(dot)eisentraut(at)2ndquadrant(dot)com> wrote:

> On 7/13/16 4:11 PM, Robert Haas wrote:
> > On Thu, Jun 16, 2016 at 3:42 AM, Magnus Hagander <magnus(at)hagander(dot)net>
> wrote:
> >> You would think so.
> >>
> >> The default mode of "prefer" is ridiculous in a lot of ways. If you are
> >> using SSL in any shape or form you should simply not use "prefer".
> That's
> >> really the only answer at this point, unfortunately.
> >
> > Suppose we changed the default to "require". How crazy would that be?
>
> If we think that that is appropriate, should we not also change the
> default pg_hba.conf to hostssl lines?
>
> I'm not convinced either of these would go over well.
>

It would actually, IMO, make more sense to change the default pg_hba lines
and not change the client settings... But I'm not sure either of those
would go over well.

>
> The original complaint was not actually that "prefer" is a bad default,
> but that in the presence of a root certificate on the client, a
> certificate validation failure falls back to plain text. That seems
> like a design flaw of the "prefer" mode, no matter whether it is the
> default or not.
>

The entire "prefer" mode is a design flaw, that we unfortunately picked as
default mode.

If it fails *for any reason*, it falls back to plaintext. Thus, you have to
assume it will make a plaintext connection. Thus, it gives you zero
guarantees, so it serves no actual purpose from a security perspective.

it will equally fall back on incompatible SSL configs. Or on a network
hiccup. The presence of the certificate is just one of many different
scenarios where it will fall back.

If you care about encryption, you should pick something else
(require/verify). If you don't care about encryption, you should pick
something else (allow, probably) so as not to pay unnecessary overhead.

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

On 14.07.2016 23:34, Magnus Hagander wrote:
>
>
> On Thu, Jul 14, 2016 at 11:27 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us
> <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>> wrote:
>
> Greg Stark <stark(at)mit(dot)edu <mailto:stark(at)mit(dot)edu>> writes:
> > Well what's required to "configure SSL" anyways? If you don't have
> > verify-ca set or a root canal cert present then the server just needs a
> > certificate -- any certificate. Can the server just cons one up on demand
> > (or server startup or initdb)?
>
> Hmm, good old "snake oil certificate" approach. Yeah, we could probably
> have initdb create a cert all the time. I had memories of this taking
> an undue amount of time, but it seems pretty fast on a modern server.
>
>
> It can still take a very significant amount of time in some virtual
> environments, due to lack of entropy. And virtual environments aren't
> exactly uncommon these days...

What expire time would you chose for the certificate? One year? Two years?
Which tool is going to re-generate your new cert, once this one expires?
You don't want to run initdb again ...

Regards,

--
Andreas 'ads' Scherbaum
German PostgreSQL User Group
European PostgreSQL User Group - Board of Directors
Volunteer Regional Contact, Germany - PostgreSQL Project

Magnus Hagander <magnus(at)hagander(dot)net> writes:
> The entire "prefer" mode is a design flaw, that we unfortunately picked as
> default mode.
> ...
> If you care about encryption, you should pick something else
> (require/verify). If you don't care about encryption, you should pick
> something else (allow, probably) so as not to pay unnecessary overhead.

Yeah. The problem with going over to any one of those as the built-in
default is that we can't know whether the user cares about encryption
or not; but all the other choices depend on making that value judgment.
"prefer" is surely an ugly compromise, but nonetheless it often manages
to do the right thing for both camps.

I'm inclined to think that a better answer than changing libpq's behavior
is to encourage DBAs to specify "hostssl" in pg_hba.conf for all external
connections.

regards, tom lane

On 07/15/2016 09:55 AM, Tom Lane wrote:

> I'm inclined to think that a better answer than changing libpq's behavior
> is to encourage DBAs to specify "hostssl" in pg_hba.conf for all external
> connections.

Do those packagers who install dummy certificates and turn SSL on also
change their pg_hba.conf.sample files to use hostssl?. That could go a
long way towards encouraging people.

cheers

andrew

On Fri, Jul 15, 2016 at 4:14 AM, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
>> The original complaint was not actually that "prefer" is a bad default,
>> but that in the presence of a root certificate on the client, a
>> certificate validation failure falls back to plain text. That seems
>> like a design flaw of the "prefer" mode, no matter whether it is the
>> default or not.
>
> The entire "prefer" mode is a design flaw, that we unfortunately picked as
> default mode.

Well, you keep saying that, but what I'm saying is you should stop
complaining about and start figuring out how to fix it. :-)

> If it fails *for any reason*, it falls back to plaintext. Thus, you have to
> assume it will make a plaintext connection. Thus, it gives you zero
> guarantees, so it serves no actual purpose from a security perspective.
>
> it will equally fall back on incompatible SSL configs. Or on a network
> hiccup. The presence of the certificate is just one of many different
> scenarios where it will fall back.
>
> If you care about encryption, you should pick something else
> (require/verify). If you don't care about encryption, you should pick
> something else (allow, probably) so as not to pay unnecessary overhead.

If we think trying to push everyone on to SSL isn't a good plan, then
how about changing the default to allow?

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

On 7/15/16 3:07 PM, Andrew Dunstan wrote:
> Do those packagers who install dummy certificates and turn SSL on also
> change their pg_hba.conf.sample files to use hostssl?. That could go a
> long way towards encouraging people.

Debian, which I guess sort of started this, does not, but there are
allusions to it in the TODO list.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On 7/15/16 4:14 AM, Magnus Hagander wrote:
> The entire "prefer" mode is a design flaw, that we unfortunately picked
> as default mode.
>
> If it fails *for any reason*, it falls back to plaintext. Thus, you have
> to assume it will make a plaintext connection. Thus, it gives you zero
> guarantees, so it serves no actual purpose from a security perspective.

I could imagine a variant of "prefer" that tries SSL if available, but
fails the connection if the SSL setup fails for some reason (e.g.,
certificates). That would be more similar to how browsers with
HTTPS-Everywhere work.

Modulo that, I don't think that "prefer" is a bad default.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Peter Eisentraut 2016-07-17 <d6b22200-0e65-d17e-b227-b63d81720fd0(at)2ndquadrant(dot)com>
> On 7/15/16 3:07 PM, Andrew Dunstan wrote:
> > Do those packagers who install dummy certificates and turn SSL on also
> > change their pg_hba.conf.sample files to use hostssl?. That could go a
> > long way towards encouraging people.
>
> Debian, which I guess sort of started this, does not, but there are
> allusions to it in the TODO list.

I guess we should actually do that if we had any non-local(host)
entries in there by default, but we don't touch the default
pg_hba.conf from pg_createcluster.

Possibly we could add some hostssl example in comments to the end of
the .sample file so people could grow the habit of using that instead
of host (I certainly aren't doing myself that yet), but I'd rather see
that changed upstream.

So, how about something like this for the end of pg_hba.conf.sample?

# Examples for allowing access from given networks:
#hostssl all all 192.0.2.0/24 @authmethod@
#hostssl all all 2001:DB8::/32 @authmethod@

(These are "documentation" networks from RF5737/RFC3849.)

Christoph

On Sun, Jul 17, 2016 at 10:07 PM, Christoph Berg <myon(at)debian(dot)org> wrote:

> Re: Peter Eisentraut 2016-07-17 <
> d6b22200-0e65-d17e-b227-b63d81720fd0(at)2ndquadrant(dot)com>
> > On 7/15/16 3:07 PM, Andrew Dunstan wrote:
> > > Do those packagers who install dummy certificates and turn SSL on also
> > > change their pg_hba.conf.sample files to use hostssl?. That could go a
> > > long way towards encouraging people.
> >
> > Debian, which I guess sort of started this, does not, but there are
> > allusions to it in the TODO list.
>
> I guess we should actually do that if we had any non-local(host)
> entries in there by default, but we don't touch the default
> pg_hba.conf from pg_createcluster.
>

What could actually be useful there is to explicitly put hostnossl on the
localhost entries. With the current defaults on the clients, that wouldn't
break anything, and it would leave people without the performance issues
that you run into in the default deployments. And for localhost it really
does't make sense to encrypt -- for the local LAN segment that can be
argued, but for localhost...

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

Makes sense. Is this something that should be implemented in postgresql, or via pg_createcluster?

Am 19. Juli 2016 16:00:05 MESZ, schrieb Magnus Hagander <magnus(at)hagander(dot)net>:
>On Sun, Jul 17, 2016 at 10:07 PM, Christoph Berg <myon(at)debian(dot)org>
>wrote:
>
>> Re: Peter Eisentraut 2016-07-17 <
>> d6b22200-0e65-d17e-b227-b63d81720fd0(at)2ndquadrant(dot)com>
>> > On 7/15/16 3:07 PM, Andrew Dunstan wrote:
>> > > Do those packagers who install dummy certificates and turn SSL on
>also
>> > > change their pg_hba.conf.sample files to use hostssl?. That could
>go a
>> > > long way towards encouraging people.
>> >
>> > Debian, which I guess sort of started this, does not, but there are
>> > allusions to it in the TODO list.
>>
>> I guess we should actually do that if we had any non-local(host)
>> entries in there by default, but we don't touch the default
>> pg_hba.conf from pg_createcluster.
>>
>
>What could actually be useful there is to explicitly put hostnossl on
>the
>localhost entries. With the current defaults on the clients, that
>wouldn't
>break anything, and it would leave people without the performance
>issues
>that you run into in the default deployments. And for localhost it
>really
>does't make sense to encrypt -- for the local LAN segment that can be
>argued, but for localhost...
>
>
>--
> Magnus Hagander
> Me: http://www.hagander.net/
> Work: http://www.redpill-linpro.com/

On Tue, Jul 19, 2016 at 8:53 PM, Christoph Berg <cb(at)df7cb(dot)de> wrote:

> Makes sense. Is this something that should be implemented in postgresql,
> or via pg_createcluster?
>
>
Personally I'd like to see pg_createcluster et al mimic upstream as close
as possible, so I'd advocate these changes being made upstream in
PostgreSQL iteslf.

//Magnus

>
> Am 19. Juli 2016 16:00:05 MESZ, schrieb Magnus Hagander <
> magnus(at)hagander(dot)net>:
>>
>>
>>
>> On Sun, Jul 17, 2016 at 10:07 PM, Christoph Berg <myon(at)debian(dot)org> wrote:
>>
>>> Re: Peter Eisentraut 2016-07-17 <
>>> d6b22200-0e65-d17e-b227-b63d81720fd0(at)2ndquadrant(dot)com>
>>> > On 7/15/16 3:07 PM, Andrew Dunstan wrote:
>>> > > Do those packagers who install dummy certificates and turn SSL on
>>> also
>>> > > change their pg_hba.conf.sample files to use hostssl?. That could go
>>> a
>>> > > long way towards encouraging people.
>>> >
>>> > Debian, which I guess sort of started this, does not, but there are
>>> > allusions to it in the TODO list.
>>>
>>> I guess we should actually do that if we had any non-local(host)
>>> entries in there by default, but we don't touch the default
>>> pg_hba.conf from pg_createcluster.
>>>
>>
>> What could actually be useful there is to explicitly put hostnossl on the
>> localhost entries. With the current defaults on the clients, that wouldn't
>> break anything, and it would leave people without the performance issues
>> that you run into in the default deployments. And for localhost it really
>> does't make sense to encrypt -- for the local LAN segment that can be
>> argued, but for localhost...
>>
>>
>> --
>> Magnus Hagander
>> Me: http://www.hagander.net/
>> Work: http://www.redpill-linpro.com/
>>
>

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

On 7/19/16 10:00 AM, Magnus Hagander wrote:
> What could actually be useful there is to explicitly put hostnossl on
> the localhost entries. With the current defaults on the clients, that
> wouldn't break anything, and it would leave people without the
> performance issues that you run into in the default deployments. And for
> localhost it really does't make sense to encrypt -- for the local LAN
> segment that can be argued, but for localhost...

But even on localhost you ideally want a way to confirm that the server
you are connecting to is the right one, so you might want certificates.
Plus the server might want certificates from the clients. (See also the
occasional discussion about supporting SSL over Unix-domain sockets.)

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Tue, Jul 19, 2016 at 9:24 PM, Peter Eisentraut <
peter(dot)eisentraut(at)2ndquadrant(dot)com> wrote:

> On 7/19/16 10:00 AM, Magnus Hagander wrote:
> > What could actually be useful there is to explicitly put hostnossl on
> > the localhost entries. With the current defaults on the clients, that
> > wouldn't break anything, and it would leave people without the
> > performance issues that you run into in the default deployments. And for
> > localhost it really does't make sense to encrypt -- for the local LAN
> > segment that can be argued, but for localhost...
>
> But even on localhost you ideally want a way to confirm that the server
> you are connecting to is the right one, so you might want certificates.
> Plus the server might want certificates from the clients. (See also the
> occasional discussion about supporting SSL over Unix-domain sockets.)
>
>
There are definitely cases where it's useful. I'm only arguing for changing
the default.

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

On 7/19/16 3:32 PM, Magnus Hagander wrote:
> There are definitely cases where it's useful. I'm only arguing for
> changing the default.

I don't understand why you want to change the default. Is it for
performance? Has it been measured?

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Iirc we changed the default to be SSL for localhost to address a particular
problem. It seemed surprising at the time but it was the most effective
solution.
On 19 Jul 2016 21:58, "Peter Eisentraut" <peter(dot)eisentraut(at)2ndquadrant(dot)com>
wrote:

> On 7/19/16 3:32 PM, Magnus Hagander wrote:
> > There are definitely cases where it's useful. I'm only arguing for
> > changing the default.
>
> I don't understand why you want to change the default. Is it for
> performance? Has it been measured?
>
> --
> Peter Eisentraut http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

On Tue, Jul 19, 2016 at 10:57 PM, Peter Eisentraut <
peter(dot)eisentraut(at)2ndquadrant(dot)com> wrote:

> On 7/19/16 3:32 PM, Magnus Hagander wrote:
> > There are definitely cases where it's useful. I'm only arguing for
> > changing the default.
>
> I don't understand why you want to change the default. Is it for
> performance? Has it been measured?
>
>
Yes. I've run into it multiple times, but I haven't specifically measured
it. But I've had more than one situation where turning it off has
completely removed a performance problem.

I've only seen it in apps without proper connection pooling. It's the
negotiation of new sessions that's expensive, not actually encrypting the
data.

Most people definitely don't run into it, because most people don't use
localhost when they're local - they use the Unix socket. But for example a
locally running java application will be using localhost.

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

Magnus Hagander wrote:

> > I don't understand why you want to change the default. Is it for
> > performance? Has it been measured?
> >
> >
> Yes. I've run into it multiple times, but I haven't specifically measured
> it. But I've had more than one situation where turning it off has
> completely removed a performance problem.

Here's a test case retrieving 133000 rows representing
100Mbytes of text, that shows a 4x slowdown with ssl.
ssl_renegotiation_limit is set to 0 and the cache is warmed up
by repeated executions.

Without SSL:

$ time psql -At "postgresql://localhost/mlists?sslmode=disable"\
-c "select subject from mail" -o /dev/null
real 0m1.359s
user 0m0.544s
sys 0m0.084s

With SSL:
$ time psql -At "postgresql://localhost/mlists?sslmode=require"\
-c "select subject from mail" -o /dev/null
real 0m5.395s
user 0m1.080s
sys 0m0.116s

The CPU is Intel(R) Xeon(R) CPU E31230 @ 3.20GHz, OS is Debian7
with kernel 3.2.0-4.

Personally I think that TLS for local networking is wrong as a default, and
it's unfortunate that distros like Debian/Ubuntu end up using that.

Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

On 7/20/16 8:55 AM, Daniel Verite wrote:
> Personally I think that TLS for local networking is wrong as a default, and
> it's unfortunate that distros like Debian/Ubuntu end up using that.

There is something to that, but I'm not sure that just giving up and
disabling SSL in the default configuration is a forward-looking answer.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Tue, Jul 19, 2016 at 03:24:26PM -0400, Peter Eisentraut wrote:
> On 7/19/16 10:00 AM, Magnus Hagander wrote:
> > What could actually be useful there is to explicitly put hostnossl on
> > the localhost entries. With the current defaults on the clients, that
> > wouldn't break anything, and it would leave people without the
> > performance issues that you run into in the default deployments. And for
> > localhost it really does't make sense to encrypt -- for the local LAN
> > segment that can be argued, but for localhost...
>
> But even on localhost you ideally want a way to confirm that the server
> you are connecting to is the right one, so you might want certificates.
> Plus the server might want certificates from the clients. (See also the
> occasional discussion about supporting SSL over Unix-domain sockets.)

Yes, I am thinking of a case where Postgres is down but a malevolent
user starts a Postgres server on 5432 to gather passwords. Verifying
against an SSL certificate would avoid this problem, so there is some
value in using SSL on localhost. (There is no such security available
for Unix-domain socket connections.)

--
Bruce Momjian <bruce(at)momjian(dot)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 7/29/16 11:13 AM, Bruce Momjian wrote:
> Yes, I am thinking of a case where Postgres is down but a malevolent
> user starts a Postgres server on 5432 to gather passwords. Verifying
> against an SSL certificate would avoid this problem, so there is some
> value in using SSL on localhost. (There is no such security available
> for Unix-domain socket connections.)

Sure, there is the requirepeer connection option for that.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Fri, Jul 29, 2016 at 4:13 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> Yes, I am thinking of a case where Postgres is down but a malevolent
> user starts a Postgres server on 5432 to gather passwords.

Or someone spoofs your DNS lookup, which is an attack that can
actually be done remotely in some cases.

For what it's worth the SCRAM work also addresses precisely this
danger though it doesn't prevent the attacker from pretending to be a
real server and capturing private data from the SQL updates.

Even in the case where there's no known server certificate it could
save the fingerprint seen once and require it not change. This proves
to be a headache to manage though. It's equivalent to the SSH
known_hosts scheme. How many times have you seen that warning message
and just automatically removed the entry in known_hosts without
verifying...

One day DNSSEC will solve all these problems though. Then you'll just
store the certificate in the DNS entry for the server and the client
will insist it match.

--
greg

On Fri, Jul 29, 2016 at 11:27:06AM -0400, Peter Eisentraut wrote:
> On 7/29/16 11:13 AM, Bruce Momjian wrote:
> > Yes, I am thinking of a case where Postgres is down but a malevolent
> > user starts a Postgres server on 5432 to gather passwords. Verifying
> > against an SSL certificate would avoid this problem, so there is some
> > value in using SSL on localhost. (There is no such security available
> > for Unix-domain socket connections.)
>
> Sure, there is the requirepeer connection option for that.

Oh, nice, I had not seen that.

--
Bruce Momjian <bruce(at)momjian(dot)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, Jul 30, 2016 at 11:18 AM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> On Fri, Jul 29, 2016 at 11:27:06AM -0400, Peter Eisentraut wrote:
> > On 7/29/16 11:13 AM, Bruce Momjian wrote:
> > > Yes, I am thinking of a case where Postgres is down but a malevolent
> > > user starts a Postgres server on 5432 to gather passwords. Verifying
> > > against an SSL certificate would avoid this problem, so there is some
> > > value in using SSL on localhost. (There is no such security available
> > > for Unix-domain socket connections.)
> >
> > Sure, there is the requirepeer connection option for that.
>
> Oh, nice, I had not seen that.
>

Hi Bruce,

There is typo in the doc patch you just committed

"On way to prevent spoofing of"

s/On/One/

Cheers,

Jeff

On Fri, Aug 19, 2016 at 09:22:32AM -0700, Jeff Janes wrote:
> On Sat, Jul 30, 2016 at 11:18 AM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>
> On Fri, Jul 29, 2016 at 11:27:06AM -0400, Peter Eisentraut wrote:
> > On 7/29/16 11:13 AM, Bruce Momjian wrote:
> > > Yes, I am thinking of a case where Postgres is down but a malevolent
> > > user starts a Postgres server on 5432 to gather passwords.  Verifying
> > > against an SSL certificate would avoid this problem, so there is some
> > > value in using SSL on localhost.  (There is no such security available
> > > for Unix-domain socket connections.)
> >
> > Sure, there is the requirepeer connection option for that.
>
> Oh, nice, I had not seen that.
>
>
>
> Hi Bruce,
>
> There is typo in the doc patch you just committed
>
> "On way to prevent spoofing of"
>
> s/On/One/

Oops, thanks, fixed.

--
Bruce Momjian <bruce(at)momjian(dot)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 +