How to change root to native-password plugin with dockerhub image
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 Answered by Daniel Black in this comment.
Just to make sure I understand this correctly this is what I have tested:
$ 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) MariaDB [(none)]> select user,host from mysql.global_priv; +-------------+-----------+ | user | host | +-------------+-----------+ | root | % | | mariadb.sys | localhost | | root | localhost | +-------------+-----------+ 3 rows in set (0.001 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 | +---------------------------------------+
Testing with secrets:
$ 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.
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:
$ 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 |
+---------------------------------------+
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 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.
