Dear List
The below runs on PostgreSQL 16.4
We are trying to implement a certain operation based on a
security definer function : mariner_update_availability_date
This is supposed to update a table : mariner , which has several other triggers :
mariner_build_natural_id_tg BEFORE INSERT OR UPDATE ON mariner
FOR EACH ROW EXECUTE FUNCTION mariner_build_natural_id()
mariner_force_integrity_tg AFTER INSERT OR UPDATE ON mariner
FOR EACH ROW EXECUTE FUNCTION mariner_force_integrity()
mariner_manage_past_tg BEFORE UPDATE ON mariner FOR EACH ROW
EXECUTE FUNCTION mariner_manage_past()
mariner_xadmin_prod_tmp_map_ins__crew_tg AFTER INSERT ON
mariner FOR EACH ROW EXECUTE FUNCTION
xadmin_prod_tmp_map_ins__crew()
mariner_zb_dbmirror_trig AFTER INSERT OR DELETE OR UPDATE ON
mariner FOR EACH ROW EXECUTE FUNCTION dbmirror_recordchange()
zzzmariner_dmq_tg AFTER INSERT OR DELETE OR UPDATE ON mariner
DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION
export_dmq()
Yes, for those highly observant veterans,
dbmirror_recordchange
is indeed DBMIRROR. And no, we cannot replace it, since this is
our own ultra hacked and customized version, not replaceable by
any past, present (and most likely future) extension.
As you noticed the last trigger is a CONSTRAINT DEFERRABLE
trigger. This function mariner_update_availability_date
is supposed to be run by a user : cbt_results_import
stripped of any
privileges to the rest of the system. Here is what we get : when
we SET the constraint of the last trigger to IMMEDIATE, the
function runs on behalf of its owner (postgres) who has all needed
privileges (as superuser) to run the update on mariner table and
also run the triggers . However, when we run with this CONSTRAINT
as DEFERRED then it seems to NOT run the last deferrable trigger
as postgres.
postgres@smadb-pgsql16:~$ psql
psql (16.4)
Type "help" for help.
postgres@[local]/dynacom=# set role cbt_results_import ;
SET
postgres@[local]/dynacom=> begin ;
BEGIN
postgres@[local]/dynacom=*> SET CONSTRAINTS zzzmariner_dmq_tg
IMMEDIATE;
SET CONSTRAINTS
postgres@[local]/dynacom=*> select
mariner_update_availability_date(13916, '2020-02-28');
mariner_update_availability_date
----------------------------------
(1 row)
postgres@[local]/dynacom=*> commit ;
COMMIT
postgres@[local]/dynacom=> begin ;
BEGIN
postgres@[local]/dynacom=*> SET CONSTRAINTS zzzmariner_dmq_tg
DEFERRED;
SET CONSTRAINTS
postgres@[local]/dynacom=*> select
mariner_update_availability_date(13916, '2020-02-28');
mariner_update_availability_date
----------------------------------
(1 row)
postgres@[local]/dynacom=*> commit ;
ERROR: permission denied for table export_dmq
CONTEXT: SQL statement "DELETE FROM export_dmq where id=($1).id
and op='U' and tbl='mariner'"
PL/pgSQL function export_dmq() line 15 at EXECUTE
postgres@[local]/dynacom=>
Is this supposed to be normal? Documented anywhere ?
Thank you
