PostgreSQL 11 Administration Cookbook

Securing views

It is a common technique to use a view to disclose only some parts of a secret table; however, a clever attacker can use access to the view to display the rest of the table using log messages. For instance, consider the following example:

CREATE VIEW for_the_public AS   SELECT * FROM reserved_data WHERE importance < 10; GRANT SELECT ON for_the_public TO PUBLIC;

A malicious user could define the following function:

CREATE FUNCTION f(text) RETURNS boolean COST 0.00000001 LANGUAGE plpgsql AS $$ BEGIN   RAISE INFO '$1: %', $1;   RETURN true; END; $$;

They could use it to filter rows from the view:

SELECT * FROM for_the_public x WHERE f(x :: text);

The PostgreSQL optimizer will then internally rearrange the query, expanding the definition ...