Re: My first patch! (to \df output) - Mailing list pgsql-hackers

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
bothirritating, and I think perhaps dangerous since security definer functions can be  so powerful. I thought that
rectifyingthat would make an excellent first patch, and I was bored today here in Prague since pgconf.eu is now
over...sohere it is. :) 
>>
>> This patch adds a column to the output of \df titled "Security" with values of "definer" or "invoker" based on the
booleansecdef 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
forthat 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
executionpermissions (which are also exposed nowhere outside the catalog to my knowledge), and maybe search_path since
that'srelated 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
regressiontests, so please consider this my formal and complete submission.  

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