FATAL: database "root" does not exist about libzbxpgsql HOT 8 CLOSED

Thanks for logging this issue. Hopefully we can get it fixed.

I'm unclear where you are seeing the pasted error log. You've said it's the output from zabbix_agentd -p | grep '^pg.' but it looks like content from the agent logs. Can you please clarify the following:

• A sample of the agent log content
• The output of sudo zabbix_agentd -p | grep '^pg.' (sudo so it runs as the same account as the zabbix agent daemon)
• The error shown in the Zabbix web console for the unsupported items

From the information above, it looks like the Zabbix agent is running as root and you are attempting to connect using Unix sockets (not TCP) with peer or ident authentication. If you intend to run the agent as root, but connect to the PostgreSQL server as zabbix, you need to map the root identity to the zabbix role if your pg_ident.conf file. E.g:

# MAPNAME       SYSTEM-USERNAME         PG-USERNAME
zabbix                  root                                       zabbix

You then also need to specify the username in your connection string (E.g. user=zabbix). By default this is set in the {$PG_CONN} macro for the PostgreSQL monitoring template.

Restart/reload your PostgreSQL server to refresh the configuration.

If I've understood your scenario correctly, please give this a try and advise the outcome.

from libzbxpgsql.

Here is an example of zabbix_agentd.log output:

10545:20151222:204651.979 Failed to connect to PostgreSQL in pg_connect():
fe_sendauth: no password supplied

10545:20151222:204651.979 active check "pg.stats_reset[host=127.0.0.1 port=5432 user=zabbix,zabbix]" is not supported
10545:20151222:204651.982 Failed to connect to PostgreSQL in pg_connect():
fe_sendauth: no password supplied

Here is the results of running the command: sudo zabbix_agentd -p | grep '^pg.'
[....]$ sudo zabbix_agentd -p | grep '^pg.'
[sudo] password for .....:
zabbix_agentd [9429]: Starting agent module libzbxpgsql 0.2.1
zabbix_agentd [9429]: Warning: Executing command 'echo test'
pg.modver [s|libzbxpgsql 0.2.1]
zabbix_agentd [9429]: Error: Failed to connect to PostgreSQL in pg_connect():
FATAL: database "root" does not exist

pg.connect [u|0]
zabbix_agentd [9429]: Error: Failed to connect to PostgreSQL in pg_connect():
FATAL: database "root" does not exist

pg.version [m|ZBX_NOTSUPPORTED] [Item is not supported.]
zabbix_agentd [9429]: Error: Failed to connect to PostgreSQL in pg_connect():
FATAL: database "root" does not exist

pg.starttime [m|ZBX_NOTSUPPORTED] [Item is not supported.]
zabbix_agentd [9429]: Error: Failed to connect to PostgreSQL in pg_connect():
FATAL: database "root" does not exist
........
pg.index.idx_blks_hit [m|ZBX_NOTSUPPORTED] [Item is not supported.]
pgsql.get.pg.transactions [t|sh: /pgsql_server_funcs.sh: No such file or directory]
pgsql.get.pg.bgwriter [t|sh: /pgsql_server_funcs.sh: No such file or directory]
pgsql.get.pg.slow_query [t|sh: /pgsql_server_funcs.sh: No such file or directory]
pgsql.get.pg.stat_database [t|sh: /pgsql_db_funcs.sh: No such file or directory]
pgsql.get.pg.size [t|sh: /pgsql_userdb_funcs.sh: No such file or directory]
pgsql.get.pg.stat_table [t|sh: /pgsql_tbl_funcs.sh: No such file or directory]
pgsql.get.pg.stat_replication [t|sh: /pgsql_sr_server_funcs.sh: No such file or directory]
pgsql.get.pg.sr.status [t|sh: /pgsql_sr_server_funcs.sh: No such file or directory]
pgpool.running [t|sh: /pgpool_simple.sh: No such file or directory]
pgpool.have_delegate_ip [t|sh: /pgpool_delegate_ip.sh: No such file or directory]
pgpool.get.nodes [t|sh: /pgpool_backend_status.sh: No such file or directory]
pgpool.get.connections [t|sh: /pgpool_connections.sh: No such file or directory]
pgpool.get.cache [t|sh: /pgpool_cache.sh: No such file or directory]

The Zabbix web console shows the error "Item is not supported."

The added line in pg_hba.conf:
local all root ident map=root
local zabbix zabbix md5

The added line in pg_ident.conf:
root zabbix root
root root root

My {$PG_CONN} macro in Zabbix web interface, specified under the host:
host=127.0.0.1 port=5432 user=zabbix

from libzbxpgsql.

I have updated my {$PG_CONN} macro in Zabbix web interface, specified under the host to the following:
host=127.0.0.1 port=5432 user=zabbix password=(password for this user)

I have also updated my pg_ident.conf file to match the configuration that you had specified above.

I now have most of the items successfully being collected by the Zabbix agent and the connection is successful after following your directions. There are a few still becoming unsupported. Will update as more information is available.

I believe that the main cause of the problem was that my pg_ident.conf file was configured incorrectly, and my {$PG_CONN} macro was also missing the "password" key/value pair

from libzbxpgsql.

Excellent thank you!

Your pg_hba.conf should look like:

local all zabbix peer map=zabbix

Your pg_ident.conf should look like:

zabbix root zabbix

You should also omit host and port from your connection string, or set it to the local socket path (E.g. host=/tmp) instead of TCP/IP. If you want to use TCP/IP with md5 auth, you need to specify a password in your connection string or ~/.pgpass file.

If you configure the above setup:

• The zabbix agent (running as root) uses the connection string to establish a Unix socket connection as the zabbix PostgreSQL role
• PostgreSQL uses peer authentication, which queries the OS and determines the connection is coming from the root system user
• PostgreSQL uses pg_ident.conf to allow the root system user to map as the zabbix PostgreSQL role

If you then see the database "foo" does not exist error, it is because PostgreSQL, by default, assumes the database you wish to connect to has the same name as the connected user. To fix this, simply specify the desired database in the second parameter of your item keys (or the provided {$PG_DB} macro).

from libzbxpgsql.

For further clarity, please see:

• Host base access: http://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html
• User name maps: http://www.postgresql.org/docs/current/static/auth-username-maps.html

from libzbxpgsql.

Everything is now working as expected! 👯

Thank you very much for your help, for this template and rpm.
I believe that this issue can be closed at this point.
Great work 👍

from libzbxpgsql.

You're welcome. Thanks for the feedback!

from libzbxpgsql.

Everything is now working as expected! 👯

Thank you very much for your help, for this template and rpm.
I believe that this issue can be closed at this point.
Great work 👍

how did it work bro? what changes that you made ??

from libzbxpgsql.