Re: Removing pg_pltemplate and creating "trustable" extensions

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.  (These names could stand a visit to the
> bikeshed, no doubt.)  Extensions matching trusted_extensions_dba can
> be installed by a database owner, while extensions matching
> trusted_extensions_anyone can be installed by anybody.  The default
> settings of these GUCs provide backwards-compatible behavior, but
> they can be adjusted to provide more or less ability to install
> extensions.  (This design is basically what Andres advocated in [2].)

I think this overall direction is good.  I'm not so fond of the interfaces.

Using GUCs to control some of this creates yet another place where 
permission information is kept, and with it questions about how to get 
to it, how to edit it, or to back it up and restore it, etc.  Also, 
list-based parameters are particularly hard to manage by automated 
tools.  I think we can do this within the existing permission system, 
for example with pre-defined roles (for example, GRANT 
pg_create_trusted_extension ...).  Also, CREATE EXTENSION should somehow 
be controlled by the CREATE privilege on the containing database, so a 
separate setting for database owner vs. regular user might not be 
necessary.  Regular users would need both the role membership (given by 
the overall superuser) and the privilege within the database (given by 
the database owner).

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services