Thread

1. Trigger and permissions

   Jan Wieck <jwieck@debis.com> — 1998-02-05T18:10:08Z

   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) #
   
   
   
   

2. Re: [HACKERS] Trigger and permissions

   Vadim B. Mikheev <vadim@sable.krasnoyarsk.su> — 1998-02-06T01:54:19Z

   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