Different search paths of SECURITY DEFINER functions

Back to Catalog
Goal Find the different search paths used in case of SECURITY DEFINER functions and the number of their occurrences. Make sure that these have been specified correctly and consistently and that they do not refer to any non-existent schemas.
Type General (Overview of some aspect of the database.)
License MIT License
Data Source INFORMATION_SCHEMA+system catalog
SQL Query 
SELECT 
  array_to_string(proconfig, ',') AS search_path,
  Count(*) AS nr_of_occurrences
FROM 
  pg_catalog.pg_proc, 
  pg_catalog.pg_namespace
WHERE 
  pg_proc.pronamespace = pg_namespace.oid
 AND prosecdef=TRUE
AND pg_namespace.nspname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)
GROUP BY array_to_string(proconfig, ',')
ORDER BY Count(*) DESC;
Collections

This query belongs to the following collections:

NameDescription
Find problems by overviewQueries that results point to different aspects of database that might have problems. A human reviewer has to decide based on the results as to whether there are problems or not .
Categories

This query is classified under the following categories:

NameDescription
SecurityQueries of this category provide information about the security measures.
User-defined routinesQueries of this category provide information about the user-defined routines