When upgrading your Zabbix Database, you might encounter the following error “[Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR: permission denied for schema public”. The zabbix user you’ve created doesn’t normally need access to the database schema to work, so this would only be relevant when upgrading the zabbix version, for example between 6.2.x to 6.4.0.
To fix the error, you need to give the zabbix database user access to modify the public schema. In the following example I gave the zabbix user all permissions to the schema public.
“grant all on schema public to zabbix;”
# sudo systemctl stop zabbix-server
# sudo -u postgres psql
postgres=# \c zabbix postgres
You are now connected to database "zabbix" as user "postgres".
zabbix=# \d
List of relations
Schema | Name | Type | Owner
--------+----------------------------+----------+--------
public | acknowledges | table | zabbix
public | actions | table | zabbix
public | alerts | table | zabbix
....
zabbix=# grant all on schema public to zabbix;
GRANT
zabbix=# \q
# sudo systemctl start zabbix-server
# tail -fn 200 /var/log/zabbix/zabbix_server.log
126401:20230307:091205.217 current database version (mandatory/optional): 06030010/06030010
126401:20230307:091205.217 required mandatory version: 06040000
126401:20230307:091205.217 starting automatic database upgrade
126401:20230307:091205.227 completed 0% of database upgrade
126401:20230307:091205.229 completed 1% of database upgrade
126401:20230307:091205.232 completed 2% of database upgrade
126401:20230307:091205.235 completed 3% of database upgrade
....
126401:20230307:091205.572 completed 98% of database upgrade
126401:20230307:091205.579 completed 99% of database upgrade
126401:20230307:091205.579 completed 100% of database upgrade
126401:20230307:091205.581 database upgrade fully completed