From: Михаил <bemewe@mail.ru>
To: Luis Antonio Dias de Sá Junior <luisjunior.sa@gmail.com>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re[2]: [ADMIN] getting client_addr not as a superuser
Date: Fri, 13 Feb 2015 13:58:35 +0300
Message-ID: <1423825115.520792280@f229.i.mail.ru> (raw)
In-Reply-To: <CA+BNcWaUADQ4L3+Yg-qPsPnkm=vdmZUEGtiRdtuXsZrby6Xopw@mail.gmail.com>
References: <CAGuFTBWoL76nZ-DJqjX4aTyR0i5zJT+1KOFpe0-VTFB3y3DPtw@mail.gmail.com>
<CADyrUxNfX3P_wc7rCXu9dL_c9=1uwJ=YSWBfNyKOMeo3CvCr3Q@mail.gmail.com>
<CA+BNcWaUADQ4L3+Yg-qPsPnkm=vdmZUEGtiRdtuXsZrby6Xopw@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: bemewe@mail.ru, luisjunior.sa@gmail.com, pgsql-admin@postgresql.org
Subject: Re: Re[2]: [ADMIN] getting client_addr not as a superuser
In-Reply-To: <1423825115.520792280@f229.i.mail.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox