My apologies, that trace is wrong, it is supposed to be tsdbrw: Jun 4 20:19:26 ip-172-31-77-193 haproxy[2113]: 1622837966.765959 [<<<pgfe>>>/pg_ingress] [strm 0x558944c88340(0) 0x00060000 0x30000000] trace_tcp_payload : channel=REQUEST - mode=TCP (backend) - offset=0 - len=78 - forward=78
Jun 4 20:19:26 ip-172-31-77-193 haproxy[2113]: #0110x000000: 00 00 00 4e 00 03 00 00 75 73 65 72 00 74 73 64 |...N....user.tsd| Jun 4 20:19:26 ip-172-31-77-193 haproxy[2113]: #0110x000010: 62 72 77 00 64 61 74 61 62 61 73 65 00 74 73 64 |brw.database.tsd| Jun 4 20:19:26 ip-172-31-77-193 haproxy[2113]: #0110x000020: 62 00 61 70 70 6c 69 63 61 74 69 6f 6e 5f 6e 61 |b.application_na| Jun 4 20:19:26 ip-172-31-77-193 haproxy[2113]: #0110x000030: 6d 65 00 70 73 71 6c 00 63 6c 69 65 6e 74 5f 65 |me.psql.client_e| Jun 4 20:19:26 ip-172-31-77-193 haproxy[2113]: #0110x000040: 6e 63 6f 64 69 6e 67 00 55 54 46 38 00 00 |ncoding.UTF8..| Config: root@ip-172-31-77-193:/etc/haproxy# cat haproxy.cfg global log /dev/log local0 log /dev/log local1 notice chroot /var/lib/haproxy stats socket /run/haproxy/admin.sock mode 660 level admin expose-fd listeners stats timeout 30s user haproxy group haproxy daemon # Default SSL material locations ca-base /etc/ssl/certs crt-base /etc/ssl/private # See: https://ssl-config.mozilla.org/#server=haproxy&server-version=2.0.3&config=intermediate ssl-default-bind-ciphers ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-CHACHA20-POLY1305:ECDHE-RSA-CHACHA20-POLY1305:DHE-RSA-AES128-GCM-SHA256:DHE-RSA-AES256-GCM-SHA384 ssl-default-bind-ciphersuites TLS_AES_128_GCM_SHA256:TLS_AES_256_GCM_SHA384:TLS_CHACHA20_POLY1305_SHA256 ssl-default-bind-options ssl-min-ver TLSv1.2 no-tls-tickets defaults log global mode http mode tcp option httplog option dontlognull timeout connect 5000 timeout client 50000 timeout server 50000 errorfile 400 /etc/haproxy/errors/400.http errorfile 403 /etc/haproxy/errors/403.http errorfile 408 /etc/haproxy/errors/408.http errorfile 500 /etc/haproxy/errors/500.http errorfile 502 /etc/haproxy/errors/502.http errorfile 503 /etc/haproxy/errors/503.http errorfile 504 /etc/haproxy/errors/504.http #--------------------------------------------------------------------- # statistics #--------------------------------------------------------------------- # Host HA-Proxy's web stats on Port 7000. listen HAProxy-Statistics bind *:7000 mode http option httplog stats enable stats uri /haproxy?stats stats refresh 20s stats realm PSQL Haproxy\ Statistics # Title text for popup window stats show-node stats show-legends stats show-desc PSQL load balancer stats (master) stats auth pgadmin:pgsecret frontend pg_ingress bind *:5000 mode tcp option tcplog # enable advanced logging log global tcp-request inspect-delay 5s acl pg_msg_term req.payload(8,0),hex -m end 0000 tcp-request content accept if pg_msg_term #tcp-request content capture req.payload(8,32) len 32 #log-format "%ci:%cp -> %fi:%fp [%t] %ft %b/%s %Tw/%Tc/%Tt %B %ts %ac/%fc/%bc/%sc/%rc %sq/%bq captured_user:%{+Q}[capture.req.hdr(0)] req.len:%[capture.req.hdr(1)]" #log-format "captured_data:%{+Q}[capture.req.hdr(1)]" # hex convert tsdbrw # 757365720074736462727700 acl check-rw req.payload(8,32),hex -m sub 757365720074736462727700 use_backend pg_readwrite if check-rw #use_backend pg_readwrite unless check-rw default_backend pg_readonly #filter trace name pg-trace hexdump backend pg_readwrite mode tcp option httpchk http-check expect status 200 default-server inter 3s fall 3 rise 3 on-marked-down shutdown-sessions server tstshd01 172.31.68.147:6432 check port 8008 server tstshd02 172.31.69.227:6432 check port 8008 backend pg_readonly mode tcp balance leastconn default-server inter 3s fall 3 rise 3 on-marked-down shutdown-sessions server tstshd01 172.31.68.147:6432 server tstshd02 172.31.69.227:6432 # end Log: Jun 8 16:53:11 ip-172-31-77-193 haproxy[15694]: [WARNING] 158/165311 (15694) : Exiting Master process... Jun 8 16:53:11 ip-172-31-77-193 haproxy[15694]: [NOTICE] 158/165311 (15694) : haproxy version is 2.2.14-1ppa1~bionic Jun 8 16:53:11 ip-172-31-77-193 haproxy[15694]: [NOTICE] 158/165311 (15694) : path to executable is /usr/sbin/haproxy Jun 8 16:53:11 ip-172-31-77-193 haproxy[15694]: [ALERT] 158/165311 (15694) : Current worker #1 (15705) exited with code 143 (Terminated) Jun 8 16:53:11 ip-172-31-77-193 haproxy[15694]: [WARNING] 158/165311 (15694) : All workers exited. Exiting... (0) Jun 8 16:53:11 ip-172-31-77-193 haproxy[15843]: Proxy HAProxy-Statistics started. Jun 8 16:53:11 ip-172-31-77-193 haproxy[15843]: Proxy HAProxy-Statistics started. Jun 8 16:53:11 ip-172-31-77-193 haproxy[15843]: Proxy pg_ingress started. Jun 8 16:53:11 ip-172-31-77-193 haproxy[15843]: Proxy pg_ingress started. Jun 8 16:53:11 ip-172-31-77-193 haproxy[15843]: Proxy pg_readwrite started. Jun 8 16:53:11 ip-172-31-77-193 haproxy[15843]: Proxy pg_readwrite started. Jun 8 16:53:11 ip-172-31-77-193 haproxy[15843]: Proxy pg_readonly started. Jun 8 16:53:11 ip-172-31-77-193 haproxy[15843]: Proxy pg_readonly started. Jun 8 16:53:11 ip-172-31-77-193 haproxy[15843]: [NOTICE] 158/165311 (15843) : New worker #1 (15847) forked Jun 8 16:35:12 ip-172-31-77-193 haproxy[15705]: Server pg_readwrite/tstshd02 is DOWN, reason: Layer7 wrong status, code: 503, info: "Service Unavailable", check duration: 1ms. 1 active and 0 backup servers left. 0 sessions active, 0 requeued, 0 remaining in queue. Jun 8 16:53:13 ip-172-31-77-193 haproxy[15847]: Server pg_readwrite/tstshd02 is DOWN, reason: Layer7 wrong status, code: 503, info: "Service Unavailable", check duration: 2ms. 1 active and 0 backup servers left. 0 sessions active, 0 requeued, 0 remaining in queue. Jun 8 16:53:13 ip-172-31-77-193 haproxy[15843]: [WARNING] 158/165313 (15847) : Server pg_readwrite/tstshd02 is DOWN, reason: Layer7 wrong status, code: 503, info: "Service Unavailable", check duration: 2ms. 1 active and 0 backup servers left. 0 sessions active, 0 requeued, 0 remaining in queue. Jun 8 16:53:13 ip-172-31-77-193 haproxy[15847]: Server pg_readwrite/tstshd02 is DOWN, reason: Layer7 wrong status, code: 503, info: "Service Unavailable", check duration: 2ms. 1 active and 0 backup servers left. 0 sessions active, 0 requeued, 0 remaining in queue. Jun 8 16:53:25 ip-172-31-77-193 haproxy[15847]: 172.31.77.187:51070 [08/Jun/2021:16:53:20.487] pg_ingress pg_readonly/tstshd01 5001/0/5010 430 -- 1/1/0/0/0 0/0 Jun 8 16:53:34 ip-172-31-77-193 haproxy[15847]: 172.31.77.187:51084 [08/Jun/2021:16:53:29.840] pg_ingress pg_readonly/tstshd02 5003/0/5011 429 -- 1/1/0/0/0 0/0 Jun 8 16:53:45 ip-172-31-77-193 haproxy[15847]: 172.31.77.187:51096 [08/Jun/2021:16:53:40.112] pg_ingress pg_readonly/tstshd01 5001/0/5002 430 -- 1/1/0/0/0 0/0 Jun 8 16:53:11 ip-172-31-77-193 haproxy[15694]: [WARNING] 158/165311 (15694) : Exiting Master process... Jun 8 16:53:11 ip-172-31-77-193 haproxy[15694]: [NOTICE] 158/165311 (15694) : haproxy version is 2.2.14-1ppa1~bionic Jun 8 16:53:11 ip-172-31-77-193 haproxy[15694]: [NOTICE] 158/165311 (15694) : path to executable is /usr/sbin/haproxy Jun 8 16:53:11 ip-172-31-77-193 haproxy[15694]: [ALERT] 158/165311 (15694) : Current worker #1 (15705) exited with code 143 (Terminated) Jun 8 16:53:11 ip-172-31-77-193 haproxy[15694]: [WARNING] 158/165311 (15694) : All workers exited. Exiting... (0) Jun 8 16:53:11 ip-172-31-77-193 haproxy[15843]: Proxy HAProxy-Statistics started. Jun 8 16:53:11 ip-172-31-77-193 haproxy[15843]: Proxy HAProxy-Statistics started. Jun 8 16:53:11 ip-172-31-77-193 haproxy[15843]: Proxy pg_ingress started. Jun 8 16:53:11 ip-172-31-77-193 haproxy[15843]: Proxy pg_ingress started. Jun 8 16:53:11 ip-172-31-77-193 haproxy[15843]: Proxy pg_readwrite started. Jun 8 16:53:11 ip-172-31-77-193 haproxy[15843]: Proxy pg_readwrite started. Jun 8 16:53:11 ip-172-31-77-193 haproxy[15843]: Proxy pg_readonly started. Jun 8 16:53:11 ip-172-31-77-193 haproxy[15843]: Proxy pg_readonly started. Jun 8 16:53:11 ip-172-31-77-193 haproxy[15843]: [NOTICE] 158/165311 (15843) : New worker #1 (15847) forked Jun 8 16:35:12 ip-172-31-77-193 haproxy[15705]: Server pg_readwrite/tstshd02 is DOWN, reason: Layer7 wrong status, code: 503, info: "Service Unavailable", check duration: 1ms. 1 active and 0 backup servers left. 0 sessions active, 0 requeued, 0 remaining in queue. Jun 8 16:53:13 ip-172-31-77-193 haproxy[15847]: Server pg_readwrite/tstshd02 is DOWN, reason: Layer7 wrong status, code: 503, info: "Service Unavailable", check duration: 2ms. 1 active and 0 backup servers left. 0 sessions active, 0 requeued, 0 remaining in queue. Jun 8 16:53:13 ip-172-31-77-193 haproxy[15843]: [WARNING] 158/165313 (15847) : Server pg_readwrite/tstshd02 is DOWN, reason: Layer7 wrong status, code: 503, info: "Service Unavailable", check duration: 2ms. 1 active and 0 backup servers left. 0 sessions active, 0 requeued, 0 remaining in queue. Jun 8 16:53:13 ip-172-31-77-193 haproxy[15847]: Server pg_readwrite/tstshd02 is DOWN, reason: Layer7 wrong status, code: 503, info: "Service Unavailable", check duration: 2ms. 1 active and 0 backup servers left. 0 sessions active, 0 requeued, 0 remaining in queue. Jun 8 16:53:25 ip-172-31-77-193 haproxy[15847]: 172.31.77.187:51070 [08/Jun/2021:16:53:20.487] pg_ingress pg_readonly/tstshd01 5001/0/5010 430 -- 1/1/0/0/0 0/0 Jun 8 16:53:34 ip-172-31-77-193 haproxy[15847]: 172.31.77.187:51084 [08/Jun/2021:16:53:29.840] pg_ingress pg_readonly/tstshd02 5003/0/5011 429 -- 1/1/0/0/0 0/0 Jun 8 16:53:45 ip-172-31-77-193 haproxy[15847]: 172.31.77.187:51096 [08/Jun/2021:16:53:40.112] pg_ingress pg_readonly/tstshd01 5001/0/5002 430 -- 1/1/0/0/0 0/0 From: Lukas Tribus <[email protected]> Sent: Tuesday, June 8, 2021 11:33 AM To: Godfrin, Philippe E <[email protected]> Cc: [email protected]; [email protected] Subject: Re: [EXTERNAL] Re: built in ACL, REQ_CONTENT Use caution when interacting with this [EXTERNAL] email! Hello, On Tue, 8 Jun 2021 at 17:36, Godfrin, Philippe E <[email protected]> wrote: > > Certainly, > > Postrgres sends this message across the wire: > > Jun 2 21:14:40 ip-172-31-77-193 haproxy[9031]: #0110x000000: 00 00 00 4c 00 > 03 00 00 75 73 65 72 00 74 73 64 |...L....user.tsd| > Jun 2 21:14:40 ip-172-31-77-193 haproxy[9031]: #0110x000010: 62 00 64 61 74 > 61 62 61 73 65 00 74 73 64 62 00 |b.database.tsdb.| > Jun 2 21:14:40 ip-172-31-77-193 haproxy[9031]: #0110x000020: 61 70 70 6c 69 > 63 61 74 69 6f 6e 5f 6e 61 6d 65 |application_name| > Jun 2 21:14:40 ip-172-31-77-193 haproxy[9031]: #0110x000030: 00 70 73 71 6c > 00 63 6c 69 65 6e 74 5f 65 6e 63 |.psql.client_enc| > Jun 2 21:14:40 ip-172-31-77-193 haproxy[9031]: #0110x000040: 6f 64 69 6e 67 > 00 55 54 46 38 00 00 |oding.UTF8..| > > > > Bytes, 8 – are user\0 Byte 13 starts the userid. I would like to be able to > test that userid and make a routing decision on that. This is what the > HAProxy docs suggest: > > > > acl check-rw req.payload(8,32),hex -m sub 757365720074736462727700 And don't see how this is supposed to match? 62727700 is not what it's in your trace. Is the username tsdb, like in your trace, or is it tsdbrw, like in your ACL? Also, put a "tcp-request inspect-delay 5s" in front of the ACL (you can optimize performance later) and share the entire configuration. Please try to ask the actual question directly next time, so we can help you right away (https://xyproblem.info/). Thanks, Lukas
