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

On 2/11/22 15:14, Bryn Llewellyn wrote:
>> /david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com> wrote:/
>>

> I looked at Chapter 52, "System Catalogs" at 
> https://www.postgresql.org/docs/current/catalogs.html 
> <https://www.postgresql.org/docs/current/catalogs.html>. It lists 97 
> relations. I'll have to defer reading about every one of these to 
> another day. I searched the page for likely names looking for ones with 
> "priv" and "rol". There's just a small number of hits. I drilled down on 
> these. But none seemed to help finding out which objects, of which 
> kinds, have which privileges (or roles) granted to which grantees.
> 
> Which catalog relations are sufficient to support a query that lists 
> out, for example, every user-defined function and procedure with its (at 
> least first-level) grantees?

Tip if you do:

psql -d test -U postgres -h localhost -E

the -E will get you the queries for the \ meta-commands.

So:

\df+ tag_changeset_fnc

Yields:

SELECT n.nspname as "Schema",
   p.proname as "Name",
   pg_catalog.pg_get_function_result(p.oid) as "Result data type",
   pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types",
  CASE p.prokind
   WHEN 'a' THEN 'agg'
   WHEN 'w' THEN 'window'
   WHEN 'p' THEN 'proc'
   ELSE 'func'
  END as "Type",
  CASE
   WHEN p.provolatile = 'i' THEN 'immutable'
   WHEN p.provolatile = 's' THEN 'stable'
   WHEN p.provolatile = 'v' THEN 'volatile'
  END as "Volatility",
  CASE
   WHEN p.proparallel = 'r' THEN 'restricted'
   WHEN p.proparallel = 's' THEN 'safe'
   WHEN p.proparallel = 'u' THEN 'unsafe'
  END as "Parallel",
  pg_catalog.pg_get_userbyid(p.proowner) as "Owner",
  CASE WHEN prosecdef THEN 'definer' ELSE 'invoker' END AS "Security",
  pg_catalog.array_to_string(p.proacl, E'\n') AS "Access privileges",
  l.lanname as "Language",
  p.prosrc as "Source code",
  pg_catalog.obj_description(p.oid, 'pg_proc') as "Description"
FROM pg_catalog.pg_proc p
      LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
      LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang
WHERE p.proname OPERATOR(pg_catalog.~) '^(tag_changeset_fnc)$' COLLATE 
pg_catalog.default
   AND pg_catalog.pg_function_is_visible(p.oid)
ORDER BY 1, 2, 4;

The parts you would be interested in are "Owner", "Security" and "Access 
privileges". You could modify the query to slim the results down some.

-- 
Adrian Klaver
adrian.klaver@aklaver.com