Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Yd2ia-0003Ks-HA for pgsql-admin@arkaria.postgresql.org; Tue, 31 Mar 2015 20:25:00 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Yd2iZ-00059W-Gp for pgsql-admin@arkaria.postgresql.org; Tue, 31 Mar 2015 20:24:59 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1Yd2iY-00059K-9U for pgsql-admin@postgresql.org; Tue, 31 Mar 2015 20:24:58 +0000 Received: from mail-ie0-f181.google.com ([209.85.223.181]) by makus.postgresql.org with esmtps (TLS1.2:RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1Yd2iU-0004c3-9J for pgsql-admin@postgresql.org; Tue, 31 Mar 2015 20:24:56 +0000 Received: by iedfl3 with SMTP id fl3so29268591ied.1 for ; Tue, 31 Mar 2015 13:24:53 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc:content-type; bh=W3nwjxEkOPd4mnE86mKIEYga1sDc+VrO0aWuzgvq5T4=; b=ZwTM0ZPrYB1Fh6wsKz2Mz0KoKMLJJe17FKOEWjQgm7kQVU5WUF6H4roU6uLSr1O3Bd SOTgX6gVsGf36tOPQC35kg/PqfRg6HDKt7pTiPy66SfAHxRjDjx4yQoh9+/RZCS9ykQe 33Cu0w6HLWVhKOf+urVwHp1y8qVsAlT2cGoI8wNWkj5Pt3Zw7jXsUiXRPpmg+W8y7c1M s/1e+y/VKhs6yvN0TMjaX1T10gRqQSBWq8MgaJfAA2U3GoDcwFiNxm/n4g7aHdrQ+s7F c0qZCmjDWApKQbWnogTrMY2KDbXae6mIBLW9OtaLETgmiSVFO2QSsuWdKNVd2TadRaRx 7COw== X-Gm-Message-State: ALoCoQlCRyR/Rz24Rl151ny+iCoiFijtUnNxMY8JhY243MWytgZyJ8nmmEhhMk/0evmOCceErZXG X-Received: by 10.50.143.36 with SMTP id sb4mr7175552igb.0.1427833492685; Tue, 31 Mar 2015 13:24:52 -0700 (PDT) MIME-Version: 1.0 Received: by 10.36.155.131 with HTTP; Tue, 31 Mar 2015 13:24:32 -0700 (PDT) In-Reply-To: References: <20150331131252.GI3663@tamriel.snowman.net> <45998.192.168.207.54.1427812184.squirrel@webmail.etecsa.cu> <20150331133130.GK3663@tamriel.snowman.net> <46009.192.168.207.54.1427812926.squirrel@webmail.etecsa.cu> <20150331134440.GL3663@tamriel.snowman.net> <46016.192.168.207.54.1427813265.squirrel@webmail.etecsa.cu> From: Payal Singh Date: Tue, 31 Mar 2015 16:24:32 -0400 Message-ID: Subject: Re: Permission select pg_stat_replication To: jesse.waters@gmail.com Cc: "pgsql-admin@postgresql.org" Content-Type: multipart/alternative; boundary=001a11c26a56215e4d05129b64a6 X-Pg-Spam-Score: -2.6 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org --001a11c26a56215e4d05129b64a6 Content-Type: text/plain; charset=UTF-8 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' : grant select on public.pg_stat_repl to 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.sql Payal Singh, Database Administrator, OmniTI Computer Consulting Inc. Phone: 240.646.0770 x 253 On Tue, Mar 31, 2015 at 1:52 PM, 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 > wrote: > > > > > >> Gilberto, > >> > >> * Gilberto Castillo (gilberto.castillo@etecsa.cu) wrote: > >>> > * Gilberto Castillo (gilberto.castillo@etecsa.cu) wrote: > >>> >> > * jesse.waters@gmail.com (jesse.waters@gmail.com) 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.viruslist.com> > > > > > -- > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin > --001a11c26a56215e4d05129b64a6 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
As an adhoc solution on 9.2, you can do something like thi= s:

1. Create a function that extracts all from pg_stat_r= eplication:
create or replace function pg_stat_repl() returns set= of pg_catalog.pg_stat_replication as $$begin return query(select * from pg_= catalog.pg_stat_replication); end$$ language plpgsql security definer;
<= /div>

2. Create a view that uses this function t= o get data in it:
create view public.pg_stat_repl as select * fro= m pg_stat_repl();

3. Grant select on t= his view to your unprivileged user, sat 'common_user' :
<= blockquote style=3D"margin:0px 0px 0px 40px;border:none;padding:0px">
g= rant select on public.pg_stat_repl to common_user;

= After this, you can do a select on this view to get the requir= ed information. You can do this for other pg_catalog functions as well. Ref= erence -=C2=A0https://github.com/xzilla/secure_check_= postgres/blob/master/sql/pg_stat_activity.sql

Payal= Singh,=
Dat= abase Administrator,
OmniTI Computer Consulting Inc.
Phone:=C2=A0240.646.0770=C2=A0x 253

On Tue, Mar 31, 2015 at 1:52 PM, <je= sse.waters@gmail.com> wrote:

On Tue, Mar 31, 2015 at 10:47 AM, Gilberto Castillo
<gilberto.castillo@etecsa= .cu> wrote:
>
>
>> Gilberto,
>>
>> * Gilberto Castillo (gilberto.castillo@etecsa.cu) wrote:
>>> > * Gilberto Castillo (gilberto.castillo@etecsa.cu) wrote:
>>> >> > * jess= e.waters@gmail.com (jesse.wat= ers@gmail.com) wrote:
>>> >> >>=C2=A0 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 exac= tly this issue.=C2=A0 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 t= hat 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.=C2= =A0 Note that this won't
>>> > do anything for you today as the view is already availabl= e to all
>>> users
>>> > on the system and it's actually the function undernea= th which is
>>> > filtering the result set.
>>>
>>> ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT SELECT ON VIE= WS=C2=A0 TO
>>> usuario1;
>>>
>>> Nor does it work?
>>
>> No.=C2=A0 Feel free to try, but there's an explicit check in t= he C code which
>> is what the SQL-level function that's under the view is callin= g.=C2=A0 In
>> current 9.5/master, at least, it's at:
>>
>> src/backend/replication/walsender.c:2797
>>
>>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0if (!superuser())
>>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0{
>>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0/*
>>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 * Only superusers = can see details. Other users only get the
>> pid
>>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 * value to know it= 's a walsender, but no details.
>>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 */
>>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0MemSet(&nulls[1= ], true, PG_STAT_GET_WAL_SENDERS_COLS - 1);
>>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0}
>>
>
>
> Thanks Stephen for you information.
>
>
> Rgds,
> Gilberto Castillo
> La Habana, Cuba
>
> ---
> This message was processed by Kaspersky Mail Gateway 5.6.28/RELEASE ru= nning at host imx3.etec= sa.cu
> Visit our web-site: <http://www.kaspersky.com>, <http://www.viruslist.com>
>


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

--001a11c26a56215e4d05129b64a6--