To: pgsql-sql@postgresql.org
From: Mark Stosberg <mark@summersault.com>
Subject: Re: Need help revoking access  WHERE state = 'deleted'
Date: Thu, 28 Feb 2013 13:35:15 -0500
Lines: 60
Message-ID: <kgo811$9al$1@ger.gmane.org>
References: <kgo14h$vm3$1@ger.gmane.org>
	<20130228180201.GA10412@anubis.morrow.me.uk>
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
In-Reply-To: <20130228180201.GA10412@anubis.morrow.me.uk>
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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


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