CODEWITHSUNDEEP
postgresqlreplicationmonitoring
Aleksei Golubev
Aleksei Golubev

Reputation: 352

Can't get access to streaming replication stats in PostgreSQL

I have streaming replication which I need to monitor. So there is a special user for Zabbix. I don't want to use pg_mongz and decided to set my own queries to pg_catalog schema's view pg_stat_replication to get replication state.

When I use query:

select * 
from pg_stat_replication;

it returns replication state record for admin. But when I logged in as monitoring user it returns just: 

pid, usesysid, usename, application_name

So such parameters as client_addr, client_hostname, client_port, backend_start, state, sent_location, write_location, etc. are empty.

First I granted rights to my user on schema and tables:

grant usage on schema pg_catalog to usrmonitor;
grant select on all tables in schema pg_catalog to usrmonitor;

but it didn't help. When I looked at view I found that query uses functions and granted execution:

grant execute on function pg_stat_get_wal_senders() to usrmonitor;
grant execute on function pg_stat_get_activity(integer) to usrmonitor;

But the select query still returns empty columns. What maybe the problem?

Upvotes: 3

Views: 3403

Answers (2)

martoche
martoche

Reputation: 529

Since PostgreSQL 10, it is as simple as :

GRANT pg_monitor TO monitoring_user;

(Source: https://pganalyze.com/blog/whats-new-in-postgres-10-monitoring-improvements)

Upvotes: 7

Daniel Vérité
Daniel Vérité

Reputation: 61646

Yes, access to these fields is intentionally restricted to superusers.

As a workaround, you may use a function as a proxy with the SECURITY DEFINER attribute:

SECURITY DEFINER specifies that the function is to be executed with the privileges of the user that created it.

So as a superuser (typically the postgres user), do:

CREATE FUNCTION func_stat_replication() RETURNS SETOF pg_stat_replication as
$$ select * from pg_stat_replication; $$
LANGUAGE sql SECURITY DEFINER;

Then revoke/grant the permission to use that function so that only the monitoring user is allowed to execute it:

REVOKE EXECUTE ON FUNCTION func_stat_replication() FROM public;
GRANT EXECUTE ON FUNCTION func_stat_replication() to usrmonitor;

Then usrmonitor should execute:

 SELECT * FROM func_stat_replication();

and it will have the same results as if it was superuser.

Upvotes: 3

Related Questions