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.<\/p>\n
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.<\/p>\n
postgres=# create schema test;\nCREATE SCHEMA\npostgres=# revoke USAGE on SCHEMA test from PUBLIC ;\nREVOKE\npostgres=# revoke execute on all functions in schema test from public;\nREVOKE\npostgres=# set search_path to test, public;\nSET\npostgres=# CREATE OR REPLACE FUNCTION row_count()\npostgres-# RETURNS integer AS $total$\npostgres$# declare\npostgres$# total integer;\npostgres$# BEGIN\npostgres$# SELECT count(1) into total FROM public.employee;\npostgres$# RETURN total;\npostgres$# END;\npostgres$# $total$\npostgres-# LANGUAGE plpgsql;\nCREATE FUNCTION\npostgres=#\npostgres=# create role reader;\nCREATE ROLE\npostgres=# create role temp;\nCREATE ROLE\npostgres=# grant usage on schema test to reader ;\nGRANT\npostgres=# grant USAGE on SCHEMA test to temp;\nGRANT\npostgres=# grant SELECT on TABLE public.employee to reader ;\nGRANT\npostgres=# grant EXECUTE on FUNCTION test.row_count to reader ;\nGRANT<\/pre>\nTo know the reader and temp roles has permissions on the row_count() function<\/p>\n
postgres=# SELECT has_function_privilege('reader', 'test.row_count()', 'execute');\n has_function_privilege \n------------------------\n t\n(1 row)\n\npostgres=# SELECT has_function_privilege('temp', 'test.row_count()', 'execute');\n has_function_privilege \n------------------------\n f\n(1 row)<\/pre>\nThe reader role has permissions to execute but not the temp.<\/p>\n
postgres=# set role reader ;\nSET\npostgres=> \npostgres=> select test.row_count();\n row_count \n-----------\n 1\n(1 row)\n\npostgres=> set role temp;\nSET\npostgres=> \npostgres=> select test.row_count();\nERROR: permission denied for function row_count\npostgres=><\/pre>\n<\/p>\n","protected":false},"excerpt":{"rendered":"
IDENTIFY A ROLE HAS EXECUTE PERMISSION ON FUNCTION IN\u00a0POSTGRESQL 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…<\/p>\n","protected":false},"author":1,"featured_media":2507,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[28],"tags":[243],"class_list":["post-2523","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-postgresql","tag-permission","category-28","description-off"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts\/2523","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/comments?post=2523"}],"version-history":[{"count":0,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts\/2523\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/media\/2507"}],"wp:attachment":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/media?parent=2523"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/categories?post=2523"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/tags?post=2523"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}