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

Question

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 safe¹ 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;

---

_{¹ There are performance reasons why you might not want to go this route even if it is safe}

Answer 1

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.

• Jack Douglas: Thanks Craig - fv() isn't strict here and is language SQL, so presumably there is some other reason why it isn't being inlined in this particular case, right?
• Craig Ringer: @JackDouglas Volatility, probably. There's also never any guarantee a function will get inlined, but just because it isn't this time doesn't mean it won't be later, possibly without being caused by specific and obvious changes to the DB or code.
• Craig Ringer: @JackDouglas Yeah; see postgresql.org/message-id/[email protected] for example. I think it might be mentioned in postgresql.org/docs/current/static/xfunc-sql.html but haven't re-read in detail to find the reference, just vaguely recall.
• Craig Ringer: @JackDouglas It's not inherently guaranteed that STRICT blocks inlining, but there's also no sign of that changing any time soon. Arguably we should permit the security_barrier attribute to apply to SQL functions, disabling inlining. There's a really ugly hack you can use that's guaranteed to prevent predicate pushdown, btw: the OFFSET 0 hack. Otherwise known as "PostgreSQL doesn't have hints, it has awful misfeatures that've been grandfathered into not-hints".
• Jack Douglas: I read about that on your blog earlier today while researching this question :)