Updatable views that have not been turned to read only

Back to Catalog
Goal Find views that are theoretically updatable but do not have INSTEAD OF trigger or DO INSTEAD NOTHING rule to prevent data modifications through the view.
Type Problem detection (Each row in the result could represent a flaw in the design)
Reliability Medium (Medium number of false-positive results)
License MIT License
Fixing Suggestion Make data modifications by using database routines and prevent data modifications through views. Thus, use DO INSTEAD NOTHING rules or INSTEAD OF triggers to prevent data modifications through a view that is theoretically updatable. Essentially it means implementing WITH READ ONLY constraint.
Data Source INFORMATION_SCHEMA+system catalog
SQL Query 
WITH updatable_views AS (SELECT table_schema AS schema, table_name AS view, is_updatable, is_insertable_into, is_trigger_updatable, is_trigger_deletable, is_trigger_insertable_into
FROM Information_schema.views
WHERE (is_insertable_into='YES' OR is_updatable='YES') 
AND 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))
SELECT schema, view, 'INSERT is permitted' AS possible_problem
FROM updatable_views
WHERE is_insertable_into='YES'
AND is_trigger_insertable_into='NO'
AND (schema, view) NOT IN (SELECT schemaname, tablename    
FROM pg_rules
WHERE definition~'ON[[:space:]]INSERT.*DO[[:space:]]INSTEAD[[:space:]]NOTHING')
UNION SELECT schema, view, 'UPDATE is permitted' AS possible_problem
FROM updatable_views
WHERE is_updatable='YES'
AND is_trigger_updatable='NO'
AND (schema, view) NOT IN (SELECT schemaname, tablename    
FROM pg_rules
WHERE definition~'ON[[:space:]]UPDATE.*DO[[:space:]]INSTEAD[[:space:]]NOTHING')
UNION SELECT schema, view, 'DELETE is permitted' AS possible_problem
FROM updatable_views
WHERE (is_insertable_into='YES' OR is_updatable='YES')
AND is_trigger_deletable='NO'
AND (schema, view) NOT IN (SELECT schemaname, tablename    
FROM pg_rules
WHERE definition~'ON[[:space:]]DELETE.*DO[[:space:]]INSTEAD[[:space:]]NOTHING')
ORDER BY schema, view, possible_problem;

SQL statements that help generate fixes for the identified problem.

SQL Query to Generate FixDescription
WITH updatable_views AS (SELECT table_schema AS schema, table_name AS view, is_updatable, is_insertable_into, is_trigger_updatable, is_trigger_deletable, is_trigger_insertable_into
FROM Information_schema.views
WHERE (is_insertable_into='YES' OR is_updatable='YES') 
AND 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))
SELECT format('CREATE OR REPLACE RULE %2$s_insert AS ON INSERT TO %1$I.%2$I DO INSTEAD NOTHING;', schema, view) AS statements
FROM updatable_views
WHERE is_insertable_into='YES'
AND is_trigger_insertable_into='NO'
AND (schema, view) NOT IN (SELECT schemaname, tablename    
FROM pg_rules
WHERE definition~'ON[[:space:]]INSERT.*DO[[:space:]]INSTEAD[[:space:]]NOTHING')
ORDER BY schema, view;
Prevent INSERT operations
WITH updatable_views AS (SELECT table_schema AS schema, table_name AS view, is_updatable, is_insertable_into, is_trigger_updatable, is_trigger_deletable, is_trigger_insertable_into
FROM Information_schema.views
WHERE (is_insertable_into='YES' OR is_updatable='YES') 
AND 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))
SELECT format('CREATE OR REPLACE RULE %2$s_update AS ON UPDATE TO %1$I.%2$I DO INSTEAD NOTHING;', schema, view) AS statements
FROM updatable_views
WHERE is_updatable='YES'
AND is_trigger_updatable='NO'
AND (schema, view) NOT IN (SELECT schemaname, tablename    
FROM pg_rules
WHERE definition~'ON[[:space:]]UPDATE.*DO[[:space:]]INSTEAD[[:space:]]NOTHING')
ORDER BY schema, view;
Prevent UPDATE operations
WITH updatable_views AS (SELECT table_schema AS schema, table_name AS view, is_updatable, is_insertable_into, is_trigger_updatable, is_trigger_deletable, is_trigger_insertable_into
FROM Information_schema.views
WHERE (is_insertable_into='YES' OR is_updatable='YES') 
AND 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))
SELECT format('CREATE OR REPLACE RULE %2$s_delete AS ON DELETE TO %1$I.%2$I DO INSTEAD NOTHING;', schema, view) AS statements
FROM updatable_views
WHERE (is_insertable_into='YES' OR is_updatable='YES')
AND is_trigger_deletable='NO'
AND (schema, view) NOT IN (SELECT schemaname, tablename    
FROM pg_rules
WHERE definition~'ON[[:space:]]DELETE.*DO[[:space:]]INSTEAD[[:space:]]NOTHING')
ORDER BY schema, view;
Prevent DELETE operations
Collections

This query belongs to the following collections:

NameDescription
Find problems automaticallyQueries, 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 .
Categories

This query is classified under the following categories:

NameDescription
Derived tablesQueries of this category provide information about the derived tables (views, materialized views), which are used to implement virtual data layer.
Triggers and rulesQueries of this category provide information about triggers and rules in a database.