pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedNeed help revoking access WHERE state = 'deleted'
7+ messages / 4 participants
[nested] [flat]
* Need help revoking access WHERE state = 'deleted'
@ 2013-02-28 16:37 Mark Stosberg <mark@summersault.com>
0 siblings, 1 reply; 7+ messages in thread
From: Mark Stosberg @ 2013-02-28 16:37 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Need help revoking access WHERE state = 'deleted'
@ 2013-02-28 18:02 Ben Morrow <ben@morrow.me.uk>
parent: Mark Stosberg <mark@summersault.com>
0 siblings, 3 replies; 7+ messages in thread
From: Ben Morrow @ 2013-02-28 18:02 UTC (permalink / raw)
To: mark@summersault.com; pgsql-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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Need help revoking access WHERE state = 'deleted'
@ 2013-02-28 18:35 Mark Stosberg <mark@summersault.com>
parent: Ben Morrow <ben@morrow.me.uk>
2 siblings, 1 reply; 7+ messages in thread
From: Mark Stosberg @ 2013-02-28 18:35 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Need help revoking access WHERE state = 'deleted'
@ 2013-02-28 19:08 Tom Lane <tgl@sss.pgh.pa.us>
parent: Mark Stosberg <mark@summersault.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2013-02-28 19:08 UTC (permalink / raw)
To: Mark Stosberg <mark@summersault.com>; +Cc: pgsql-sql
Mark Stosberg <mark@summersault.com> writes:
> # 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
What's failing is that the *owner of the view* needs, and hasn't got,
select access on the entities table. This is a separate check from
whether the current user has permission to select from the view.
Without such a check, views would be a security hole.
regards, tom lane
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Need help revoking access WHERE state = 'deleted'
@ 2013-02-28 19:29 Mark Stosberg <mark@summersault.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Mark Stosberg @ 2013-02-28 19:29 UTC (permalink / raw)
To: pgsql-sql
On 02/28/2013 02:08 PM, Tom Lane wrote:
> Mark Stosberg <mark@summersault.com> writes:
>> # 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
>
> What's failing is that the *owner of the view* needs, and hasn't got,
> select access on the entities table. This is a separate check from
> whether the current user has permission to select from the view.
> Without such a check, views would be a security hole.
This was precisely our issue. Thanks, Tom.
I changed the owner of the view, and our approach is working now.
Mark
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Need help revoking access WHERE state = 'deleted'
@ 2013-03-02 18:20 Wayne Cuddy <lists-pgsql@useunix.net>
parent: Ben Morrow <ben@morrow.me.uk>
2 siblings, 0 replies; 7+ messages in thread
From: Wayne Cuddy @ 2013-03-02 18:20 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Need help revoking access WHERE state = 'deleted'
@ 2013-03-02 23:45 Ben Morrow <ben@morrow.me.uk>
parent: Ben Morrow <ben@morrow.me.uk>
2 siblings, 0 replies; 7+ messages in thread
From: Ben Morrow @ 2013-03-02 23:45 UTC (permalink / raw)
To: lists-pgsql@useunix.net; pgsql-sql
Quoth lists-pgsql@useunix.net (Wayne Cuddy):
> On Thu, Feb 28, 2013 at 06:02:05PM +0000, Ben Morrow wrote:
> >
> > (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...)
>
> 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..."?
I should first say that AIUI the general recommendation is to avoid
rules (except for views), since they are often difficult to get right.
Certainly I've never tried to use rules in a production system.
That said, what I mean was something along the lines of renaming the
table to (say) entities_table, creating an entities view which filters
state = 'deleted', and then
create rule entities_delete
as on delete to entities do instead
update entities_table
set state = 'deleted'
where key = OLD.key;
create rule entities_insert
as on insert to entities
where NEW.state != 'deleted'
do instead
insert into entities_table
select NEW.*;
create rule entities_update
as on update to entities
where NEW.state != 'deleted'
do instead
update entities_table
set key = NEW.key,
state = NEW.state,
field1 = NEW.field1,
field2 = NEW.field2
where key = OLD.key;
(This assumes that "key" is the PK for entities, and that the state
field is visible in the entities view with values other than 'deleted'.
I don't entirely like the duplication of the view condition in the WHERE
clauses, but I'm not sure it's possible to get rid of it.)
This is taken straight out of the 'Rules on INSERT, UPDATE and DELETE'
section of the documentation; I haven't tested it, so it may not be
quite right, but it should be possible to make something along those
lines work.
Ben
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2013-03-02 23:45 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-02-28 16:37 Need help revoking access WHERE state = 'deleted' Mark Stosberg <mark@summersault.com>
2013-02-28 18:02 ` Ben Morrow <ben@morrow.me.uk>
2013-02-28 18:35 ` Mark Stosberg <mark@summersault.com>
2013-02-28 19:08 ` Re: Need help revoking access WHERE state = 'deleted' Tom Lane <tgl@sss.pgh.pa.us>
2013-02-28 19:29 ` Re: Need help revoking access WHERE state = 'deleted' Mark Stosberg <mark@summersault.com>
2013-03-02 18:20 ` Wayne Cuddy <lists-pgsql@useunix.net>
2013-03-02 23:45 ` Ben Morrow <ben@morrow.me.uk>
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