The Ultimate Guide to High Availability with MariaDB
This is a read-only copy of the MariaDB Knowledgebase generated on 2024-11-16. For the latest, interactive version please visit https://mariadb.com/kb/.

Haproxy mysql-check user removal

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 Answered by Daniel Black in this comment.

mysql-check requires a user. This is the answer given by the haproxy people too.

Per haproxy docs and 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 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.

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;

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.