Re: PG13 Trusted Extension usability issue

On 6/26/20 12:03 PM, Brad Nicholson wrote:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote on 2020/06/26 02:47:25 PM:
>
> > From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> > To: "Brad Nicholson" <bradn(at)ca(dot)ibm(dot)com>
> > Cc: pgsql-general(at)lists(dot)postgresql(dot)org
> > Date: 2020/06/26 02:51 PM
> > Subject: [EXTERNAL] Re: PG13 Trusted Extension usability issue
> >
> > "Brad Nicholson" <bradn(at)ca(dot)ibm(dot)com> writes:
> > > First, as a long time user of the pgextwlist extension, I'm happy
> to see
> > > this functionality appearing in core.  However, as a long term user
> of that
> > > extension, I can say that ability to create an extension as a non-super
> > > user is pretty limited in itself in a lot of cases.  Many
> extensions both
> > > in contrib and external ones (like PostGIS for example) don't give
> > > appropriate permissions to actually use the extension.
> >
> > > Taking postgresql_fdw as an example.
> >
> > I'm confused about your point here.  postgresql_fdw has intentionally
> > *not* been marked trusted.  That's partly because it doesn't seem like
> > outside-the-database access is something we want to allow by default,
> > but it's also the case that there are inside-the-database permissions
> > issues.
>
> Ah - I misread the docs.  Specifically I read this:
>
> "For many extensions this means superuser privileges are needed.
> However, if the extension is marked trusted in its control file, then it
> can be installed by any user who has CREATE privilege on the current
> database"

The rest of that paragraph:

"In this case the extension object itself will be owned by the calling
user, but the contained objects will be owned by the bootstrap superuser
(unless the extension's script explicitly assigns them to the calling
user). This configuration gives the calling user the right to drop the
extension, but not to modify individual objects within it."

>
> To mean that you could mark any extension as trusted in the control file
> to allow non-superuser installation.
>
> Thanks,
> Brad
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com