haproxy
haproxy copied to clipboard
haproxy
Support more PostgreSQL authentication methods (not just TRUST)
Detailed Description of the Problem
HAProxy is unable to connect PostgreSQL if authentication method is not TRUST.
When /etc/postgresql/14/main/pg_hba.conf contains host all pguser 0.0.0.0/0 scram-sha-256
and run: psql -h 192.168.1.1 -U pguser
The error message is as follows. psql: error: connection to server at "192.168.1.1", port 5432 failed: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.
However, the database can be connected directly without using HAProxy. e.g. run: psql -h 192.168.1.21 -U pgcheck
if /etc/postgresql/14/main/pg_hba.conf contains instead host all pguser 0.0.0.0/0 trust
it can connect successfully via both haproxy and psql.
Expected Behavior
The HAProxy expected behavior should be the same as connecting to database directly.
Steps to Reproduce the Behavior
Switch the config of /etc/postgresql/14/main/pg_hba.conf between host all pguser 0.0.0.0/0 scram-sha-256 and host all pguser 0.0.0.0/0 trust
and the haproxy.cfg can be similar to this listen pgcluster bind *:5432 mode tcp timeout client 30h timeout server 30h timeout connect 10s balance leastconn option tcplog option pgsql-check user pguser default-server inter 10s fall 5 rise 10 server pg01 192.168.x.x:5432 weight 1 check server pg02 192.168.x.x:5432 weight 3 check
Do you have any idea what may have caused this?
HAProxy may not support PostgreSQL authentication method other than TRUST.
Do you have an idea how to solve the issue?
No response
What is your configuration?
listen pgcluster
bind *:5432
mode tcp
timeout client 30h
timeout server 30h
timeout connect 10s
balance leastconn
option tcplog
option pgsql-check user pguser
default-server inter 10s fall 5 rise 10
server pg01 192.168.x.x:5432 weight 1 check
server pg02 192.168.x.x:5432 weight 3 check
Output of haproxy -vv
HAProxy version 2.4.10-1ppa1~focal 2021/12/23 - https://haproxy.org/
Status: long-term supported branch - will stop receiving fixes around Q2 2026.
Known bugs: http://www.haproxy.org/bugs/bugs-2.4.10.html
Running on: Linux 5.4.0-65-generic #73-Ubuntu SMP Mon Jan 18 17:25:17 UTC 2021 x86_64
Build options :
TARGET = linux-glibc
CPU = generic
CC = cc
CFLAGS = -O2 -g -O2 -fdebug-prefix-map=/build/haproxy-5eivBV/haproxy-2.4.10=. -fstack-protector-strong -Wformat -Werror=format-security -Wdate-time -D_FORTIFY_SOURCE=2 -Wall -Wextra -Wdeclaration-after-statement -fwrapv -Wno-address-of-packed-member -Wno-unused-label -Wno-sign-compare -Wno-unused-parameter -Wno-clobbered -Wno-missing-field-initializers -Wno-cast-function-type -Wtype-limits -Wshift-negative-value -Wshift-overflow=2 -Wduplicated-cond -Wnull-dereference
OPTIONS = USE_PCRE2=1 USE_PCRE2_JIT=1 USE_OPENSSL=1 USE_LUA=1 USE_SLZ=1 USE_SYSTEMD=1 USE_PROMEX=1
DEBUG =
Feature list : +EPOLL -KQUEUE +NETFILTER -PCRE -PCRE_JIT +PCRE2 +PCRE2_JIT +POLL -PRIVATE_CACHE +THREAD -PTHREAD_PSHARED +BACKTRACE -STATIC_PCRE -STATIC_PCRE2 +TPROXY +LINUX_TPROXY +LINUX_SPLICE +LIBCRYPT +CRYPT_H +GETADDRINFO +OPENSSL +LUA +FUTEX +ACCEPT4 -CLOSEFROM -ZLIB +SLZ +CPU_AFFINITY +TFO +NS +DL +RT -DEVICEATLAS -51DEGREES -WURFL +SYSTEMD -OBSOLETE_LINKER +PRCTL -PROCCTL +THREAD_DUMP -EVPORTS -OT -QUIC +PROMEX -MEMORY_PROFILING
Default settings :
bufsize = 16384, maxrewrite = 1024, maxpollevents = 200
Built with multi-threading support (MAX_THREADS=64, default=8).
Built with OpenSSL version : OpenSSL 1.1.1f 31 Mar 2020
Running on OpenSSL version : OpenSSL 1.1.1f 31 Mar 2020
OpenSSL library supports TLS extensions : yes
OpenSSL library supports SNI : yes
OpenSSL library supports : TLSv1.0 TLSv1.1 TLSv1.2 TLSv1.3
Built with Lua version : Lua 5.3.3
Built with the Prometheus exporter as a service
Built with network namespace support.
Built with libslz for stateless compression.
Compression algorithms supported : identity("identity"), deflate("deflate"), raw-deflate("deflate"), gzip("gzip")
Built with transparent proxy support using: IP_TRANSPARENT IPV6_TRANSPARENT IP_FREEBIND
Built with PCRE2 version : 10.34 2019-11-21
PCRE2 library supports JIT : yes
Encrypted password support via crypt(3): yes
Built with gcc compiler version 9.3.0
Available polling systems :
epoll : pref=300, test result OK
poll : pref=200, test result OK
select : pref=150, test result OK
Total: 3 (3 usable), will use epoll.
Available multiplexer protocols :
(protocols marked as <default> cannot be specified using 'proto' keyword)
h2 : mode=HTTP side=FE|BE mux=H2 flags=HTX|CLEAN_ABRT|HOL_RISK|NO_UPG
fcgi : mode=HTTP side=BE mux=FCGI flags=HTX|HOL_RISK|NO_UPG
<default> : mode=HTTP side=FE|BE mux=H1 flags=HTX
h1 : mode=HTTP side=FE|BE mux=H1 flags=HTX|NO_UPG
<default> : mode=TCP side=FE|BE mux=PASS flags=
none : mode=TCP side=FE|BE mux=PASS flags=NO_UPG
Available services : prometheus-exporter
Available filters :
[SPOE] spoe
[CACHE] cache
[FCGI] fcgi-app
[COMP] compression
[TRACE] trace
Last Outputs and Backtraces
No response
Additional Information
No response
This will need someone with some pgsql knowledge to have a look I guess. But I suspect that we should turn this into a feature request to support a different authentication scheme for health-checks.
Thank you @wtarreau . As it is not only affecting the pgsql-check, functionality of load balancing is not working as well.
I don't see how this is possible, the load balancing does nothing specific at all, there's no pgsql support, it's pure TCP forwarding. I think that the problem you're seeing is simply that since the checks fail, the servers are down and the traffic cannot be sent to the servers.
Yes, the check fail and the communication from haproxy to pgsql closes immediately.
Thanks for confirming. I'm going to turn this into a feature request but I need some help to define it. What are the other authentication methods ? Or at least which one do you need to support ? Is there any dependency with certain pgqsl versions ? For example, is the current one being phased out, or is the new one only the default since a recent version ?
There are many authentication types in pgsql according to https://www.postgresql.org/docs/14/auth-methods. For password authentication, there are "scram-sha-256", "md5", and "password". Before the v10, pgsql uses md5 and cram-sha-256 was introduced since v10 and should be the default password authentication method since v13 and the md5 method cannot be used with the db_user_namespace feature.
Based on https://www.postgresql.org/docs/14/auth-password.html, to ease transition from the md5 method to the newer scram method, if md5 is specified as a method in pg_hba.conf but the user's password on the server is encrypted for scram, then scram-based authentication will automatically be chosen instead. Therefore, it is better to support both scram-sha-256 and md5 for compatibility in password authentication and the trust authentication can be kept to be supported for diagnosing problems. Thank you for your help.
taking into account the complexity of postgresql protocol, wouldn't it be better to implement proxy protocol support on postgresql side ?
https://www.postgresql.org/message-id/20190519155903.GI6197%40tamriel.snowman.net
I confirm, a health check like option pgsql-check user foo fails on haproxy 2.5.1-86b093a if a) user foo exists in the db cluster and b) has a SCRAM-SHA-256 password configured (as opposed to md5).
It is good that the existence of a user in db is not required for the pgsql-check, so as a workaround you can use some nonexistent user.
This used to work in haproxy 1.9 and is broken since ce355074f1fc76102753cc588d17e2530b4f31a0. I see an attempt to fix it by making the regex more complex in commit history, however it didn't seem to help. And frankly, I failed to come up with a full regex that worked well across all auth methods.
However, simplifying it to something like:
- chk = parse_tcpcheck_expect((char *[]){"tcp-check", "expect", "rbinary", "^52000000(08|0A|0C)000000(00|02|03|04|05|06)",
+ chk = parse_tcpcheck_expect((char *[]){"tcp-check", "expect", "rbinary", "^52000000",
does help - basically checking just that we received a common prefix across all Authentication* protocol messages (this is what haproxy 1.9 and older checked for).
It depends on how strict we want to be in terms of validation, but PG won't send this prefix in any other situation AFAICS, so it should be enough.
A fix was merged. The issue should be fixed now. Note the purpose of this health-check is to validate the database is working, not to validate the user authentication. Thus, the commit message is not accurate. It fully fix the issue.
Hi! Thanks for merging the fix. When and which version do we expect it to be released? Thanks.
The fix should be backported as far as 2.2. I will work on the backports this afternoon I guess, or the next monday. No new releases are planned for now. I must manage all backports first.