Trigger and permissions

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(at)debis(dot)com (Jan Wieck) #