Re: "grant usage on schema" confers the ability to execute all user-defined functions in that schema, with needing to grant "execute"

> rjuju123(at)gmail(dot)com wrote:
>
>> adrian(dot)klaver(at)aklaver(dot)com wrote:
>>
>>> bryn(at)yugabyte(dot)com wrote:
>>>
>>> I s'pose that I can interpret this output in the light of the "miriam" example by guessing than an empty LHS means "public" and that the initial "X" means "execute". It looks like what follows the slash is the owner of the object (a denormalization of what the "Owner" column shows.) Where is this notation, "miriam=arwdDxt/miriam", explained?
>>
>> Here:
>> Table 5.1. ACL Privilege Abbreviations
>> https://www.postgresql.org/docs/current/ddl-priv.html#PRIVILEGE-ABBREVS-TABLE
>
> You might also be interested in aclexplode() function, see:
> 9.26. System Information Functions and Operators
> https://www.postgresql.org/docs/current/functions-info.html

Thanks, Adrian. I'm embarrassed that I didn't notice Table 5.1.

Thanks, Julien. I tried a little test. I created a function "s.q()" with owner "x" and then granted "execute" on it to user "z". But I didn't yet revoke "execute" on "s.q()" from "public".

This is whet the "Access privilege" column in the \df+ report for "s.q()" shows:

=X/x
x=X/x
z=X/x

I.e. three facts per row: grantee, privilege, and grantee. Then I did this:

with c as (
select
proname::text as name,
pronamespace::regnamespace::text as schema,
aclexplode(proacl) as "aclexplode(proacl)"
from pg_catalog.pg_proc)
select "aclexplode(proacl)" from c
where name = 'q' and schema = 's';

This is the result:

aclexplode(proacl)
-----------------------------
(1494148,0,EXECUTE,f)
(1494148,1494148,EXECUTE,f)
(1494148,1494150,EXECUTE,f)

This is consistent with the doc that says the array is exploded to records with this signature:

(grantor oid, grantee oid, privilege_type text, is_grantable boolean )

This is the perfect starting point for the table function that I was after that would list all user-defined functions and procedures that have "execute" granted to "public". A little bit of perfectly manageable effort will be needed for the special case that when "proacl" is "null", it means that "public" has "execute"—and also to translate the "oid" values" to text.

I'll try this presently and report back.