Now for something completely different

    Somewhere  in  the  Oracle  doc's  I read that a trigger when
    fired is executed under the permissions of the trigger owner.
    That makes really sense for me. This way a normal user can be
    revoked from modifying a table directly, but if  he  modifies
    another  table  where he has permissions for, a trigger fired
    for that update can access the protected table. Good  feature
    on  logging  tables,  so joe-user can delete a customer but a
    trigger will log that and joe-user cannot modify the log.

    In PostgreSQL, triggers are currently executed under the same
    username  as the one caused it to fire. So the user must have
    the proper permissions on the tables,  the  trigger  touches,
    too.

    I'm  not  sure  if  changing the current database user in the
    backend for the time a trigger function is  active  is  easy.
    Looks  like  all  the  permissions  are  checked against what
    GetPgUserName() returns.  But I'm not sure if this is true in
    all places that a trigger can invoke.

    I  think  it  would  be nice to to have something like setuid
    triggers. Some triggers cause a change of the user  and  some
    don't.  A  setuid_user  attribute in pg_trigger could do that
    job.  If that doesn't  contain  NULL,  the  trigger  will  be
    invoked  under  the  permissions  of  the user in that field.
    Obviously to say that a normal user can  only  create  setuid
    triggers with his own name.

    Should  the  syntax  for  CREATE  TRIGGER be extended here or
    should we have a completely different command for that?

    Any suggestions/comments?


Until later, Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #



Thu, 5 Feb 1998 19:10:08 +0100 (MET)
jwieck@debis.com (Jan Wieck)
Trigger and permissions
Jan Wieck wrote:
> 
>     I  think  it  would  be nice to to have something like setuid
>     triggers. Some triggers cause a change of the user  and  some
>     don't.  A  setuid_user  attribute in pg_trigger could do that
>     job.  If that doesn't  contain  NULL,  the  trigger  will  be
>     invoked  under  the  permissions  of  the user in that field.
>     Obviously to say that a normal user can  only  create  setuid
>     triggers with his own name.
> 
>     Should  the  syntax  for  CREATE  TRIGGER be extended here or
>     should we have a completely different command for that?
> 
>     Any suggestions/comments?

First, what standard says about execution permissions of triggers, 
functions and procedures ?

Second, if we would decide to have both setuid and non-setuid
triggers (etc) then I would suggest special command to set
setuid "bit" - just to don't change syntax of both CREATE TRIGGER
and CREATE FUNCTION. Also, in "normal" dbsystem, user first
does CREATE VIEW and only after that does GRANT ... TO ... (it likes
setting setuid bit on VIEW).

Vadim

Fri, 06 Feb 1998 08:54:19 +0700
"Vadim B. Mikheev" <vadim@sable.krasnoyarsk.su>
Re: [HACKERS] Trigger and permissions