mysql_user: Not using the mysql database as default can break replication

d-rupp commented 2 years ago

SUMMARY

In replication setups that use filters on the mysql database (to have different privileges for single nodes or certain branches of the replication tree) using this module will break replication. Since the module is not using any database the statements will not be filtered out and you may try to change users that do not exists on the filtered replica.

I could open a PR to fix this, but i wanted to discuss how you want this solved as i see two possible solutions.

Root cause is that the mysql_user plugin does not set a database. This makes the user change statements bypass the mysql binlog/replication filters.

We could either A) hardcode the default database for the mysql_user plugin to "mysql" as this is the place the permissions are stored anyway and usually the database that is filtered. B) make the database setting configureable for the mysql_user plugin so the user can decide if this feature is needed.

I did test A on my setup and it works flawlessly but i could understand why one would want to make this configureable. Let me know which solution you prefer and i will open a PR.

ISSUE TYPE

Bug Report

COMPONENT NAME

mysql_user

ANSIBLE VERSION

ansible [core 2.11.1] 
  config file = None
  configured module search path = ['/home/vagrant/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /home/vagrant/.local/lib/python3.8/site-packages/ansible
  ansible collection location = /home/vagrant/.ansible/collections:/usr/share/ansible/collections
  executable location = /home/vagrant/.local/bin/ansible
  python version = 3.8.10 (default, Nov 26 2021, 20:14:08) [GCC 9.3.0]
  jinja version = 2.10.1
  libyaml = True

COLLECTION VERSION

# /home/vagrant/.ansible/collections/ansible_collections
Collection        Version
----------------- -------
containers.podman 1.7.1  
netbox.netbox     3.1.1  

# /home/vagrant/.local/lib/python3.8/site-packages/ansible_collections
Collection                    Version
----------------------------- -------
amazon.aws                    1.5.0  
ansible.netcommon             2.0.2  
ansible.posix                 1.2.0  
ansible.utils                 2.1.0  
ansible.windows               1.5.0  
arista.eos                    2.1.1  
awx.awx                       19.0.0 
azure.azcollection            1.5.0  
check_point.mgmt              2.0.0  
chocolatey.chocolatey         1.1.0  
cisco.aci                     2.0.0  
cisco.asa                     2.0.1  
cisco.intersight              1.0.15 
cisco.ios                     2.0.1  
cisco.iosxr                   2.1.0  
cisco.meraki                  2.2.1  
cisco.mso                     1.1.0  
cisco.nso                     1.0.3  
cisco.nxos                    2.2.0  
cisco.ucs                     1.6.0  
cloudscale_ch.cloud           2.1.0  
community.aws                 1.5.0  
community.azure               1.0.0  
community.crypto              1.6.2  
community.digitalocean        1.1.1  
community.docker              1.5.0  
community.fortios             1.0.0  
community.general             3.0.2  
community.google              1.0.0  
community.grafana             1.2.1  
community.hashi_vault         1.1.3  
community.hrobot              1.1.1  
community.kubernetes          1.2.1  
community.kubevirt            1.0.0  
community.libvirt             1.0.1  
community.mongodb             1.2.1  
community.mysql               2.1.0  
community.network             3.0.0  
community.okd                 1.1.2  
community.postgresql          1.2.0  
community.proxysql            1.0.0  
community.rabbitmq            1.0.3  
community.routeros            1.1.0  
community.skydive             1.0.0  
community.sops                1.0.6  
community.vmware              1.9.0  
community.windows             1.3.0  
community.zabbix              1.3.0  
containers.podman             1.5.0  
cyberark.conjur               1.1.0  
cyberark.pas                  1.0.6  
dellemc.enterprise_sonic      1.0.3  
dellemc.openmanage            3.3.0  
dellemc.os10                  1.1.1  
dellemc.os6                   1.0.7  
dellemc.os9                   1.0.4  
f5networks.f5_modules         1.9.0  
fortinet.fortimanager         2.0.2  
fortinet.fortios              2.0.1  
frr.frr                       1.0.3  
gluster.gluster               1.0.1  
google.cloud                  1.0.2  
hetzner.hcloud                1.4.3  
hpe.nimble                    1.1.3  
ibm.qradar                    1.0.3  
infinidat.infinibox           1.2.4  
inspur.sm                     1.1.4  
junipernetworks.junos         2.1.0  
kubernetes.core               1.2.1  
mellanox.onyx                 1.0.0  
netapp.aws                    21.2.0 
netapp.azure                  21.5.0 
netapp.cloudmanager           21.5.1 
netapp.elementsw              21.3.0 
netapp.ontap                  21.5.0 
netapp.um_info                21.5.0 
netapp_eseries.santricity     1.2.7  
netbox.netbox                 3.0.0  
ngine_io.cloudstack           2.1.0  
ngine_io.exoscale             1.0.0  
ngine_io.vultr                1.1.0  
openstack.cloud               1.4.0  
openvswitch.openvswitch       2.0.0  
ovirt.ovirt                   1.4.2  
purestorage.flasharray        1.8.0  
purestorage.flashblade        1.6.0  
sensu.sensu_go                1.9.4  
servicenow.servicenow         1.0.5  
splunk.es                     1.0.2  
t_systems_mms.icinga_director 1.16.0 
theforeman.foreman            2.0.1  
vyos.vyos                     2.2.0  
wti.remote                    1.0.1

CONFIGURATION

no changed configuration

OS / ENVIRONMENT

Ubuntu 20.04.2 LTS @ ansible controller RHEL7.9 with Percona Server 5.7.35 @ database hosts

STEPS TO REPRODUCE

You need a replication setup with at least three chained nodes.

writer -> intermediate -> backend

On the intermediate node you configure a replication filter ("binlog-ignore-db = mysql") to apply changes to the mysql database locally but not write them to the binlog for further replication (you could also do replicate-ignore-db to apply on this node).

The writer and intermediate pair share their mysql permissions. The backend has completely different permissions.

When you use the mysql_user plugin to change or remove a user the replication will break on the backend with it being unable to find the entries that need to be changed. When you use the mysql_user plugin to add a user it will add the user on the backend even though that is not supposed to happen.

EXPECTED RESULTS

Statements properly filtered by the intermediate node and not replicated to the backend.

ACTUAL RESULTS

Replication breaks or user is wrongfully added to the backend

Andersson007 commented 2 years ago

@d-rupp hi and welcome to the project! Needs opinion of specialists, @bmalynovytch @Jorge-Rodriguez @rsicart what do you think of the issue and the suggested options?

bmalynovytch commented 2 years ago

Hi @d-rupp !

Thank you for your submission and your time. What I don't get is, at what point in time did MariaDB or MySQL introduce "per database" user management ?

Module mysql_user doesn't allow a database name to be specified because user management is global (but still allows to specify per database privileges)

d-rupp commented 2 years ago

No, that´s not what i mean.

User management is global - and it is written into the "mysql" database.

We are using replication filters (for example binlog-ignore-db) to exclude the "mysql" database from replication on ONE backend node (of several) so we can manage separate permissions on that server (for customer BI queries for example). Replication filters are a complicated issue, i suggest you take a look at the documentation link.

But global statements are ALWAY replicated and can not be filtered. Since the mysql_user module issues the user management queries as global statements they will always be applied, no matter if you want them filtered out or not.

But if i set the database to "mysql" or connect to the mysql shell, issue a "use mysql" and then issue "create user xyz, grant foo to user, etc" those statements will not be replicated to a node that is using a replication filter on the mysql database.

My suggestion would be to make this a configureable setting for those who need it.

bmalynovytch commented 2 years ago

Got it !

Maybe dumb question, but why not enforcing usage of mysql DB when managing users ? That would solve your issue, and shouldn't harm other users ? (it would still require some tests to be confirmed)

d-rupp commented 2 years ago

That is a 5 letter solution, i´m all for it :)

bmalynovytch commented 2 years ago

If you're ready to be part of History, feel free to fill up a PR 😉

ansible-collections / community.mysql