MariaDB; 인증(Authentication) 문제.

Posted by 아무도안 Posted on 2019-07-09 3 Comments on MariaDB; 인증(Authentication) 문제.
Posted in Linux Server / General Tagged 

** 긴 글, 줄여보면(tldr;)

굳이 mysql root 를 사용하지 말고, 그냥 sudo mysql 을 사용하는게 지금 추세인 듯 하다. 적어도 새롭게 만드는 DB 라면, 그냥 이 상태로 사용하는 편이 여러모로 정신건강에 좋을 듯 하다.

이 글은, 원래 다른 글의 일부로 있던 내용이었는데, 그 일부를 떼어 독립(?)시켰고, 주로 다음 글을 참고하여 작성했다.

Authentication from MariaDB 10.4

위 글은, 아래 글이 원문으로 보인다.

Authentication in MariaDB 10.4 — Understanding the Changes

MariaDB 는 10.0 부터 인증 방식에 변화를 줬는데, 그게 바로 Unix Socket 방식이다.
이게 뭔 소리냐 하면, mysql 사용자와, 시스템 사용자를 일치시킨다는 의미가 된다.

mysql 내부에는 ‘root’ 라는 사용자가 존재한다. 이 root 는 mysql 이 돌아가고 있는 시스템(리눅스)의 root 와는 전혀 관계가 없다. 시스템 root 가 무소불위 권력을 가진 CEO 라고 한다면, mysql root 는 CIO 라고 할 수 있을까?

여기엔, CEO 가 CIO 에게 필요 시에, 모든 정보를 내놓으라고 할 수 있다는 전제가 깔려있다.
그리고, 그야말로 대형 시스템이라면 모르겠지만, 소규모 서버에서는 이 전제가 아마도 맞다고 본다. (즉, 시스템 root 가 마음만 먹는다면 mysql 의 모든 정보에 접근할 수 있다.)
그렇기에, MariaDB 측에선 이런 방식을 택한게 아니었을까.

어쨌든, 시스템 root 는 mysql root 와 동일인이 된다.
다시 말해서, mysql 사용자는 시스템 사용자와 동일한 ID 를 갖고, 시스템 사용자의 인증을 그대로 따라간다.
시스템에 root 가 있으면 mysql 에도 root 가 있다.
시스템에 mysql 이라는 사용자가 있다면, 역시 mysql 에도 같은 사용자가 있다.

실 사용은 이렇다.

예전, 두 root 가 분리되어 있을 때는, mysql 사용자 root 를 명시하고 로그인했다.

$ mysql -u root -p

Unix Socket 방식은 이렇다.

$ sudo mysql

sudo 로서 root 권한이 있다는 사실만 증명하면, mysql 에선 다른 인증을 하지 않는다.

만약, Unix Socket 을 사용하여, mysql 이라는 사용자로 mysql 에 접근하고 싶다면? 다소 헷갈리는 명령어를 사용해야 한다.

$ sudo -u mysql mysql -u mysql
  • sudo -u user : 특정 사용자로 명령을 내리고자 할 때 사용한다.
  • mysql : 시스템 사용자 mysql 을 뜻한다. (sudo -u mysql 로, 시스템 사용자 mysql 로 실행 주체를 바꾼다.)
  • mysql : /usr/bin/mysql 을 뜻한다.
  • -u mysql : mysql 사용자인 ‘mysql’ 을 명시한다.

mysql 이 계속 나오지만, 뜻은 모두 다름을 이해해야 한다.

이렇게, 시스템 ID = mysql ID 를 대응시켜 사용하는 게 현재 추세다.

그럼에도 불구하고 예전처럼 mysql root 를 시스템 root 와 분리하고 싶다면? 즉, mysql_native_password 방식을 사용하고 싶다면?
MariaDB 10.1 까지는 다음 방법으로 해줄 수 있다.

# 10.1 까지
$ sudo mysql_secure_installation
... mysql root 비밀번호 새로 설정. 기타 작업은 모두 Y 로.

$ sudo mysql -u root
MariaDB [(none)]> use mysql;
MariaDB [mysql]> update user set plugin='mysql_native_password' where user='root';
MariaDB [mysql]> flush privileges;
MariaDB [mysql]> quit;

10.4 이후는 이런 식이다.

$ sudo mysql -u root
MariaDB [(none)]> set password = password("새로운 비밀번호 입력");

set password 를 해주기 전엔, “authentication_string” 이 “Invalid” 로 되어 있어서, 이 인증을 사용할 수가 없게 돼 있었다.
위 명령을 내린 후에도, unix_socket 방식과 mysql_native_password 방식을 그대로 유지할 수 있다.
즉, sudo mysql 로도, mysql -u root -p 로도 모두 접근 가능하다.

User, plugin 등을, 예전 방식으로 확인해보자면, 이런 결과가 나온다.

MariaDB [(none)]> SELECT user,authentication_string,plugin,host FROM mysql.user;
+----------+-----------------------+-----------------------+-----------+
| User     | authentication_string | plugin                | Host      |
+----------+-----------------------+-----------------------+-----------+
| root     | invalid               | mysql_native_password | localhost |
| mysql    | invalid               | mysql_native_password | localhost |
+----------+-----------------------+-----------------------+-----------+

MariaDB 10.4 에도 mysql.user 테이블이 아직 있긴 있는데, 과거 호환을 위해서만 존재한다고 하고, 이제는 mysql.global_priv 를 써야 한다고 한다.

따라서, 아래와 같은 명령으로 인증 정보등을 확인할 수 있다.

MariaDB [(none)]> select concat(user, '@', host, ' => ', json_detailed(priv)) from mysql.global_priv;
.... 중요부분만 발췌..
| root@localhost => {
    "access": 18446744073709551615,
    "plugin": "mysql_native_password",
    "authentication_string": "*4AD47E08DAE2BD4F0977EED5D23DC901359DF617",
    "auth_or": 
    [
        
        {
        },
        
        {
            "plugin": "unix_socket"
        }
    ],
    "password_last_changed": 1562644150
}

두 방식이 모두 설정돼 있음을 볼 수 있긴 한데.. (뒤에 다시 언급하겠지만, mysql_native_password 만 할 수 있게 바꿔도 위 내용은 달라지질 않는다.)

만약, mysql_native_password 만 사용하게끔 하려면? MariaDB 문서에선 이렇게 하라고 한다.

$ sudo mysql -u root
MariaDB [(none)]> ALTER USER root@localhost IDENTIFIED VIA mysql_native_password USING PASSWORD("verysecret")

이렇게 되면 sudo mysql 은 사용이 불가능해진다.
하지만 인증 방식을 mysql_native_password 전용으로 바꿔줬다 해도,

MariaDB [(none)]> ALTER USER root@localhost IDENTIFIED VIA mysql_native_password USING PASSWORD("verysecret")
MariaDB [(none)]> select concat(user, '@', host, ' => ', json_detailed(priv)) from mysql.global_priv;
.... 중요부분만 발췌..
| root@localhost => {
    "access": 1073741823,
    "plugin": "mysql_native_password",
    "authentication_string": "*4AD47E08DAE2BD4F0977EED5D23DC901359DF617",
    "auth_or": 
    [
        
        {
        },
        
        {
            "plugin": "unix_socket"
        }
    ],
    "password_last_changed": 1562649627
}

이렇게, 눈에 보이기엔 바뀐 부분이 없다. 그러나, 어쨌든, sudo mysql 은 불가능해진다.

  • 인증을 mysql_native_password 로 바꾸면, Nextcloud 등에서 사용하는 초기 설치 스크립트등을 무난하게 사용할 수 있다.

만약, 다시 두 인증방식을 모두 쓰고 싶다면,

MariaDB [(none)]> alter USER root@localhost IDENTIFIED VIA mysql_native_password USING password('verystrongpassword');

이 명령으로 원상태 복귀가 가능하다.

왜 이렇게(Unix Socket 방식 인증)해야하는 지에 대해, 내용을 읽어봐도 정확하겐 이해가 되지 않는다. 아마도 MariaDB 측은 ‘시스템 관리자 = DB 관리자’라는 정책을 피력하는 듯 하다. 해당 내용이 MariaDB 10.4 문서에 나와있다.

“Using unix_socket means that if you are the system root user, you can login as root@locahost without a password. This technique was pioneered by Otto Kekäläinen in Debian MariaDB packages and has been successfully used in Debian since as early as MariaDB 10.0.”


그렇다는 얘기는, mysql root 를 password 로그인하여 쓰지 말라는 거다. (즉, Unix Socket 방식이 보안에 더 좋다는 얘기.)

SQL 에 대한 이해가 부족해서겠지만, 모든 mysql 사용자를 시스템 사용자와 1:1 대응시키라는 얘기는 아닌 듯 하다. 그렇게 사용하려면 불편한 점이 더 많을텐데..?
아무튼 적어도 root 와 mysql(사용자) 은, 시스템/mysql 을 연동시켜 사용하라는게 MariaDB 10.4 의 골자다.

그러나, 예전방식을 고수하고자 mysql root 를 독립(?)시키면, 다시 말해서 mysql root 에 password 를 할당하면, 또 다른 문제가 발생한다.

이 문제는 우분투 18.04 에서 제공하는 10.1.40 에서 발생하고, MariaDB 저장소에 있는 Deb 꾸러미로 설치하면 발생하지 않는다.

어떤 문제냐 하면..

$ sudo systemctl status mariadb.service 
● mariadb.service - MariaDB 10.1.40 database server
   Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
  Drop-In: /etc/systemd/system/mariadb.service.d
           └─migrated-from-my.cnf-settings.conf
   Active: active (running) since Wed 2019-06-26 16:30:38 KST; 1s ago
     Docs: man:mysqld(8)
           https://mariadb.com/kb/en/library/systemd/
  Process: 6444 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
  Process: 6439 ExecStartPost=/etc/mysql/debian-start (code=exited, status=0/SUCCESS)
  Process: 6398 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ]   && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=ex
  Process: 6391 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
  Process: 6373 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
 Main PID: 6408 (mysqld)
   Status: "Taking your SQL requests now..."
    Tasks: 26 (limit: 2285)
   CGroup: /system.slice/mariadb.service
           └─6408 /usr/sbin/mysqld

Jun 26 16:30:38 nthnht mysqld[6408]: Version: '10.1.40-MariaDB-0ubuntu0.18.04.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  Ubuntu 18.04
Jun 26 16:30:38 nthnht /etc/mysql/debian-start[6443]: Upgrading MySQL tables if necessary.
Jun 26 16:30:38 nthnht /etc/mysql/debian-start[6447]: /usr/bin/mysql_upgrade: the '--basedir' option is always ignored
Jun 26 16:30:38 nthnht /etc/mysql/debian-start[6447]: Looking for 'mysql' as: /usr/bin/mysql
Jun 26 16:30:38 nthnht /etc/mysql/debian-start[6447]: Looking for 'mysqlcheck' as: /usr/bin/mysqlcheck
Jun 26 16:30:38 nthnht /etc/mysql/debian-start[6447]: Version check failed. Got the following error when calling the 'mysql' command line client
Jun 26 16:30:38 nthnht /etc/mysql/debian-start[6447]: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
Jun 26 16:30:38 nthnht /etc/mysql/debian-start[6447]: FATAL ERROR: Upgrade failed
Jun 26 16:30:38 nthnht /etc/mysql/debian-start[6459]: Checking for insecure root accounts.
Jun 26 16:30:38 nthnht systemd[1]: Started MariaDB 10.1.40 database server.

이렇게, mariadb service 시작 시에 오류가 발생한다. 오류가 발생해도 작동에는 이상은 없는 듯 하다. 이 오류는 /etc/mysql/debian-start 가 실행되면서 발생하는데, 이 파일은 /etc/mysql/debian.cnf 를 참조하고, 이 파일의 내용은 아래와 같다.

# /etc/mysql/debian.cnf
# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host     = localhost
user     = root
password = 
socket   = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host     = localhost
user     = root
password = 
socket   = /var/run/mysqld/mysqld.sock
basedir  = /usr

debian.cnf 의 권한(소유자는 물론 root)은 600 으로 되어 있어, root 가 아니면 볼 수도 없다. 그렇다곤 해도, 저기에 mysql root 의 암호를 넣게끔 되어 있는 건, 보안 측면에서 최선은 아니라고 생각한다.

Mariadb 기본 설정에선 root password 가 설정되어 있지 않기 때문에 저렇게만 해놔도 아무 문제가 없지만, mysql root pw 를 임의로 할당한 경우엔 위와 같이 오류가 발생할 수 밖에 없다.

이 오류를 해결하려면, [mysql_upgrade] 항목에 있는 password 에 새로 설정한 비밀번호를 넣어주면 된다.
아니면, (해보진 않았지만) [mysql_upgrade] 부분을 모두 주석처리해도 괜찮을 듯 한데.. 업그레이드를 굳이 체크할 필요가 없지 않나?

어차피 이 파일은 root 만 볼 수 있으므로, 보안에 큰 문제가 되지는 않겠으나..
어쨌든 비밀번호를 Hash도 아니고 평문으로 그냥 저장한다는 건 결코 보안 측면에서 권장할만한 일은 아니다.

MariaDB 10.4 에선, 적어도 MariaDB 저장소에서 배포하는 꾸러미에는 이 파일(/etc/mysql/dedian.cnf)이 그냥 비어있다. 즉, debian.cnf 파일은 존재하기는 하되 내용은 없기에, 저런 오류가 발생할 여지가 아예 없다.

그리하여, 결론은! (문두의 tldr; 을 다시 옮겨보자면!)

굳이 mysql root 를 사용하지 말고, 그냥 sudo mysql 을 사용하는게 지금 추세인 듯 하다. 적어도 새롭게 만드는 DB 라면, 그냥 이 상태로 사용하는 편이 여러모로 정신건강에 좋을 듯 하다.

Author: 아무도안