Re: Discerning when functions had execute revoked from public

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(at)omniscient(dot)com> 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres(dot)consulting(at)comcast(dot)net
p: 312.241.7800