Re: Removing pg_pltemplate and creating "trustable" extensions

Greetings,

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> > On Mon, Jan 6, 2020 at 1:27 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >> After sleeping on it, I'm liking that idea; it's simple, and it
> >> preserves the existing behavior that DB owners can install trusted PLs
> >> without any extra permissions. Now, if we follow this up by marking
> >> most of contrib as trusted, we'd be expanding that existing privilege.
> >> But I think that's all right: I don't recall anybody ever complaining
> >> that they wanted to prevent DB owners from installing trusted PLs, and
> >> I do recall people wishing that it didn't take superuser to install
> >> the other stuff.
>
> > If somebody were to complain about this, what could they complain
> > about? Potential complaints:
>
> > 1. I'm the superuser and I don't want my DB owners to be able to
> > install extensions other than trusted PLs.

I don't agree that this is actually a sensible use-case, so I'm not
really sure why we're discussing solutions to make it work. It happens
to be how things work because pg_pltemplate exists before we had
extensions and we never went back and cleaned that up- but that's
exactly what we're trying to do here, and adding in a nice feature at
the same time.

> > 2. Or I want to control which specific ones they can install.

This is exactly what the 'trusted' bit is for, isn't it? If you think
that we need something that's actually a permission matrix between roles
and specific extensions, that's a whole different level, certainly, and
I don't think anyone's asked for or contemplated such a need.

I do like the idea of having a way to install more-or-less all
extensions out on to the filesystem and then giving superusers an
ability to decide which ones are 'trusted' and which ones are not,
without having to hand-hack the control files. I don't particularly
like using GUCs for that but I'm not sure what a better option looks
like and I'm not completely convinced we really need this. If we really
go down this route (without resorting to GUCs or something) then we'd
need an additional catalog table that $someone is allowed to populate
through some kind of SQL and a whole lot of extra work and I definitely
don't think we need that right now.

> > 3. I'm a non-superuser DB owner and I want to delegate permissions to
> > install trusted extensions to some other user who is not a DB owner.

This is a use-case that I do think exists (or, at least, I'm a superuser
or a DB owner and I'd like to delegate that privilege to another user).

> Sure, but all of these seem to be desires for features that could be
> added later.

We can't very well add a default role in one release and then decide we
want to use the GRANT-privilege system in the next and remove it...

> As for #1, we could have that just by not taking the
> next step of marking anything but the PLs trusted (something that is
> going to happen anyway for v13, if this patch doesn't move faster).

Ugh. I find that to be a pretty horrible result. Yes, we could mark
extensions later as 'trusted' but that'd be another year..

> #2 is not a feature that exists now, either; actually, the patch *adds*
> it, to the extent that the superuser is willing to adjust extension
> control files. Likewise, #3 is not a feature that exists now. Also,
> the patch adds something that looks partly like #3, in that the
> superuser could grant pg_install_trusted_extension with admin option
> to database users who should be allowed to delegate it. Perhaps that's
> inadequate, but I don't see why we can't wait for complaints before
> trying to design something that satisfies hypothetical use cases.

#3 from Robert's list certainly strikes me as a valid use-case and not
just hypothetical.

> The facts that I'm worried about are that this is already the January
> 'fest, and if we don't want to ship v13 with python 2 as still the
> preferred python, we need to not only get this patch committed but do
> some less-than-trivial additional work (that hasn't even been started).
> So I'm getting very resistant to requests for more features in this patch.
> I think everything you're suggesting above could be tackled later,
> when and if there's actual field demand for it.

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. I agree that addressing
some of the use-cases proposed above could be a great deal of work but,
as I say above, I don't agree that we need to address all of them.

> > "GRANT INSTALL ON mydb" seems like it would solve #1 and #3.
>
> It's not apparent to me that that's better, and it seems possible that
> it's worse. The fact that a DB owner could grant that privilege to
> himself means that you might as well just have it on all the time.

I agree that the DB owner should have that right by default, just like
they have any of the other DB-level rights that exist, just like how the
GRANT system in general works today. If they remove it from themselves,
then that's on them and they won't be able to create extensions until
they GRANT it back to themselves.

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. I
definitely don't like the idea of having this privilege act in any more
special way than our other privileges (be it a default-role or a GRANT'd
privilege, though obviously I'm much happier with the latter than the
former for this case).

> Like a table owner's DML rights, it would only be useful to prevent
> accidentally shooting yourself in the foot ... but who accidentally
> issues CREATE EXTENSION? And if they do (for an extension that
> deserves to be marked trusted) what harm is done really? Worst
> case is you drop it again.

Why are we talking about adding code for this though? The GRANT system
already handles all of this just fine and we rarely hear complaints from
people about it.

Thanks,

Stephen