4

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

3

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.

  • 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? – Jack Douglas Dec 9 '14 at 9:29
  • @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 Dec 9 '14 at 9:30
  • 2
    @JackDouglas Yeah; see postgresql.org/message-id/4EB9BB3F.6090504@agliodbs.com 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 Dec 9 '14 at 10:49
  • 2
    @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". – Craig Ringer Dec 9 '14 at 10:54
  • 1
    I read about that on your blog earlier today while researching this question :) – Jack Douglas Dec 9 '14 at 10:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.