What are the privileges required to execute a trigger function in PostgreSQL 8.4?

What are the privileges required to execute a trigger function in PostgreSQL 8.4?

It seems that the privileges set to a role does not matter to execute a trigger function. I think I have seen some day that the privileges required to execute a trigger function is the EXECUTE privilege but for the owner of the table, not the actual role that performs the action which fires the trigger that calls the trigger function.

I cannot find the documentation part that explains that point, any help?

The manual:

To create or replace a trigger on a table, the user must have the
TRIGGER privilege on the table. The user must also have EXECUTE
privilege on the trigger function.

But note this open TODO item in the Postgres Wiki:

Tighten trigger permission checks

Linked to this thread on Postgres hackers. Currently (incl. Postgres 14), 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 SECURITY DEFINER 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).

Be extra careful who you grant the EXECUTE privilege and what the function can do to avoid abuse, especially if the owner is a superuser.

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.

StackExchange Database Administrators Q#46833, answer score: 13