community.mysql
community.mysql copied to clipboard
ansible-collections
msg": "Error granting privileges, invalid priv string: ALL"
SUMMARY
When I run my playbook I try to set up a simple user with a database from your FAQ.
Creating a database : works
But when it tries to set a user with privileges it doesn't work. I've tried like 100 different types of 'bob.*:ALL' etc.. but nothing works
with and without "" and ''
ISSUE TYPE
- Bug Report
COMPONENT NAME
mysql_user
ANSIBLE VERSION
ansible [core 2.13.5]
COLLECTION VERSION
Collection Version
----------------- -------
community.general 5.7.0
community.mysql 3.5.1
also tested it with: community.mysql 3.5.0
CONFIGURATION
#Ansible config:
- name: Create .my.cnf
template:
src: "templates/my.cnf.j2"
dest: "/root/.my.cnf"
owner: root
group: root
mode: 0600
- name: Removes anonymous user account for localhost
mysql_user:
name: ''
host: localhost
state: absent
- name: Create database user with name bob
mysql_user:
name: bob
password: {{my_db_password}}
priv: '*.*:ALL'
state: present
OS / ENVIRONMENT
Ansible Host: Linux Mint 21
Target: Debian 11 / MariaDB 10.9
ACTUAL RESULTS
The full traceback is:
"/tmp/ansible_mysql_user_payload_p_u38pra/ansible_mysql_user_payload.zip/ansible_collections/community/mysql/plugins/module_utils/user.py", line 725, in privileges_grant
raise InvalidPrivsError("Error granting privileges, invalid priv string: %s" % priv_string)
fatal: [172.16.200.37]: FAILED! => {
"changed": false,
"invocation": {
"module_args": {
"append_privs": false,
"ca_cert": null,
"check_hostname": null,
"check_implicit_admin": false,
"client_cert": null,
"client_key": null,
"config_file": "/root/.my.cnf",
"connect_timeout": 30,
"encrypted": false,
"force_context": false,
"host": "localhost",
"host_all": false,
"login_host": "localhost",
"login_password": null,
"login_port": 3306,
"login_unix_socket": null,
"login_user": null,
"name": "bob",
"password": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER",
"plugin": null,
"plugin_auth_string": null,
"plugin_hash_string": null,
"priv": "*.*:ALL",
"resource_limits": null,
"sql_log_bin": true,
"state": "present",
"subtract_privs": false,
"tls_requires": null,
"update_password": "always",
"user": "bob"
}
},
"msg": "Error granting privileges, invalid priv string: ALL"
}
I can confirm this is a bug when you trying to install mysql user on a Debian 11 machine.
It's working as it should in Rocky Linux v9 without any problems. So i guess this is something you have to take a deeper look at.
If the ALL priv is supported depends on the database provider.
In case of AWS RDS (as a database provider), ALL is also not available. The only way to work around this is to name every needed privilege.
Basically you name the privileges in a ALL variable and replace the ALL in a string.
---
- name: prepare nextcloud container
hosts: localhost
connection: local
tasks:
- name: test
vars:
mysql_parameters: &mysql_params
login_user: root
login_password: mariadb
login_host: 127.0.0.1
login_port: 33066
ALL: "ALTER,ALTER ROUTINE,CREATE,CREATE ROUTINE,CREATE TEMPORARY TABLES,CREATE VIEW,DELETE,DROP,EVENT,EXECUTE,INDEX,INSERT,LOCK TABLES,REFERENCES,SELECT,SHOW VIEW,TRIGGER,UPDATE"
block:
- name: start nextcloud container
register: out
community.docker.docker_container:
name: mariadb_test_container
image: mariadb:10.5.15
state: started
auto_remove: true
recreate: true
container_default_behavior: no_defaults
published_ports:
- "127.0.0.1:{{ mysql_parameters.login_port }}:3306"
env:
MARIADB_ROOT_PASSWORD: "{{ mysql_parameters.login_password }}"
- name: mariadb container needs some sec for initialization
pause:
seconds: 10
- name: create user with two grants
mysql_user:
<<: *mysql_params
name: testuser
password: testpassword
update_password: on_create
priv: "{{ '*.*:ALL' | replace('ALL', ALL) }}"
state: present
always:
- name: stop mariadb test container
community.docker.docker_container:
name: mariadb_test_container
state: absent
So imho, this is not a community.mysql issue at first place.
@razerrazer have you tried with ALL PRIVILEGES instead of ALL. I think ALL is an alias and is not present on all mysql flavors. So normally you would write GRANT ALL PRIVILEGES TO db.* ON user@localhost.
For what is worth I had this error but the issue was related to the account ansible was using to login to MySQL (v8) to try to grant that ALL priv did not actually have the grant permission. Had to first give the login account grant permission and after that ansible was able to create the users with ALL priv
For what is worth I had this error but the issue was related to the account ansible was using to login to MySQL (v8) to try to grant that
ALLprivdid not actually have thegrantpermission. Had to first give the login accountgrantpermission and after that ansible was able to create the users withALLpriv
@redeyesdemonkyo thanks for sharing the solution! So
- Can we close the issue?
- Should the doc be improved anyhow or it's fine as is?
In my case, this particular instance of mysql was part of a replication cluster
which was configured with a --super-read-only flag/variable.
In that case, no Ansible commands can be run on it See: https://www.percona.com/blog/using-the-super_read_only-system-variable/ which says:
It is well known that replica servers in a master/slave configuration, to avoid breaking replication due to duplicate keys, missing rows or other similar issues, should not receive write queries.
I see this error now with mariadb:11.3 official docker image as a server (user and db name is the same):
failed: [host] (item={'key': 'user', 'value': 'pw'}) => {"ansible_loop_var": "item", "changed": false, "item": {"key": "user", "value": "pw"}, "msg": "Error granting privileges, invalid priv string: ALL PRIVILEGES , params: ('user', '%'), query: GRANT ALL PRIVILEGES ON `user`.* TO %s@%s , exception: (1044, \"Access denied for user 'root'@'%' to database 'user'\")."}
Tried both ALL and ALL PRIVILEGES variants.
mariadb:11.2 works fine without this error.
Hi @pschiffe and thanks for reporting the issue on 11.3.
I believe we test only long term support versions, which are atm 10.4, 10.5, 10.6 and 10.11. The v11 is still kind of a beta. I'm not saying we won't fix an issue on v11, because at some point they will release a LTS on that branch too. Just saying we haven't tested it yet.
@pschiffe, could you paste the whole community.mysql.mysql_user task so we can see what you tried to achieve please. It could be that the user you're using doesn't have enough rights to create a new user?
Yeah, I understand, thanks for getting back to me. Here's the task. The user is root, and before this task, I'm creating other users with privileges like priv: '*.*:RELOAD, PROCESS, LOCK TABLES, BINLOG MONITOR, CONNECTION ADMIN' and priv: '*.*:SELECT, PROCESS, REPLICATION CLIENT, RELOAD' and these tasks works. The issue is only with ALL priv:
- name: Create db users
community.mysql.mysql_user:
name: '{{ item.key }}'
password: '{{ item.value }}'
host: '%'
priv: '{{ item.key }}.*:ALL'
login_user: root
login_password: '{{ mariadb_root_pw }}'
login_host: '{{ db_result.container.NetworkSettings.Networks[mariadb_network_name].IPAddress }}'
state: present
loop: '{{ mariadb_users | dict2items }}'
What grants do root@'%' have?
Can you paste the results of SHOW GRANTS FOR root@'%'; please @pschiffe?
Mariadb 11.2:
MariaDB [(none)]> SHOW GRANTS FOR root@'%';
+--------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@% |
+--------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `root`@`%` IDENTIFIED BY PASSWORD '******' WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'%' WITH GRANT OPTION |
+--------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.001 sec)
Mariadb 11.3:
MariaDB [(none)]> SHOW GRANTS FOR root@'%';
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@% |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, BINLOG MONITOR, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, DELETE HISTORY, SET USER, FEDERATED ADMIN, CONNECTION ADMIN, READ_ONLY ADMIN, REPLICATION SLAVE ADMIN, REPLICATION MASTER ADMIN, BINLOG ADMIN, BINLOG REPLAY, SLAVE MONITOR ON *.* TO `root`@`%` IDENTIFIED BY PASSWORD '******' WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'%' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.001 sec)
Amazing, this is the same DB on a volume, I've changed only the image.
Interesting! @pschiffe have you created the root@'%' user using the Ansible module community.mysql.mysql_user in both cases? Or did you created it by other means?
The root user was created via MARIADB_ROOT_PASSWORD env var given to the mariadb container. The original mariadb version was 11.something.
@pschiffe I'm not able to reproduce what you experienced above. Maybe you altered one of the root accounts at some point? Also, since the container creates 2 accounts, root@localhost and root@'%', be careful which one is used by the Ansible module.
podman run -d -ti --env MARIADB_ROOT_PASSWORD=manager --name mariadb112 mariadb:11.2
podman exec -it mariadb112 bash
root@9d0b8edb089e:/# mariadb -pmanager
MariaDB [(none)]> select user, host from mysql.user;
+-------------+-----------+
| User | Host |
+-------------+-----------+
| root | % |
| healthcheck | 127.0.0.1 |
| healthcheck | ::1 |
| healthcheck | localhost |
| mariadb.sys | localhost |
| root | localhost |
+-------------+-----------+
6 rows in set (0.001 sec)
MariaDB [(none)]> show grants for root@'%';
+--------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@% |
+--------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `root`@`%` IDENTIFIED BY PASSWORD '*7D2ABFF56C15D67445082FBB4ACD2DCD26C0ED57' WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'%' WITH GRANT OPTION |
+--------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)
MariaDB [(none)]> show grants for root@localhost;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED BY PASSWORD '*7D2ABFF56C15D67445082FBB4ACD2DCD26C0ED57' WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.001 sec)
podman run -d -ti --env MARIADB_ROOT_PASSWORD=manager --name mariadb113 mariadb:11.3
podman exec -it mariadb113 bash
root@746a6dc0605e:/# mariadb -pmanager
MariaDB [(none)]> select user, host from mysql.user;
+-------------+-----------+
| User | Host |
+-------------+-----------+
| root | % |
| healthcheck | 127.0.0.1 |
| healthcheck | ::1 |
| healthcheck | localhost |
| mariadb.sys | localhost |
| root | localhost |
+-------------+-----------+
6 rows in set (0.002 sec)
MariaDB [(none)]> show grants for root@'%';
+--------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@% |
+--------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `root`@`%` IDENTIFIED BY PASSWORD '*7D2ABFF56C15D67445082FBB4ACD2DCD26C0ED57' WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'%' WITH GRANT OPTION |
+--------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)
MariaDB [(none)]> show grants for root@localhost;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED BY PASSWORD '*7D2ABFF56C15D67445082FBB4ACD2DCD26C0ED57' WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)
Thank you @laurent-indermuehle for taking the time to look into this. I can confirm I can see the same as you in this case.
To reproduce my issue, do the following:
podman run -d -ti --env MARIADB_ROOT_PASSWORD=manager --name mariadb11 -v mariadb:/var/lib/mysql mariadb:11.2
podman exec -it mariadb11 bash
root@3f0e6649e7b1:/# mariadb -pmanager
MariaDB [(none)]> show grants for root@'%';
+--------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@% |
+--------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `root`@`%` IDENTIFIED BY PASSWORD '*7D2ABFF56C15D67445082FBB4ACD2DCD26C0ED57' WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'%' WITH GRANT OPTION |
+--------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)
So far, so good. Now exit the container and continue:
podman rm -f mariadb11
podman run -d -ti --env MARIADB_ROOT_PASSWORD=manager --name mariadb11 -v mariadb:/var/lib/mysql mariadb:11.3
podman exec -it mariadb11 bash
root@ad62bd011ef3:/# mariadb -pmanager
MariaDB [(none)]> show grants for root@'%';
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@% |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, BINLOG MONITOR, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, DELETE HISTORY, SET USER, FEDERATED ADMIN, CONNECTION ADMIN, READ_ONLY ADMIN, REPLICATION SLAVE ADMIN, REPLICATION MASTER ADMIN, BINLOG ADMIN, BINLOG REPLAY, SLAVE MONITOR ON *.* TO `root`@`%` IDENTIFIED BY PASSWORD '*7D2ABFF56C15D67445082FBB4ACD2DCD26C0ED57' WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'%' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)
Even after running mariadb-upgrade -pmanager it's the same.
@pschiffe thank you for testing again. One thing I left out is that I downloaded the containers from docker.io. I don't know if it's the same image than Quay.io or other registries.
I now see that you're creating the mysql schema with 11.2 then upgrade it to 11.3. But the question is, do the Ansible module fails to creates the new user if you start from a fresh 11.3?
On a side note, it bother me to not be able to find a documentation about why the grants are differents in this case. I see nothing in the script that built the container 11.3: https://github.com/MariaDB/mariadb-docker/blob/master/11.3/Dockerfile neither in the changelog: https://mariadb.com/kb/en/changes-improvements-in-mariadb-11-3/#privileges
I'm also using images from Docker Hub.
But the question is, do the Ansible module fails to creates the new user if you start from a fresh 11.3?
Ansible module works fine when starting from fresh 11.3.
I also didn't find any changes related to this behavior. I'm starting to believe that this is a bug in MariaDB directly :(
I've reported it to the MariaDB bug tracker: https://jira.mariadb.org/browse/MDEV-33554
I've reported it to the MariaDB bug tracker: https://jira.mariadb.org/browse/MDEV-33554
According to the report, this was fixed in MariaDB 11.4.2. Can this be closed?
@lahwaacz hello, thanks for letting us know!
What the other folks thinks? Can we close the issue?
@pschiffe can you confirm this bug is gone on 11.4.2+ ? @Andersson007 since it's a MariaDB bug, I think we can close this issue. But we could create a workaround for the affected versions, if enough people need it.