Re: Removing pg_pltemplate and creating "trustable" extensions

Greetings,

On Thu, Jan 9, 2020 at 14:48 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> > So, if I understand correctly, the patch you are proposing to commit
> > has a new system role, and if you've got that system role, then you
> > can install extensions.
>
> Install *trusted* extensions, correct. The patch as it stands also
> allows DB owners to install trusted extensions.
>
> > I thought that part of the earlier debate was
> > whether DB owners should also be able to install trusted extensions
> > even without that role, and I thought it would be cleaner if the
> > answer was "no," because then the superuser could decide whether to
> > grant that role or not in particular cases. But I'm not clear whether
> > you agreed with that, what Stephen thought about it, or whether that's
> > still what you are proposing to commit.
>
> I agree that if we dropped the proviso about DB owners, it would be
> a cleaner design. I included that only for backwards compatibility
> with the existing behavior that DB owners can install trusted PLs.
> If we can agree that we're willing to lose that behavior, I'd be
> perfectly fine with removing the special case for DB owners.
> However, I'm unsure whether that compatibility cost is acceptable.
> It's definitely likely that it would cause an upgrade headache
> for some installations.
>
> One idea for working around the upgrade problem would be to teach
> pg_dumpall to automatically issue "GRANT pg_install_trusted_extension"
> to each DB-owner role, when dumping from a pre-v13 database. There's
> room to object to that, because it would end with more privilege than
> before (that is, an owner of some DB could now install extensions
> even in DBs she doesn't own, as long as she can connect to them).
> So maybe it's a bad idea. But it would probably reduce the number
> of complaints --- and I think a lot of installations would end up
> making such grants anyway, because otherwise their DB owners can't
> do things they expect to be able to do.
>
> I should not put words into Stephen's mouth, but perhaps his
> concern about having some DB-level privilege here is to alleviate
> the problem that there's no exact equivalent to the old level of
> privilege that DB ownership afforded, ie you can install in your
> own DB but not others. It's not clear to me whether that behavior
> is critical to preserve.

I am not particularly concerned about that backwards compatibility issue
and I don’t intend to base my argument on it, but I would use that case to
point out that we have long had the ability to install trusted C functions
into the backend as a DB owner, without complaint from either users or
security pedants, and that backs up my position that we are setting up this
privilege at the wrong level by using a default role which a superuser must
grant independently from DB ownership.

Thanks,

Stephen

>