> b...@yugabyte.com wrote:
> 
>> ddevie...@gmail.com wrote:
>> 
>>> b...@yugabyte.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;

Reply via email to