Date: Fri, 28 Aug 2020 09:18:56 -0400
From: Stephen Frost <sfrost@snowman.net>
To: gkokolatos@pm.me
Cc: "pgsql-hackers@lists.postgresql.org" <pgsql-hackers@lists.postgresql.org>
Subject: Re: New default role- 'pg_read_all_data'
Message-ID: <20200828131856.GY29590@tamriel.snowman.net>
References: <20200828003023.GU29590@tamriel.snowman.net>
 <CABUevEy9QAnx5h_BmSk4j6K9AfS9=co15RUFda0kknMHO9NJOg@mail.gmail.com>
 <159861699631.18329.14711450552365728705.pgcf@coridan.postgresql.org>
 <20200828124303.GW29590@tamriel.snowman.net>
 <knPzzcYHv2mAGCKE_HQ4HCinPYKGdQHCTj2K3KY5XC8GzKlFLmAb6o9BSAkRDmKfxGvn785XU0Vh9-divNUgE6hufDihLa4ELP6aATUU7LE=@pm.me>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="PTdhBr2Ly68TfuJh"
Content-Disposition: inline
In-Reply-To: 
 <knPzzcYHv2mAGCKE_HQ4HCinPYKGdQHCTj2K3KY5XC8GzKlFLmAb6o9BSAkRDmKfxGvn785XU0Vh9-divNUgE6hufDihLa4ELP6aATUU7LE=@pm.me>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk


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

Greetings,

* gkokolatos@pm.me (gkokolatos@pm.me) wrote:
> On Friday, 28 August 2020 15:43, Stephen Frost <sfrost@snowman.net> wrote:
> > > What privileges would the user be left with? Would it be possible to =
end up in the same privilege only with a GRANT command?
> >
> > I'm not sure what's being asked here.
>=20
> You are correct. My phrasing is not clear. Please be patient and allow me=
 to try again.
>=20
> I was playing around with the code and I was trying a bit the opposite of=
 what you have submitted in the test file.
>=20
> You have, (snipped):
>=20
> GRANT pg_read_all_data TO regress_priv_user6;
>=20
> SET SESSION AUTHORIZATION regress_priv_user6;
> SELECT * FROM atest1; -- ok
> INSERT INTO atest2 VALUES ('foo', true); -- fail
>=20
>=20
> I was expecting:
> REVOKE pg_read_all_data FROM regress_priv_user6;

Are you sure this REVOKE was successful..?

> SET SESSION AUTHORIZATION regress_priv_user6;
> SELECT * FROM atest1; -- fail
> INSERT INTO atest2 VALUES ('foo', true); -- ok

=3D# create role r1;
CREATE ROLE
=3D*# grant pg_read_all_data to r1;
GRANT ROLE
=3D*# create table t1 (c1 int);
CREATE TABLE
=3D*# set role r1;
=3D*> select * from t1;
 c1=20
----
(0 rows)
=3D*> reset role;
RESET
=3D*# revoke pg_read_all_data from r1;
REVOKE ROLE
=3D*# set role r1;
SET
=3D*> select * from t1;
ERROR:  permission denied for table t1

Seems to be working as expected here.

> My expectation was not met since in my manual test (unless I made a mista=
ke which is entirely possible), the SELECT above did not fail. The insert d=
id succeed though.

That the INSERT worked seems pretty odd- could you post the exact
changes you've made to the regression tests, or the exact script where
you aren't seeing what you expect?  I've not been able to reproduce the
GRANT allowing a user to INSERT into a table.

> The first question: Was my expectation wrong?

If there aren't any other privileges involved, then REVOKE'ing the role
=66rom a user should prevent that user from being able to SELECT from the
table.

> The second question: Is there a privilege that can be granted to regress_=
priv_user6 that will not permit the select operation but will permit the in=
sert operation? If no, should there be one?

GRANT INSERT ON atest1 TO regress_prive_user6; would allow just
INSERT'ing.

Magnus also brought up the idea of a 'write_all_data' role, but that's
pretty independent of this, imv.  Not against adding it, if we can agree
as to what it means, exactly, but we should probably discuss over in
that sub-thread.

Thanks,

Stephen

--PTdhBr2Ly68TfuJh
Content-Type: application/pgp-signature; name="signature.asc"

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

iQIcBAEBCgAGBQJfSQRAAAoJEO1sijiDR2RV5xQP/jCS8M8h4bV7kAnQlntwBHii
VFuW6rOab+9zo7oRzj6OHQOt9Sl4bRpdLteUlOswWSIrj4cnbOaX2vx7h+tHjwoI
p6hgbwvjIWAV/qTLviKmuHvPdhXCRLBCtvQljgEBD+7ztiiL5cm/VXYMkDij4lOJ
zqUxQZrRPGNKLxO0Fx3Xy/1SpOe1Sv6mF71HIviSXQL7S4V2N6yqiwEbX6fJAsR4
nbzNZ8WeCyz6AYTOtPj8YUzdA/aFTHSNTizyFFyyHSXNA9wdG77UXjYP1JvdMImv
W8saHWJIh4uqJZThetCmQXksPewS+Jbkl4fGvvFKau+i4d4neVv7j4F3Ui29h2lB
2QJBoCi1c0RX/Vc32dXHTg4wvz1zGqv/GT4WBQt+Dx1SZt3y7noCrGRmUdJW+X0p
3xKiE9ojQL2LuCm4/POCf4oNfrtfjorJcU0pw4qY/lBsEfd4wfL8lX+MYBGxR5Vk
tmUed4FushX5BguLGv+otbHe9uoCEVgUWlgMfKn3anMgm2DcOhThU2+heQry0uK7
aXZxRjryw4ifo/G859kGTBWZxX/cV3/cbdeJ37vLP4me82AOUQ0CS2VVg+Rl9GGz
ialcpGcsf2PxzrSxbeA1W9sUw5b1g5NgNfg/VltcEHjuAJzBqUi4wRcG5ljCmccO
0FNg0VOyqXZUaiLN3Fx8
=Utk9
-----END PGP SIGNATURE-----

--PTdhBr2Ly68TfuJh--