IDENTIFY A ROLE HAS EXECUTE PERMISSION ON FUNCTION IN POSTGRESQL
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=>
