As an adhoc solution on 9.2, you can do something like this:
1. Create a function that extracts all from pg_stat_replication:
create or replace function pg_stat_repl() returns setof pg_catalog.pg_stat_replication as $$begin return query(select * from pg_catalog.pg_stat_replication); end$$ language plpgsql security definer;
2. Create a view that uses this function to get data in it:
create view public.pg_stat_repl as select * from pg_stat_repl();
3. Grant select on this view to your unprivileged user, sat 'common_user' :
After this, you can do a select on this view to get the required information. You can do this for other pg_catalog functions as well. Reference - https://github.com/xzilla/secure_check_postgres/blob/master/sql/pg_stat_activity.sqlgrant select on public.pg_stat_repl to common_user;
On Tue, Mar 31, 2015 at 1:52 PM, <jesse.waters@xxxxxxxxx> wrote:
Stephen,
Thanks for the information. We are currently running 9.2 instance
which we are using streaming replication for DR. I do not see an
upgrade anytime in the near future to 9.4 or later due to specfic
requirements for our application.
Will your modifications be backported to 9.2?
TIA,
Jesse
ps, sorry gilberto, selected wrong send to
> ---
On Tue, Mar 31, 2015 at 10:47 AM, Gilberto Castillo
<gilberto.castillo@xxxxxxxxx> wrote:
>
>
>> Gilberto,
>>
>> * Gilberto Castillo (gilberto.castillo@xxxxxxxxx) wrote:
>>> > * Gilberto Castillo (gilberto.castillo@xxxxxxxxx) wrote:
>>> >> > * jesse.waters@xxxxxxxxx (jesse.waters@xxxxxxxxx) wrote:
>>> >> >> Could someone tell me what permission is required to
>>> >> >> select * from pg_stat_replication; ?
>>> >> >
>>> >> > Currently, you're required to have superuser rights.
>>> >> >
>>> >> >> I like to setup a monitor to query database with minimal
>>> privileges
>>> >> >> necessary.
>>> >> >
>>> >> > I agree 110% and am actively working to fix exactly this issue. I
>>> >> hope
>>> >> > to have a patch in the next day or so which will allow you to GRANT
>>> >> > rights to such a monitor user which will allow that user to see all
>>> >> the
>>> >> > contents of pg_stat_replication.
>>> >> >
>>> >> > One thing which would be really great is if you have time to test
>>> with
>>> >> > the patch I'm working up (it's against 9.5, but this is strictly
>>> >> > functionality testing and should be just in in a dev/test
>>> environment,
>>> >> > I wouldn't suggest running 9.5 in production, of course!).
>>> > [...]
>>> >> SET SESSION AUTHORIZATION postgres;
>>> >>
>>> >> GRANT SELECT ON pg_stat_replication TO usuario1;
>>> >
>>> > This is (essentially) what I'm hoping to enable. Note that this won't
>>> > do anything for you today as the view is already available to all
>>> users
>>> > on the system and it's actually the function underneath which is
>>> > filtering the result set.
>>>
>>> ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT SELECT ON VIEWS TO
>>> usuario1;
>>>
>>> Nor does it work?
>>
>> No. Feel free to try, but there's an explicit check in the C code which
>> is what the SQL-level function that's under the view is calling. In
>> current 9.5/master, at least, it's at:
>>
>> src/backend/replication/walsender.c:2797
>>
>> if (!superuser())
>> {
>> /*
>> * Only superusers can see details. Other users only get the
>> pid
>> * value to know it's a walsender, but no details.
>> */
>> MemSet(&nulls[1], true, PG_STAT_GET_WAL_SENDERS_COLS - 1);
>> }
>>
>
>
> Thanks Stephen for you information.
>
>
> Rgds,
> Gilberto Castillo
> La Habana, Cuba
>
> This message was processed by Kaspersky Mail Gateway 5.6.28/RELEASE running at host imx3.etecsa.cu
> Visit our web-site: <http://www.kaspersky.com>, <http://www.viruslist.com>
>
--
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
