From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> |
Cc: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>, Marc Munro <marc(at)bloodnok(dot)com> |
Subject: | Re: Using views for row-level access control is leaky |
Date: | 2009-10-22 10:51:28 |
Message-ID: | 4AE03930.8010102@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Heikki Linnakangas wrote:
> CREATE VIEW phone_number AS
> SELECT person, phone FROM phone_data WHERE phone NOT LIKE '6%';
> CREATE OR REPLACE FUNCTION expose_person (person text, phone text)
> RETURNS bool AS $$
> begin
> RAISE NOTICE 'person: % number: %', person, phone;
> RETURN true;
> END; $$ LANGUAGE plpgsql COST 0.000001;
>
> postgres=> SELECT * FROM phone_number WHERE expose_person(person, phone);
> NOTICE: person: public person number: 12345
> NOTICE: person: secret person number: 67890
> person | phone
> ---------------+-------
> public person | 12345
Ouch!
> 1. Change the planner so that conditions (and join!) in the view are
> always enforced first, before executing any quals from the user-supplied
> query. Unfortunately that would have a catastrophic effect on performance.
I have the horrible feeling that you're going to end up doing this
(possibly in conjunction with #4). Once you've executed a user-defined
function on a "hidden" row I think the game is lost. That might even
apply to non-trivial expressions too.
> 2. As an optimization, we could keep the current behavior if the user
> has access to all the underlying tables anyway, but that's nontrivial
> because permission checks are supposed to be executed at runtime, not
> plan time.
>
> 3. Label every function as safe or unsafe, depending on whether it can
> leak information about the arguments. Classifying functions correctly
> can be a bit tricky; e.g functions that throw an error on some input
> values could be exploited.
[snip]
I'm sure there's a way to generate an error on-demand for rows with
specific numbers. That opens you up to fishing for hidden rows.
It might be possible to label a subset of operators etc as safe. I'd
guess that would exclude any casts in it, and perhaps CASE. Hmm - you
could probably generate a divide-by-zero or overflow error or some such
for any targetted numeric value though.
> 4. Make the behavior user-controllable, something along the lines of
> "CREATE RESTRICTED VIEW ...", to avoid the performance impact when views
> are not used for access control.
Not pretty, but solves the problem.
--
Richard Huxton
Archonet Ltd
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2009-10-22 11:01:30 | Re: Using views for row-level access control is leaky |
Previous Message | Pavel Stehule | 2009-10-22 10:36:14 | Re: Controlling changes in plpgsql variable resolution |