Thread: Need help revoking access WHERE state = 'deleted'

Need help revoking access WHERE state = 'deleted'

From
Mark Stosberg
Date:
We are working on a project to start storing some data as "soft deleted"
(WHERE state = 'deleted') instead of hard-deleting it.

To make sure that we never accidentally expose the deleted rows through
the application, I had the idea to use a view and permissions for this
purpose.

I thought I could revoke SELECT access to the "entities" table, but then
grant SELECT access to a view:
 CREATE VIEW entities_not_deleted AS SELECT * FROM entities WHERE state
!= 'deleted';

We could then find/replace in the code to replace references to the
"entities" table with the "entities_not_deleted" table

However, this isn't working, I "permission denied" when trying to use
the view. (as the same user that has had their SELECT access removed to
the underlying table.)

We are not stuck on this design. What's a recommended way to solve this
problem?
  Mark




Re: Need help revoking access WHERE state = 'deleted'

From
Ben Morrow
Date:
Quoth mark@summersault.com (Mark Stosberg):
> 
> We are working on a project to start storing some data as "soft deleted"
> (WHERE state = 'deleted') instead of hard-deleting it.
> 
> To make sure that we never accidentally expose the deleted rows through
> the application, I had the idea to use a view and permissions for this
> purpose.
> 
> I thought I could revoke SELECT access to the "entities" table, but then
> grant SELECT access to a view:
> 
>   CREATE VIEW entities_not_deleted AS SELECT * FROM entities WHERE state
> != 'deleted';
> 
> We could then find/replace in the code to replace references to the
> "entities" table with the "entities_not_deleted" table

(If you wanted to you could instead rename the table, and use rules on
the view to transform DELETE to UPDATE SET state = 'deleted' and copy
across INSERT and UPDATE...)

> However, this isn't working, I "permission denied" when trying to use
> the view. (as the same user that has had their SELECT access removed to
> the underlying table.)

Works for me. Have you made an explicit GRANT on the view? Make sure
you've read section 37.4 'Rules and Privileges' in the documentation,
since it explains the ways in which this sort of information hiding is
not ironclad.

Ben




Re: Need help revoking access WHERE state = 'deleted'

From
Mark Stosberg
Date:
On 02/28/2013 01:02 PM, Ben Morrow wrote:
> Quoth mark@summersault.com (Mark Stosberg):
>>
>> We are working on a project to start storing some data as "soft deleted"
>> (WHERE state = 'deleted') instead of hard-deleting it.
>>
>> To make sure that we never accidentally expose the deleted rows through
>> the application, I had the idea to use a view and permissions for this
>> purpose.
>>
>> I thought I could revoke SELECT access to the "entities" table, but then
>> grant SELECT access to a view:
>>
>>   CREATE VIEW entities_not_deleted AS SELECT * FROM entities WHERE state
>> != 'deleted';
>>
>> We could then find/replace in the code to replace references to the
>> "entities" table with the "entities_not_deleted" table
> 
> (If you wanted to you could instead rename the table, and use rules on
> the view to transform DELETE to UPDATE SET state = 'deleted' and copy
> across INSERT and UPDATE...)
> 
>> However, this isn't working, I "permission denied" when trying to use
>> the view. (as the same user that has had their SELECT access removed to
>> the underlying table.)
> 
> Works for me. Have you made an explicit GRANT on the view? Make sure
> you've read section 37.4 'Rules and Privileges' in the documentation,
> since it explains the ways in which this sort of information hiding is
> not ironclad.

Thanks for the response, Ben.

Here's a "screenshot" of our issue, showing that even an explicit grant
on the view doesn't fix things. This with 9.1.

# Revoke from the underlying table
db=> revoke select on entities from myuser;
REVOKE

# Try selecting through the view
db=> SELECT 1 FROM entities_not_deleted WHERE some_col = 'y';
ERROR:  permission denied for relation entities

# Explicitly grant access to the view.
db=> grant select on entities_not_deleted to myuser;
GRANT

# Try again to use the view. Still fails
db=> SELECT 1 FROM entities_not_deleted WHERE some_col = 'y';
ERROR:  permission denied for relation entities

###

I've also now read 37.4. That was helpful, but didn't lead to a
breakthrough for me.
   Mark





Re: Need help revoking access WHERE state = 'deleted'

From
Tom Lane
Date:
Mark Stosberg <mark@summersault.com> writes:
> # Explicitly grant access to the view.
> db=> grant select on entities_not_deleted to myuser;
> GRANT

> # Try again to use the view. Still fails
> db=> SELECT 1 FROM entities_not_deleted WHERE some_col = 'y';
> ERROR:  permission denied for relation entities

What's failing is that the *owner of the view* needs, and hasn't got,
select access on the entities table.  This is a separate check from
whether the current user has permission to select from the view.
Without such a check, views would be a security hole.
        regards, tom lane



Re: Need help revoking access WHERE state = 'deleted'

From
Mark Stosberg
Date:
On 02/28/2013 02:08 PM, Tom Lane wrote:
> Mark Stosberg <mark@summersault.com> writes:
>> # Explicitly grant access to the view.
>> db=> grant select on entities_not_deleted to myuser;
>> GRANT
> 
>> # Try again to use the view. Still fails
>> db=> SELECT 1 FROM entities_not_deleted WHERE some_col = 'y';
>> ERROR:  permission denied for relation entities
> 
> What's failing is that the *owner of the view* needs, and hasn't got,
> select access on the entities table.  This is a separate check from
> whether the current user has permission to select from the view.
> Without such a check, views would be a security hole.

This was precisely our issue. Thanks, Tom.

I changed the owner of the view, and our approach is working now.
  Mark




Re: Need help revoking access WHERE state = 'deleted'

From
Wayne Cuddy
Date:
On Thu, Feb 28, 2013 at 06:02:05PM +0000, Ben Morrow wrote:
> Quoth mark@summersault.com (Mark Stosberg):
> > 
> > We are working on a project to start storing some data as "soft deleted"
> > (WHERE state = 'deleted') instead of hard-deleting it.
> > 
> > To make sure that we never accidentally expose the deleted rows through
> > the application, I had the idea to use a view and permissions for this
> > purpose.
> > 
> > I thought I could revoke SELECT access to the "entities" table, but then
> > grant SELECT access to a view:
> > 
> >   CREATE VIEW entities_not_deleted AS SELECT * FROM entities WHERE state
> > != 'deleted';
> > 
> > We could then find/replace in the code to replace references to the
> > "entities" table with the "entities_not_deleted" table
> 
> (If you wanted to you could instead rename the table, and use rules on
> the view to transform DELETE to UPDATE SET state = 'deleted' and copy
> across INSERT and UPDATE...)

Ben,

Sorry to barge in but I'm just curious... I understand this part
"transform DELETE to UPDATE SET state = 'deleted'". Can you explain a
little further what you mean by "copy across INSERT and UPDATE..."?

> 
> > However, this isn't working, I "permission denied" when trying to use
> > the view. (as the same user that has had their SELECT access removed to
> > the underlying table.)
> 
> Works for me. Have you made an explicit GRANT on the view? Make sure
> you've read section 37.4 'Rules and Privileges' in the documentation,
> since it explains the ways in which this sort of information hiding is
> not ironclad.
> 
> Ben

Thanks,
Wayne



Re: Need help revoking access WHERE state = 'deleted'

From
Ben Morrow
Date:
Quoth lists-pgsql@useunix.net (Wayne Cuddy):
> On Thu, Feb 28, 2013 at 06:02:05PM +0000, Ben Morrow wrote:
> > 
> > (If you wanted to you could instead rename the table, and use rules on
> > the view to transform DELETE to UPDATE SET state = 'deleted' and copy
> > across INSERT and UPDATE...)
> 
> Sorry to barge in but I'm just curious... I understand this part
> "transform DELETE to UPDATE SET state = 'deleted'". Can you explain a
> little further what you mean by "copy across INSERT and UPDATE..."?

I should first say that AIUI the general recommendation is to avoid
rules (except for views), since they are often difficult to get right.
Certainly I've never tried to use rules in a production system.

That said, what I mean was something along the lines of renaming the
table to (say) entities_table, creating an entities view which filters
state = 'deleted', and then
   create rule entities_delete   as on delete to entities do instead    update entities_table    set state = 'deleted'
where key = OLD.key;
 
   create rule entities_insert   as on insert to entities        where NEW.state != 'deleted'   do instead   insert
intoentities_table    select NEW.*;
 
   create rule entities_update   as on update to entities        where NEW.state != 'deleted'   do instead   update
entities_table  set key     = NEW.key,       state   = NEW.state,       field1  = NEW.field1,       field2  =
NEW.field2  where key = OLD.key;
 

(This assumes that "key" is the PK for entities, and that the state
field is visible in the entities view with values other than 'deleted'.
I don't entirely like the duplication of the view condition in the WHERE
clauses, but I'm not sure it's possible to get rid of it.)

This is taken straight out of the 'Rules on INSERT, UPDATE and DELETE'
section of the documentation; I haven't tested it, so it may not be
quite right, but it should be possible to make something along those
lines work.

Ben