Обсуждение: PG Startup message and HAProxy ACL

Поиск
Список
Период
Сортировка

PG Startup message and HAProxy ACL

От
"Godfrin, Philippe E"
Дата:

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.Godfrin@nov.com

 

Re: PG Startup message and HAProxy ACL

От
Vijaykumar Jain
Дата:
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

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.godfrin@nov.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.Godfrin@nov.com

 

Re: PG Startup message and HAProxy ACL

От
Vijaykumar Jain
Дата:
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
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.

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

there was also a similar project I checked 
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
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.github@gmail.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

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.godfrin@nov.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.Godfrin@nov.com

 

RE: [EXTERNAL] Re: PG Startup message and HAProxy ACL

От
"Godfrin, Philippe E"
Дата:

Thanks,

Those links were helpful thanks! I’ve been asked if there’s a way to capture connection info to target servers…

pg

 

From: Vijaykumar Jain <vijaykumarjain.github@gmail.com>
Sent: Thursday, June 3, 2021 1:43 AM
To: Godfrin, Philippe E <Philippe.Godfrin@nov.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: [EXTERNAL] Re: PG Startup message and HAProxy ACL

 

Use caution when interacting with this [EXTERNAL] email!

 

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

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.

 

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

 

there was also a similar project I checked 

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

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.github@gmail.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

 

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.godfrin@nov.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.Godfrin@nov.com