Re[2]: [ADMIN] getting client_addr not as a superuser - Mailing list pgsql-admin

From Михаил
Subject Re[2]: [ADMIN] getting client_addr not as a superuser
Date
Msg-id 1423825115.520792280@f229.i.mail.ru
Whole thread Raw
In response to Re: getting client_addr not as a superuser  (Luis Antonio Dias de Sá Junior <luisjunior.sa@gmail.com>)
List pgsql-admin
Hi,
replication permissions doesn't help:

=> \du zabbix
List of roles
Role name │ Attributes │ Member of
───────────┼─────────────┼───────────
zabbix │ Replication │ {}

[local] zabbix@postgres
=> select client_addr from pg_stat_replication;
client_addr
─────────────
NULL
(1 row)
Seems like for that moment function with security definer is the only solution, though it smells like workaround.


Вторник, 10 февраля 2015, 12:11 -02:00 от Luis Antonio Dias de Sá Junior <luisjunior.sa@gmail.com>:

Hi,

Have you try to put replication permissions? 

Ex. CREATE ROLE username LOGIN
  PASSWORD 'bla'
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE REPLICATION;

2015-02-10 10:53 GMT-02:00 Jov <amutu@amutu.com>:

you can create a function with security differ option as superuser.

2015年2月10日 8:22 PM于 "Михаил" <bemewe@mail.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




--
Luis Antonio Dias de Sá Junior


С уважением,

bemewe@mail.ru

pgsql-admin by date:

Previous
From: Михаил
Date:
Subject: database role create date
Next
From: Dave Johansen
Date:
Subject: Re: Postgres 8.4.20 seqfault on RHEL 6.4