This is a follow-up of my recent blog tittled “Stored Procedures in PG 11 – Better late then never” posted on highgo.ca and also on planet postgres. It is available at https://www.highgo.ca/2020/04/10/stored-procedures-in-pg-11-better-late-then-never/. In this short blog titled “Stored Procedures also have rights”, I will be discussing the definer and invoker rights for stored procedures, the same concept also applies to stored functions.

The purpose of definer and invoker rights is to be able specify the user privlieges which will be used to execute the stored procedure. The selection of the security rights determines the user privlieges which will be used to execute the stored procedure as well as the name resolution of the database objects used in the routine. The security definer means that the procedure will be executed using the rights of the user that created the procedure. The invoker right means that the proceudre will be executed with the rights of the user that call or invokes the procedure.

Syntax
The minimal syntax for creating a stored procedure with security rights is the following :

CREATE [OR REPLACE] PROCEDURE proc_name(param_list)
LANGUAGE language_name
[EXTERNAL] SECURITY INVOKER | [EXTERNAL] SECURITY DEFINER
AS 
$
   procedure_body;
$;

They keyword EXTERNAL is optional, it is only there for SQL conformance. The default security right is definer, the user that creates the stored procedure. Oracle uses the keyword AUTHID to specify the security right and instead of INVOKER it uses current_user to specify that the procedure will be executed with caller privliege.

How it works

I am going to use some example stored procedures to demonstrate how it works, i will start with creating couple of users and grant some specific privlieges to show the difference between invoker and definer rights behaviour.

postgres=# create user use1;
CREATE ROLE
postgres=# create user use2;
CREATE ROLE

I will create the student table with user use1 and create a procedure with definer right and another one with invoker right. I will grant execute privlieges on these procedures to user use2 but it won’t have select privlieges on the student table. So when the definer right procedure is executed by user use2, it will run fine and return the result while running the procedure with invoker right from user use2 will result in an error.

Here is the demo… first i create the definer and invoker procedure with user use1 and grant execute on these procedures to user use2.

postgres=> CREATE OR REPLACE PROCEDURE GetTotalMarks_def(INOUT tot_marks INT default 0)
postgres-> LANGUAGE plpgsql
postgres-> SECURITY DEFINER
postgres-> AS $$
postgres$> BEGIN
postgres$>      select sum(total_marks) into tot_marks from student;
postgres$> END;
postgres$> $$;
CREATE PROCEDURE
postgres=> 
postgres=> CREATE OR REPLACE PROCEDURE GetTotalMarks_inv(INOUT tot_marks INT default 0)
postgres-> LANGUAGE plpgsql
postgres-> SECURITY INVOKER
postgres-> AS $$
postgres$> BEGIN
postgres$>      select sum(total_marks) into tot_marks from student;
postgres$> END;
postgres$> $$;
CREATE PROCEDURE
postgres=> grant execute on procedure GetTotalMarks_def to use2;
GRANT
postgres=> grant execute on procedure GetTotalMarks_inv to use2;
GRANT

Next i will connect as user use2 and will create the procedure which will call the definer and invoker procedures created by user use1 in the step above. Please note the first procedure is calling the definer right stored procedure and second one is calling the invoker right stored procedure.

postgres=> CREATE OR REPLACE PROCEDURE call_GetTotalMarks_Def()
postgres-> LANGUAGE plpgsql
postgres-> AS $$
postgres$> Declare
postgres$>         tot_marks INT;
postgres$> BEGIN
postgres$>         -- Get total marks
postgres$>         call GetTotalMarks_def(tot_marks);
postgres$>         raise info 'total marks from definer procedure : %',tot_marks;
postgres$> END;
postgres$> $$;
CREATE PROCEDURE

postgres=> CREATE OR REPLACE PROCEDURE call_GetTotalMarks_Inv()
postgres-> LANGUAGE plpgsql
postgres-> AS $$
postgres$> Declare
postgres$>         tot_marks INT;
postgres$> BEGIN
postgres$>         -- Get total marks
postgres$>         call GetTotalMarks_inv(tot_marks);
postgres$>         raise info 'total marks from definer procedure : %',tot_marks;
postgres$> END;
postgres$> $$;
CREATE PROCEDURE

Next i will go ahead and call these procedures from user use2, the call to definer procedure will return a result because it is executed with definer rights (user use1) which has rights on the student table. The call to definer procedure will return a error because it is called with invoker user (user use2) which doesn’t have rights on the student table.

postgres=> call call_GetTotalMarks_Def();
INFO:  total marks from definer procedure : 610
CALL

postgres=> call call_GetTotalMarks_Inv();
2020-04-22 15:33:19.043 UTC [14821] ERROR:  permission denied for table student
2020-04-22 15:33:19.043 UTC [14821] CONTEXT:  SQL statement "select sum(total_marks)                from student"
	PL/pgSQL function gettotalmarks_inv(integer) line 3 at SQL statement
	SQL statement "CALL GetTotalMarks_inv(tot_marks)"
	PL/pgSQL function call_gettotalmarks_inv() line 6 at CALL
2020-04-22 15:33:19.043 UTC [14821] STATEMENT:  call call_GetTotalMarks_Inv();
ERROR:  permission denied for table student
CONTEXT:  SQL statement "select sum(total_marks)                from student"
PL/pgSQL function gettotalmarks_inv(integer) line 3 at SQL statement
SQL statement "CALL GetTotalMarks_inv(tot_marks)"
PL/pgSQL function call_gettotalmarks_inv() line 6 at CALL
postgres=> 

Please note that transaction control statements i.e. commit/rollback are not allowed with definer right procedure, you will get this error if you try to do that. In this case the stored procedure was created as DEFINER security rights.

Conclusion

The definer and invoker right functionality is really useful in situations where the business logic is created as a super user and how the procedures and functions underpinnign the business logic are executed with rights of every calling user. In this blog we have learned how to create the procedures with security rights and understand the difference between definer and invoker rights.

Ahsan Hadi

Ahsan Hadi