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

[Bryn Llewellyn <bryn@xxxxxxxxxxxx>]

ddevienne@xxxxxxxxx wrote:

bryn@xxxxxxxxxxxx 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.