Обсуждение: sslmode=require fallback
Hi!<div class=""><br class="" /></div><div class="">I've looked at the way libpq handles TLS certificates and plaintext fallback,and I am somewhat surprised.</div><div class=""><br class="" /></div><div class="">The default ssmode is prefer.According to the documentation, this will make libpq use an SSL connection if possible, but will use a plain textconnection as a fallback. The certificate will not be verified.</div><div class=""><br class="" /></div><div class="">If,however, there is a root certificate in ~/.postgresql/root.crt, libpq will check if the server cert matches thiscertificate, and refuse any certfificates that don't match. This means that libpq will fall back to a plain text connection!</div><divclass=""><br class="" /></div><div class="">This is very unexpected behavior! Shouldn't libpq preferan *unauthenticated but encrypted* connection over an *unauthenticated and unencrypted* connection?</div><div class=""><brclass="" /></div><div class="">This behavior also causes sslmode=require to behave like sslmode=verify-ca when~/.postgresql/root.crt exists.</div><div class=""><br class="" /></div><div class="">From my limited understanding, itseems the way to fix this would be in fe-secure-openssl.c, to change initialize_SSL() to only read the root certificatefile when sslmode=verify_*</div><div class=""><br class="" /></div><div class="">However, if this is the expectedbehavior, the documentation at <a class="" href="https://www.postgresql.org/docs/current/static/libpq-ssl.html">https://www.postgresql.org/docs/current/static/libpq-ssl.html</a> should beupdated to make this more clear. It should be made clear that the existence of the file ~/.postgresql/root.crt changesthe behavior of sslmode=require and sslmode=prefer.</div><div class=""><br class="" /></div><div class="">Best regards,</div><divclass="">Jakob</div>
On Thu, Jun 16, 2016 at 10:39 AM, Jakob Egger <jakob@eggerapps.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.
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@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 Thu, Jun 23, 2016 at 1:50 AM, Bruce Momjian <bruce@momjian.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...
On Thu, Jun 16, 2016 at 3:42 AM, Magnus Hagander <magnus@hagander.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@gmail.com> writes:
> On Thu, Jun 16, 2016 at 3:42 AM, Magnus Hagander <magnus@hagander.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@sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas@gmail.com> writes: >> On Thu, Jun 16, 2016 at 3:42 AM, Magnus Hagander <magnus@hagander.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@gmail.com> writes:
> On Wed, Jul 13, 2016 at 3:16 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Robert Haas <robertmhaas@gmail.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
<p dir="ltr">On 13 Jul 2016 9:28 pm, "Tom Lane" <<a href="mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>> wrote:<br/> ><br /> > Robert Haas <<a href="mailto:robertmhaas@gmail.com">robertmhaas@gmail.com</a>> writes:<br/> > > On Wed, Jul 13, 2016 at 3:16 PM, Tom Lane <<a href="mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>>wrote:<br /> > >> Robert Haas <<a href="mailto:robertmhaas@gmail.com">robertmhaas@gmail.com</a>>writes:<br /> > >>> Suppose we changed the defaultto "require". How crazy would that be?<br /> ><br /> > >> You mean, aside from the fact that it breaksevery single installation<br /> > >> that hasn't configured with SSL?<br /> ><br /> > > No, includingthat.<p dir="ltr">Well what's required to "configure SSL" anyways? If you don't have verify-ca set or a root canalcert present then the server just needs a certificate -- any certificate. Can the server just cons one up on demand(or server startup or initdb)?<p dir="ltr">Yes, that would not help with active MITM attacks but at least removes anychance that people are unknowingly using an unencrypted connection vulnerable to passive sniffers. <br />
Greg Stark <stark@mit.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@sss.pgh.pa.us> wrote:
Greg Stark <stark@mit.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 <magnus@hagander.net> writes:
> On Thu, Jul 14, 2016 at 11:27 PM, Tom Lane <tgl@sss.pgh.pa.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@hagander.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.eisentraut@2ndquadrant.com> wrote:
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.
-- On 7/13/16 4:11 PM, Robert Haas wrote:
> On Thu, Jun 16, 2016 at 3:42 AM, Magnus Hagander <magnus@hagander.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.
On 14.07.2016 23:34, Magnus Hagander wrote: > > > On Thu, Jul 14, 2016 at 11:27 PM, Tom Lane <tgl@sss.pgh.pa.us > <mailto:tgl@sss.pgh.pa.us>> wrote: > > Greg Stark <stark@mit.edu <mailto:stark@mit.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@hagander.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@hagander.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 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
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
Re: Peter Eisentraut 2016-07-17 <d6b22200-0e65-d17e-b227-b63d81720fd0@2ndquadrant.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@debian.org> wrote:
Re: Peter Eisentraut 2016-07-17 <d6b22200-0e65-d17e-b227-b63d81720fd0@2ndquadrant.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...
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@hagander.net>:
On Sun, Jul 17, 2016 at 10:07 PM, Christoph Berg <myon@debian.org> wrote:Re: Peter Eisentraut 2016-07-17 <d6b22200-0e65-d17e-b227-b63d81720fd0@2ndquadrant.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...--
On Tue, Jul 19, 2016 at 8:53 PM, Christoph Berg <cb@df7cb.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@hagander.net>:On Sun, Jul 17, 2016 at 10:07 PM, Christoph Berg <myon@debian.org> wrote:Re: Peter Eisentraut 2016-07-17 <d6b22200-0e65-d17e-b227-b63d81720fd0@2ndquadrant.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...--
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.eisentraut@2ndquadrant.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.
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
<p dir="ltr">Iirc we changed the default to be SSL for localhost to address a particular problem. It seemed surprising atthe time but it was the most effective solution.<div class="gmail_quote">On 19 Jul 2016 21:58, "Peter Eisentraut" <<ahref="mailto:peter.eisentraut@2ndquadrant.com">peter.eisentraut@2ndquadrant.com</a>> wrote:<br type="attribution"/><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">On7/19/16 3:32 PM, Magnus Hagander wrote:<br /> > There are definitely cases where it's useful.I'm only arguing for<br /> > changing the default.<br /><br /> I don't understand why you want to change the default. Is it for<br /> performance? Has it been measured?<br /><br /> --<br /> Peter Eisentraut <a href="http://www.2ndQuadrant.com/"rel="noreferrer" target="_blank">http://www.2ndQuadrant.com/</a><br /> PostgreSQL Development,24x7 Support, Remote DBA, Training & Services<br /><br /><br /> --<br /> Sent via pgsql-hackers mailing list(<a href="mailto:pgsql-hackers@postgresql.org">pgsql-hackers@postgresql.org</a>)<br /> To make changes to your subscription:<br/><a href="http://www.postgresql.org/mailpref/pgsql-hackers" rel="noreferrer" target="_blank">http://www.postgresql.org/mailpref/pgsql-hackers</a><br/></blockquote></div>
On Tue, Jul 19, 2016 at 10:57 PM, Peter Eisentraut <peter.eisentraut@2ndquadrant.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 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@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 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@momjian.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@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, Jul 30, 2016 at 11:18 AM, Bruce Momjian <bruce@momjian.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@momjian.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@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 +