Haproxy mysql-check user removal - Source

Revision: 108269
User: Davuluri.Ajay Ajay
Date: 2021-07-12 07:24

HI ,
I am using the galera cluster and haproxy mysql-check with the user, I have a security issue by using the user. so we have to remove the user in the mysql check

##############################################

Maria DB
##############################################

listen mariadb
bind 127.0.0.1:13306
mode tcp
option tcplog
option tcpka
option mysql-check user haproxy
timeout connect 30s
timeout client 28800s
timeout server 28800s
stick-table type ip size 1m expire 2m
stick on src
balance static-rr
server mariadb-2 pilot-2:3306 check inter 2000 rise 2 fall 3 on-marked-down shutdown-sessions
server mariadb-0 pilot-0:3306 check inter 2000 backup rise 2 fall 3 on-marked-down shutdown-sessions

so i remove the haproxy user from the option mysql-check user haproxy , and i stopped the haproxy user creation in the mairadb after that my cluster is not starting getting continuous errors in the logs,

so can you help here whether can we use the mysql check without user or not .

error logs
2021-06-30T03:41:34.706749+05:30, err, , journal:2021-06-30 3:41:34 55571 [Warning] Aborted connection 55571 to db: ‘unconnected’ user: ‘unauthenticated’ host: ‘192.168.2.22’ (This connection closed normally without authentication)
2021-06-30T03:41:34.750783+05:30, err, , journal:2021-06-30 3:41:34 55572 [Warning] Aborted connection 55572 to db: ‘unconnected’ user: ‘unauthenticated’ host: ‘192.168.2.20’ (This connection closed normally without authentication)
2021-06-30T03:41:36.495067+05:30, notice, , crmd[21820]: notice: State transition S_IDLE → S_POLICY_ENGINE
2021-06-30T03:41:36.534755+05:30, err, , journal:2021-06-30 3:41:36 55573 [Warning] Aborted connection 55573 to db: ‘unconnected’ user: ‘unauthenticated’ host: ‘192.168.2.21’ (This connection closed normally without authentication)
2021-06-30T03:41:36.543678+05:30, notice, , pengine[21819]: notice: Clearing failure of MsFwManager-0 on pilot-0 because it expired
2021-06-30T03:41:36.544628+05:30, notice, , pengine[21819]: notice: Clearing failure of MsFwManager-0 on pilot-0 because it expired
2021-06-30T03:41:36.545062+05:30, notice, , pengine[21819]: notice: Re-initiated expired calculated failure MsFwManager-0_monitor_10000 (rc=7, magic=0:7;39:2536:0:4e2ef0fb-8062-4e82-8594-83fffefc830a) on pilot-0
2021-06-30T03:41:36.546104+05:30, notice, , pengine[21819]: notice: Clearing failure of MsFwManager-0 on pilot-0 because it expired
2021-06-30T03:41:36.548351+05:30, notice, , pengine[21819]: notice: Clearing failure of MsFwManager-1 on pilot-1 because it expired
2021-06-30T03:41:36.549173+05:30, notice, , pengine[21819]: notice: Clearing failure of MsFwManager-1 on pilot-1 because it expired
2021-06-30T03:41:36.549598+05:30, notice, , pengine[21819]: notice: Re-initiated expired calculated failure MsFwManager-1_monitor_10000 (rc=7, magic=0:7;45:2537:0:4e2ef0fb-8062-4e82-8594-83fffefc830a) on pilot-1
2021-06-30T03:41:36.550344+05:30, notice, , pengine[21819]: notice: Clearing failure of MsFwManager-1 on pilot-1 because it expired
2021-06-30T03:41:36.552722+05:30, notice, , pengine[21819]: notice: Clearing failure of MsFwManager-2 on pilot-2 because it expired
2021-06-30T03:41:36.553473+05:30, notice, , pengine[21819]: notice: Clearing failure of MsFwManager-2 on pilot-2 because it expired
2021-06-30T03:41:36.553881+05:30, notice, , pengine[21819]: notice: Re-initiated expired calculated failure MsFwManager-2_monitor_10000 (rc=7, magic=0:7;15:2537:0:4e2ef0fb-8062-4e82-8594-83fffefc830a) on pilot-2
2021-06-30T03:41:36.554644+05:30, notice, , pengine[21819]: notice: Clearing failure of MsFwManager-2 on pilot-2 because it expired
2021-06-30T03:41:36.561269+05:30, notice, , pengine[21819]: notice: * Start oam_migrate_ip ( pilot-0 )
2021-06-30T03:41:36.561822+05:30, notice, , pengine[21819]: notice: * Start oam_migrate_ip_internal ( pilot-0 )
2021-06-30T03:41:36.562162+05:30, notice, , pengine[21819]: notice: * Start psqlserver ( pilot-0 )
2021-06-30T03:41:36.562429+05:30, notice, , pengine[21819]: notice: * Start sdmeserver ( pilot-0 )
2021-06-30T03:41:36.562755+05:30, notice, , pengine[21819]: notice: * Start webserver ( pilot-0 )
2021-06-30T03:41:36.563030+05:30, notice, , pengine[21819]: notice: * Start MsFwManager-0 ( pilot-0 )
2021-06-30T03:41:36.563298+05:30, notice, , pengine[21819]: notice: * Start MsFwManager-1 ( pilot-1 )
2021-06-30T03:41:36.563569+05:30, notice, , pengine[21819]: notice: * Start MsFwManager-2 ( pilot-2 )
2021-06-30T03:41:36.565701+05:30, notice, , pengine[21819]: notice: Calculated transition 2544, saving inputs in /var/lib/pacemaker/pengine/pe-input-2532.bz2
2021-06-30T03:41:36.567164+05:30, notice, , crmd[21820]: notice: Initiating start operation MsFwManager-0_start_0 locally on pilot-0
2021-06-30T03:41:36.582334+05:30, notice, , crmd[21820]: notice: Transition aborted by deletion of lrm_rsc_op[@id=‘MsFwManager-0_last_failure_0’]: Resource operation removal
2021-06-30T03:41:36.695142+05:30, info, , docker(MsFwManager-0)[29521]:INFO: starting existing container MsFwManager-0.
2021-06-30T03:41:36.708785+05:30, err, , journal:2021-06-30 3:41:36 55574 [Warning] Aborted connection 55574 to db: ‘unconnected’ user: ‘unauthenticated’ host: ‘192.168.2.22’ (This connection closed normally without authentication)
2021-06-30T03:41:36.754093+05:30, err, , journal:2021-06-30 3:41:36 55575 [Warning] Aborted connection 55575 to db: ‘unconnected’ user: ‘unauthenticated’ host: ‘192.168.2.20’ (This connection closed normally without authentication)
2021-06-30T03:41:36.832213+05:30, info, , systemd:Started libcontainer container 1e3bcac36196af015085425d8efb5472764ebc6c7d8cb32cd96debe9ff41e147.
2021-06-30T03:41:36.853550+05:30, warning, , kernel:SELinux: mount invalid. Same superblock, different security settings for (dev mqueue, type mqueue)
2021-06-30T03:41:37.241254+05:30, err, , journal:Picked up JAVA_TOOL_OPTIONS: -Xmx1g
2021-06-30T03:41:37.246661+05:30, info, , docker(MsFwManager-0)[29521]:INFO: MsFwManager-0
2021-06-30T03:41:37.305743+05:30, notice, , docker(MsFwManager-0)[29521]:NOTICE: Container MsFwManager-0 started successfully
2021-06-30T03:41:37.311599+05:30, notice, , crmd[21820]: notice: Result of start operation for MsFwManager-0 on pilot-0: 0 (ok)
2021-06-30T03:41:37.314201+05:30, notice, , crmd[21820]: notice: Transition 2544 (Complete=9, Pending=0, Fired=0, Skipped=2, Incomplete=19, Source=/var/lib/pacemaker/pengine/pe-input-2532.bz2): Stopped
2021-06-30T03:41:37.352421+05:30, notice, , pengine[21819]: notice: * Start oam_migrate_ip ( pilot-0 )
2021-06-30T03:41:37.352784+05:30, notice, , pengine[21819]: notice: * Start oam_migrate_ip_internal ( pilot-0 )
2021-06-30T03:41:37.353047+05:30, notice, , pengine[21819]: notice: * Start psqlserver ( pilot-0 )
2021-06-30T03:41:37.353284+05:30, notice, , pengine[21819]: notice: * Start sdmeserver ( pilot-0 )
2021-06-30T03:41:37.353571+05:30, notice, , pengine[21819]: notice: * Start webserver ( pilot-0 )
2021-06-30T03:41:37.353836+05:30, notice, , pengine[21819]: notice: * Start MsFwManager-1 ( pilot-1 )
2021-06-30T03:41:37.354085+05:30, notice, , pengine[21819]: notice: * Start MsFwManager-2 ( pilot-2 )
2021-06-30T03:41:37.354345+05:30, notice, , pengine[21819]: notice: Calculated transition 2545, saving inputs in /var/lib/pacemaker/pengine/pe-input-2533.bz2
2021-06-30T03:41:37.356061+05:30, notice, , crmd[21820]: notice: Initiating monitor operation MsFwManager-0_monitor_10000 locally on pilot-0
2021-06-30T03:41:37.360047+05:30, notice, , crmd[21820]: notice: Initiating start operation MsFwManager-1_start_0 on pilot-1
2021-06-30T03:41:38.103477+05:30, notice, , crmd[21820]: notice: Initiating monitor operation MsFwManager-1_monitor_10000 on pilot-1
2021-06-30T03:41:38.103818+05:30, notice, , crmd[21820]: notice: Initiating start operation MsFwManager-2_start_0 on pilot-2
2021-06-30T03:41:38.414769+05:30, info, , journal:
2021-06-30T03:41:38.415215+05:30, info, , journal: . ____ _ __ _ _
2021-06-30T03:41:38.415567+05:30, info, , journal: /\ / ’ __ _ () __ __ _ \ \ \
2021-06-30T03:41:38.415877+05:30, info, , journal:( ( )__ | '_ | '| | ’ / ` | \ \ \
2021-06-30T03:41:38.416183+05:30, info, , journal: \/ )| |)| | | | | || (| | ) ) ) )
2021-06-30T03:41:38.416501+05:30, info, , journal: ’ || .__|| ||| |_, | / / / /
2021-06-30T03:41:38.416813+05:30, info, , journal: =========||==============|/=////
2021-06-30T03:41:38.419103+05:30, info, , journal: :: Spring Boot :: (v1.5.1.RELEASE)
2021-06-30T03:41:38.419476+05:30, info, , journal:
2021-06-30T03:41:38.537137+05:30, err, , journal:2021-06-30 3:41:38 55576 [Warning] Aborted connection 55576 to db: ‘unconnected’ user: ‘unauthenticated’ host: ‘192.168.2.21’ (This connection closed normally without authentication)
2021-06-30T03:41:38.543359+05:30, info, , journal:2021-06-29 22:11:38.541 INFO 8 — [ main] m.ServiceManager : Starting ServiceManager on LATEST-SDLA-0000-OPE-VM with PID 8 (/usr/sdme/bin/MsFwManager.jar started by sdmexpert in /)
2021-06-30T03:41:38.546473+05:30, info, , journal:2021-06-29 22:11:38.545 INFO 8 — [ main] m.ServiceManager : No active profile set, falling back to default profiles: default
2021-06-30T03:41:38.632668+05:30, info, , journal:2021-06-29 22:11:38.631 INFO 8 — [ main] ationConfigEmbeddedWebApplicationContext : Refreshing org.springframework.boot.context.embedded.AnnotationConfigEmbeddedWebApplicationContext@1bc6a36e: startup date [Tue Jun 29 22:11:38 UTC 2021]; root of context hierarchy
2021-06-30T03:41:38.711111+05:30, err, , journal:2021-06-30 3:41:38 55577 [Warning] Aborted connection 55577 to db: ‘unconnected’ user: ‘unauthenticated’ host: ‘192.168.2.22’ (This connection closed normally without authentication)
2021-06-30T03:41:38.756221+05:30, err, , journal:2021-06-30 3:41:38 55578 [Warning] Aborted connection 55578 to db: ‘unconnected’ user: ‘unauthenticated’ host: ‘192.168.2.20’ (This connection closed normally without authentication)
2021-06-30T03:41:38.843149+05:30, notice, , crmd[21820]: notice: Initiating monitor operation MsFwManager-2_monitor_10000 on pilot-2
2021-06-30T03:41:38.843585+05:30, notice, , crmd[21820]: notice: Initiating start operation oam_migrate_ip_start_0 locally on pilot-0
2021-06-30T03:41:38.921636+05:30, info, , IPaddr2(oam_migrate_ip)[29783]:INFO: Adding inet address 100.69.146.15/24 with broadcast address 100.69.146.255 to device eth0
2021-06-30T03:41:38.931806+05:30, info, , IPaddr2(oam_migrate_ip)[29783]:INFO: Bringing device eth0 up

Answer

mysql-check requires a user. This is the answer given by the [[https://discourse.haproxy.org/t/haproxy-mysql-check-user-removal/6685|haproxy people too]].

Per haproxy docs and [[https://stackoverflow.com/questions/57786879/haproxy-with-mysql-check-option-not-working|other answers]] there is no password and setting one causes the check to fail. Because there is no password PASSWORD EXIRE options cannot be used. ACCOUNT LOCK I also checked isn't compatible with the haproxy mysql-check.

The user that haproxy uses does not need any access to anything. This is the default for a created user. Per [[https://cbonte.github.io/haproxy-dconv/2.5/configuration.html#4-option%20mysql-check|the ha-proxy documentation]] it doesn't even execute a query and aborts very soon into the protocol.

What could be done is a constraint of the user to the network of the ha-proxy and apply resource constraints.

<<code>>
CREATE USER 'haproxycheck'@'192.168.2.20/255.255.255.252'   WITH MAX_QUERIES_PER_HOUR 1 MAX_UPDATES_PER_HOUR 0  MAX_STATEMENT_TIME 0.0000000001;
<</code>>

Any risk based assessment with a basic understanding of haproxy and mariadb and networking shouldn't have a problem here.

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.

Haproxy mysql-check user removal - Source

Answer

Products

Services

Pricing

Resources

About Us

Download MariaDB