From: Bruce Momjian <bruce@momjian.us>
To: Harinath Kanchu <hkanchu@apple.com>
Cc: pgsql-bugs@lists.postgresql.org, Peter Eisentraut <peter@eisentraut.org>
Subject: Re: Trusted extension cannot be dropped by the owner of the extension
Date: Wed, 20 Jul 2022 12:35:32 -0400
Message-ID: <Ytgu1LZnOL/9dqj2@momjian.us> (raw)
In-Reply-To: <DC52E9C2-B349-4291-89D0-F015E289DEB8@apple.com>
References: <DC52E9C2-B349-4291-89D0-F015E289DEB8@apple.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: bruce@momjian.us, hkanchu@apple.com, peter@eisentraut.org
Subject: Re: Trusted extension cannot be dropped by the owner of the extension
In-Reply-To: <Ytgu1LZnOL/9dqj2@momjian.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox