Selecting backend based on jdbc string connection

Help!
1

Hello,
I need to select a backend based on jdbc string connection.

jdbc:postgresql://localhost:443/mydb

For example, if the jdbc connection contains postgresql the request must be redirected to the corresponding backend. I tried to capture content with req.payload but I got nothing. I think this is because one of these thee options:

  • The jdbc driver encrypts the credentials by default and not way to see it.
  • I am working in the wrong tcp layer.
  • I am trying to capture content when there is not content in this type of request.
    The script is this:
global
    log /dev/log local0
    log localhost local1 notice
    maxconn 2000
    daemon

defaults
    log global
    mode tcp
    timeout connect 5000
    timeout client 50000
    timeout server 50000


frontend tcp-in
    mode tcp
    bind *:443
    tcp-request inspect-delay 1s
    log-format %[capture.req.hdr(0)]

    tcp-request content capture req.payload(0,0) len 100
    tcp-request content capture req.len len 100
    tcp-request content accept if WAIT_END
    acl postgresql req.payload(0,0) -m sub postgresql
    use_backend postgresql if postgresql
    acl virtuoso req.payload(0,0) -m sub virtuoso
    use_backend virtuoso if virtuoso

backend postgresql
    mode tcp
    option pgsql-check user postgres
    server postgresql postgresql:5432 check

backend virtuoso
    mode tcp
    server virtuoso virtuoso:1111 check

Any idea how to overcome this?

Thanks in advance

2

How did you come to the conclusion that postgresql protocol sends the database name in the beginning of the handshake - on offset 0? Is that a fact or an assumption?

I find it highly unlikely, given also that the pgsql-check implemented in haproxy requires a user but doesn’t require a database name.

3

it was an assumption. There is not way to check database name in the frontend? so, it has to be done in the backend like depicted in this example?

frontend fr_pg
	mode tcp
	bind *:5431
	default_backend bk_pg

backend bk_pg
	option tcp-check
	tcp-check connect

	#
	# write: startup message
	#
	# startup message params:
	# user: web
	# database: template1
	#
	tcp-check send-binary 00000025 # packet length
	tcp-check send-binary 00030000 # protocol version
	tcp-check send-binary 7573657200 # "user"
	tcp-check send-binary 77656200 # "web"

Source
https://gist.github.com/arkady-emelyanov/af2993ab242f9a1ec0427159434488c4

4

The example is just a configuration making health checks look like real PSQL startup messages (by artificially constructing them). It does not read or match anything.

You can match in the frontend whatever you want, however finding exactly what you are looking for is something that can be complex, depending where you need to look for it.

In the postgresql startup message for example, the username is send first, so the offset in the packet where you can find the database name is different based on the username length.

The following works, but requires some low-level tinkering:

First, transform your database name in hex, without spaces and with uppercase letters:

lukas@dev:~/haproxy$ echo -n "virtuoso" | od -A n -t x1 | sed 's/ //g' | tr '[:lower:]' '[:upper:]'
76697274756F736F
lukas@dev:~/haproxy$

Postgresql will send null terminated strings like this:
user\0actualusername\0database\0actualdatabase\0\0

You can access the packet payload and basically look for database\0virtuoso\0 by concatenating database\0 (646174616261736500) with the hex representation of your database name virtuoso (76697274756F736F) and another null byte (00).

Frontend would look like this (please also replace the tcp-request content rules, as the following code properly waits for the entire message to be terminated - instead of artificially delaying all database connections for 1 second like in your case):

 # check if the payload is terminated with two null bytes
 # (skipping static packet length and procotol version headers)
 acl pgsql_msg_termination req.payload(8,0),hex -m end 0000

 tcp-request inspect-delay 5s
 tcp-request content accept if pgsql_msg_termination

 acl postgresql req.payload(8,0),hex -m sub 646174616261736500706F737467726573716C00
 use_backend postgresql if postgresql
 
 acl virtuoso req.payload(8,0),hex -m sub 64617461626173650076697274756F736F00
 use_backend virtuoso if virtuoso
5

Thanks very much. Just one question more. May you tell me how can I capture packets like the one you got for postgresql.

user\0actualusername\0database\0actualdatabase\0\0

Do you output the tcp stream in the haproxy log? if so, how? because I know I cannot use this one

log-format %[capture.req.hdr(0)]
6

Hi,
I have a similar issue. I need to decide backend based on username in connection string. Did this get solved?
I want to log the tcp payload in HAProxy when trying to connect to Sybase IQ to write ACL accordingly… However when I use log-format %[capture.req.hdr(0)] -> it logs junk values.