Re: PG13 Trusted Extension usability issue

Greetings,

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> "Brad Nicholson" <bradn(at)ca(dot)ibm(dot)com> writes:
> > Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote on 2020/06/26 02:47:25 PM:
> >> 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"
> > To mean that you could mark any extension as trusted in the control file to
> > allow non-superuser installation.
>
> Well, it's just like anything else in an open-source database: you can
> change the code however you want, but the fallout from that is on you.
>
> In the case at hand, you might be able to do what you want by adding
> something along the line of
>
> GRANT USAGE ON FOREIGN DATA WRAPPER postgres_fdw
> TO @extowner@ WITH GRANT OPTION;
>
> to the extension install script. But nobody's researched whether that's
> reasonable from a security standpoint, or whether it will play nicely
> with dump/reload, etc etc.

Indeed that could be done, and almost certainly will be by cloud
vendors, resulting in more forks of PG done in different ways.

I do agree that, in general, we need a way to allow a superuser to GRANT
the right to connect to other systems (so that it could be done
initially when creating the not-really-superuser-privileged-user, before
the extension is created), since we don't want that to be allowed from
the start, then have that checked by extensions, but once that's done it
seems like it'd be alright to include the above GRANT USAGE in upstream
(regarding this specific concern, at least).

Seems like a default role would probably be the way to add that.

Thanks,

Stephen