pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedgetting client_addr not as a superuser
3+ messages / 3 participants
[nested] [flat]
* getting client_addr not as a superuser
@ 2015-02-10 12:21 Михаил <bemewe@mail.ru>
0 siblings, 1 reply; 3+ messages in thread
From: Михаил @ 2015-02-10 12:21 UTC (permalink / raw)
To: pgsql-admin <pgsql-admin@postgresql.org>
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: getting client_addr not as a superuser
@ 2015-02-10 12:53 Jov <amutu@amutu.com>
parent: Михаил <bemewe@mail.ru>
0 siblings, 1 reply; 3+ messages in thread
From: Jov @ 2015-02-10 12:53 UTC (permalink / raw)
To: Михаил <bemewe@mail.ru>; +Cc: pgsql-admin <pgsql-admin@postgresql.org>
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
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: getting client_addr not as a superuser
@ 2015-02-10 14:11 Luis Antonio Dias de Sá Junior <luisjunior.sa@gmail.com>
parent: Jov <amutu@amutu.com>
0 siblings, 0 replies; 3+ messages in thread
From: Luis Antonio Dias de Sá Junior @ 2015-02-10 14:11 UTC (permalink / raw)
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2015-02-10 14:11 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-02-10 12:21 getting client_addr not as a superuser Михаил <bemewe@mail.ru>
2015-02-10 12:53 ` Jov <amutu@amutu.com>
2015-02-10 14:11 ` Luis Antonio Dias de Sá Junior <luisjunior.sa@gmail.com>
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