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

I see you are using version 10.5. I have been trying with the mariadb:latest image, which I now see defaults to version 10.5.

When I try the following commands in a script:

read -p "Enter your MYSQL_ROOT_PASSWORD : " mysql_root_password podman exec -e MYSQL_ROOT_PASSWORD=${mysql_root_password} -e DB_NAME=${DB_NAME} -e DB_USER=${DB_USER} -e DB_HOST=${DB_HOST} -e DB_PASSWORD=${DB_PASSWORD} -it ${MARIA_CONT_NAME} bash -c "mysql -uroot -p\${MYSQL_ROOT_PASSWORD} -hlocalhost -e \"CREATE DATABASE \${DB_NAME} CHARSET utf8; grant all privileges on \${DB_NAME}.* TO \${DB_USER}@\${DB_HOST} identified by '$\{DB_PASSWORD}';\""

or when I try and log in from a command line having exec'ed into a bash session inside the maria container, I get the following error: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

I have checked and the variable 'mysql_root_password' does not contain a newline character. There is no space between -p and the password in any command.

This is my command for making the Mariadb secret: $(read -p "Enter variable for MARIADB_ROOT_PASSWORD : " token) && printf token | podman secret create "MARIADB_ROOT_PASSWORD" -

which I then later use: podman run -dit --secret=MARIADB_ROOT_PASSWORD,type=env --name ${MARIA_CONT_NAME} --pod ${POD_NAME} ${MARIA_IMAGE}

the above run command is followed immediately by:

read -p "Enter your MYSQL_ROOT_PASSWORD : " mysql_root_password

podman exec -e MARIADB_ROOT_PASSWORD=${mysql_root_password} -e DB_NAME=${DB_NAME} -e DB_USER=${DB_USER} -e DB_HOST=${DB_HOST} -e DB_PASSWORD=${DB_PASSWORD} -it ${MARIA_CONT_NAME} bash -c "mysql -uroot -p\${MARIADB_ROOT_PASSWORD} -hlocalhost -e \"CREATE DATABASE \${DB_NAME} CHARSET utf8; grant all privileges on \${DB_NAME}.* TO \${DB_USER}@\${DB_HOST} identified by '$\{DB_PASSWORD}';\""

And this command (or me manually trying to log into the mariadb with the root password) fails with access denied.

I am creating it this way, because I want to use the command 'podman generate systemd --files --new' so that the systemd script create the container from new each time. So, I will, if I can connect to mariadb, mount the /var/lib/sql directory on the host in the run command. The aim is that the newly created by systemd container will reuse the database each time.

But I can't get this basic command to work. I have tried creating the maria container with -e MARIADB_ROOT_PASSWORD="some_password", but I still get no luck.

I'm stumped...

$(read -p "Enter variable for MARIADB_ROOT_PASSWORD : " token) && printf token
Enter variable for MARIADB_ROOT_PASSWORD : what is this password
token

Note:

1. a literal "token" is printed so that is the password.
2. because read is in a subshell, the token variable isn't saved.

To observe results:

Use podman inspect {container} | grep -i password

Use:

read -p "Enter variable for MARIADB_ROOT_PASSWORD : " token && echo -n $token | podman secret create "MARIADB_ROOT_PASSWORD" -

If you use the environment variables MARIADB_USER, MARIADB_PASSWORD and MARIADB_DATABASE you end up with that user having all grants on the database exactly how you are trying to achieve this.

Attempted your podman exec:

$ podman exec -e ROOT_PASSWORD='davebob' -e DB_NAME=space -e DB_USER=major -e DB_HOST=% -e DB_PASSWORD=soverysecret -it mdbtest bash -c "mysql -uroot -p\${ROOT_PASSWORD} -hlocalhost -e \"CREATE DATABASE \${DB_NAME} CHARSET utf8; grant all privileges on \${DB_NAME}.* TO \${DB_USER}@\${DB_HOST} identified by '$\{DB_PASSWORD}';\""
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '% identified by '$\{DB_PASSWORD}'' at line 1

$ podman exec   mdbtest mysql -pdavebob -e 'drop database space'

$ podman exec -e ROOT_PASSWORD='davebob' -e DB_NAME=space -e DB_USER=major -e DB_HOST=% -e DB_PASSWORD=soverysecret -it mdbtest bash -c "mysql -uroot -p\${ROOT_PASSWORD} -hlocalhost -e \"CREATE DATABASE \${DB_NAME} CHARSET utf8; grant all privileges on \${DB_NAME}.* TO '\${DB_USER}'@'\${DB_HOST}' identified by '\${DB_PASSWORD}';\""

$ podman exec   mdbtest mysql -pdavebob -e 'show databases'
Database
information_schema
mysql
performance_schema
space

$ podman exec   mdbtest mysql -pdavebob -e 'show create user major'
CREATE USER for major@%
CREATE USER `major`@`%` IDENTIFIED BY PASSWORD '*D03D15427D788A324B1D75E87F702540998589DB'

$ podman exec   mdbtest mysql -u major -psoverysecret -e 'show databases'
Database
information_schema
space

To fix (as I did above):

1. quote your hostname, and possibly username if it can be more than just basic
2. \ should be before $ near DB_PASSWORD

$ podman exec   mdbtest mysql -pdavebob -e 'select * from mysql.global_priv'
Host	User	Priv
localhost	mariadb.sys	{"access":0,"plugin":"mysql_native_password","authentication_string":"","account_locked":true,"password_last_changed":0}
localhost	root	{"access":549755813887,"plugin":"mysql_native_password","authentication_string":"*F64E0B8EB4D0A34AD85562E6BF2E2F9C8274DAB5","password_last_changed":1624925339,"version_id":100510}
%	root	{"access":549755813887,"version_id":100510,"plugin":"mysql_native_password","authentication_string":"*F64E0B8EB4D0A34AD85562E6BF2E2F9C8274DAB5","password_last_changed":1624925339}
%	major	{"access":0,"version_id":100510,"plugin":"mysql_native_password","authentication_string":"*D03D15427D788A324B1D75E87F702540998589DB","password_last_changed":1624925600}

In 10.4+ don't depend on the mysql.user view, look at mysql.global_priv directly.

Focus on your scripting and observing its behaviour. The container is actually ok.

edit use the following to prevent shell escaping of the password:

read -p "Enter variable for MARIADB_ROOT_PASSWORD : " token && echo -n "$token" | podman secret create "MARIADB_ROOT_PASSWORD" 

Note I recommend leaving it to the container entrypoint to create the non-root user and its password. It handles the escaping of passwords in shell and SQL significantly better that the scripting you have written.

Many thanks for your help. I discovered the issue. When a pod is created, a network namespace is created for it automatically. This has the following name 'ceramic_isles_dev_pod', in my case. So, if I run: podman exec -it maria_cont2 bash -c "mysql -uroot -h'ceramic_isles_dev_pod' -p" I am able to successfully enter the password with no issue. ....you said.... Note I recommend leaving it to the container entrypoint to create the non-root user and its password. It handles the escaping of passwords in shell and SQL significantly better that the scripting you have written. ..... There are two reasons that I don't use the container entrypoint with environment variables. The first and principle reason is that I intend to start/restart the container with a systemd script that generates a new container each time the host starts. This container will then connect to an existing database, as although the container will be new and fresh, the database will still exist at the bind mount to /var/lib/mysql. This will allow me to run/create the containers under a system (<999 id) user, for extra security. Since the podman generate-systemd command uses the initial run command to start the new container each time, I don't want to have a new database and user recreated each time, as I want to reuse the existing database. I will be using the --files and --new flags of the podman generate systemd command... http://docs.podman.io/en/latest/markdown/podman-generate-systemd.1.html

The second reason, is that in the past, when I tried to use the initial database creation environment variables, is that it didn't work, as I remember. The container failed to run, quitting immediately, and on restarting it, I seem to recall that either the database wasn't created or I couldn't see it because of my current issues. So, I have just tried it again, and counter-intuitively, the new user and database is created but on the host of 'ceramic_isles_dev_pod' - the pod name, but root is created on localhost. I am able to login successfully in both cases. So, if I don't create a database initially, then root is pod_name but if I do then root is 'localhost'. My bash scripting is not particularly good, you are quite right. I had changed the printf in the read command from echo, to remove a newline character and must have deleted the $ sign in front of the variable 'token' then. I will use your syntax provided, many thanks. And many thanks overall, not just for your help, but for your work in supporting the community with your maintenance of this image.

just in case anyone reads the post, the secret creation command is correct except for a hyphen at the end -

read -p "Enter variable for MARIADB_ROOT_PASSWORD : " token && echo -n "$token" | podman secret create "MARIADB_ROOT_PASSWORD" -

So, I finally got it to work. I was having another issue which is that once I had created the container using podman run, I had to wait an unspecified amount of time before the database in the container would accept commands. I ended up running a podman exec command to delete root@% as a do, done loop, like so... until podman exec -e ROOT_PASSWORD="$mysql_root_password" -it "${MARIA_CONT_NAME}" bash -c "mysql -uroot -p\"\${ROOT_PASSWORD}\" -h'localhost' -e \"delete from mysql.global_priv where user='root' and host='%'; flush privileges;\"" > /dev/null 2>&1 do echo -n "." done

Note, I added the redirection to null after I last tested it, but it should be ok, I guess.

Then I ran the podman exec command to create the database, and it worked fine.

Thanks for explaining your use and fixing my missing -.

Using MARIADB_ROOT_HOST=localhost will prevent a root@% creation. There will still be a root@localhost user which is unix socket only, so only a podman exec can use it.

If you do need to remove a user please use drop user. The entrypoint that does similar hackery needs to be cleaned up and is obviously a bad example.

Waiting until it starts is something I plan to fix with a healthcheck script (MDEV-25434).

In the mean time your loop should be using mysql --protocol=tcp as there can be a few temporary starts which could get your command executed early.

Happy to take issues/feature requests on github or JIRA.

Many thanks for all your help.

Perhaps it is because my maria_container is being created inside a pod which only exposes ports 80 and 445? But I am execing into the container so the port shouldn't be necessary.

This...

MariaDB [(none)]> show create user root; ERROR 1290 (HY000): The MariaDB server is running with the --skip-grant-tables option so it cannot execute this statement MariaDB [(none)]> select user,host,plugin from mysql.user; +-------------+--------------------------+-----------------------+

+-------------+--------------------------+-----------------------+

+-------------+--------------------------+-----------------------+ 5 rows in set (0.062 sec)

So that explains why I am getting access denied, because no plugin is being used

If I do the following: podman run -dit -e MARIADB_ROOT_PASSWORD='davebob' -e MARIADB_ROOT_HOST='localhost' --name ${MARIA_CONT_NAME} --pod ${POD_NAME} ${MARIA_IMAGE} and podman exec -e ROOT_PASSWORD='davebob' -e DB_NAME=${DB_NAME} -e DB_USER=${DB_USER} -e DB_HOST=${DB_HOST} -e DB_PASSWORD=${DB_PASSWORD} -it ${MARIA_CONT_NAME} bash -c "mysql -uroot -p\${ROOT_PASSWORD} -hlocalhost -e \"CREATE DATABASE \${DB_NAME} CHARSET utf8; grant all privileges on \${DB_NAME}.* TO \${DB_USER}@\${DB_HOST} identified by '$\{DB_PASSWORD}';\""

I get told that no connection can be made through the socket. tearing my hair out

Ok, I am certain it is because it is running inside a pod. I made a new container using the same commands, and was able to get into it easily.

So, something about the container being inside a pod is causing the issues that I am having.

I guess it is because it is using a network namespace with only 445 and 80 exposed, but that shouldn't stop me from being able to exec into the container and connect using localhost socket. Gah....