Date: Tue, 28 Mar 2017 14:43:48 -0400
From: Stephen Frost <sfrost@snowman.net>
To: Mark Dilger <hornschnorter@gmail.com>
Cc: Dave Page <dpage@pgadmin.org>,
	Peter Eisentraut <peter.eisentraut@2ndquadrant.com>,
	Robert Haas <robertmhaas@gmail.com>,
	PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Subject: Re: Monitoring roles patch
Message-ID: <20170328184348.GI9812@tamriel.snowman.net>
References: 
 <CA+OCxoyt9OMWaK=g2sL8qCTcwrS-8j3=06AZLzXQZ61sUn7gtA@mail.gmail.com>
 <CA+OCxozGTGd-x+cx03N=KJVK=soJwOpTWkq_=zac3P04aBCU4Q@mail.gmail.com>
 <20170323122335.GK9812@tamriel.snowman.net>
 <CA+OCxozZxJouNjY4+-heYSUk=qagoytQXnSjMyO5-R_a_mddKA@mail.gmail.com>
 <c1d3db24-85f0-96bb-0ec3-aeddd9d35941@2ndquadrant.com>
 <CA+OCxox8HW6obNKY5atVA6pX=Z=wQw6rTGcggy4N05k=mLkaAQ@mail.gmail.com>
 <FEE8D3A7-FDBF-432F-BEEF-F28B7D3AD2F4@gmail.com>
 <CA+OCxoy7rqEZ65byS+3E_0dsTKYD8NcUzQB1SiGknARmbxBTDg@mail.gmail.com>
 <957FCA21-99DA-4477-9A6E-C86ACAC23E36@gmail.com>
 <7161672F-1EC5-478F-B307-FF28B01D0D62@gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="G2hTv39+sQMPSeFF"
Content-Disposition: inline
In-Reply-To: <7161672F-1EC5-478F-B307-FF28B01D0D62@gmail.com>
User-Agent: Mutt/1.5.21 (2010-09-15)
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org


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

Greetings,

* Mark Dilger (hornschnorter@gmail.com) wrote:
> After a bit of introspection, I think what is really bothering me is not =
the
> inability to revoke permissions, since as you say I can choose to not ass=
ign
> the role to anybody.  What bothers me is that this feature implicitly red=
efines
> what is meant by the keyword PUBLIC.  If I go around the database
> revoking privileges on everything from PUBLIC, I should end up with
> a database that is inaccessible to anyone but superuser, right? =20

While I accept your concern and confusion, this isn't accurate.
REVOKE'ing rights from PUBLIC only removes those rights GRANT'd to
PUBLIC, it doesn't remove rights which have been GRANT'd to other users.

In a degenerate system where the only roles which exist are the
superuser role and the pseudo-role 'public', you would be correct with
your statement above, but this patch doesn't change that because you
will not have GRANT'd the new role to anyone.

> All views,
> functions, tables, etc., would all be locked down.  But after this propos=
ed
> change, IIUC, there would still be a bit of access available to this/these
> proposed non-superuser role(s) which have hardcoded permissions.

These non-superuser roles can't be logged into and if they are not
GRANT'd to anyone then it's essentially the same as if they don't exist.

> That's quite a significant change to the security model of the database,
> and I don't think it is something users would expect from the release not=
es
> if the release notes for this feature say something like:
>=20
> 	"added database monitoring functionality"

Having more in the release notes and in the documentation is certainly
important, but this is not changing the security model of the database
in any significant way.

> To be fair, I have not tried to revoke everything from everybody on a
> database to verify that their aren't already problems of this sort.  Perh=
aps
> the cows have already left the barn.

Right, which means that, in addition to the points made above, this
isn't a use-case which is actually even all that interesting to
consider.

Thanks!

Stephen

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

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

iQIcBAEBCgAGBQJY2q7kAAoJEO1sijiDR2RVDb4P/0Py2feQJ5QA5rhaMA3HzRGi
BUZqvbE41bW4PEjh3K0Xsh8v7CxL+FAFcZHxupOvG0QQwlUg4HPhGpEAqKoLPNGl
llee8l8wn5NeBuaC3VH2dorCyCR6SeXup+Gc344kMmNelS2lGcTGWDjp/ZNLKNKy
U3Gcfp/Au++ON3ez60vOJcIH16T4eEnuXDFSSyVyT4SIk7ihwpHz+q8xl8HVaSqC
SLBpiS24laPTUJOAA6MpVCuq/+NCi4e4eelc9ofj76q4Zi5XugDRWDHdCh27vIj0
zGpmIwhIxswtkBOS0nqqAx316uiHS4uXkfHWA+LUjKE9+cjWrzndmCrEXdyIIKm5
rmd0BaOR7Ntu3wI6RzDzS33IJgh7v/NQEhO1iwrnHhM313Tx2/hbthZcBhshm2el
GDT5+NPx+k/7vNQK5bAeFPbB90AKdfC5eOGsmo5MaWd+IiGyO+GqhITiiXxbElQD
fcW/mKEP3ANKGuqq32RJOHP6Qxucv25G1Eeq2xgI9LzoTZslSs8bF6XHDebwq81b
Wg3FZ0FXsc4BYSGMxb+QKc681OLwj1cp4ObyQa6EL+GNK4r130M2hiAfzY3PpPD8
FCIPrTZQt11nKAytopSIGPknWZFnpDZobb9KgVYUB3bLV+J3hh1B6Us6Eu38UXQb
MPEoc1Q009AHUjgU3n/B
=zDxC
-----END PGP SIGNATURE-----

--G2hTv39+sQMPSeFF--