Thread: PG13 Trusted Extension usability issue

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 modify the control file to allow it to be trusted (I'd suggest how to do this be documented as well)

cat /usr/pgsql-13/share/extension/postgres_fdw.control
# postgres_fdw extension
comment = 'foreign-data wrapper for remote PostgreSQL servers'
default_version = '1.0'
module_pathname = '$libdir/postgres_fdw'
relocatable = true
trusted = true

My current, non-super user:

\du admin
List of roles
Role name | Attributes | Member of
-----------+------------------------+-----------
admin | Create role, Create DB | {}


Create the extension:
test2=> create extension postgres_fdw ;
CREATE EXTENSION

Actually try and use it:

test2=> CREATE SERVER foreign_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '192.83.123.89', port '5432', dbname 'foreign_db');
ERROR: permission denied for foreign-data wrapper postgres_fdw


To make it work, you need a superuser to run GRANT usage ... on the foreign data wrapper to the user that creates it. And while a superuser can apply the needed grants, it's an awkard feature to allow a non-superuser to create an extension, then require a superuser to grant things to allow it to actually be used.

There are lots of other cases of such behavior. pg_stats_statements_reset() for example. Big external extensions like PostGIS also suffer from permissions issues.

Brad.

"Brad Nicholson" <bradn@ca.ibm.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.  So no, we have not solved those, but that is not a shortcoming
of the trusted-extensions feature AFAICS.  It is not the intent of
that feature that you can randomly mark unsafe extensions as trusted
and have every one of their permissions safety-checks vanish.

            regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> wrote on 2020/06/26 02:47:25 PM:

> From: Tom Lane <tgl@sss.pgh.pa.us>
> To: "Brad Nicholson" <bradn@ca.ibm.com>
> Cc: pgsql-general@lists.postgresql.org
> Date: 2020/06/26 02:51 PM
> Subject: [EXTERNAL] Re: PG13 Trusted Extension usability issue
>
> "Brad Nicholson" <bradn@ca.ibm.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"

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

Thanks,
Brad

On 6/26/20 12:03 PM, Brad Nicholson wrote:
> Tom Lane <tgl@sss.pgh.pa.us> wrote on 2020/06/26 02:47:25 PM:
> 
>  > From: Tom Lane <tgl@sss.pgh.pa.us>
>  > To: "Brad Nicholson" <bradn@ca.ibm.com>
>  > Cc: pgsql-general@lists.postgresql.org
>  > Date: 2020/06/26 02:51 PM
>  > Subject: [EXTERNAL] Re: PG13 Trusted Extension usability issue
>  >
>  > "Brad Nicholson" <bradn@ca.ibm.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.klaver@aklaver.com

"Brad Nicholson" <bradn@ca.ibm.com> writes:
> Tom Lane <tgl@sss.pgh.pa.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.

            regards, tom lane

Greetings,

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> "Brad Nicholson" <bradn@ca.ibm.com> writes:
> > Tom Lane <tgl@sss.pgh.pa.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