Query goal: | Find views with security invoker option that do not have any underlying base table with a security policy. Security invoker option of views is possible starting from PostgreSQL 15. "The main use case (and the one that inspired the feature) is to be able to use views and still check row-level security policies on the underlying tables as the invoker." |
Query type: | Problem detection (Each row in the result could represent a flaw in the design) |
Query reliability: | Medium (Medium number of false-positive results) |
Query license: | MIT License |
Fixing suggestion: | Change the view to a security definer view. |
Data source: | INFORMATION_SCHEMA+system catalog |
SQL query: | Click on query to copy it
WITH invoker_views AS (SELECT t.table_schema, t.table_name, vtu.view_schema, vtu.view_name FROM information_schema.tables AS t INNER JOIN information_schema.view_table_usage AS vtu ON t.table_schema = vtu.table_schema AND t.table_name = vtu.table_name WHERE t.table_type = 'BASE TABLE' AND t.table_schema NOT IN (SELECT schema_name FROM INFORMATION_SCHEMA.schemata WHERE schema_name<>'public' AND schema_owner='postgres' AND schema_name IS NOT NULL) AND (vtu.view_schema, vtu.view_name) IN (SELECT pg_namespace.nspname, pg_class.relname FROM pg_catalog.pg_class, pg_catalog.pg_namespace WHERE pg_catalog.pg_class.relnamespace=pg_catalog.pg_namespace.oid AND 'security_invoker=true'=ANY(pg_catalog.pg_class.reloptions))) SELECT view_schema, view_name FROM invoker_views AS iv WHERE NOT EXISTS (SELECT * FROM pg_policies AS pp WHERE pp.schemaname=iv.table_schema AND pp.tablename=iv.table_name) ORDER BY view_schema, view_name; |
SQL query | Description |
---|---|
WITH invoker_views AS (SELECT t.table_schema, t.table_name, vtu.view_schema, vtu.view_name FROM information_schema.tables AS t INNER JOIN information_schema.view_table_usage AS vtu ON t.table_schema = vtu.table_schema AND t.table_name = vtu.table_name WHERE t.table_type = 'BASE TABLE' AND t.table_schema NOT IN (SELECT schema_name FROM INFORMATION_SCHEMA.schemata WHERE schema_name<>'public' AND schema_owner='postgres' AND schema_name IS NOT NULL) AND (vtu.view_schema, vtu.view_name) IN (SELECT pg_namespace.nspname, pg_class.relname FROM pg_catalog.pg_class, pg_catalog.pg_namespace WHERE pg_catalog.pg_class.relnamespace=pg_catalog.pg_namespace.oid AND 'security_invoker=true'=ANY(pg_catalog.pg_class.reloptions))) SELECT format('ALTER VIEW %1$I.%2$I SET (security_invoker=false);', view_schema, view_name) AS statements FROM invoker_views AS iv WHERE NOT EXISTS (SELECT * FROM pg_policies AS pp WHERE pp.schemaname=iv.table_schema AND pp.tablename=iv.table_name) ORDER BY view_schema, view_name; | Set security_invoker=false |
Collection name | Collection description |
---|---|
Find problems automatically | Queries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not . |
Category name | Category description |
---|---|
Derived tables | Queries of this category provide information about the derived tables (views, materialized views), which are used to implement virtual data layer. |
Security | Queries of this category provide information about the security measures. |
Reference |
---|
https://www.cybertec-postgresql.com/en/view-permissions-and-row-level-security-in-postgresql/ |