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. 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