Date: Tue, 31 Mar 2015 09:44:40 -0400
From: Stephen Frost <sfrost@snowman.net>
To: Gilberto Castillo <gilberto.castillo@etecsa.cu>
Cc: jesse.waters@gmail.com, pgsql-admin@postgresql.org
Subject: Re: Permission select pg_stat_replication
Message-ID: <20150331134440.GL3663@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>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
 protocol="application/pgp-signature"; boundary="gKaPnVNVpyX08bAX"
Content-Disposition: inline
In-Reply-To: <46009.192.168.207.54.1427812926.squirrel@webmail.etecsa.cu>
User-Agent: Mutt/1.5.21 (2010-09-15)
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org


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

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 wi=
th
> >> > 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 environmen=
t,
> >> > 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.
>=20
> ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT SELECT ON VIEWS  TO
> usuario1;
>=20
> 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

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

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

iQIcBAEBCgAGBQJVGqTIAAoJEO1sijiDR2RVne4P+QHLRnXYyPe6E+TDRRes0af3
H98Xxy61RMw0Ak7CW2bm03ocKS0F3VmMzhiMPm+G5v/i7MSRa4khOgSVq9vnVKEI
RUmfYP4pYYVQJmo55yIddLecaX4gRHU/7lT46e8aPr2U+E732uCCNBX/arFC5CW0
P+Jwfb/cHZ4H9PO45QKTs+CDQ9RJuWGwMqMpT6KimeUtXSk3LQEV6HxeKl81P/Af
i/gaJoMQYE28WlS+dBO1lWcZ4C/DRegXUEChEMLbPBbzp+gWSZafaF9qyQPr4+4+
4vBZjRDzQHGmbhRPiKR1CxFMg7ZdLOmy0VdG3wQEKTwwY/BK+T1skTFKeOinWJFG
WCD8C/TT2ilbFJeOhh3STFF7RSzUTlZ8b5DtzqiIsIZTvJsfC73bMua4PDgr4ZfM
TjByoofx8ODfeHSctFAppygldGBqfaQg4XHCOAPv7+t8LCE3riaEKWVUtkFe6YZa
HwnAXGJ6BN+C9ZS3SxFu765/X1XL0E9KUGbQN+G+fakB64c1k5wW8bkqrcwrQBKa
mtB6+dbfq9of8OeBAKYEc+/v+K3nbWCXNRRivZVDnp5hgTucDWlxIrO1LF63/PMg
Ca7l3wa6rPew+FOlD+ayzhvErdScjwerN/RtCZ6l6K5JWOsKrHoBPbvE5/aWtGkm
ahG54y9JLU7BtIaHTQAq
=mttb
-----END PGP SIGNATURE-----

--gKaPnVNVpyX08bAX--