you can create a function with security differ option as superuser.
2015年2月10日 8:22 PM于 "Михаил" <bemewe(at)mail(dot)ru>写道：
> Hi all,
> I have a PG 9.3 and a streaming replication and I need standby ip address
> in the monitoring. To get that i can run
>
> select client_addr from pg_stat_replication
>
> but i have to connect as a superuser what's not desirable.
>
> As i see in that view, it uses two functions: pg_stat_get_activity
> and pg_stat_get_wal_senders and one table pg_authid. As i don't need role
> information, i've cutted the table from the query and got the following
> query:
>
> SELECT s.pid, s.client_addr
> FROM pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid,
> application_name, state, query, waiting, xact_start, query_start,
> backend_start, state_change, client_addr, client_hostname, client_port)
> ,pg_stat_get_wal_senders() w(pid, state, sent_location,
> write_location, flush_location, replay_location, sync_priority, sync_state)
> WHERE s.pid = w.pid;
>
> When i run it as a superuser, everything is ok, when i run it as an
> ordinary user, the client_addr is NULL. As the
> function pg_stat_get_wal_senders() returns the result, the problem is in
> receiving the address from pg_stat_get_activity.
>
> Using/granting pg_stat_get_backend_client_addr() is not solving the
> problem.
>
> Is there any way to get client_addr value running not as a superuser?
>
>
> Regards, Mikhail
>
>