Обсуждение: Trusted extension cannot be dropped by the owner of the extension
Hello Postgres community,
test_db=> SELECT version();
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 the following is observed.
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