Since 9.2, it's been possible to use the
security_barrier attribute to guard against maliciously-chosen functions and operators accessing data 'hidden' behind filters in views (full info in the postgres docs).
You can see this happening in the test below, but the same effect isn't observed with the set-returning function instead of a view at the end of the test.
Is this just a quirk of this individual test or are set-returning functions always a safe1 way to guard against this sort of leak?
create schema stack; set search_path=stack; create table t(secret) as values (1), (2);
create view v as select * from t where secret^4>1; select * from v; /* ┌────────┐ │ secret │ ├────────┤ │ 2 │ └────────┘ */ create function f(integer) returns integer cost 1 language plpgsql as $$ begin raise notice 'secret is: %',$1; return $1; end; $$; select * from v where f(secret)>0; /* NOTICE: secret is: 1 <-------- SECURITY LEAK NOTICE: secret is: 2 ┌────────┐ │ secret │ ├────────┤ │ 2 │ └────────┘ */
create function fv() returns setof t language sql as $$ select * from t where secret^4>1 $$; select * from fv() where f(secret)>0; /* NOTICE: secret is: 2 <-------- no leak ┌────────┐ │ secret │ ├────────┤ │ 2 │ └────────┘ */
drop schema stack cascade;
1 There are performance reasons why you might not want to go this route even if it is safe