postgres views updatable views - ghdrako/doc_snipets GitHub Wiki

https://www.postgresql.org/docs/current/sql-createview.html

Rename view

alter view v1rename to v2;

The information_schema.views data dictionary view contains the column is_updatable.

A view is updatable when it meets the following conditions:

There should be only one entry in the FROM clause of the defining query of the view, which can be a table or another updatable view.
The query defining the view must not include GROUP BY, 'HAVING', LIMIT, OFFSET, DISTINCT, WITH, UNION, INTERSECT, or EXCEPT statements.
Third, the selection list of the defining query must not contain any:
- [Window functions]
- [Set-returning function]
- [Aggregate functions]

An updatable view may contain both updatable and non-updatable columns. If you attempt to modify a non-updatable column, PostgreSQL will raise an error.

WITH CHECK OPTION clause

WITH CHECK option is prohibiting the user not to [INSERT] or [UPDATE] any data which is not relevant to the view created. You can only perform insert, [update], and [delete] on table that satisfies the WHERE clause in the defining query of the view.

WITH LOCAL CHECK OPTION

WITH LOCAL CHECK OPTION, PostgreSQL checks only the first view when we modify the data in the table.

WITH CASCADED CHECK OPTION

PostgreSQL checks all nested view when we modify the data in the table.

SECURITY BARIER (9.1)

Simple view are not safe to restrict access to data. Always use View with SECURITY BARRIER

create table emp (name text, jobtitle text, department text);
insert into emp values ('Robert Haas', 'Senior Database Architect', 'EnterpriseDB'), 
                       ('KaiGai Kohei', 'SELinux Guru', 'NEC'), 
                       ('Tom Lane', 'Ninja', 'CIA');
create view unclassified_emp as select * from emp where organization <> 'CIA';
create user bob;
grant select on unclassified_emp to bob;
\c - bob    
select * from emp;
ERROR:  permission denied for relation emp
select * from unclassified_emp;
     name     |         jobtitle          | organization
--------------+---------------------------+--------------
 Robert Haas  | Senior Database Architect | EnterpriseDB
 KaiGai Kohei | SELinux Guru              | NEC
(2 rows)

# problem
create or replace function pg_temp.leak(text) 
returns bool 
as 
$$begin
 raise notice '%', $1; 
return true; 
end$$ 
language plpgsql cost 0.0000000000000001; 

select * from unclassified_emp e where pg_temp.leak(name);
NOTICE:  Robert Haas
NOTICE:  KaiGai Kohei
NOTICE:  Tom Lane  <------------------------------------revealed 
     name     |         jobtitle          | organization
--------------+---------------------------+--------------
 Robert Haas  | Senior Database Architect | EnterpriseDB
 KaiGai Kohei | SELinux Guru              | NEC
(2 rows)

security_barrier views fix that by forcing the qualifiers on the view to be executed first, before any user-supplied qualifiers run. Instead of expanding the view and appending any view qualifiers to the outer query, they replace the reference to the view with a subquery. This subquery has the security_barrier flag set on its range-table entry, which tells the optimizer it shouldn’t flatten the subquery or push outer query conditions down into it like it would for a normal subquery.

create or replace view unclassified_emp with (security_barrier) as select * from emp where organization <> 'CIA';

Simple views in PostgreSQL 9.3 are automatically updatable, but security_barrier views aren’t considered “simple”. That’s because updating views relies on being able to flatten the view subquery away, turning the update into a simple update on a table. The whole point of security_barrier views is to prevent that flattening. UPDATE cannot currently operate on a subquery directly, so PostgreSQL will reject any attempt to update a security_barrier view: