Re: Removing pg_pltemplate and creating "trustable" extensions

Greetings,

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> Stephen Frost <sfrost(at)snowman(dot)net> writes:
> > Perhaps I'm wrong, but I wouldn't think changing this from a
> > default-role based approach over to a GRANT'able right using our
> > existing GRANT system would be a lot of work.
>
> Nobody has proposed a GRANT-based API that seems even close to
> acceptable from where I sit. A new privilege bit on databases
> is not it, at least not unless it works completely unlike
> any other privilege bit. It's giving control to the DB owners,
> not the superuser, and that seems like quite the wrong thing
> for this purpose.

I'm seriously confused by this. Maybe we need to step back for a moment
because there are things that already exist today that I don't think
we're really contemplating.

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.

> Or to put it another way: I think that the grantable role, which
> ultimately is handed out by the superuser, is the primary permissions
> API in this design. The fact that DB owners effectively have that
> same privilege is a wart for backwards-compatibility. If we were
> doing this from scratch, that wart wouldn't be there. What you're
> proposing is to make the wart the primary (indeed sole) permissions
> control mechanism for extension installation, and that just seems
> completely wrong. Superusers would have effectively *no* say in
> who gets to install trusted extensions, which is turning the whole
> thing on its head I think; it's certainly not responding to either
> of Robert's first two points.

Superusers don't have any (direct) say in who gets to create schemas
either, yet we don't seem to have a lot of people complaining about it.
In fact, superusers don't have any say in who gets to create functions,
or operators, or tables, or indexes, or EXTENSIONS, either. The fact is
that DB owners can *already* create most objects, including extensions,
in the DB system without the superuser being able to say anything about
it.

I really don't understand this hold-up when it comes to (trusted)
extensions. Consider that, today, in many ways, PLs *are* the 'trusted'
extensions that DB owners are already allowed to install. They're
libraries of C functions that are trusted to do things right and
therefore they can be allowed to be installed by DB owners.

If we had a generic way to have a C library declare that it only exposes
'trusted' C functions, would we deny users the ability to create those
functions in the database, when they can create functions in a variety
of other trusted languages? Why would the fact that they're C
functions, in that case, make them somehow special? That is, in fact,
*exactly* what's already going on with pltemplate and trusted languages.

Having trusted extensions is giving us exactly what pltemplate does, but
in a generic way where any C library (or whatever else) can be declared
as trusted, as defined by the extension framework around it, and
therefore able to be installed by DB owners. Considering we haven't got
any kind of check in the system today around extension creation, itself,
this hardly seems like a large step to me- one could even argue that
maybe we should just let ANYONE create them, but I'm not asking for
that (in fact, I could probably be argued into agreeing to remove the
ability for $anyone to create non-superuser extensions today, if we
added this privilege...).

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

If we can't come to agreement regarding using a regular GRANT'able
right, then I'd much rather break backwards compatibility than have such
a hacked up wart like this special case you're talking about for PLs.

> > I do *not* agree that this means we shouldn't have DB-level rights for
> > database owners and that we should just go hand-hack the system to have
> > explicit "is this the DB owner?" checks. The suggestion you're making
> > here seems to imply we should go hack up the CREATE SCHEMA check to have
> > it see if the user is the DB owner and then allow it, instead of doing
> > our normal privilege checks, and I don't think that makes any sense.
>
> Uh, what? Nothing in what I'm proposing goes anywhere near the
> permissions needed for CREATE SCHEMA.

I understand that- you're talking about just having this 'wart' for
CREATE EXTENSION and I don't agree with having the 'wart' at all. To
start doing this for PLs would be completely inconsistent with the way
the rest of the privilege system works and that's not ok.

Thanks,

Stephen