Which tables and columns are altered for both REVOKE/GRANT ALL ON FUNCTION

Question

Which tables/columsn are altered for the following queries:

REVOKE ALL PRIVILEGES ON FUNCTION "..."() FROM PUBLIC CASCADE;
-- function_owner can still update the function

GRANT ALL PRIVILEGES ON FUNCTION "..."() TO function_owner CASCADE;


REVOKE ALL PRIVILEGES ON FUNCTION "..."() FROM function_owner CASCADE;
-- function_owner can't update the function. 

GRANT ALL PRIVILEGES ON FUNCTION "..."() TO function_owner CASCADE;
-- function_owner can now update the function.

I know that pg_catalog.pg_proc.proacl are altered. Are there any other tables and columns?

Answer 1

pg_proc.proacl is indeed the only column that is modified if you GRANT or REVOKE privileges on a function.

There are two things to keep in mind:

1. When a function is created, it has the default privileges (PUBLIC and the owner may EXECUTE it) and the proacl column is NULL (this signifies default privileges).

   That is why the column is empty in the beginning and contains a value after you REVOKE the privileges for PUBLIC.

2. If you REVOKE a privilege that hasn't been granted before, nothing happens. Similarly, if you GRANT a privilege that is already granted, nothing happens.

   Your GRANT is such a no-operation, because the owner has the EXECUTE privilege by default. You just don't see it before you changed the privilege from the default with your REVOKE.