INSERT does not respect WHERE clause from GRANT

[wundrian]

So if you say

GRANT INSERT ON t TO mbickel WHERE id IN (1, 2, 3);

Then connect as mbickel and do:

INSERT INTO t (id) VALUES (4);

Allowing you to probe if t already has a value 4. This effectivly gives SELECT privileges even if not granted by the sysadmin.

The only known way around this is to shadow values, ie allow the INSERT to succeed and keep both the existing tuple and the newly inserted one each tagged with a label that says who should see this value.

A workaround is rate-limiting operations, so an attacker can't enumerate the whole universe of keys to find all inserted values. But this won't stop attackers that seek only a specific information - for example "has Bob Brown been employed within my company (even if not in the department I'm doing the data management for)?"

[wundrian]

The same issue comes up with UPDATE privileges. A user with row-constrained update privileges can probe for existing values (that are marked unique) by issuing an update query for the rows he has access to, effectively circumventing the SELECT restriction. Once again, only shadowing the existing values would fully mitigate this problem.

To clarify, here's code:

GRANT SELECT, UPDATE ON t TO mbickel WHERE id = 1;
INSERT INTO t VALUES (1);
INSERT INTO t VALUES (2);

Then connect as mbickel and do:

UPDATE t SET id = 2 WHERE id = 1;
UPDATE t SET id = 3 WHERE id = 1;

etc. If id is declared as unique, the update will fail on the first statement but succeed on the second.