On Oct 27, 2012, at 10:45 AM, Pavel Stehule <pavel.steh...@gmail.com> wrote:
> Hello > > 2012/10/27 Jon Erdman <postgre...@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
Description: Binary data
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
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
