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

Oops! Here it is in the proper diff format. I didn't have my env set up correctly :(



--
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
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
theregression 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 |
Sourcecode  | Description  
>
--------+-------+------------------+---------------------+--------+----------+------------+---------+----------+--------------+-------------
> public | akeys | text[]           | hstore              | normal | invoker  | immutable  | jerdman | c        |
hstore_akeys|  
> (1 row)
>
> --
> Jon T Erdman
> Postgresql Zealot
>
>