From: Tom Lane <tgl@sss.pgh.pa.us>
To: Robert Haas <robertmhaas@gmail.com>
cc: Stephen Frost <sfrost@snowman.net>,
        Peter Eisentraut <peter.eisentraut@2ndquadrant.com>,
        PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: Removing pg_pltemplate and creating "trustable" extensions
In-reply-to: 
 <CA+Tgmobk+ihJ498JF_XD=aLryTAujmmA5qn86qRUdBvd3g-hog@mail.gmail.com>
References: <20200107181727.GB3195@tamriel.snowman.net>
 <CA+Tgmob1cEG=CHnUnryBKdpLhg+jxXH0ZRXpF2OGX1WU8hgQvQ@mail.gmail.com>
 <20200107213618.GC3195@tamriel.snowman.net>
 <CA+TgmoY0yY-mN6FUjD11pruaEoLDHmjD2kS6Y-sVEwk2XwXX2A@mail.gmail.com>
 <20200108003247.GE3195@tamriel.snowman.net>
 <CA+TgmoYB4z5R1E61qtwJHDrogsY+dg4R48TRNFSiaO3JDT=X3g@mail.gmail.com>
 <20200108230935.GN3195@tamriel.snowman.net>
 <CA+TgmoYKLetGg7wRREYUQ=M+gSjYfZBb2AV6ad5C8aurJFqPjA@mail.gmail.com>
 <20200109150911.GP3195@tamriel.snowman.net>
 <CA+Tgmob62hXe2Xkn4S7wRFpe6tPF=Gc32OBRzcS1GzGL=vj+4w@mail.gmail.com>
 <20200109163013.GR3195@tamriel.snowman.net>
 <CA+TgmoYbsVCHNkX0g5QTZS=fgf9AXYoXHT2_4Jy6gpEFD5yBXQ@mail.gmail.com>
 <6440.1578594907@sss.pgh.pa.us>
 <CA+Tgmobk+ihJ498JF_XD=aLryTAujmmA5qn86qRUdBvd3g-hog@mail.gmail.com>
Comments: In-reply-to Robert Haas <robertmhaas@gmail.com>
	message dated "Thu, 09 Jan 2020 14:06:03 -0500"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <9932.1578599320.1@sss.pgh.pa.us>
Date: Thu, 09 Jan 2020 14:48:40 -0500
Message-ID: <9933.1578599320@sss.pgh.pa.us>
Precedence: bulk

Robert Haas <robertmhaas@gmail.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.

			regards, tom lane