Date: Mon, 26 Apr 2021 13:29:45 -0400
From: Stephen Frost <sfrost@snowman.net>
To: Mats Kindahl <mats@timescale.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: Bug in handling default privileges inside extension update
 scripts
Message-ID: <20210426172945.GW20766@tamriel.snowman.net>
References: 
 <CA+144272PPX+y7L2V6DiYsv8pLhKrmpLmaZJnka2CKrXLnfY_A@mail.gmail.com>
 <20210422151509.GU20766@tamriel.snowman.net>
 <CA+14425Oyw1Wt7j0nUU2hOf3qa_=L8pR0q-K12TAkQ4EW2CXTw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="P4dgr8cd34/ubiGA"
Content-Disposition: inline
In-Reply-To: 
 <CA+14425Oyw1Wt7j0nUU2hOf3qa_=L8pR0q-K12TAkQ4EW2CXTw@mail.gmail.com>
User-Agent: Mutt/1.5.24 (2015-08-30)
Archived-At: 
 <https://www.postgresql.org/message-id/20210426172945.GW20766%40tamriel.snowman.net>
Precedence: bulk


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

Greetings,

* Mats Kindahl (mats@timescale.com) wrote:
> On Thu, Apr 22, 2021 at 5:15 PM Stephen Frost <sfrost@snowman.net> wrote:
> > * Mats Kindahl (mats@timescale.com) wrote:
> > > * To be able to read the configuration tables, "reader" need to have
> > >   SELECT privileges.
> > >
> > > * Since the new role is added by the user and not by the extension,
> > >   the grants have to be dumped as well. Otherwise, a restore of the
> > >   data will have wrong privileges.
> > >
> > > * Since new configuration tables could be added by an update of the
> > >   extension, it is necessary to make sure that these privileges are
> > >   added to new tables when updating. Typically, this means changing
> > >   the default privileges on the schema for the configuration files.
> >
> > If the extension is updated, I think it's entirely reasonable to expect
> > an admin to have to go in and update the relevant permissions on any new
> > tables that have come into existance and, as I've said elsewhere, I
> > don't think that schema-level default privs should be applied to tables
> > created by extensions.  Sadly, no one else seems to have an opinion
> > regarding that and so there hasn't been a change in that, yet, but
> > that's the source of the issue imv.
>=20
> That is a different way to solve it, but I think that is a little
> unintuitive. I am actually proposing to still assign default privileges,
> but not add them to initprivs, to make sure that they are treated the same
> way before and after an update.

Yes, I understood your suggestion, but I did, and still do, disagree
with that approach- how is an admin supposed to correctly guess what
permissions would be appropriate for new tables being added during an
upgrade of an extension?  Not to mention that extensions routinely get
added to existing schemas and I don't think it's at all obvious to
users that tables, functions, etc, added by an extension into a schema
should get the default privileges for that schema (and that could even
lead to security issues, I suspect...), not to mention that you have to
wonder if the privileges installed by the extension should be applied
*first*, and default privs after, or if the default privileges should
be first and the extension's privileges after.  As it's currently the
latter, it's rather complicated as the extension has no idea what to
expect the privileges on the object to be and so how can it sensibly set
privileges on it..?

More and more it looks clear to me that this is really just broken and
we need to stop applying default privs to objects created by extensions.

Thanks,

Stephen

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

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

iQIcBAEBCgAGBQJghviJAAoJEO1sijiDR2RV/UcQAMW+GZuzGz6/n52NYUBgDcdA
PfEKVaZz078S3spEf2AYqJH8uUlj42zhTQfpMEAsDgqtR3fpkAvv2kBob5yqmRBK
D9KENXXtsnclwmBiJ04n9QNcFcDBA5UGV6l7T5M/Xes+AbWLDvd9MiwUSt3dgoyB
Pfjl5n13OvG6vfLIetn5QA+IXYcrV0jcdjdqKPf1diUmEfW0hc6U42Dewr3YrYl3
uTWkr6cq6XU/L/pxK+sCRRkXS28WzD5mpfBHiiln5PhU7e4KwtCieoqc0NRRvZ6u
h+Pxu2OV835Cl/eWI5TB5R8lwTPylbviKHTTNyp1CexpkVftnBqLx+5aMuApxQV3
+Kyuz6GTSBlo9CWIw+83uGKDjm/lrT7dAaaWSpbClGrHBEqdFoVR9+HNuSj+499m
y+CW0SwhefeQlNEbp3Z1+Qv/2WU258NIwTRggJbu49RFbSoIleJzbOb4Lq4wnMhD
2Jx2d96RpOGVuzFGCV7ioacBhuVOccJHxHJ2wclo6WdSK4dQFBaeJZaCuqzoRMLU
I5u781CkDYSO/6fdtHc38BC7OcLrH4Hw00boRSxJ05PEFgkOzYqYs5viWwel+Kc4
M0v9HBCMP0WP7FYKSS/cNZWbIDIDX1kHZnPNbaDOCd/otz4Ts5gYP0Je7DCn1Y0A
8ONIcptr+bbyrPmMvgZM
=raL1
-----END PGP SIGNATURE-----

--P4dgr8cd34/ubiGA--