Strange issue

[philou31]

Hello all,

Sorry for my english, I'm french.

I have a strange issue regarding zabbix account on zabbix server. I explain...

About 1 year ago I've installed Zabbix on a Linux server (Debian) named "serverA".
I've created the user 'zabbix'@'serverA' and gave grants privileges to zabbix DB (on MariaDB).
All worked fine.

A couple of months later I've decided to change the hostname from "serverA" to "serverB".
I've created the user 'zabbix'@'serverB' and gave grants privileges to zabbix DB (on MariaDB) and I've deleted the user 'zabbix'@'serverA'.
That's when the first issue arrose :
When I wanted to add or modify something on zabbix UI I received an error message saying that the user definer 'zabbix'@'serverA' (user with old hostname) didn't have rights on DB. (???)
After a couple of hours to try to understand why I've decided to recreate the user 'zabbix'@'serverA' and gave grants privileges to zabbix DB.
And all worked fine.

And yesterday I've decide to (re)modify the hostname from "serverB" to "serverC".
I've created the user 'zabbix'@'serverC' and gave grants privileges to zabbix DB (on MariaDB) and I've deleted the users 'zabbix'@'serverA' and 'zabbix'@'serverB'.
But I've forgotten the first issue...
So another issue arrose :
When I wanted to add or modify something on zabbix UI I received an error message saying that the user definer 'zabbix'@'serverB' (user with second old hostname) didn't have rights on DB. (???)
I've decided to recreate the user 'zabbix'@'serverB' and gave grants privileges to zabbix DB.
But... and it's very strange... I received an second error message saying that the user definer 'zabbix'@'serverA' (user with first old hostname) didn't have rights on DB.
Incomprehensible !!!
So I've decided to recreate the user 'zabbix'@'serverA' and gave grants privileges to zabbix DB.
And all worked fine.

But now I have 3 definer users "zabbix" with 3 different hosts !!!
I don't understand why and how to resolve this issue and keep only the last definer user "zabbix" with the last hostname 'serverC'.

Thank's for your help.

For information :
- only hostnames have been changed, not IP address


*******
Zabbix 7.0.0rc3

[philou31]

I've resolved the issue!!!!!


This issue was related to zabbix database triggers.


I've found this solution to resolve it:

- Create mysql triggers dump:

mysqldump -u root -p --routines --add-drop-trigger --no-create-info --no-data --no-create-db --skip-opt zabbix > outputfile.sql

- Change old DEFINER in the file outputfile.sql (using sed or manually as you want). For me it was old DEFINERS.

- Import all new triggers in the database:

mysql -u root -p zabbix < outputfile.sql

After these actions I've modified "zabbix"@"%" to "zabbix"@"fr-srvmonitor" and all works fine now with zabbix.

[Markku]

Is MariaDB on the same server as Zabbix or on different server? What is the database server address you are using in Zabbix server and frontend configurations? Is everything ok in the DB server's /etc/hosts?

Markku

[philou31]

MariaDB is on a different server (on a NAS).
Database server address used in Zabbix server and fronted are exactly the same used since the first installation: NAS address.
DB server's /etc/hosts is OK and never change since the first installation (except the hostname).

[Markku]

For some reason the DNS reverse name resolution does not work expectedly if MariaDB there still thinks the incoming user is "@serverA".

Workaround (if you cannot fix the NAS DNS resolution for, you know, reasons) could be using the Zabbix server IP address in the MariaDB username spec. See the MariaDB documentation:

https://mariadb.com/kb/en/create-user/#host-name-component

Markku

[philou31]

So I must create the user 'zabbix'@'IP_ZABBIX_SERVER' and delete users 'zabbix'@'serverA,B,C', that's it?

[Markku]

So I must create the user 'zabbix'@'IP_ZABBIX_SERVER' and delete users 'zabbix'@'serverA,B,C', that's it?

Yes, that's what I would do to remove any dependency for name resolution.

Markku

[philou31]

OK. Done. User 'zabbix'@'IP_ZABBIX_SERVER' created and users 'zabbix'@'serverA,B,C' deleted.
But no change, always the same issue. Zabbix need to have users 'zabbix'@'serverA,B' created.

[Markku]

I see these options right of the bat:

1. We have misunderstood something. Maybe post the exact error message (text for searchability and screenshot for exact context), along with the relevant exact configurations you have (confidential information replaced with unique identifiers)
2. MariaDB server (the NAS) has problems with reverse name resolution and resolves IP addresses to incorrect hostnames (could explain why incorrect hostnames are shown in the said errors)
3. The database permission configurations are somehow incorrect (could explain why the IP address-based permissions are not working in your MariaDB)
4. You have network address translation (NAT) between Zabbix and the database so that the database does not see the real IP of Zabbix frontend (could also mess the DNS resolution depending on the setup)

For reference, IP addresses work in my remote MariaDB (10.11.6-MariaDB-0+deb12u1) just fine:

MariaDB [mysql]> show grants for 'zabbix'@'192.168.7.99'\G
*************************** 1. row ***************************
Grants for [email protected]: GRANT USAGE ON *.* TO `zabbix`@`192.168.7.99` IDENTIFIED BY PASSWORD 'xxx'
*************************** 2. row ***************************
Grants for [email protected]: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `zabbix`.* TO `zabbix`@`192.168.7.99`
2 rows in set (0.000 sec)

In any case, unless some new information arrives, the problem is not exactly a Zabbix problem so you may be best to search from some database-centric place for the help.

Markku

[philou31]

I will go to a MariaDB forum, perhaps I will find something.

[philou31]

After some tests I think the issue is from Zabbix and not from MariaDB.
Test done:
I changed hostname for one of my servers (not Zabbix server), modified rights on DB (exactly as I've made for Zabbix server) ==> no issue.

[Markku]

Note that Zabbix (or any other app) does not provide the DB connection with the hostname, it is the MariaDB host (NAS, you say) that determines the hostname/IP of the incoming connection to use for the hostname part in the authentication. Your test confirms that NAS is able to resolve the hostname of the test server.

Markku

[philou31]

I don't understand...
When I use Zabbix server to modify something using the user "zabbix"@"serverC" (using php I suppose) it doesn't work because there is a need to have users "zabbix"@"serverA,B" created. OK.
But when I use another user (myuser) from another server (server with hostname changed serverX to serverY), using command line it works without a need to have the old user "myuser"@"serverX" created. And when I use the same user from the same server (with hostname changed), using php (I have a website in php), no issue too.
And when I check /etc/hosts on NAS, it's OK: good hostname associated to good IP.
So, if I'm right, the issue doesn't come from NAS.

[Markku]

Use for example "dig -x x.x.x.x" (where x.x.x.x is the Zabbix server+frontend IP address) to resolve the hostname on the NAS. What do you get?

You haven't commented about the NAT.

Also, how about testing with "zabbix"@"%"?

Markku

[philou31]

I haven't "dig" on the NAS. For having it I must install a DNS server on the NAS but I don't want to, my internet box is my DNS (perhaps it's a bad idea).

I haven't NAT configured on my internet box or elsewhere.

I will test "zabbix"@"%", with all users "zabbix"@"serverA,B,C" deleted.

[philou31]

"zabbix"@"%", with all users "zabbix"@"serverA,B,C" deleted ==> works without issue.