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

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800