Home > mailing lists

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

Trusted extension cannot be dropped by the owner of the extension

From

Harinath Kanchu

Date:

20 July 2022, 09:44:05

Hello Postgres community,

We recently encountered a bug regarding the install/uninstall of extensions. Here are the details.

Facts/Observations:

Unable to drop TRUSTED extension.

Postgres version:

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)

Steps to reproduce the bug:

Compile and install postgres 13.2
1. ./configure --prefix=${INSTALL_PATH} --with-openssl --with-uuid=e2fs --with-perl --with-python --with-tcl
2. make install-world
Mark “bloom” extension as trusted
1. Add “trusted = true” to “bloom.control” file located in ${INSTALL_PATH}/share/extension/
2. Contents of bloom.control file after adding
  1. # bloom extension
  2. comment = 'bloom access method - signature file based index'
  3. default_version = '1.0'
  4. module_pathname = '$libdir/bloom'
  5. relocatable = true
  6. trusted = true
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

Expected:

As the extension is marked as TRUSTED, if superuser privilege is not necessary during the “create extension” then superuser privilege should not be required when the owner of the extension drops it.

Why this expectation:

According to the documentation referred here
https://www.postgresql.org/docs/current/sql-createextension.html the following is observed.

“””

The user who runs CREATE EXTENSION becomes the owner of the extension for purposes of later privilege checks, and normally also becomes the owner of any objects created by the extension's script.

“””

if the user is the owner of the extension then it is expected that the access methods created during the extension installation should be under the same user’s ownership and there should not be an error when dropping the extension.

Thank you,

Best,

Harinath

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

From

Bruce Momjian

Date:

20 July 2022, 19:35:32

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@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

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