Date: Thu, 7 Nov 2019 14:13:20 -0500
From: Stephen Frost <sfrost@snowman.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>,
	pgsql-hackers@lists.postgresql.org
Subject: Re: Removing pg_pltemplate and creating "trustable" extensions
Message-ID: <20191107191320.GZ6962@tamriel.snowman.net>
References: <5889.1566415762@sss.pgh.pa.us>
 <c15c0f58-79a9-4051-1f43-8fc06564df69@2ndquadrant.com>
 <27727.1573052802@sss.pgh.pa.us>
 <20191107183856.GY6962@tamriel.snowman.net>
 <31658.1573152852@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="w3uzjWcAFGvbVs0G"
Content-Disposition: inline
In-Reply-To: <31658.1573152852@sss.pgh.pa.us>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk


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

Greetings,

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Stephen Frost <sfrost@snowman.net> writes:
> >> Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
> >>> Using GUCs to control some of this creates yet another place where=20
> >>> permission information is kept, and with it questions about how to ge=
t=20
> >>> to it, how to edit it, or to back it up and restore it, etc.  Also,=
=20
> >>> list-based parameters are particularly hard to manage by automated=20
> >>> tools.  I think we can do this within the existing permission system,=
=20
> >>> for example with pre-defined roles (for example, GRANT=20
> >>> pg_create_trusted_extension ...).  Also, CREATE EXTENSION should some=
how=20
> >>> be controlled by the CREATE privilege on the containing database, so =
a=20
> >>> separate setting for database owner vs. regular user might not be=20
> >>> necessary.  Regular users would need both the role membership (given =
by=20
> >>> the overall superuser) and the privilege within the database (given b=
y=20
> >>> the database owner).
>=20
> > Two things- first, this doesn't actually cover everything that the
> > proposed GUCs do- specifically, the proposed GUCs give you a way to
> > limit what specific extensions are allowed to be installed, and by whom.
> > Moving to a GRANT-based system removes the extension specificity and
> > leaves with just "is user X allowed to install extensions".
>=20
> True.  But do we care?  We did not have that flexibility before, either.

I'm not 100% sure that we do, but I wanted to mention it as a
difference.  Certainly there have previously been suggestions of having
a 'whitelist' similar to what you initially proposed, that are
extensions which non-superusers are allowed to install.

> I'd still keep the "trustable" property (probably renamed to "trusted"
> for simplicity) for extensions, so in the worst case, an admin could
> edit extension control files to add or remove the per-extension flag.

At a high level, I agree with the idea of an extension being able to be
marked as one that's "trusted" or not, but we would also need to come up
with exactly what that means for it to really have value, and I don't
think we've really done that yet.

> > Second,
> > this approach is requiring that a user who is allowed to create
> > extensions must also be allowed to create schemas on the database in
> > question.
>=20
> That doesn't seem like a big objection from here.  We could fix it
> by making a separate privilege bit, but I doubt that it's worth using
> up one of our limited set of spare bits for.

I do not agree that we should just shift to using default roles instead
of adding new options to GRANT because of an entirely internal
implementation detail that we could fix (and should, as I've said for
probably 10 years now...).

> >> I agree with the idea of requiring a DB-level privilege as well as
> >> the overall role.  Is it okay to re-use the CREATE privilege (which
> >> today only allows for CREATE SCHEMA), or do we need another one?
>=20
> > If we just created another one, wouldn't that remove the need to have a
> > database role?
>=20
> No, because then being DB owner would be alone be enough to let you
> install extensions (since as owner, you could certainly grant yourself
> all privileges on the DB, even if this were somehow not the default).
> We'd have to mangle GRANT's behavior to avoid that, and I don't think
> we should.  Nor do I think that DB ownership ought to be enough
> privilege by itself.

Really?  Why do you think that DB ownership shouldn't be enough for
this, for trusted extensions?

I agree that we don't want to mangle GRANT's behavior, at all, for this.

> >> I think re-using CREATE is probably all right, since it would only be
> >> useful for this purpose to users who also have "pg_install_extensions".
>=20
> > With this, you couldn't have a user who is able to create extensions but
> > not able to create schemas though.  That kind of combining of privileges
> > together really goes against the general principle of 'least privilege',
> > unless the action associated with one necessairly requires the other,
> > but I don't believe that's the case here.
>=20
> A point here is that many extensions involve creating their own schemas
> anyway.  Also, the ability to "relocate" an extension to a different
> schema is pretty meaningless if you can't create a schema to put it in.

What extensions require creating their own schema?  Every single
extension that's in contrib can be installed into the public schema
(concurrently, even) except for two hacks- plpgsql and adminpack, and
those go into pg_catalog for historical reasons more than anything else.

Creating a schema is an option for extensions but it isn't a
requirement.  I agree that you need the ability to create schemas if you
want to relocate one, but that's like needing SELECT to do an UPDATE
without a WHERE clause.  I also don't know that extension relocation is
really something that's commonly done.

> If I thought that there were a use-case for letting someone create
> extensions but not schemas, I'd be more eager to invent a new bit.
> But I'm having a *really* hard time envisioning a live use-case
> for that.  Granting extension-creation ability requires a whole lot
> more trust in the grantee than the ability to make new schemas
> (which, in themselves, have about zero impact on anybody else).

The idea of 'least privilege' isn't "well, I'm gonna grant you this
other thing that you don't actually need, just because I trust you with
this privilege that you do need."

Thanks,

Stephen

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

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

iQIcBAEBCgAGBQJdxGzQAAoJEO1sijiDR2RVq/0QAJJka5h6trbyHmxGCcioYsIN
eoRFjP4MAYdiZhPkxTkqLEd53SwyDJ+VsZGffLv54OY++g8mU6q7naMPrXvs5atN
PIzd4a57wl7fENzQI8nZuEd1Ys4ESCwgRNr2KUMxji/rBxX6MpfNYQus2meunrMD
9WZIMtUl53ME1rH2FrLAw/y7m6LKbVauvGn8UHOzcL+lJcSPzN+MTOvFlTvqwj64
KCyaHgqPg1oYileA3VgqVkFS9dc1u2HzTVR4C6j7+Y5ajGOLOhW/abquWLDfepQF
JBtjCuaVbTJgwGH5aS6fWEbyCzLl9qS1HZEZrl0ERVHKOqh4Z8CWEZleDFpLnkuA
yKlHiZUIyJte9d/KgtcFcrooehl9mjn8h+NO6EgFuQo9bwezbMu+haPHyZMqjZ/s
qpKsP6NE6Mk2CXAR+O+0RbtHIpk7MkQ2HynAxtYsVxFkqHTgrz9vt7tQ4853kNDs
vSs/VKOTU/QdTmg01AljpmAht5CeG3oaDx9YrP58iyU1JbHuSEBUv42tp54E4CDW
pWo95spYjipmD2JSVmXUCgSxncm3r31uydWjHiyABXWiwG+CmAV4n1lv/DvfXFgz
CgvE03tZH+aQVef7iiJ0g9obOAxu1Z0BvN7k/co3/wMfzAz9+vSDDdcFZj5BpQe7
SQapDDrfZR3E3uAlVpeQ
=lrAj
-----END PGP SIGNATURE-----

--w3uzjWcAFGvbVs0G--