Thread: Discerning when functions had execute revoked from public
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
Todd Kover <kovert@omniscient.com> writes:
> 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?
pg_proc.proacl is what to look at. If that is NULL then it means the
function has default privileges (viz, public execute). If it's not NULL
then the function has whatever privileges it says.
regards, tom lane
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
Jerry Sievers <gsievers19@comcast.net> writes:
> 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'.
> select (aclexplode(proacl)).* from pg_proc where proname = 'foo';
I don't think this will work reliably, because you'll get zero rows out
in two cases: when proacl is null (which implies the default of public
execute) and when proacl is a non-null empty array (which implies that
no privileges are granted to anybody). The latter case is probably not
very common in practice, but if you do run into it you don't want to
confuse it with the default.
Here's a more extensively worked-out example:
postgres=# create user alice;
CREATE ROLE
postgres=# create user bob;
CREATE ROLE
postgres=# select oid, rolname from pg_authid;
oid | rolname
-------+----------
10 | postgres
95618 | alice
95619 | bob
(3 rows)
postgres=# \c - alice
You are now connected to database "postgres" as user "alice".
postgres=> create function foo() returns int as $$select 1$$ language sql;
CREATE FUNCTION
postgres=> select proacl, proacl is null from pg_proc where proname = 'foo';
proacl | ?column?
--------+----------
| t
(1 row)
postgres=> grant execute on function foo() to bob;
GRANT
postgres=> select proacl, proacl is null from pg_proc where proname = 'foo';
proacl | ?column?
--------------------------------------+----------
{=X/alice,alice=X/alice,bob=X/alice} | f
(1 row)
postgres=> select (aclexplode(proacl)).* from pg_proc where proname = 'foo';
grantor | grantee | privilege_type | is_grantable
---------+---------+----------------+--------------
95618 | 0 | EXECUTE | f
95618 | 95618 | EXECUTE | f
95618 | 95619 | EXECUTE | f
(3 rows)
It's worth explaining that what happened here was that GRANT
instantiated the default permissions for the function (namely, "all
rights for owner, plus execute rights for public") and then added the
requested privileges for bob.
postgres=> revoke all on function foo() from public;
REVOKE
postgres=> select proacl, proacl is null from pg_proc where proname = 'foo';
proacl | ?column?
-----------------------------+----------
{alice=X/alice,bob=X/alice} | f
(1 row)
postgres=> select (aclexplode(proacl)).* from pg_proc where proname = 'foo';
grantor | grantee | privilege_type | is_grantable
---------+---------+----------------+--------------
95618 | 95618 | EXECUTE | f
95618 | 95619 | EXECUTE | f
(2 rows)
postgres=> revoke all on function foo() from bob;
REVOKE
postgres=> revoke all on function foo() from alice;
REVOKE
postgres=> select proacl, proacl is null from pg_proc where proname = 'foo';
proacl | ?column?
--------+----------
{} | f
(1 row)
postgres=> select (aclexplode(proacl)).* from pg_proc where proname = 'foo';
grantor | grantee | privilege_type | is_grantable
---------+---------+----------------+--------------
(0 rows)
At this point it would be wrong to conclude that EXECUTE privileges are
available to PUBLIC, or indeed to anybody except a superuser.
postgres=> select foo();
ERROR: permission denied for function foo
However, this representation is still not telling the whole truth,
because the owner always has full grant options; alice can't revoke her
own grant options. (They're gone according to the ACL representation,
but Postgres will behave as though she still has them.) So this is not
a dead-end state --- alice can still re-grant permissions if she
chooses.
postgres=> grant execute on function foo() to public;
GRANT
postgres=> select foo();
foo
-----
1
(1 row)
postgres=> select proacl, proacl is null from pg_proc where proname = 'foo';
proacl | ?column?
------------+----------
{=X/alice} | f
(1 row)
postgres=> select (aclexplode(proacl)).* from pg_proc where proname = 'foo';
grantor | grantee | privilege_type | is_grantable
---------+---------+----------------+--------------
95618 | 0 | EXECUTE | f
(1 row)
At this point alice is getting her permissions to call her own function
via PUBLIC, not directly.
regards, tom lane