Re: Trusted extension cannot be dropped by the owner of the extension

On Tue, Jul 19, 2022 at 11:44:05PM -0700, Harinath Kanchu wrote:
> Hello Postgres community,
>
> We recently encountered a bug regarding the install/uninstall of extensions.
> Here are the details.

This is an interesting report from PG 13.2.

> test_db=> SELECT version();
> version
>
> -------------------------------------------------------------------------------------------------------------------
> PostgreSQL 13.2 on x86_64-apple-darwin20.3.0, compiled by Apple clang version
> 11.0.0 (clang-1100.0.33.17), 64-bit
> (1 row)

> 2. Mark “bloom” extension as trusted
> 1. Add “trusted = true” to “bloom.control” file located in $
> {INSTALL_PATH}/share/extension/

I didn't know you could modify the control files and still be considered
safe, and there is a note about this in the docs:

https://www.postgresql.org/docs/15/extend-extensions.html#id-1.8.3.20.11

Generally, this should not be set true for extensions that could
allow access to otherwise-superuser-only abilities, such as
file system access. Also, marking an extension trusted requires
significant extra effort to write the extension's installation
and update script(s) securely; see Section 38.17.6.

> 3. Run the below commands in PSQL client (attaching the commands with output)
>
>
> test_db=> create extension bloom;
> CREATE EXTENSION
> test_db=> drop extension bloom;
> ERROR: 42501: must be superuser to drop access methods
> LOCATION: RemoveAccessMethodById, amcmds.c:130

Yes, I was able to recreate this with PG 13.7. However, it works for PG
14 and later, and it is this commit in PG 14 that fixed it:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=b1d32d3e32

There are a number of Remove${Something}ById() functions that are
essentially identical in structure and only different in which catalog
they are working on. Refactor this to be one generic function. The
information about which oid column, index, etc. to use was already
available in ObjectProperty for most catalogs, in a few cases it was
easily added.

Before this commit, e.g., PG 13, the call to RemoveAccessMethodById()
had a super-user check in it that was removed when the drop
functionality was unified by calling DropObjectById() because I guess it
was determined that it was not needed.

In summary, I don't think marking this as trusted is safe, and we are
unlikely to backpatch this fix to PG 13.X.

Here is it working in PG master:

$ initdb
$ make -C contrib/bloom
$ echo 'trusted = true' >> /u/pg/share/extension/bloom.control

$ psql postgres
CREATE USER bob;
CREATE DATABASE test2 OWNER bob;
\c test2 bob
CREATE EXTENSION bloom;
DROP EXTENSION bloom;

--
Bruce Momjian <bruce(at)momjian(dot)us> https://momjian.us
EDB https://enterprisedb.com

Indecision is a decision. Inaction is an action. Mark Batterson