public inbox for [email protected]
help / color / mirror / Atom feedStrange permission effect depending on DEFERRABILITY
2+ messages / 2 participants
[nested] [flat]
* Strange permission effect depending on DEFERRABILITY
@ 2024-09-09 13:14 Achilleas Mantzios - cloud <[email protected]>
2024-09-09 14:21 ` Re: Strange permission effect depending on DEFERRABILITY Tom Lane <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Achilleas Mantzios - cloud @ 2024-09-09 13:14 UTC (permalink / raw)
To: [email protected] <[email protected]>
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 strippedof 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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Strange permission effect depending on DEFERRABILITY
2024-09-09 13:14 Strange permission effect depending on DEFERRABILITY Achilleas Mantzios - cloud <[email protected]>
@ 2024-09-09 14:21 ` Tom Lane <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2024-09-09 14:21 UTC (permalink / raw)
To: Achilleas Mantzios - cloud <[email protected]>; +Cc: [email protected] <[email protected]>
Achilleas Mantzios - cloud <[email protected]> writes:
> 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 strippedof 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.
AFAIR the trigger mechanisms do not change the execution environment.
If they did, then for example a trigger that stuffs CURRENT_USER into
a last_updated_by column would not give the desired results.
I'd suggest marking the problem trigger function as SECURITY DEFINER
if you want it to run as its owner.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-09-09 14:21 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-09-09 13:14 Strange permission effect depending on DEFERRABILITY Achilleas Mantzios - cloud <[email protected]>
2024-09-09 14:21 ` Tom Lane <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox