Re: Using views for row-level access control is leaky

Richard Huxton wrote:
> 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

Hmm - just using SQL (but with an expensive view filtering function):

SELECT * FROM phone_number WHERE (CASE WHEN phone = '67890' THEN
person::int ELSE 2 END)=2;
ERROR: invalid input syntax for integer: "secret person"

You could get a related problem where a view exposes a text column full
of valid dates which the user then tries to cast to date. If the
underlying table contains non-dates you could still get an error.
Arguably the view should have handled the cast in this case though.

--
Richard Huxton
Archonet Ltd