To: pgsql-sql@postgresql.org
From: Mark Stosberg <mark@summersault.com>
Subject: Need help revoking access  WHERE state = 'deleted'
Date: Thu, 28 Feb 2013 11:37:39 -0500
Lines: 25
Message-ID: <kgo14h$vm3$1@ger.gmane.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:17.0) Gecko/20130106 Thunderbird/17.0.2
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org


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


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql