How to change root to native-password plugin with dockerhub image - Source

Revision: 107951
User: James Stewart Miller
Date: 2021-06-28 03:35

The version of maria in the latest dockerhub image expects root to login using root access ie sudo mysql.
This is not possible with podman rootless, and so I want to log in using a password.  When creating the container I use the environment variable MARIADB_ROOT_PASSWORD, but when I try to connect to maria using 'mysql -u root -p, I get access denied.

In the Maria literature it states:

You want passwords back, no unix_socket authentication anymore? Run

ALTER USER root@localhost IDENTIFIED VIA mysql_native_password USING PASSWORD("verysecret")

But how can I run sql, including a password, without being able to log in to maria as root?

I could try creating an executable .sh file, for example, and baking it into a custom image, having placed it into /docker-entrypoint-initdb.d.  But the .sh file would still have to contain:
mysql -u root -p ${_can_get_root_password_in_environment_variable_when_I_start_container_with_run } -e "ALTER USER root... as above"
An executable sh file would allow me to use the MARIADB_ROOT_PASSWORD, creating it as a podman secret and passing it in as an environment variable when I run the container - ie podman run --secret=MARIADB_ROOT_PASSWORD,type=env -it container_name

If I try the same thing with a raw sql file, I can't pass in the password.

So, basically, I am stuck, and unable to connect to the mariadb database using the docker image.

Any help gratefully received...

Answer

Just to make sure I understand this correctly this is what I have tested:

<<code>>
$ podman run --name mdbtest_root_password -e MARIADB_ROOT_PASSWORD=bob -d -P  docker.io/library/mariadb:10.5

$ podman exec -ti mdbtest_root_password bash
root@499f950b9a5d:/# env | grep PASSWORD
MARIADB_ROOT_PASSWORD=bob
root@499f950b9a5d:/# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
root@499f950b9a5d:/# mysql -pbob
Welcome to the MariaDB monitor.  Commands end with ; or \g.
MariaDB [(none)]> show create user root@localhost;
+---------------------------------------------------------------------------------------------------+
| CREATE USER for root@localhost                                                                    |
+---------------------------------------------------------------------------------------------------+
| CREATE USER `root`@`localhost` IDENTIFIED BY PASSWORD '*61584B76F6ECE8FB9A328E7CF198094B2FAC55C7' |
+---------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> show create user root@`%`;
+-------------------------------------------------------------------------------------------+
| CREATE USER for root@%                                                                    |
+-------------------------------------------------------------------------------------------+
| CREATE USER `root`@`%` IDENTIFIED BY PASSWORD '*61584B76F6ECE8FB9A328E7CF198094B2FAC55C7' |
+-------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

$ podman port mdbtest_root_password 3306/tcp
0.0.0.0:36243

$ mysql -u root --protocol tcp --port 36243 -pbob -e 'select version()'
+---------------------------------------+
| version()                             |
+---------------------------------------+
| 10.5.10-MariaDB-1:10.5.10+maria~focal |
+---------------------------------------+
<</code>>

Testing with secrets:
<<code>>
$ echo $(pwgen 30) | podman secret create MARIADB_ROOT_PASSWORD -
$ podman run --name mdbtest_root_password --secret=MARIADB_ROOT_PASSWORD,type=env -d  -P  docker.io/library/mariadb:10.5
2f2f8ccc81a165016e0b264f48aa537f51019bee53d6c678573753ca23e05dac
$ podman inspect mdbtest_root_password | grep PASSWORD
                "MARIADB_ROOT_PASSWORD=doocoh8AeChee8eimieb0xePhiTh8u\n"
                "--secret=MARIADB_ROOT_PASSWORD,type=env",
$ podman logs 2f2f8ccc81a165016e0b264f48aa537f51019bee53d6c678573753ca23e05dac
2021-06-28 00:10:02+00:00 [Note] [Entrypoint]: Stopping temporary server
2021-06-28  0:10:02 6 [Warning] Access denied for user 'root'@'localhost' (using password: YES)
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: YES)'
2021-06-28 00:10:02+00:00 [ERROR] [Entrypoint]: Unable to shut down server.
<</code>>

So this looks like there's an error handing a password that ends with \n. So I suspect that https://github.com/MariaDB/mariadb-docker/commit/58f4020613e4b96ab0a937890af1a7a4e0dc4b00 needs more handling.

Having a password without \n however:
<<code>>
$ echo -n $(pwgen 30) | podman secret create MARIADB_ROOT_PASSWORD -
70b2de3385c806d5a3fa3a8eb
$ podman run --name mdbtest_root_password --secret=MARIADB_ROOT_PASSWORD,type=env -d  -P  docker.io/library/mariadb:10.5
53172c18a18898f384ef1b130e5fa14538e154adb71fe90ac147ea28b6f6ebef
$ podman port  mdbtest_root_password
3306/tcp -> 0.0.0.0:35889
$ podman inspect mdbtest_root_password | grep PASSWORD
                "MARIADB_ROOT_PASSWORD=Jool0ae0eew2aeha2cahchoovaSha6"
                "--secret=MARIADB_ROOT_PASSWORD,type=env",
$ mysql -u root --protocol=tcp --port 35889 -pJool0ae0eew2aeha2cahchoovaSha6 -e 'select version()'
+---------------------------------------+
| version()                             |
+---------------------------------------+
| 10.5.10-MariaDB-1:10.5.10+maria~focal |
+---------------------------------------+
<</code>>

So in conclusion:
* the mariadb container doesn't have unix_socket authentication at all (would of shown up in the SHOW CREATE USER)
* Both root users, @localhost and @% get the password from MARIADB_ROOT_PASSWORD
* No sudo/su is needed as the root mapped user is understood as being root by mariadb
* Most likely this is the old old compatibility thing that there must not be a space between {{-p}} and the password.
* The image needs to be more testing with passwords ending in \n.

As the maintainer of the MariaDB docker library image I do most of my testing with podman. Thanks for cluing me into the use of podman secrets. I haven't used them before.

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.

How to change root to native-password plugin with dockerhub image - Source

Answer

Products

Solutions

Resources

Company

Pricing

Download MariaDB