On Thu, Feb 28, 2013 at 06:02:05PM +0000, 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...)
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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
