Re: Strange permission effect depending on DEFERRABILITY

On 9/9/24 17:21, Tom Lane wrote:
> Achilleas Mantzios - cloud<a(dot)mantzios(at)cloud(dot)gatewaynet(dot)com> 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.

Thank you Tom.  With a little bit of debugging is obvious that the last
deferred trigger function runs outside the security environment of the
top SECURITY DEFINER function (naturally), however current_user seems to
be on par with the security definer owner,

cbt_results_import(at)[local]/dynacom=> begin ;
BEGIN
cbt_results_import(at)[local]/dynacom=*> SET CONSTRAINTS zzzmariner_dmq_tg
IMMEDIATE;
SET CONSTRAINTS
cbt_results_import(at)[local]/dynacom=*> select
mariner_update_availability_date(13916, '2020-02-28');
NOTICE:  manage past : the current_user is postgres, session_user is
cbt_results_import, system_user id md5:cbt_results_import
NOTICE:  manage past : the current_user is postgres, session_user is
cbt_results_import, system_user id md5:cbt_results_import
mariner_update_availability_date
----------------------------------

(1 row)

cbt_results_import(at)[local]/dynacom=*> commit ;
COMMIT
cbt_results_import(at)[local]/dynacom=> begin ;
BEGIN
cbt_results_import(at)[local]/dynacom=*> SET CONSTRAINTS zzzmariner_dmq_tg
DEFERRED;
SET CONSTRAINTS
cbt_results_import(at)[local]/dynacom=*> select
mariner_update_availability_date(13916, '2020-02-28');
NOTICE:  manage past : the current_user is postgres, session_user is
cbt_results_import, system_user id md5:cbt_results_import
mariner_update_availability_date
----------------------------------

(1 row)

cbt_results_import(at)[local]/dynacom=*> commit ;
NOTICE:  manage past : the current_user is cbt_results_import,
session_user is cbt_results_import, system_user id md5:cbt_results_import
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 18 at EXECUTE

Thank you and sorry for missing something so obvious.

>
> regards, tom lane