Andres Freund wrote:
> I don't think it is that simple. Allowing inserts without regard for row
> level restrictions makes it far easier to probe for data. E.g. by
> inserting rows and checking for unique violations.
Unless you want to go to a military-style security level system
where people at each security level have a separate version of the
same data, primary keys (and I think other unique constraints) can
leak. It seems clear enough that sensitive data should not be used
for such constraints.
That doesn't even require completely meaningless numeric keys.
Court cases in Wisconsin have been numbered within county by year,
case type, a sequential portion, and an optional apha suffix since
before things were computerized -- you may know that there is a
2010 case in Dane County for mental commitment number 45, but that
doesn't leak any sensitive data.
In the sealed address example, if that were moved to the database
layer, someone might be able to test whether addess number 5
existed on a case by seeing whether an insert succeeded; but if it
did, there would be a record of that insert with their user ID that
they could not retract in any way -- they would know very little,
and be exposed as having done something inappropriate.
-Kevin