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

> bryn(at)yugabyte(dot)com wrote:
>
>> 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.

*Listing publicly executable subprograms*

I mentioned earlier in this thread that I thought that it would be useful to be able to list all the user-defined functions and procedures in a database which "public" is authorized to execute. I think that I mentioned "table function" as a possible useful encapsulation. Forget this. It was just a metaphor for "reusable". I wrote this short "language sql" function. The idea is the it would be installed with a dedicated owner in a dedicated schema so that all users in the database could execute it. Here's the DDL to create it:

create function pg.public_has_execute(proacl in aclitem[])
returns boolean
security invoker
immutable
language sql
as $body$
select
(select proacl is null)
or
(with c as (select aclexplode(proacl) as r)
select exists (select * from c where (r).grantee = 0));
$body$;

Have I understood right that because this is "language sql", its defining subquery is inlined into the statement that uses it early enough in the compilation that it ends up the same as if its text has been included directly in the using statement—in other words, that the encapsulation as a function brings no performance considerations?

I've copied a self-contained script below that creates and tests it. The few tests that I did show that it works as I wanted it to. This is the result that it produces. It's what I expect:

owner | schema | name | type | language | public_has_execute
----------+--------+--------------------+------+----------+--------------------
postgres | utils | public_has_execute | func | sql | true
x | s | f | func | plpgsql | true
x | s | f | func | sql | false
x | s | p | proc | plpgsql | true
y | s | g | func | plpgsql | false

I did mention that it might be good if such a function could ship as part of a future PG Version. Forget that I said this, too. It's so short that anyone who wants it could write it. Moreover, somebody might want to list subprograms that, say, "mary" and "john" can execute. It seems that it would be far better just to implement this explicitly than to lard up a generic function with an elaborate parameterization and implementation.

So, unless anybody has comments, it's "case closed" from me.

--------------------------------------------------------------------------------
-- Setup
\c postgres postgres
set client_min_messages = warning;
drop database if exists db;
create database db owner postgres;

\c db postgres
set client_min_messages = warning;
drop schema if exists public cascade;
-- A more realistic example would have a dedicated user, say "utl"
-- to own utility subprograms.
create schema utils authorization postgres;

create function utils.public_has_execute(proacl in aclitem[])
returns boolean
security invoker
immutable
language sql
as $body$
select
(select proacl is null)
or
(with c as (select aclexplode(proacl) as r)
select exists (select * from c where (r).grantee = 0));
$body$;

-- Example use.
create view utils.publicly_executable_subprograms(owner, schema, name, type, language, public_has_execute) as
select distinct
pg_catalog.pg_get_userbyid(p.proowner) as "owner",
p.pronamespace::regnamespace::text,
p.proname::text,
case p.prokind
when 'a' then 'agg'
when 'w' then 'window'
when 'p' then 'proc'
else 'func'
end,
l.lanname,
utils.public_has_execute(p.proacl)
from
pg_catalog.pg_proc p
left join pg_catalog.pg_language l
on l.oid = p.prolang
where l.lanname in ('plpgsql', 'sql');
--------------------------------------------------------------------------------
-- Create some example subprograms.

create schema s authorization postgres;
drop user if exists x;
create user x login password 'p';
drop user if exists y;
create user y login password 'p';
drop user if exists z;
create user z login password 'p';

create procedure s.p(i in int)
language plpgsql
as $body$
begin
assert (i between 1 and 10);
end;
$body$;
alter procedure s.p(int) owner to x;
grant execute on procedure s.p(int) to z;

create function s.f(i in int)
returns int
language plpgsql
as $body$
begin
return i*2;
end;
$body$;
alter function s.f(int) owner to x;
grant execute on function s.f(int) to z;

create function s.f(i in text)
returns text
language sql
as $body$
select i||'*'::text;
$body$;
alter function s.f(text) owner to x;
grant execute on function s.f(text) to z;
revoke execute on function s.f(text) from public;

create function s.g(i in int)
returns int
language plpgsql
as $body$
begin
return i*2;
end;
$body$;
alter function s.g(int) owner to y;
grant execute on function s.g(int) to z;
revoke execute on function s.g(int) from public;
--------------------------------------------------------------------------------
-- Test the scheme.

select owner, schema, name, type, language, public_has_execute::text
from utils.publicly_executable_subprograms
where schema not in ('information_schema', 'pg_catalog')
order by 1, 2, 3, 4;