Grant EXECUTE to many PostGIS functions

Question

Hello.

I have a Web Application which is based on MapServer, which uses PostGIS as underlying database extension. Now I want to have a dedicated database role which is used for MapServer, cause I don't want to access the database via the postgres superuser. This role should only have SELECT permission on public tables (which is easy to achieve) and EXECUTE permissions on public PostGIS functions.

Several questions arise: Are ALL the PostGIS relevant functions stored in the public schema of a database or is there anything else to consider?

How can I extract all the functions information - i.e. function name, number and names of arguments - from the information_schema or the pg_catalog of the database?! I need this information for the GRANT EXECUTE on function(args) to MapServerUser statements!

Thank you in advance!!!

Answer 1

In PostgreSQL 8.4.x:

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"
FROM pg_catalog.pg_proc p
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE pg_catalog.pg_function_is_visible(p.oid)
     AND n.nspname <> 'pg_catalog'
  AND n.nspname <> 'information_schema'
ORDER BY 1, 2, 4;

Found by running psql with the -E parameter (show hidden queries) and then running the \df command.

Also, the "public" schema in PostgreSQL is just named that way. It carries no special meaning. It's a bad name. What you DO need to look out for is the "PUBLIC" role (all caps). While tables are not automatically granted the PUBLIC role, my experience is that PUBLIC automatically gets execute permission on functions defined with SECURITY INVOKER.