Date: Thu, 7 Nov 2019 13:38:56 -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: <20191107183856.GY6962@tamriel.snowman.net>
References: <5889.1566415762@sss.pgh.pa.us>
 <c15c0f58-79a9-4051-1f43-8fc06564df69@2ndquadrant.com>
 <27727.1573052802@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="27dBW1J+4xep0vgM"
Content-Disposition: inline
In-Reply-To: <27727.1573052802@sss.pgh.pa.us>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk


--27dBW1J+4xep0vgM
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

Greetings,

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
> > On 2019-08-21 21:29, Tom Lane wrote:
> >> Patch 0001 below addresses this problem by inventing a concept of
> >> "trustable" (not necessarily trusted) extensions.  An extension that
> >> would normally require superuser permissions (e.g., because it creates
> >> C functions) can now be installed by a non-superuser if (a) it is
> >> marked trustable in the extension's control file, AND (b) it is
> >> listed as trusted in one of two new GUCs, trusted_extensions_dba and
> >> trusted_extensions_anyone.
>=20
> > I think this overall direction is good.  I'm not so fond of the interfa=
ces.

I'm not really thrilled with this interface either.

> > Using GUCs to control some of this creates yet another place where=20
> > permission information is kept, and with it questions about how to get=
=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 someho=
w=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 by=
=20
> > the database owner).

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".  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.

> Hm.  In principle I'm okay with the idea of having a predefined role
> or two for extension installation.  I think though that we could not
> easily make that design emulate the current behavior, wherein database
> owners automatically have the ability to install trusted PLs.  The
> superuser would have to take the additional step of granting them a
> role to let them do that.  Maybe that's just fine --- from some
> angles it could be seen as an improvement --- but it is an
> incompatibility.  Anybody have a problem with that?

I'm certainly fine with a little backwards incompatibility breakage to
remove pg_pltemplate.

> Do we need more than one level of extension trust-ability (and more
> than one predefined role to go with that)?  Assuming that we go ahead
> and mark all the safe-looking contrib modules as trustable, granting
> "pg_install_extensions" or whatever we call it would then give a DB
> owner more privilege than just the ability to install trusted PLs.
> But maybe that's fine too.

I also agree with the idea of making PLs be closer to extensions, and
this change would move us in that direction too.

> 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?

If we just created another one, wouldn't that remove the need to have a
database role?  I certainly understand that default roles in the
database are useful, but I don't think we should be using them in cases
where a traditional GRANT-based privilege could be used instead, and
this certainly seems like a case where a user could just have "CREATE
EXTENSION" as a privilege GRANT'd to their role, at a database level,
and they would then be able to create (trusted) extensions in that
database.  That would also make it independent of the "CREATE SCHEMA"
privilege that we have now, removing the need to wonder about the above
question regarding combining the two.

This is far from the first time we've talked about allowing privilege
based control over who is allowed to create what kind of objects in the
system.  That kind of fine-grained control over other objects would also
be a good improvement to our privilege system (not everyone needs to be
able to create functions and operators, particularly when those are
actually roles that are logged into by services who shouldn't ever be
creating those kinds of objects even if they, maybe, need to create
tables or similar...).

> 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".

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.

Thanks,

Stephen

--27dBW1J+4xep0vgM
Content-Type: application/pgp-signature; name="signature.asc"

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

iQIcBAEBCgAGBQJdxGTAAAoJEO1sijiDR2RViYMQALoX8TmIMdZ8ZqQtNpY8Gcu5
tvLSaZLwkEG6KSBKjD1C890j9wXhDa8nmojMVpH2rnLBLbbah6fVr5ecHnVbo6aZ
ET5O/rTLCqT03/v7dzoe4mFdSmlPdM3/KWEDUdCgkIoXiZBMd+B6KHgDlvNCVgmk
yYjtoYGnYwtch0n+FZ6J4j4XhM5eL4w1Py3IfBV20j8sQpBdUMjrYHwFWpJV2CHr
VnlFxE1cKaE0Vl0JIK+CYLHdTuoYXe6Ww6iURbG4IbDamfS6L2QmN9NYCP9zIEdZ
4IYMRCQP9jBB+Cq+Vltirvu9RqG/PnRSMlgEqiEdYjF1gHLzuH9GSUhpbJGq2TMs
f9boUOSgQvGjq4ECX4Sxy5gEF0vRgRs/Z/D6T429K2rU12tBFw4ZE9W8FXvnhbbu
fY+PqR6+6Qdvt4KuMh5xUH8eLCkbsZJft8HUEW+I36yBpc621PajscsfIwEr1mGF
4LP10N0KIRsi3sfuoumbd/qkOIiBRqUPhXCxe2TVTOI29k8mNDPu3MK/65iEEEtW
HcEPaEc6K7PjGaUExM2SGzuJCALfEhSxw4H5a8O1OoM4QKb1hbKxpunHacv1T8l3
yzoiEMwglmJaPZWU8WksYMOEvDn4cCiaXiONz1dYrS5lrhG33N5IUmFdo9g2dOET
VDlHgjsJ//EkPxzeP/tE
=Xx9M
-----END PGP SIGNATURE-----

--27dBW1J+4xep0vgM--