PL/pgSQL and Security

By default, a PL/pgSQL function executes with the privileges of the user that calls it. That's safe because an unprivileged user won't gain extra privileges simply by calling a PL/pgSQL function. However, there are times when you may want to convey extra privileges to a function. For example, you might hide sensitive information (such as payroll data) from a clerical user, but you want that user to “close the books” at the end of each month. Presumably, the close_the_books() function can do its work without exposing secret data to the user. If that's the case, you can tell PostgreSQL that you want the close_the_books() function to inherit the privileges of the author of the function. To convey extra privileges to a function, just add a SECURITY clause to the function definition. The SECURITY clause follows the function body and can precede or follow the LANGUAGE clause:

CREATE [OR REPLACE] FUNCTION name ( [[argname] argtype [, ...] ] )
    RETURNS return_type
    AS $$definition$$
    LANGUAGE langname |  [ SECURITY INVOKER | SECURITY DEFINER ]

For example:

CREATE OR REPLACE FUNCTION close_the_books( ) RETURNS void AS $$
  BEGIN
    ...
  END;
$$ LANGUAGE 'plpgsql' SECURITY DEFINER;

If you don't include a SECURITY clause, PostgreSQL assumes SECURITY INVOKER (meaning that the function executes with the privileges of the invoker). Be aware that when you convey extra privileges to close_the_books(), you are also conveying extra privileges to any SECURITY INVOKER functions called by close_the_books().