On 02/28/2013 01:02 PM, Ben Morrow wrote: > Quoth m...@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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql