How to allow user call stored procedure in Postgresql without granting additional rights?

Question

I have a stored procedure in Postgresql that contains COMMIT statement and I would like users to only access this procedure without giving them right to select, insert, etc. In function I would use SECURITY DEFINER, but from official docs https://www.postgresql.org/docs/11/sql-createprocedure.html:

A SECURITY DEFINER procedure cannot execute transaction control statements (for example, COMMIT and ROLLBACK, depending on the language).

As I understand this means that I can not use SECURITY DEFINER and have to grant user any right that is needed in stored procedure to allow user to execute it which completely defeats the whole purpose of allowing users only functions or procedures.

How does one call stored procedure with COMMIT statement in it without having individual grants to all statements inside the procedure?

Answer 1

You can grant privileges to a specific procedure so that the user can have transaction control privileges for that procedure only.

GRANT ALL ON PROCEDURE <procedure_name> TO <role>;

Answer 2

There is a workaround for this limitation.

A SECURITY DEFINER procedure cannot execute transaction control statements (for example, COMMIT and ROLLBACK, depending on the language).

It is possible to call (SELECT or PERFORM) function with SECURITY DEFINER from stored procedure. Of course the user must have a GRANT EXECUTE on both the procedure and the function.

CREATE OR REPLACE PROCEDURE procedure_test()
    LANGUAGE plpgsql
AS $$
begin

    PERFORM function_test();
    COMMIT;
    PERFORM function_test();

end
$$;

CREATE OR REPLACE FUNCTION function_test(
) RETURNS void
    LANGUAGE plpgsql SECURITY DEFINER
AS $$
begin

    -- some code

end
$$;