Re: SE-PostgreSQL and row level security/Alternatives

From: Andres Freund <andres(at)anarazel(dot)de>
To: Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, KaiGai Kohei <kaigai(at)ak(dot)jp(dot)nec(dot)com>, Gregory Stark <stark(at)enterprisedb(dot)com>, David Fetter <david(at)fetter(dot)org>, KaiGai Kohei <kaigai(at)kaigai(dot)gr(dot)jp>, bogdan(at)omnidatagrup(dot)ro, pgsql-hackers(at)postgresql(dot)org, Martijn van Oosterhout <kleptog(at)svana(dot)org>
Subject: Re: SE-PostgreSQL and row level security/Alternatives
Date: 2009-02-16 16:26:32
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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)
COST 0.1
LANGUAGE plpgsql
AS $body$
raise notice 'hah: %s', $1::text;
return true;

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?


In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Robert Haas 2009-02-16 16:39:09 Re: SE-PostgreSQL and row level security
Previous Message Greg Stark 2009-02-16 16:21:03 Re: SE-PostgreSQL and row level security