(unnamed)
text/plain
-- Jon T Erdman Postgresql Zealot On Nov 9, 2012, at 1:53 PM, Jon Erdman <postgresql@thewickedtribe.net> wrote: > On Oct 27, 2012, at 10:45 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote: > >> Hello >> >> 2012/10/27 Jon Erdman <postgresql@thewickedtribe.net>: >>> >>> Hello Hackers! >>> >>> So, currently the only way to see if a function is security definer or not is to directly query pg_proc. This is both irritating, and I think perhaps dangerous since security definer functions can be so powerful. I thought that rectifying that would make an excellent first patch, and I was bored today here in Prague since pgconf.eu is now over...so here it is. :) >>> >>> This patch adds a column to the output of \df titled "Security" with values of "definer" or "invoker" based on the boolean secdef column from pg_proc. I've also included a small doc patch to match. This patch is against master from git. Comments welcome! >>> >>> I just realized I didn't address regression tests, so I guess this is not actually complete yet. I should have time for that next week after I get back to the states. >>> >>> I would also like to start discussion about perhaps adding a couple more things to \df+, specifically function execution permissions (which are also exposed nowhere outside the catalog to my knowledge), and maybe search_path since that's related to secdef. Thoughts? >> >> I prefer show this in \dt+ for column "Security" - and for other >> functionality maybe new statement. > > I'm assuming you meant "\df+", and I've changed it accordingly. With this change there is now nothing to change in the regression tests, so please consider this my formal and complete submission. <describe.patch> > > Is there anything else I need to do to get this considered? > > Oh, in case anyone is interested, here's what the query now looks like and the new output: > > jerdman=# \df+ public.akeys > ********* QUERY ********** > 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 > WHEN p.proisagg THEN 'agg' > WHEN p.proiswindow THEN 'window' > WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN 'trigger' > ELSE 'normal' > END as "Type", > CASE > WHEN prosecdef THEN 'definer' > ELSE 'invoker' > END AS "Security", > CASE > WHEN p.provolatile = 'i' THEN 'immutable' > WHEN p.provolatile = 's' THEN 'stable' > WHEN p.provolatile = 'v' THEN 'volatile' > END as "Volatility", > pg_catalog.pg_get_userbyid(p.proowner) as "Owner", > 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 ~ '^(akeys)$' > AND n.nspname ~ '^(public)$' > ORDER BY 1, 2, 4; > ************************** > > List of functions > Schema | Name | Result data type | Argument data types | Type | Security | Volatility | Owner | Language | Source code | Description > --------+-------+------------------+---------------------+--------+----------+------------+---------+----------+--------------+------------- > public | akeys | text[] | hstore | normal | invoker | immutable | jerdman | c | hstore_akeys | > (1 row) > > -- > Jon T Erdman > Postgresql Zealot > >