GRANT/REVOKE on functions

Question

I am using the has_function_privilege, to check and see if a certain user can access a function in my database. However, if I use REVOKE to revoke access, has_function_privilege still returns true:

db=> revoke execute on function getid(text,text)  from user1;
REVOKE
db=> select has_function_privilege('user1', 'getid(text, text)', 'execute');
 has_function_privilege 
------------------------
 t
(1 row)

I am using PostgreSQL 9.2.3 on Windows 7.

Answer 1

This is probably because public has execute privilege on your function (it's the default).

Try this:

revoke execute on function getid(text,text)  from public;

and recheck the result of has_function_privilege.

For public to not get this privilege on newly created functions, you may change the default with:

ALTER default privileges revoke execute on functions from public;