Re: Discerning when functions had execute revoked from public

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