Re: PG Startup message and HAProxy ACL

Also if you do not mind,
May I ask you what is the goal of this setup?
Because I have a feeling I am not helping enough by sharing links.

Will this work when you introduce ssl unless you are terminating it before
you check startup message ?

If this is to split read and writes, there are options like
https://severalnines.com/resources/database-management-tutorials/postgresql-load-balancing-haproxy
We had a similar setup with small variations where there were dedicated
haproxy nodes for read and write servers as the goal was to load balance
read only.
anyways,
If you are trying to parse based on type of query,
envoyproxy wrote a filter to parse query using the above protocol to be
able to get metrics without querying system tables.
https://www.envoyproxy.io/docs/envoy/latest/configuration/listeners/network_filters/postgres_proxy_filter

I do not understand c++ and envoy is written in c++ so just FYI.

there was also a similar project I checked
https://github.com/jhunt/pgrouter
which seems interesting but then I do not know how do you parse function
calls that performs reads and writes in the body.
So I have my doubts Parsing query would get read write split magically.
Also
https://blog.heimdalldata.com/2019/06/06/improve-read-replica-utilization-azure/
They use intelligence to split read and write which I cannot trust as I do
not understand.

basically the way we setup things were, each app would have two connection
pools one for writes and one for reads. read nodes can be load balanced at
haproxy or use client-side shuffling with basic health checks.
Since pg10 libpq made use of allowing multiple nodes in connection string,
it made the above task easy.

We also made use of the same connection string in fdw to minimize config
changes in event of node failures etc.
I can share the entire setup, with pg_auto_failover to make it more robust,
but if this not your goal then I am just adding noise :).

On Thu, Jun 3, 2021, 3:23 AM Vijaykumar Jain <
vijaykumarjain(dot)github(at)gmail(dot)com> wrote:

> https://www.postgresql.org/docs/13/protocol-flow.html
>
> The above explains what goes over the wire in what order.
>
> I understood the implementation above from reading
> https://github.com/tlocke/pg8000/blob/master/pg8000/core.py
>
> I may be diverting here, this helped me understand how the message flows
> from client to server.
> Ignore if not relevant.
>
>
> On Thu, Jun 3, 2021, 2:40 AM Godfrin, Philippe E <philippe(dot)godfrin(at)nov(dot)com>
> wrote:
>
>> Greetings folks!
>>
>>
>>
>> I am trying to parse the PG startup message using an HAProxy ACL – but
>> the acl never returns true. Here’s what it looks like:
>>
>>
>>
>> listen pg_ingress
>>
>> #mode tcp
>>
>> bind *:5000
>>
>> option tcplog # enable addvanced logging
>>
>> # hex convert tsdbrw
>>
>> acl check-rw req.payload(0,0),hex -m sub 757365720074736462727700
>>
>> use_backend pg_readwrite if check-rw
>>
>> default_backend pg_readonly
>>
>>
>>
>> In detail:
>>
>>
>>
>> acl check-rw req.payload(0,0),hex -m sub 757365720074736462727700
>>
>>
>>
>> The req.payload should return a binary block of the entire request
>> buffer. I am assuming that the startup message will be there but I suspect
>> it is not.
>>
>> The “hex” statement converts the binary into hex, and the -m sub attempts
>> to match a substring of the following hex – which is “user\0tsdbrw\0”
>>
>>
>>
>> I think this should work, but it doesn’t look that way…
>>
>>
>>
>> When exactly does the startup message come across the tcp wire?
>>
>> Much thanks,
>>
>> Pg
>>
>>
>>
>> Phil Godfrin | *Database Administrator*
>>
>> *NOV*
>>
>> NOV US | Engineering Data
>>
>> 9720 Beechnut St | Houston, Texas 77036
>>
>> M 281.825.2311
>>
>> E Philippe(dot)Godfrin(at)nov(dot)com
>>
>>
>>
>