MariaDB 10.4.1〜のユーザー認証がカオスな話（Unix_Socket）

MariaDB10.4.6をいれたらUnix_Socketを使った認証で地獄だったので書いておきます。MariaDBではデフォルトでUnix_Socketを使わせようとしてきますが、これを解除して旧来のパスワード認証に戻す方法についてです。

（要約すれば、Unix_Socketを使いたくないけど方法がわからないという問題。解決だけ知りたければ下から二つ目のmysql.global_privのところを見て下さい）

環境

CentOS Linux release 7.6
MariaDB 10.4.6

mysql_secure_installation

MySQL系を入れたら大抵やるんじゃないでしょうか。

するとUnix_Socketがどうこうと出てきます。

# mysql_secure_installation
〜〜〜（ｒｙ
Setting the root password or using the unix_socket ensures that nobody
can log into the MariaDB root user without the proper authorisation.

You already have your root account protected, so you can safely answer 'n'.

Switch to unix_socket authentication [Y/n]

Unix_Socket認証とMariaDB

UnixSocketというのは、OS側のユーザー名とDB側のユーザー名が同じであればUnixSocketを使って認証することでパスワードなしで通しましょうという仕組みのようです。

MariaDBにおけるrootアカウントの考え方

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 MariaDB packages in Debian as early as MariaDB 10.0. It is based on a simple fact, that asking the system root for a password adds no extra security — root has full access to all the data files and all process memory anyway. But not asking for a password means, there is no root password to forget (bye-bye numerous tutorials “how to reset MariaDB root password”). And if you want to script some tedious database work, there is no need to store the root password in plain text for the scipt to use (bye-bye debian-sys-maint user).

https://mariadb.org/authentication-in-mariadb-10-4/

意訳すると、rootは全てのファイル・システムにアクセスできる。なのでそこにパスワードを付け足したところでセキュリティが上がることはない。
メリットとして忘れるべきDBのrootパスワードが存在しないということは、いつものrootパスワードを忘れた時チュートリアルが必要なくなってハッピーだよね。（少し意訳過ぎたかもしれないので修正’19/7/2）

ということのようです。
なので一般ユーザーはまだしもrootについてはDB側にパスワードを設定せず、OS側がrootであることを条件に通すのがMariaDB10.xの思想としてあることがわかりました。

ちなみに一般ユーザーもMariaDBで同じ名前のユーザーを作成してあげるとUnix_Socketで認証をパスします。

パスワード認証をする

思想はわかったといっても、rootを複数ユーザーで使ってる組織（良いことではない）や、一般ユーザーにはUnix_Socketを使わしたくないという事情があるかもしれません。そしてこの認証方法の変更が実にめんどくさいのです。

方法1 Unix_Socketを一切使わなくする

シンプルにして強引な方法ですが、確実です。
まずMariaDBではUnix_SocketをPlug-In方式で導入しています。なのでこれを読み込まなくすれば問題は解決します。

設定ファイル（筆者の環境では/etc/my.cnf.d/server.cnfでした）へ

[mariadb]
disable_unix_socket
[mariadb]
disable_unix_socket

と挿入してMariaDBを再起動すれば完了です。

ただしこの方法だと、Unix_Socket認証をユーザーごとに使い分けるということはできません。

方法2 ユーザーの認証設定を変える

こっちのほうが正攻法な気はします。ただし非常に面倒な問題が発生します。

MariaDB10.4.1以降のユーザー認証について

これまでMySQLのユーザー認証が書かれている場所といえばmysql.userテーブルだったはずですが、MariaDB10.4以降この仕様が変わったおかげで面倒なことになっています。

The mysql.global_priv table was introduced in MariaDB 10.4.1 to replace the mysql.user table.

https://mariadb.com/kb/en/library/mysqlglobal_priv-table/

訳すと、MariaDB10.4.1からはmysql.userのかわりにmysql.global_privテーブルを使うよ、とのことです。

mysql.global_privテーブルに切り替わっているわけです。なので従来のmysql.userをなんやかんやしても基本的にはダメです。

調査してみる

MariaDB [(none)]> show grants for root@localhost;

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED VIA mysql_native_password USING '<PASS>' OR unix_socket WITH GRANT OPTION

rootを調べてみると、OR unix_socketと書いてあります。ではこれを変えればいけるはずです。

GRANT書き換え

ALTER USER root@localhost IDENTIFIED VIA mysql_native_password USING PASSWORD(“<PASSWORD>”);

これで変えることができます。しかし、これは問題がありました。

たしかにこれで変えることはできますし、実際MariaDBの認証もパスワードが強制されます。しかしflush privilegesを行うと元に戻ってしまい、Unix_Socket認証が復活します。この問題はおそらく先程のmysql.global_privにおける仕様変更が原因かなと思います。

実際に、ALTER文を打った後のgrantを引っ張ってみると

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '<PW>' WITH GRANT OPTION

のようにOR unix_socketが消えて成功しているように見えます。しかしこれは古いmysql.userの情報を引いてきています。

mysql.global_priv

ではmysql.global_privを調べてみます。これはselect * from mysql.global_privで見ることができます。

MariaDB [(none)]> select * from mysql.global_priv;
+-----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Host      | User        | Priv                                                                                                                                                                                                             |
+-----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| localhost | root        | {"access": 1073741823, "plugin": "mysql_native_password", "authentication_string": "<PW>, "auth_or": [{}, {"plugin": "unix_socket"}], "password_last_changed": 1561976053} |

そして、このPrivはJSON形式で入っています。

なのでSELECT CONCAT(user, ‘@’, host, ‘ => ‘, JSON_DETAILED(priv)) FROM mysql.global_priv WHERE User=’root’; とすることで見やすくなります。

| root@localhost => {
    "access": 1073741823,
    "plugin": "mysql_native_password",
    "authentication_string": "<PW>",
    "auth_or":
    [

        {
        },

        {
            "plugin": "unix_socket"
        }
    ],
    "password_last_changed": 1561976053
} |
+------------------------

ではこれをUPDATEを使って強引に書き換えます。{“plugin”: “unix_socket”}の部分を{}します。

（UPDATEで無理やり書き換えるのは本来あまり良いことではないかと思いますが、現状global_privを書き換える方法が見つかりませんでした。）

UPDATE mysql.global_priv SET Priv=‘上記の一行’ WHERE User=‘該当ユーザー’;

全部書くと
UPDATE mysql.global_priv SET Priv=‘{"access": 1073741823, "plugin": "mysql_native_password", "authentication_string": "<PW>, "auth_or": [{}, {}], "password_last_changed": 1561976053}’ WHERE User=‘該当ユーザー’;

となります。PWはハッシュ化されて保存してありますが、UPDATEで直接書き込みますので、元のパスワード文字列ではなくハッシュ化された後の文字列を入れます。

これでflush privilegesをかけようが再起動しようが問題なくパスワード認証で固定ができます。

Unix_Socket認証を元に戻したい場合は、{“plugin”: “unix_socket”}を戻して下さい。

MariaDB10.4.1の問題

新しく盛り込まれた仕様変更のせいで、色々問題もあるようです。なのでStableとしては5x系の方が良いかもしれません。
とはいえ、筆者の環境だとこの認証の問題くらいではありますが。

あと本家のDocumentはものすごく読みやすく整備されてました。これは本当に助かりました。

https://mariadb.com/kb/en/

2020-05-14
2020-05-21