Date: Tue, 31 Mar 2015 20:35:26 -0400
From: Stephen Frost <sfrost@snowman.net>
To: jesse.waters@gmail.com
Cc: pgsql-admin@postgresql.org
Subject: Re: Permission select pg_stat_replication
Message-ID: <20150401003526.GN3663@tamriel.snowman.net>
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>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
 protocol="application/pgp-signature"; boundary="Z1gw0Ba8VHj8Ap4r"
Content-Disposition: inline
In-Reply-To: 
 <CAL0F7nd=ObUBzDG-SCELm4d-zz6cN71E9dzi1bo8BEFkw3mJcw@mail.gmail.com>
User-Agent: Mutt/1.5.21 (2010-09-15)
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org


--Z1gw0Ba8VHj8Ap4r
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

Jesse,

No, it's a new feature and will not be back-patched.

9.2 will be deprecated in a few years anyway, so I suggest you work out
what your upgrade plans will be sooner rather than later as staying on
9.2 indefinitely is not a supportable option.

	Thanks!

		Stephen

* jesse.waters@gmail.com (jesse.waters@gmail.com) wrote:
> Stephen,
>=20
>  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.
>=20
> Will your modifications be backported to 9.2?
>=20
> TIA,
>=20
>  Jesse
>=20
>=20
> ps, sorry gilberto, selected wrong send to
>=20
> 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 GR=
ANT
> >>> >> > 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 wo=
n'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 whi=
ch
> >> 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 t=
he
> >> 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 run=
ning at host imx3.etecsa.cu
> > Visit our web-site: <http://www.kaspersky.com>, <http://www.viruslist.c=
om>
> >
>=20
>=20
> --=20
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin

--Z1gw0Ba8VHj8Ap4r
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iQIcBAEBCgAGBQJVGz1NAAoJEO1sijiDR2RV8WQP/iZ0D4iYWxTBzvh736JIkueL
lHvM8wUMHt6JuhS8Ji7ChbIS+8NPCAiW2gujA0WzIG16B/S8nDB49vXym0Dt7nEe
YTdgO1BYh0qgB6OK2JtEXfpvAejNkal3ZqbAZVYcTYrr0twxp60GMJMnD2nuI4np
udHP1nsSEgT346SIk1KpI5N0f/lBvnSHctHg6vsVg4DUMbI7gqoQOT0X6iBINdsP
0y6a6JJk6mvlQIIPombKRVgrBTLAzK72uNUJTNQ65wLUSZVZTOQVm+rF7xpFUFLR
uWvNoQsWdw1jE/rdxVNqD0dmp4walSfFF/n6Qrma5sVhDiKuf06MP3/5pP4gObwR
6ez7ewbpVBbEIVmvH322Ot2UmM2t+ouijTCbMRhpSuP5q4TOEW4dVNN5KyWa2zLi
vuTqq2UYIe2w86RrK3VAoAbif4iHkkOylFKS74DaRxoYawMle+AImAda8W8BlWdw
ZAsRkpyLS+1ptMOd6L58Zzx8YFD1gPok/owLj6mljwDgBFgs4OS21CBlzwsEOIcL
uDf/XWEPoYS+WWGsbVX+YZhJCatSUKvoYZU48tlg3LQHmCICqftYOPspzR6yMODG
IBU6uvzcNhzcS76fZeKr2G9t0SI/caDV1aa+FZxlgV/9nlIKDStfY5FaDEQIvp8u
YV6O8FbMZLvjj3UqEWWM
=CXHd
-----END PGP SIGNATURE-----

--Z1gw0Ba8VHj8Ap4r--