Trusted extension cannot be dropped by the owner of the extension
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
./configure --prefix=${INSTALL_PATH} --with-openssl --with-uuid=e2fs --with-perl --with-python --with-tcl
make install-world
Mark “bloom” extension as trusted
Add “trusted = true” to “bloom.control” file located in ${INSTALL_PATH}/share/extension/
Contents of bloom.control file after adding
# bloom extension
comment = 'bloom access method - signature file based index'
default_version = '1.0'
module_pathname = '$libdir/bloom'
relocatable = true
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 <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
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