Query goal: | Find views that do not have the security barrier option but restrict rows in some way. |
Notes about the query: | The query considers possibility that the security_barrier boolean value can be represented with literal "true", "on" or "1". |
Query type: | Problem detection (Each row in the result could represent a flaw in the design) |
Query reliability: | High (Few or no false-positive results) |
Query license: | MIT License |
Data source: | INFORMATION_SCHEMA+system catalog |
SQL query: | Click on query to copy it
SELECT pg_namespace.nspname AS view_schema, pg_class.relname AS view_name FROM pg_catalog.pg_class, pg_catalog.pg_namespace WHERE pg_catalog.pg_class.relnamespace=pg_catalog.pg_namespace.oid AND relkind='v' AND nspname NOT IN (SELECT schema_name FROM information_schema.schemata WHERE schema_name<>'public' AND schema_owner='postgres' AND schema_name IS NOT NULL) AND (pg_catalog.pg_class.reloptions::text!~*'security_barrier=(true|on|1)' OR pg_catalog.pg_class.reloptions IS NULL) AND pg_get_viewdef(pg_class.oid)~*'WHERE[[:space:]]' ORDER BY view_schema, view_name; |
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.2ndquadrant.com/en/blog/how-do-postgresql-security_barrier-views-work/ |