We have had in Debian/Ubuntu since late 2015 unix socket authentication by default on new MariaDB installations (see MDEV-8375).
Now in 10.3 it is time to start do this in upstream MariaDB globally.
Via Debian we have already experience that this works well in many environments and use cases. There is a Windows equivalent for unix socket auth is called auth_namepipe and has been tested to work already in 2015.
The remaining question is whether to have this as-is in upstream now or should we wait until we have automatic fallback to password if the unix_socket did not match (support for multiple authentication plugins).
I agree someone may have done it, and packaging scripts will need to account for this, however I've never seen or found a stack overflow, dba.stackexchange.com, blog or any other reference to it. I tried searching Google for CREATE USER mysql and haven't found an exact match . All examples I've seen focus on a personal name (dan), a role (admin), root. or and application name.
Daniel Black
added a comment - I agree someone may have done it, and packaging scripts will need to account for this, however I've never seen or found a stack overflow, dba.stackexchange.com, blog or any other reference to it. I tried searching Google for CREATE USER mysql and haven't found an exact match . All examples I've seen focus on a personal name ( dan ), a role ( admin ), root . or and application name.
The logic here is — both root user and the owner of the datadir do not need to know the password, they have the full access to the data anyway. So we don't ask them to provide the password.
This covers both your mysql@localhost suggestion and debian bug#848616 (installed locally under a normal user) use cases.
Sergei Golubchik
added a comment - Okay. I'll try this — two all-privilege accounts, authenticated with unix_socket. One is root. The other is:
# Use $auth_root_socket_user if explicitly specified.
# Otherwise use the owner of datadir - ${user:-$USER}
# Use 'root' as a fallback
auth_root_socket_user=${auth_root_socket_user:-${user:-${USER:-root}}}
The logic here is — both root user and the owner of the datadir do not need to know the password, they have the full access to the data anyway. So we don't ask them to provide the password.
This covers both your mysql@localhost suggestion and debian bug#848616 (installed locally under a normal user) use cases.
Daniel Black
added a comment - Nice work serg . Always great to see large chunks of packaging script disappear ( https://github.com/mariadb/server/commit/25c6d626570e048fc8ddf1ef500a0c601d8b8a60 ). Trivial, minor, and not without RPM implications, does `./support-files/mysql-log-rotate.sh` need a `su mysql mysql`?
No, I don't think so. That's the same with Debian — if a script can read /etc/mysql/debian.cnf then it's run as root and that alone is enough. In mysql-log-rotate.sh it looks like it needs to read ~root/.my.cnf, so it's enough too.
By the way, with mysql user it's somewhat tricky. I didn't dig too deep but the only sudo that I managed to get working was
$ sudosudo -umysql mysql -umysql
which looks a bit redundant to me. For running as root simple sudo mysql works.
Sergei Golubchik
added a comment - No, I don't think so. That's the same with Debian — if a script can read /etc/mysql/debian.cnf then it's run as root and that alone is enough. In mysql-log-rotate.sh it looks like it needs to read ~root/.my.cnf , so it's enough too.
By the way, with mysql user it's somewhat tricky. I didn't dig too deep but the only sudo that I managed to get working was
$ sudo sudo -umysql mysql -umysql
which looks a bit redundant to me. For running as root simple sudo mysql works.
Task
Critical
MDEV-21928 ALTER USER doesn't remove excess authentication plugins from mysql.global_priv
I agree someone may have done it, and packaging scripts will need to account for this, however I've never seen or found a stack overflow, dba.stackexchange.com, blog or any other reference to it. I tried searching Google for CREATE USER mysql and haven't found an exact match . All examples I've seen focus on a personal name (dan), a role (admin), root. or and application name.