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

> ddevienne(at)gmail(dot)com wrote:
>
>> bryn(at)yugabyte(dot)com wrote:
>>
>> …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)
>
> `aclexplode` is a table-valued function, so you normally use it in the
> FROM clause.
> Here's how I use it on schemas for example:
>
> ```
> select nspname as name,
> nspowner::regrole::text as owner,
> grantor::regrole::text,
> grantee::regrole::text,
> privilege_type, is_grantable
> from pg_namespace
> left join lateral aclexplode(nspacl) on true
> where ...
> order by nspname
> ```

Thank you very much for the tip and for the code example, Dominique. Yes, my SQL was poorly written. I wanted just a simple proof of concept that "aclexplode()" lets me access the individual values that the "proacl" column represents as an array of "aclitem" records without needing to parse text strings like "z=X/x". I'd started to picture writing my own function to do what "aclexplode()" does. But Julien Rouhaud told me about the built-in for the purpose I needed before I'd had time to give my own function any thought.

I should have at least moved my invocation of "aclexplode()" out of the CTE. But, of course, for an approach that finds many "pg_proc" rows, I'll need a proper, robust approach like you showed.