Re: Row-security on updatable s.b. views - Mailing list pgsql-hackers

From Yeb Havinga
Subject Re: Row-security on updatable s.b. views
Date
Msg-id 53178F95.9020505@mgrid.net
Whole thread Raw
In response to Re: Row-security on updatable s.b. views  (Craig Ringer <craig@2ndquadrant.com>)
List pgsql-hackers
On 05/03/14 15:44, Craig Ringer wrote:
> On 03/05/2014 05:25 PM, Yeb Havinga wrote:
>> Maybe a naive thought, but shouldn't all plans that include a table with
>> an RLS clause be invalidated when the session role switches, regardless
>> of which users from and to?
> Only if the plan is actually accessed when under a different user ID.
> Consider SECURITY DEFINER functions; you don't want to flush all cached
> plans just because you ran a SECURITY DEFINER function that doesn't even
> share any statements with the outer transaction.
Hmm good point.

>>> I've also got some concerns about the user visible API; I'm not sure it
>>> makes sense to set row security policy for row reads per-command in
>>> PostgreSQL, since we have the RETURNING clause. Read-side policy should
>>> just be "FOR READ".
>> Hmm but FOR READ would mean new keywords, and SELECT is also a concept
>> known to users. I didn't find the api problematic to understand, on the
>> contrary.
> Would you expect that FOR SELECT also affects rows you can see to
> UPDATE, INSERT, or DELETE?
Yes.
> Because that's what it would have to mean, really. Otherwise, you could
> just use `UPDATE thetable SET id = id RETURNING *` (or whatever) to read
> the rows out if you had UPDATE rights. Or do the same with DELETE.
>
> With RETURNING, it doesn't make much sense for different statements to
> have different read access. Can you think of a case where it'd be
> reasonable to deny SELECT, but allow someone to see the same rows with
> `UPDATE ... RETURNING` ?
>
>> It might be an idea to add the SELECT RLS clause for DML
>> queries that contain a RETURNING clause.
> That way lies madness: A DML statement that affects *a different set of
> rows* depending on whether or not it has a RETURNING clause.
If you state it like that, it sounds like a POLA violation. But the 
complete story is: "A user is allowed to UPDATE a set of rows from a 
table that is not a subsect of the set of rows he can SELECT from the 
table, iow he can UPDATE rows he is not allowed to SELECT. This can lead 
to unexpected results: When the user issues an UPDATE of the table 
without a returning clause, all rows the user may UPDATE are affected. 
When the user issues an UPDATE of the table with a returning clause, all 
rows the user may UPDATE and SELECT are affected."

So the madness comes from the fact that it is allowed to define RLS that 
allow to modify rows you cannot select. Either prevent these conditions 
(i.e. proof that all DML RLS qual implies the SELECT qual, otherwise 
give an error on DML with a RETURNING clause), or allow it without 
violating the RLS rules but accept that a DML with RETURNING is 
different from a DML only.

regards,
Yeb




pgsql-hackers by date:

Previous
From: Bruce Momjian
Date:
Subject: Re: tcp_keepalives_idle
Next
From: Tom Lane
Date:
Subject: Re: [PATCH] Negative Transition Aggregate Functions (WIP)