From: Ben Morrow <ben@morrow.me.uk>
To: mark@summersault.com
To: pgsql-sql@postgresql.org
Subject: Re: Need help revoking access WHERE state = 'deleted'
Date: Thu, 28 Feb 2013 18:02:05 +0000
Message-ID: <20130228180201.GA10412@anubis.morrow.me.uk> (raw)
In-Reply-To: <kgo14h$vm3$1@ger.gmane.org>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
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.
Ben
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ben@morrow.me.uk, mark@summersault.com
Subject: Re: Need help revoking access WHERE state = 'deleted'
In-Reply-To: <20130228180201.GA10412@anubis.morrow.me.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox