maria-discuss team mailing list archive

[Daniel Black <daniel@xxxxxxxxxxx>]

On Fri, Oct 14, 2022 at 12:55 AM William Edwards
<wedwards@xxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp