Postgresql – Is it safe to use set-returning sql functions instead of security-barrier views

postgresqlpostgresql-9.2Securityset-returning-functionsview

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?

testbed:

create schema stack;
set search_path=stack;
create table t(secret) as values (1), (2);

test1:

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 │
└────────┘
*/

test2:

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 │
└────────┘
*/

clean up:

drop schema stack cascade;

1 There are performance reasons why you might not want to go this route even if it is safe

Best Answer

Yes - if you use functions in a language other than SQL, or if you define them as STRICT.

Essentially, you must prevent inlining of the function. If the function isn't inlined, then predicates can't be pushed down through it and it can't be flattened.

Only SQL functions are eligible for inlining, and only if they are not defined as STRICT.

Related Question