On 2/11/22 15:14, Bryn Llewellyn wrote:
/david.g.johnston@xxxxxxxxx <mailto:david.g.johnston@xxxxxxxxx> 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@xxxxxxxxxxx
