Trigger functions behave just like other functions as far as privileges are concerned. With a minor exception:
To create a trigger on a table, the user must have the
privilege on the table. The user must also have
EXECUTE privilege on the trigger function.
After feedback in the comments I did some research. There is an open TODO item in the Postgres Wiki:
Tighten trigger permission checks
Linked to this thread on Postgres hackers. Currently,
EXECUTE privileges on a trigger function are only checked at trigger create time, but not at runtime. So revoking EXECUTE on the trigger function has no effect on a trigger once created. Your observation seems to be correct.
This does not grant any additional privileges to manipulate objects. If the calling role lacks privileges needed to execute (parts of) the function body, the usual exception is raised. To pave the way, you could make a privileged user
OWNER of the function and use the
clause, as documented in the manual here. It causes the function to be run with the permissions of the owner instead of the invoker (default).
If the owner is a superuser, you need to be extra careful who you grant the
EXECUTE privilege and what the function can do to avoid abuse. You may want to
REVOKE ALL ON FUNCTION foo() FROM public;
to begin with and use
SET search_path for the function.
Be sure to read the chapter on Writing
SECURITY DEFINER Functions Safely.
Find a code example in this related answer on SO.