Date: Sat, 2 Mar 2013 13:20:05 -0500
From: Wayne Cuddy <lists-pgsql@useunix.net>
To: pgsql-sql@postgresql.org
Subject: Re: Need help revoking access  WHERE state = 'deleted'
Message-ID: <20130302182005.GG18317@slacker.ja10629.home>
Mail-Followup-To: pgsql-sql@postgresql.org
References: <kgo14h$vm3$1@ger.gmane.org>
	<20130228180201.GA10412@anubis.morrow.me.uk>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20130228180201.GA10412@anubis.morrow.me.uk>
User-Agent: Mutt/1.4.2.3i
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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


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