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.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | 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 |
| 1 2 3 4 5 6 7 8 9 10 11 | 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) |
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | 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=> |
