Securing Functions

When you call a function (or execute a trigger), that function typically operates with the privileges granted to your user ID or group. That means, for example, that a function can't UPDATE values in a given table unless you hold UPDATE privileges for that table. That's usually a good thing—you don't want a user to suddenly gain extra privileges just by calling a function. But it can also be inconvenient.

Say you're storing bookkeeping information in a PostgreSQL database and you've created a number of stored procedures (functions) to manage that data. You're running a large company with its own accounting department and you've created a group named CLERKS that holds limited privileges. In particular, a CLERK can SELECT values from most accounting tables, but can't UPDATE or DELETE rows. At the end of each month, you want a CLERK to execute a CLOSE MONTH procedure that archives old information into a set of history tables. Of course, you must GRANT the CLERK group EXECUTE privileges for the CLOSE MONTH function, but that's not enough—once an “old” record has been copied to the history table, CLOSE MONTH must DELETE the row from the working table. An accounting CLERK doesn't hold DELETE privileges. How do you solve this dilemma? You could temporarily GRANT DELETE privileges to the CLERK group at the end of each month, but that's a hassle (and a security hole). Instead, you can convey DELETE privileges (or any required privilege) to the CLOSE MONTH function itself.

When you create the CLOSE MONTH function, you can tell PostgreSQL which set of privileges to enforce at the time the function is invoked. You have two choices: You can tell PostgreSQL to use the privileges assigned to the invoker (SECURITY INVOKER), or to use the privileges assigned to the owner of the function (SECURITY DEFINER). Most functions execute with the privileges assigned to the invoker (SECURITY INVOKER). In this case, you want the CLOSE MONTH function to execute with elevated privileges. When you create the CLOSE MONTH function, be sure to include the SECURITY DEFINER clause and make sure that the function is owned by a user that holds the required privileges (you can use the ALTER FUNCTION ... OWNER TO command to transfer ownership). Problem solved.

Note: prior to version 7.4, all functions executed with the privileges assigned to the invoker.