_etg_drop() is not SECURITY DEFINER: drops fail for non-superuser roles #50

🤖 (Claude)

_object_reference._etg_drop() runs with invoker rights, so once object_reference is installed, any non-superuser role that drops an object gets a "permission denied" error.

The sql_drop event trigger it implements fires as the role issuing the DDL. That role normally has no access to _object_reference, yet the function reads _object_reference._object_v and deletes from _object_reference.object. _etg_capture() and _etg_fix_identity() are already SECURITY DEFINER; _etg_drop() is the odd one out.

Reproduction

PostgreSQL 17.10, cat_tools and object_reference 0.2.0 installed by a superuser:

-- as superuser
CREATE DATABASE t;
\c t
CREATE ROLE app LOGIN;
CREATE EXTENSION cat_tools CASCADE;
CREATE EXTENSION object_reference VERSION '0.2.0';
GRANT CREATE ON DATABASE t TO app;
CREATE SCHEMA s AUTHORIZATION app;

-- as app
CREATE TABLE s.t();   -- succeeds
DROP TABLE s.t;

ERROR:  permission denied for schema _object_reference
LINE 3:   r_object_v _object_reference._object_v;
CONTEXT:  compilation of PL/pgSQL function "_etg_drop" near line 3

After GRANT USAGE ON SCHEMA _object_reference TO app, the same DROP TABLE fails differently:

ERROR:  permission denied for view _object_v
CONTEXT:  ... PL/pgSQL function _object_reference._etg_drop() line 14 at FOR over SELECT rows

Creating objects works; only drops fail. Any DROP by such a role is affected, including dropping temporary objects inside another extension's install script.

Affected functions

Checked via pg_proc.prosecdef and by running the DDL as app:

I did not confirm 0.1.0 (my attempt to install it in the test database failed on an unrelated cat_tools dependency error); from reading the script it has the same definition.

Suggested fix

Suggestion only: declare _etg_drop() SECURITY DEFINER with a fixed search_path (e.g. SET search_path = pg_catalog; the body already schema-qualifies its references, though object_reference.post_restore() would need to stay qualified too). The function then runs as whoever installed the extension, so that owner needs the necessary rights on _object_reference. As a check, running ALTER FUNCTION _object_reference._etg_drop() SECURITY DEFINER SET search_path = pg_catalog on the repro database above made CREATE TABLE s.t4(); DROP TABLE s.t4; succeed as app.