PostgreSQL: Permission to execute function (that inserts into a table) but no permission to insert directly

Question

Sorry if this is the wrong place to ask this question.

I have created a function that inserts values into many tables, and I want the web front-end to execute this function. I have granted execute permission to the web front-end's login role, but I do not want the web front-end code to be able to insert values directly into the tables, I want the web front-end to use only the function that I have created for this purpose.

If I grant execute permission for the web front-end's login role but I do not grant insert permissions on the tables that are inserted into within the function, then an access error occurs.

How can I go about this?

score 7 · Accepted Answer · answered Sep 21 '09 at 04:35

7

You have to have another role, that has privileges to write directly to tables. Then you create the function using this another role, and add to function definition "SECURITY DEFINER" clause. And then you grant execute on this function to your web role.

You can read more about it in documentation.

You might also want to check blog post that I once wrote about securing database.

answered Sep 21 '09 at 04:35

Thanks for that information. Now I just have to make doubly sure that these functions are safe for 'crafted input' :) – dreamlax Sep 21 '09 at 05:12
As long as you don't do "EXECUTE" in them - you should be fine. – Sep 21 '09 at 11:29

PostgreSQL: Permission to execute function (that inserts into a table) but no permission to insert directly

1 Answers1