| Query 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. |
| Query type: | General (Overview of some aspect of the database.) |
| Query license: | MIT License |
| Data source: | INFORMATION_SCHEMA+system catalog |
| SQL query: | Click on query to copy it
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; |
| Collection name | Collection description |
|---|---|
| Find problems by overview | Queries 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 . |
| Category name | Category description |
|---|---|
| Security | Queries of this category provide information about the security measures. |
| User-defined routines | Queries of this category provide information about the user-defined routines |
| Reference |
|---|
| https://www.cybertec-postgresql.com/en/abusing-security-definer-functions/ |
| https://www.postgresql.org/docs/current/sql-createfunction.html#SQL-CREATEFUNCTION-SECURITY |