Viswamitra's blog

To identify a role has execute permission on function, PostgreSQL has a catalog function(has_function_privilege) which makes it easy. Below is the simple example.

I have created a simple schema test with a function row_count() which return the row count of employee table. I have grated execut permission on the function to reader role.

postgres=# create schema test;
CREATE SCHEMA
postgres=# revoke USAGE on SCHEMA test from PUBLIC ;
REVOKE
postgres=# revoke execute on all functions in schema test from public;
REVOKE
postgres=# set search_path to test, public;
SET
postgres=# CREATE OR REPLACE FUNCTION row_count()
postgres-#  RETURNS integer AS $total$
postgres$#  declare
postgres$#  total integer;
postgres$#  BEGIN
postgres$#  SELECT count(1) into total FROM public.employee;
postgres$#  RETURN total;
postgres$#  END;
postgres$#  $total$
postgres-#  LANGUAGE plpgsql;
CREATE FUNCTION
postgres=#
postgres=# create role reader;
CREATE ROLE
postgres=# create role temp;
CREATE ROLE
postgres=# grant usage on schema test to reader ;
GRANT
postgres=# grant USAGE on SCHEMA test to temp;
GRANT
postgres=# grant SELECT on TABLE public.employee to reader ;
GRANT
postgres=# grant EXECUTE on FUNCTION test.row_count to reader ;
GRANT

To know the reader and temp roles has permissions on the row_count() function

postgres=# SELECT has_function_privilege('reader', 'test.row_count()', 'execute');
 has_function_privilege 
------------------------
 t
(1 row)

postgres=# SELECT has_function_privilege('temp', 'test.row_count()', 'execute');
 has_function_privilege 
------------------------
 f
(1 row)

The reader role has permissions to execute but not the temp.

postgres=# set role reader ;
SET
postgres=> 
postgres=> select test.row_count();
 row_count 
-----------
         1
(1 row)

postgres=> set role temp;
SET
postgres=> 
postgres=> select test.row_count();
ERROR:  permission denied for function row_count
postgres=>