From: Payal Singh <payal@omniti.com>
To: jesse.waters@gmail.com
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: Permission select pg_stat_replication
Date: Tue, 31 Mar 2015 16:24:32 -0400
Message-ID: <CANUg7LBdmn2RE=zwNCSG78obj0hPteADQZXsz+7BbfPapdzAdA@mail.gmail.com> (raw)
In-Reply-To: <CAL0F7nd=ObUBzDG-SCELm4d-zz6cN71E9dzi1bo8BEFkw3mJcw@mail.gmail.com>
References: <CAL0F7ned0Lbcu_FnzcoLTRS8zpQsmkA0fyw7jXOz9X=WHyJTYw@mail.gmail.com>
<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>
<CAL0F7nd=ObUBzDG-SCELm4d-zz6cN71E9dzi1bo8BEFkw3mJcw@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
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, <jesse.waters@gmail.com> 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@etecsa.cu> 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.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
>
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-admin@postgresql.org
Cc: payal@omniti.com, jesse.waters@gmail.com
Subject: Re: Permission select pg_stat_replication
In-Reply-To: <CANUg7LBdmn2RE=zwNCSG78obj0hPteADQZXsz+7BbfPapdzAdA@mail.gmail.com>
* 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