Re: row based security ... was Different views with same

Harald,
Yes, this solution can be a performance nightmare. I know of two ways
to deal with this, neither of them perfect. You should definitely mark
the function as iscachable no matter how you proceed.

1) Make your function return an indexed column, eg:
create view x as
select *
from t_x
where name = name_I_can_see();

This doesn't work too well though if you can see more than one row!
Naturally there are variants of this approach but they all tend to
make optimising the query that much harder than it should really be.

2) Write all your queries as though they were against the original table
"t_x", rather than the view "x".

By this I mean that your application should provide all of the key
columns and specify all of the joins, as though you could see the
full set of data. The i_can_see() function then becomes a final
filter only after all other query and join criteria have been applied
by the optimiser. This kinda sucks too, as your user now has to know
details that you would probably like to be able to abstract away.

Whatever you choose to do, you are going to have to check your
performance very carefully as a badly written ad-hoc query will give you
all sorts of headaches.

FWIW, Oracle's VPD (Virtual Private Database) implementation has its
access checking functions return a text string predicate (eg "name =
'Marc' and x = y") which is added to the query plan at rewrite time.
They seem to believe that this offers the query optimiser more scope for
getting it right, which I suppose is true, but it seems like unnecessary
complexity to me. And I can't see how to do this in PostgreSQL.

On Mon, 2002-01-21 at 13:47, Harald Massa wrote:
> Marc,
>
> thank you very much for this idea.
>
> > create view x as
> > select *
> > from t_x
> > where i_can_see(name);
>
> i had this function "can_see(bid)", and the can_see function checked for the
> name.
> It works.
>
> BUT: this method kills the query-planner. seq_scan is chosen no matter what.
>
[ . . . ]
>
> HArald
>
>
--
Marc marc(at)bloodnok(dot)com