[Maria-discuss] Migration path for unix_socket authentication plugin
Hi, On MariaDB servers that were installed with >= MariaDB 10.4, I have one root user that was created as: MariaDB [(none)]> show create user 'root'@'localhost'; CREATE USER `root`@`localhost` IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket FWIW: The statements on https://mariadb.com/kb/en/authentication-from-mariadb-104/#description are reversed (`IDENTIFIED VIA unix_socket OR mysql_native_password USING 'invalid'` instead of `IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket`). On MariaDB servers that were installed with < MariaDB 10.4, I have 3 root users that were created as: MariaDB [(none)]> show create user 'root'@'localhost'; CREATE USER `root`@`localhost` MariaDB [(none)]> show create user 'root'@'::1'; CREATE USER `root`@`::1` MariaDB [(none)]> show create user 'root'@'127.0.0.1'; CREATE USER `root`@`127.0.0.1` I've upgraded all MariaDB servers that were installed with MariaDB <= 10.4 to MariaDB 10.6. I would like to update these root users, that were created when installing on < 10.4, to use UNIX socket authentication too. If I run: ALTER USER 'root'@'localhost' IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket ... I have a user with the same create statement as on servers that were installed with >= MariaDB 10.4: MariaDB [(none)]> show create user 'root'@'localhost'; CREATE USER `root`@`localhost` IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket I have 3 questions regarding this: 1. Is the ALTER USER statement above the correct migration path, to update root users created when installing on < 10.4 to use UNIX socket authentication, like root users created when installing on >= 10.4? Am I forgetting anything? 2. I presume the 'root'@'::1' and 'root'@'127.0.0.1' users were created to support TCP connections when skip_name_resolve=1. If so, they are no longer needed when using a UNIX socket. Can I safely delete these users, after updating the 'root'@'localhost' user to use UNIX socket authentication? 3. Is there a plan to automatically remove the debian-sys-maint user on future major upgrades? It still exists on servers that were installed on < 10.4. The /etc/mysql/debian.cnf file is deprecated already, so this user should be unused. FYI: All MariaDB servers were installed from the MariaDB Debian repository. Thank you! William Edwards
On Fri, Oct 14, 2022 at 12:55 AM William Edwards <wedwards@cyberfusion.nl> wrote:
Hi,
On MariaDB servers that were installed with >= MariaDB 10.4, I have one root user that was created as:
MariaDB [(none)]> show create user 'root'@'localhost'; CREATE USER `root`@`localhost` IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket
FWIW: The statements on https://mariadb.com/kb/en/authentication-from-mariadb-104/#description are reversed (`IDENTIFIED VIA unix_socket OR mysql_native_password USING 'invalid'` instead of `IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket`).
On MariaDB servers that were installed with < MariaDB 10.4, I have 3 root users that were created as:
MariaDB [(none)]> show create user 'root'@'localhost'; CREATE USER `root`@`localhost` MariaDB [(none)]> show create user 'root'@'::1'; CREATE USER `root`@`::1` MariaDB [(none)]> show create user 'root'@'127.0.0.1'; CREATE USER `root`@`127.0.0.1`
I've upgraded all MariaDB servers that were installed with MariaDB <= 10.4 to MariaDB 10.6. I would like to update these root users, that were created when installing on < 10.4, to use UNIX socket authentication too. If I run:
ALTER USER 'root'@'localhost' IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket
... I have a user with the same create statement as on servers that were installed with >= MariaDB 10.4:
MariaDB [(none)]> show create user 'root'@'localhost'; CREATE USER `root`@`localhost` IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket
I have 3 questions regarding this:
1. Is the ALTER USER statement above the correct migration path, to update root users created when installing on < 10.4 to use UNIX socket authentication, like root users created when installing on >= 10.4? Am I forgetting anything?
No. The IDENTIFIED VIA mysql_native_password USING 'invalid' is purely there to allow a password to be set if desired. ALTER USER 'root'@'localhost' IDENTIFIED VIA unix_socket; is perfectly valid.
2. I presume the 'root'@'::1' and 'root'@'127.0.0.1' users were created to support TCP connections when skip_name_resolve=1. If so, they are no longer needed when using a UNIX socket. Can I safely delete these users, after updating the 'root'@'localhost' user to use UNIX socket authentication?
yes.
3. Is there a plan to automatically remove the debian-sys-maint user on future major upgrades? It still exists on servers that were installed on
Not that I'm aware of, but there's no need for it on new installs. Its hard to remove without extensive probing of its actual use that may have been invented by the user.
< 10.4. The /etc/mysql/debian.cnf file is deprecated already, so this user should be unused.
correct.
FYI: All MariaDB servers were installed from the MariaDB Debian repository.
Thank you!
William Edwards
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
participants (2)
-
Daniel Black -
William Edwards