MySQL ‐ Connect Access denied for user 'root'@'localhost' (using password: NO) 문제 해결부터 권한 축소까지
MySQL의 general log를 확인하던 중 10초 주기마다, Connect Access denied for user 'root'@'localhost' (using password: NO) 라는 메시지가 추가되는 것이 확인되었다.
분명 MYSQL 도커 이미지에서 제공하는 MYSQL_ROOT_PASSWORD를 환경 변수를 통해 비밀번호를 설정했고 다른 General log에서 SQL문을 확인해봤을 때 root 계정이 잘못돼서 문제가 생긴 것은 아닌 거 같았다.
무엇이 문제인지 고민을 해봤는데, 10초 주기로 MySQL에서 정기적인 작업을 시킨 게 있었는지 찾아본 결과 MySQL 컨테이너가 원활하게 동작하고 있는지 체크하기 위해, mysqladmin을 활용한 헬스 체크를 10초 간격으로 실행하고 있다는 것을 깨달았다.
그리고 직접 MySQL의 컨테이너에 들어가서 mysqladmin 명령어를 실행해보았고 똑같은 메시지를 확인할 수 있었다.
여기서 한 가지 의문점이 들었다. 지금까지 MySQL 서버는 동작 중에 오류로 인해 꺼진 적이 없었다. 재시도 또한 한 적이 없었다. 하지만 헬스 체크의 결과물로 봤을 때는 20초 대기 + 10초 간격으로 5번(50초) 이므로 70초 뒤에 MySQL 서버가 재시작되어야 했다. 하지만 그러지 않았다.
때문에 mysqladmin ping 명령어가 왜 이러한 경우에도 컨테이너가 정상적으로 동작하고 있는지 판단한 이유가 궁금했고 MySQL 사이트에서 답을 찾을 수 있었다.
[MySQL :: MySQL 8.4 Reference Manual :: 6.5.2 mysqladmin — A MySQL Server Administration Program](https://dev.mysql.com/doc/refman/8.4/en/mysqladmin.html)
해석해보면 mysqladmin ping 명령어를 입력했을 때 서버가 실행되고 있다면 0을 반환하는데, 0을 반환하는 경우는 Access denied일 때를 포함한다고 한다. 그 이유는 MySQL 연결이 거부당한거 뿐이지 서버는 정상적으로 실행하고 있기 때문이라고 나와있다.
그럼 이 문제를 어떻게 해결할까? mysqladmin에 -u와 -p 옵션을 통해 mysqladmin 명령어를 실행할 계정을 입력해 줄 수 있지만 나는 좀 더 명확하게 해결하고 싶었다. (-u랑 -p 옵션을 넣지 않고) 그래서 mysqladmin이 어떻게 MySQL 설정을 가져오는지 궁금해서 mysqladmin —help 명령어를 통해 찾아보았다.
확인해본 결과 my.cnf에 있는 설정을 기반으로 mysqladmin이 동작하고 있다는 것을 알아냈고, 그제서야 왜 root 계정으로 접속이 안된 것인지 깨달았다. root 계정의 비밀번호는 MYSQL_ROOT_PASSWORD 환경변수를 통해 등록되어 있으므로, cnf 파일에 적혀있지 않아 root 계정의 비밀번호를 알지 못했던 것이다.
그래서 cnf 파일에 root 계정의 비밀번호를 적을까 고민했지만, plain한 값으로 적혀지는 cnf 파일 특성 상 보안 위험 요소가 될 것이라고 판단하고 그러지 않고 명령어에 -p 옵션을 부여하는 것으로 해결헀다.
이후 정상적으로 root 계정에 접속하는 것을 확인할 수 있었다.
다만 직접 명령어를 쳤을 때 해당 경고문을 볼 수 있다.
CLI같은 곳에서 패스워드를 직접 입력하는 것은 보안상 위협이 될 수 있다고 한다.
그래서 이 문제를 해결하기 위해서 mysql_config_editor라는 것을 고려해보기로 했다. mysql_config_editor는 똑같이 cnf 파일을 만들어서 거기에 계정 정보를 주입하는 것이지만 실제로 유저가 해당 파일을 읽을 때는 난독화가 되어 있기 때문에 알아볼 수 없다는 장점이 있다.
그치만 MySQL Docker 이미지에는 경량화를 위해 mysql_config_editor 유틸리티 프로그램이 설치되어 있지 않다. 실제로 mysql_config_editor 명령어를 입력하면 command not found가 뜨는 것을 확인할 수 있다.
그래서 생각해본 것이 그냥 헬스 체크용으로 권한을 줄이고 비밀번호가 필요없는 계정을 만드는 것이었다.
AI 한테 물어보니 PROCESS라는 것을 통해 실제로 쿼리는 실행하지 못하게 하고, 서버 상태만 조회할 수 있도록 권한을 부여할 수 있다고 한다.
그래서 공식문서에 들어가서 찾아보았다.
[MySQL :: MySQL 8.4 Reference Manual :: 15.7.1.6 GRANT Statement]
확인해보니 PROCESS 권한은 해당 유저가 SHOW PROCESSLIST를 조회할 수 있도록 권한을 준다고 한다. 그거 외에는 다른 쿼리를 실행할 수 없기 때문에 이것을 최소 권한이라고 하는 것 같다. 하지만 권한을 정말 PROCESS를 줘야하는지 의문이 생겼고 깃허브에 있는 MYSQL Server 레포지토리를 분석해보았다.
[GitHub - mysql/mysql-server at 6b6d3ed3d5c6591b446276184642d7d0504ecc86]
실제로 mysqladmin에서 ping 명령어를 실행할 때 mysql_ping이라는 함수를 사용하고 그 함수에선 simple_command라는 함수를 호출하는데 거기에 COM_PING이라는 프로토콜을 호출한다. COM_PING 프로토콜은 인증된 계정일 경우 누구나 사용할 수 있으므로 PROCESS 권한만 가지고 있더라도 실행할 수 있는 것이다.
Mysql 서버를 통해 분석해본 mysqladmin ping 커맨드 실행 과정은 이렇다.
- mysqladmin은 mysql init을 실행해 mysql 객체를 만든다. 이때 mysql_real_connect 함수를 통해 mysql 커넥션과 mysql 일부 명령어를 실행할 수 있는 권한을 획득한다.
- 커넥션을 만들고 나서야 ping 커맨드를 실행하기 위해 내부에서는 mysql_ping 함수가 실행되며 내부적으로 mysql_simple_command 함수가 실행된다.
- mysql_simple_command 함수는 mysql 객체에 methods 프로퍼티 안에 있는 advanced_method 함수를 실행하게 되고
- cli_advance_method의 함수 인자 값으로 enum_server_command 타입이 들어가 있는데 그중 1417번째 행에
if (net_write_command(net, (uchar)command, header, header_length, arg, arg_length)) { set_mysql_error(mysql, CR_SERVER_GONE_ERROR, unknown_sqlstate); goto end;}에서 서버로 COM_PING 커맨드에 대해서 보내고 서버에서는 COM_PING 명령어를 실행하라고 요청을 처리하고 반환한다.
참고로 advanced_command에서 cli_advance_command로 바뀐 이유는 mysql의 methods 프로퍼티가 advanced_command로 cli_advanced_command를 사용하기 때문이다.
MySQL의 general log를 확인하던 중 10초 주기마다, Connect Access denied for user 'root'@'localhost' (using password: NO) 라는 메시지가 추가되는 것이 확인되었다.
분명 MYSQL 도커 이미지에서 제공하는 MYSQL_ROOT_PASSWORD를 환경 변수를 통해 비밀번호를 설정했고 다른 General log에서 SQL문을 확인해봤을 때 root 계정이 잘못돼서 문제가 생긴 것은 아닌 거 같았다.
무엇이 문제인지 고민을 해봤는데, 10초 주기로 MySQL에서 정기적인 작업을 시킨 게 있었는지 찾아본 결과 MySQL 컨테이너가 원활하게 동작하고 있는지 체크하기 위해, mysqladmin을 활용한 헬스 체크를 10초 간격으로 실행하고 있다는 것을 깨달았다.
그리고 직접 MySQL의 컨테이너에 들어가서 mysqladmin 명령어를 실행해보았고 똑같은 메시지를 확인할 수 있었다.
여기서 한 가지 의문점이 들었다. 지금까지 MySQL 서버는 동작 중에 오류로 인해 꺼진 적이 없었다. 재시도 또한 한 적이 없었다. 하지만 헬스 체크의 결과물로 봤을 때는 20초 대기 + 10초 간격으로 5번(50초) 이므로 70초 뒤에 MySQL 서버가 재시작되어야 했다. 하지만 그러지 않았다.
때문에 mysqladmin ping 명령어가 왜 이러한 경우에도 컨테이너가 정상적으로 동작하고 있는지 판단한 이유가 궁금했고 MySQL 사이트에서 답을 찾을 수 있었다.
MySQL :: MySQL 8.4 Reference Manual :: 6.5.2 mysqladmin — A MySQL Server Administration Program
6.5.2 mysqladmin — A MySQL Server Administration Program mysqladmin is a client for performing administrative operations. You can use it to check the server's configuration and current status, to create and drop databases, and more. Invoke mysqladmin li
dev.mysql.com 해석해보면 mysqladmin ping 명령어를 입력했을 때 서버가 실행되고 있다면 0을 반환하는데, 0을 반환하는 경우는 Access denied일 때를 포함한다고 한다. 그 이유는 MySQL 연결이 거부당한거 뿐이지 서버는 정상적으로 실행하고 있기 때문이라고 나와있다.
그럼 이 문제를 어떻게 해결할까? mysqladmin에 -u와 -p 옵션을 통해 mysqladmin 명령어를 실행할 계정을 입력해 줄 수 있지만 나는 좀 더 명확하게 해결하고 싶었다. (-u랑 -p 옵션을 넣지 않고) 그래서 mysqladmin이 어떻게 MySQL 설정을 가져오는지 궁금해서 mysqladmin —help 명령어를 통해 찾아보았다.
확인해본 결과 my.cnf에 있는 설정을 기반으로 mysqladmin이 동작하고 있다는 것을 알아냈고, 그제서야 왜 root 계정으로 접속이 안된 것인지 깨달았다. root 계정의 비밀번호는 MYSQL_ROOT_PASSWORD 환경변수를 통해 등록되어 있으므로, cnf 파일에 적혀있지 않아 root 계정의 비밀번호를 알지 못했던 것이다.
그래서 cnf 파일에 root 계정의 비밀번호를 적을까 고민했지만, plain한 값으로 적혀지는 cnf 파일 특성 상 보안 위험 요소가 될 것이라고 판단하고 그러지 않고 명령어에 -p 옵션을 부여하는 것으로 해결헀다.
이후 정상적으로 root 계정에 접속하는 것을 확인할 수 있었다.
그러나 직접 명령어를 쳤을 때 해당 경고문을 보게 되었다.
CLI같은 곳에서 패스워드를 직접 입력하는 것은 보안상 위협이 될 수 있다고 한다.
그래서 이 문제를 해결하기 위해서 mysql_config_editor라는 것을 고려해보기로 했다. mysql_config_editor는 똑같이 cnf 파일을 만들어서 거기에 계정 정보를 주입하는 것이지만 실제로 유저가 해당 파일을 읽을 때는 난독화가 되어 있기 때문에 알아볼 수 없다는 장점이 있다.
그치만 MySQL Docker 이미지에는 경량화를 위해 mysql_config_editor 유틸리티 프로그램이 설치되어 있지 않다. 실제로 mysql_config_editor 명령어를 입력하면 command not found가 뜨는 것을 확인할 수 있다.
그래서 생각해본 것이 그냥 헬스 체크용으로 권한을 줄이고 비밀번호가 필요없는 계정을 만드는 것이었다.
AI 한테 물어보니 PROCESS라는 것을 통해 실제로 쿼리는 실행하지 못하게 하고, 서버 상태만 조회할 수 있도록 권한을 부여할 수 있다고 한다.
그래서 공식문서에 들어가서 찾아보았다.
MySQL :: MySQL 8.4 Reference Manual :: 15.7.1.6 GRANT Statement
15.7.1.6 GRANT Statement GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ... ON [object_type] priv_level TO user_or_role [, user_or_role] ... [WITH GRANT OPTION] [AS user [WITH ROLE DEFAULT | NONE | ALL | ALL EXCEPT role [, role ] ... | role
dev.mysql.com 확인해보니 PROCESS 권한은 해당 유저가 SHOW PROCESSLIST를 조회할 수 있도록 권한을 준다고 한다. 그거 외에는 다른 쿼리를 실행할 수 없기 때문에 이것을 최소 권한이라고 하는 것 같다. 하지만 권한을 정말 PROCESS를 줘야하는지 의문이 생겼고 깃허브에 있는 MYSQL Server 레포지토리를 분석해보았다.
GitHub - mysql/mysql-server: MySQL Server, the world's most popular open source database, and MySQL Cluster, a real-time, open s
MySQL Server, the world's most popular open source database, and MySQL Cluster, a real-time, open source transactional database. - mysql/mysql-server
github.com 실제로 mysqladmin에서 ping 명령어를 실행할 때 mysql_ping이라는 함수를 사용하고 그 함수에선 simple_command라는 함수를 호출하는데 거기에 COM_PING이라는 프로토콜을 호출한다. COM_PING 프로토콜은 인증된 계정일 경우 누구나 사용할 수 있으므로 PROCESS 권한만 가지고 있더라도 실행할 수 있는 것이다.
MySQL: COM_PING
Check if the server is alive Payload TypeNameDescription int<1> command 0x0E: COM_PING ReturnsOK_Packet See alsomysql_ping, dispatch_command
dev.mysql.com
Mysql 서버를 통해 분석해본 mysqladmin ping 커맨드 실행 과정은 이렇다.
mysqladmin은 mysql init을 실행해 mysql 객체를 만든다. 이때 mysql_real_connect 함수를 통해 mysql 커넥션과 mysql 일부 명령어를 실행할 수 있는 권한을 획득한다. 커넥션을 만들고 나서야 ping 커맨드를 실행하기 위해 내부에서는 mysql_ping 함수가 실행되며 내부적으로 mysql_simple_command 함수가 실행된다.
-
mysql_simple_command 함수는 mysql 객체에 methods 프로퍼티 안에 있는 advanced_method 함수를 실행하게 되고
-
cli_advance_method의 함수 인자 값으로 enum_server_command 타입이 들어가 있는데 그중 1417번째 행에 if (net_write_command(net, (uchar)command, header, header_length, arg, arg_length)) { set_mysql_error(mysql, CR_SERVER_GONE_ERROR, unknown_sqlstate); goto end;}에서 서버로 COM_PING 커맨드에 대해서 보내고 서버에서는 COM_PING 명령어를 실행하라고 요청을 처리하고 반환한다.
참고로 advanced_command에서 cli_advance_command로 바뀐 이유는 mysql의 methods 프로퍼티가 advanced_command로 cli_advanced_command를 사용하기 때문이다.
추가적으로 이렇게 advanced_command가 호출되면 여기서 MySQL 서버에 보내기 위한 데이터를 버퍼에 넣고 해당 버퍼를 활용하여 패킷을 보내게 된다. Connection Thread로부터 패킷을 받은 MySQL 서버는 do_commands -> dispatch_command를 실행하고 거기서 COM_PING의 경우에 따라 서버가 살아있는지 확인해주는 거 같다.(많이 생략된 거 같지만, MySQL에 코드가 너무 난잡해서 확인하는데 포기를 해버렸다… 일단 확인을 해본 결과로는 Login 외에는 하지 않는 것으로 확인했다. 여기서부터는 분석을 해봤지만 틀릴 수 있다…)
이렇게 사실은 SHOW PROCESSLIST를 통해 확인하는 것이 아니라 인증된 사용자라면 누구나 사용할 수 있는 COM_PING을 사용해서 mysqladmin ping 명령어가 구현되어 있다는 것을 깨달았다.
그래서 가정을 해봤다 PROCESS 권한을 주지 않고 그냥 아무 쿼리도 사용하지 못하게 해도 상관없지 않을까?
아무 권한도 주지 않는 USAGE 옵션으로 바꿔서 테스트를 해봐도 정상적으로 헬스 체크를 하는 것으로 확인됐다!
생각보다 많은 코드에 구현이 어떻게 되어있는지 찾기 힘들었지만, 재밌는 경험이었다.
이렇게 사실은 SHOW PROCESSLIST를 통해 확인하는 것이 아니라 인증된 사용자라면 누구나 사용할 수 있는 COM_PING을 사용해서 mysqladmin ping 명령어가 구현되어 있다는 것을 깨달았다.
그래서 가정을 해봤다 PROCESS 권한을 주지 않고 그냥 아무 쿼리도 사용하지 못하게 해도 상관없지 않을까?
아무 권한도 주지 않는 USAGE 옵션으로 바꿔서 테스트를 해봐도 정상적으로 헬스 체크를 하는 것으로 확인됐다!
생각보다 많은 코드에 구현이 어떻게 되어있는지 찾기 힘들었지만, 재밌는 경험이었다.