Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 3 down vote accepted

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 TRIGGER privilege on the table. The user must also have EXECUTE privilege on the trigger function.

UPDATE 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

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

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.

share|improve this answer
    
No, I don't want a SECURITY DEFINER, I want a SECURITY INVOKER. But it seems (for trigger function, not for regular function) that by using the default option (SECURITY INVOKER), it does not act like so. –  user18077 Jul 23 '13 at 8:44
1  
@EtienneRouxel: trigger functions are functions like other functions as far as privileges are concerned. What makes you think otherwise? –  Erwin Brandstetter Jul 23 '13 at 8:51
    
@EtienneRouxel: I added a quote form the manual to document a minor exception. –  Erwin Brandstetter Jul 23 '13 at 9:05
1  
Testing: I created a simple trigger function that raises a NOTICE. I removed ALL privileges from PUBLIC and from the owner of the function. Then, if I use the owner or any other role that do no have any privilege on that function, I should expect an error due to a lack of privileges but everything runs successfully. –  user18077 Jul 23 '13 at 9:31
1  
@EtienneRouxel: Interesting. I also tested. You cannot create the trigger if you don't have the execute privilege for the trigger function. But you can still revoke that execute privilege after creating the trigger and the trigger won't stop working. I did some research. Adding links to the question ... –  Erwin Brandstetter Jul 23 '13 at 9:57

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.