Обсуждение: libpq heartbeat

On Thu, Oct 27, 2016 at 04:43:55PM +0200, Marcin Giedz wrote:
> Hello all
>
> I'm wondering if there is any natural implementation of heartbeats in
> libpq library? We've been facing specially in firewall env
> occasionally session drops between client and server. Extending
> session timeout directly on firewalls is not possible (company rules).
> Perhaps there is such mechanism "somewhere" to avoid reimplementation
> ?

check tcp_keepalives_* in postgresql.conf

Best regards,

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
                                                             http://depesz.com/

Hi Marcin:

On Thu, Oct 27, 2016 at 4:43 PM, Marcin Giedz <marcin.giedz@arise.pl> wrote:
> I'm wondering if there is any natural implementation of heartbeats in libpq
> library? We've been facing specially in firewall env occasionally session
> drops between client and server. Extending session timeout directly on
> firewalls is not possible (company rules). Perhaps there is such mechanism
> "somewhere" to avoid reimplementation ?

RTFM? Now, seriously. Have you read
https://www.postgresql.org/docs/9.6/static/libpq-connect.html#LIBPQ-PARAMKEYWORDS,
specifically search for keepalive. Keepalives generate traffic which
normally keeps overzealous firewalls happy, I have used it before
successfully.

And I'd like to point libpq sessions does not sound to be the best
kind of traffic across a firewall, not a good service / protocol to
expose.

Francisco Olarte.

On Thu, Oct 27, 2016 at 5:01 PM, hubert depesz lubaczewski
<depesz@depesz.com> wrote:
> On Thu, Oct 27, 2016 at 04:43:55PM +0200, Marcin Giedz wrote:
...
>> I'm wondering if there is any natural implementation of heartbeats in
>> libpq library?
...

> check tcp_keepalives_* in postgresql.conf

Isn't this a server setting, and so going to affect every connection,
being it from the (affected) libpq connections or from other sources (
like jdbc, although he may want keepalives for those too )?

Francisco Olarte.

On Thu, Oct 27, 2016 at 10:01 AM, Francisco Olarte
<folarte@peoplecall.com> wrote:
> And I'd like to point libpq sessions does not sound to be the best
> kind of traffic across a firewall, not a good service / protocol to
> expose.

meh -- it's perfectly fine to expose postgres to the internet as long
as you've handled the security concerns.   This could be over ssh
tunnel for example.

merlin

Merlin:

On Thu, Oct 27, 2016 at 6:10 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Thu, Oct 27, 2016 at 10:01 AM, Francisco Olarte
> <folarte@peoplecall.com> wrote:
>> And I'd like to point libpq sessions does not sound to be the best
>> kind of traffic across a firewall, not a good service / protocol to
>> expose.

> meh -- it's perfectly fine to expose postgres to the internet as long
> as you've handled the security concerns.

It is, but handling them is not easy, and you have to deal with things
like DoS which are not trivial on the server ( as it is a heavy
service ). It can be done, and sometimes needs to be done, but is not
a thing to take over lightly.

> This could be over ssh tunnel for example.

In which case it is NOT exposed to the internet. What are you trying to say?

Francisco Olarte.

Francisco Olarte <folarte@peoplecall.com> writes:
> On Thu, Oct 27, 2016 at 5:01 PM, hubert depesz lubaczewski
> <depesz@depesz.com> wrote:
>> On Thu, Oct 27, 2016 at 04:43:55PM +0200, Marcin Giedz wrote:
>>> I'm wondering if there is any natural implementation of heartbeats in
>>> libpq library?

>> check tcp_keepalives_* in postgresql.conf

> Isn't this a server setting, and so going to affect every connection,

Yes, but there are equivalent libpq parameters for firing heartbeat
pings from the client side.  Those are per-connection.

https://www.postgresql.org/docs/current/static/libpq-connect.html#LIBPQ-PARAMKEYWORDS

            regards, tom lane

Tom:

On Thu, Oct 27, 2016 at 6:32 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Francisco Olarte <folarte@peoplecall.com> writes:
>> Isn't this a server setting, and so going to affect every connection,
> Yes,

Ok, just checking.

> but there are equivalent libpq parameters for firing heartbeat
> pings from the client side.  Those are per-connection.
> https://www.postgresql.org/docs/current/static/libpq-connect.html#LIBPQ-PARAMKEYWORDS

That's what I told the OP ( with nearly the same link ).

Thanks.

Francisco Olarte.

On Thu, Oct 27, 2016 at 11:18 AM, Francisco Olarte
<folarte@peoplecall.com> wrote:
> Merlin:
>
> On Thu, Oct 27, 2016 at 6:10 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
>> On Thu, Oct 27, 2016 at 10:01 AM, Francisco Olarte
>> <folarte@peoplecall.com> wrote:
>>> And I'd like to point libpq sessions does not sound to be the best
>>> kind of traffic across a firewall, not a good service / protocol to
>>> expose.
>
>> meh -- it's perfectly fine to expose postgres to the internet as long
>> as you've handled the security concerns.
>
> It is, but handling them is not easy, and you have to deal with things
> like DoS which are not trivial on the server ( as it is a heavy
> service ). It can be done, and sometimes needs to be done, but is not
> a thing to take over lightly.
>
>> This could be over ssh tunnel for example.
>
> In which case it is NOT exposed to the internet. What are you trying to say?

what?   ssh can most certainly convey over the internet.   I said ssh
*tunnel*; not ssh.   With tunneling the ssh endpoint is the client
application.   When I built a libpq based intenet facing application
we used a modified pgbouncer to whitelist the parameterized query
strings and to force the auth.  We had zero issues.

merlin

Merlin:

On Thu, Oct 27, 2016 at 7:29 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Thu, Oct 27, 2016 at 11:18 AM, Francisco Olarte
> <folarte@peoplecall.com> wrote:
>> It is, but handling them is not easy, and you have to deal with things
>> like DoS which are not trivial on the server ( as it is a heavy
>> service ). It can be done, and sometimes needs to be done, but is not
>> a thing to take over lightly.
>>> This could be over ssh tunnel for example.
>> In which case it is NOT exposed to the internet. What are you trying to say?
>
> what?   ssh can most certainly convey over the internet.   I said ssh
> *tunnel*; not ssh.   With tunneling the ssh endpoint is the client
> application.   When I built a libpq based intenet facing application
> we used a modified pgbouncer to whitelist the parameterized query
> strings and to force the auth.  We had zero issues.

I'm not a native English speaker, so I have some problem understanding
the finer details....

I said libpq service/protocols are tricky to put on the internet.

You replied, among other things, it could be over an ssh tunnel ( I
use ssh tunnels continuously, to the point I routinely open/close them
on live connections via escape, so I know  they are ).

The I said in that case it is NOT exposed to the internet, trying to
mean libp/postgres is NOT exposed to the internet. They are exposed to
the SSH tunnel endpoint. Of course, payload still goes through the
interrnet, but they are not directly exposed.

As an example, leaving potential SSL and encryption usage in postgres
aside. If I expose postgres on the internet you can mount a DoS attack
against me, and postmaster will have to defend against it. IIRC
postmaster does not fork until client is authenticated, but even then
I suspect its accepting code is not dessigned with the same care to
deal with hostile connection attempts as the one in ssh, or even on a
web server. So you may be able to DoS me with much less resources than
you would need to DoS the ssh server.

And, also, if you are exposing the postmaster directly I think client
and server certificates are a must, aside from passwords. But I doubt
someone who does not know about the keepalive stuff like the OP uses
more than server certs and passwords, although I hope I'm proven wrong
by him. In general I use the tunneling approach for postgres, as I my
security guys continuously monitor and patches the ssh servers. But,
if he were dealing with that kind of stuff and told me I would have
pointed him in the direction of ssh[d]_config to enable ssh keepalives
for his firewall problems and not said a word about the non-existent
perils of exposing the postmaster.

Francisco Olarte.

On Thu, Oct 27, 2016 at 12:45 PM, Francisco Olarte
<folarte@peoplecall.com> wrote:
> Merlin:
>
> On Thu, Oct 27, 2016 at 7:29 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
>> On Thu, Oct 27, 2016 at 11:18 AM, Francisco Olarte
>> <folarte@peoplecall.com> wrote:
>>> It is, but handling them is not easy, and you have to deal with things
>>> like DoS which are not trivial on the server ( as it is a heavy
>>> service ). It can be done, and sometimes needs to be done, but is not
>>> a thing to take over lightly.
>>>> This could be over ssh tunnel for example.
>>> In which case it is NOT exposed to the internet. What are you trying to say?
>>
>> what?   ssh can most certainly convey over the internet.   I said ssh
>> *tunnel*; not ssh.   With tunneling the ssh endpoint is the client
>> application.   When I built a libpq based intenet facing application
>> we used a modified pgbouncer to whitelist the parameterized query
>> strings and to force the auth.  We had zero issues.
>
> I'm not a native English speaker, so I have some problem understanding
> the finer details....
>
> I said libpq service/protocols are tricky to put on the internet.
>
> You replied, among other things, it could be over an ssh tunnel ( I
> use ssh tunnels continuously, to the point I routinely open/close them
> on live connections via escape, so I know  they are ).

right -- we may be talking past each other.   You originally said,
'across a firewall', not 'exposed to the internet'.  I agree that you
should not expose a database directly to the internet without very
carefully considering the ramifications, but there are many scenarios
where you would cross a firewall where the suggestions advised here
(tcp keepalives) are useful and good to do.

In our corporate intranet here we have overzealous firewalls which
causes major problems with many applications stacks, particularly
java.  Aggressive keepalives deals with such cases pretty well.

merlin