Re: Removing pg_pltemplate and creating "trustable" extensions

On Mon, Jan 6, 2020 at 6:56 PM Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> The first is this- ANYONE can create an extension in the system today,
> if it's marked as superuser=false. If anything, it seems like that's
> probably too loose- certainly based on your contention that ONLY
> superusers should wield such a power and that letting anyone else do so
> is a right that a superuser must explicitly grant.

I don't think this argument makes any sense. Sure, anyone can create
an extension with superuser=false, but so what? From a security point
of view, when you create such an extension, you are using your own
privileges to do things that you could do anyway. The interesting case
is creating an extension that requires superuser privileges, probably
because it's going to call C functions. The superuser can and must
have the last word regarding who is allowed to do such things, because
the superuser is equivalent to the OS user and any other user of the
system is not. The "tenants" of the database system can't be allowed
to use it for things which the "owner" does not wish to permit.

On Mon, Jan 6, 2020 at 6:26 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> If we were willing to break backwards compatibility, what I'd prefer
> is to just have the grantable role, and to say that you have to grant
> that to DB owners if you want them to be able to install PLs. I'm
> not sure how loud the howls would be if we did that, but it'd be a
> lot cleaner than any of these other ideas.

That seems like a fine idea. Then the superuser has ultimate control,
and can decide which database owners they want to trust, and whether
they'd like the database owners to be able to subdelegate those
permissions. The only thing it doesn't do is give any control over
exactly which extensions can be installed by non-superusers, which
would be a really nice thing to have, especially if we're going to
significant expand the list of trusted extensions (something that I
think is, overall, quite a good idea). I accept Tom's argument that he
isn't obliged to add every related feature somebody might want just
because he's doing some work in this area, but not his contention that
the demand for such a feature is entirely hypothetical and the
suggestion that perhaps nobody will care anyway. I expect the reaction
to be along the lines of "hey, it's great that we can let DB owners do
this now, but it's really too bad that I can't blacklist
$SCARY_EXTENSION". I don't think that we'll be better off if this
entire proposal gets voted down for lack of that capability, but I
think it would be a really good thing to add.

FWIW, I don't really buy the argument that you can adjust the
extension control files to get out from under this problem.
Technically, that is true. But in practice, the extension control
files are provided by your packager, and you don't want to modify them
because then your packaging system will get grumpy with you. While
it's reasonable for the packaging to provide a tentative answer to the
question of what should be trusted, trust is ultimately a matter of
local policy, and that policy should be configured someplace that's
not managed by RPM.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company