Thread: Need help revoking access WHERE state = 'deleted'
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
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
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
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
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
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
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