Search code examples
postgresqlprivileges

postgresql privileges Ensuring inserts are only done through functions

Let's say I have a table persons which contains only a name(varchar) and a user client.

I'd like that the only way for client to insert to persons is through the function:

CREATE OR REPLACE FUNCTION add_a_person(a_name varying character)
  RETURNS void AS
$BODY$
BEGIN
    INSERT INTO persons VALUES(a_name);
END;
$BODY$
  LANGUAGE plpgsql VOLATILE COST 100;

So, I don't want to grant client insert privileges on persons and only give execute privilege for add_a_person. But without doing so, I'd get a permission denied because of the use of insert inside the function.

I have not found a way to this in the postgres documentation about granting privileges. Is there a way to do this?

Solution

  • You can define the function with SECURITY DEFINER. This will allow the function to run for the restricted user as if they had the higher privileges of the function's creator (which needs to be able to insert into the table).

    The last line of the definition would look like this:

    LANGUAGE plpgsql VOLATILE COST 100 SECURITY DEFINER;