Todd, there is no auditing that will answer the question *when* (in terms of when change took place), strictly speaking. But anyway, have a look at the functions acl* and inparticular aclexplode as seen below. If I understand correctly how it works, public execute is granted in the default case of no rows returned as seen in the first case AND when we get a row with grantee=0 and privilege='execute'. sj$ psql -ef s Pager usage is off. set datestyle to iso,ymd; SET set client_min_messages to warning; SET begin; BEGIN create function foo() returns int as $$select 1$$ language sql; CREATE FUNCTION select (aclexplode(proacl)).* from pg_proc where proname = 'foo'; grantor | grantee | privilege_type | is_grantable ---------+---------+----------------+-------------- (0 rows) revoke execute on function foo() from public; REVOKE select (aclexplode(proacl)).* from pg_proc where proname = 'foo'; grantor | grantee | privilege_type | is_grantable ---------+---------+----------------+-------------- 16385 | 16385 | EXECUTE | f (1 row) grant execute on function foo() to public; GRANT select (aclexplode(proacl)).* from pg_proc where proname = 'foo'; grantor | grantee | privilege_type | is_grantable ---------+---------+----------------+-------------- 16385 | 16385 | EXECUTE | f 16385 | 0 | EXECUTE | f (2 rows) It may be the case that other acl* functions can answer this question even more easily and/or infvormation_schema views will give useful output as well. HTH Todd Kover <kovert@xxxxxxxxxxxxxx> writes: > I am trying to write something that will enumerate grants/revokes on > functions to make sure they are adjusted properly after said function is > drop/recreated, should that happen. This will also be used to validate > that permissions are what they should be. > > According to: > > http://www.postgresql.org/docs/9.2/static/sql-createfunction.html > > } Another point to keep in mind is that by default, execute privilege > } is granted to PUBLIC for newly created functions (see GRANT for > } more information). Frequently you will wish to restrict use of a > } security definer function to only some users. To do that, you must > } revoke the default PUBLIC privileges and then grant execute privilege > } selectively. To avoid having a window where the new function is > } accessible to all, create it and set the privileges within a single > } transaction. > > This revocation from public happens in our environment. Trouble is, I > can not find where an indiciation that execute has been revoked from > public in pg_catalog.pg_proc (or any other table for that matter). Is > there a way to find this somewhere in the catalog? > > Apologies if this should be obvious. I'm sure I will find it as soon as > I hit send. :-) > > thanks, > -Todd > > > -- > Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consulting@xxxxxxxxxxx p: 312.241.7800 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
