Re: Updates of SE-PostgreSQL 8.4devel patches - Mailing list pgsql-hackers

>> I like the idea of a WITH ROW SECURITY option to enable row-level
>> security - that way, tables that don't need it don't have to pay for
>> it, but I like the idea of storing a full ACL, as KaiGai proposed,
>> rather than just a single role.  Seems much more powerful.
> ... and even more ill-defined.

I agree.  We need a better spec for this, starting with a list of
privileges that even make sense.  I'm not sure whether it makes sense
to allow anything more than SELECT, UPDATE, an DELETE.  INSERT and
TRUNCATE are clearly meaningless.  TRIGGER and REFERENCES are
arguable, but I can't imagine a reasonable use case for granting
TRIGGER or REFERENCES on only a subset of the table, so I'm not sure
there's any point in enforcing those on a per-row basis.  For an FK,
it might make more sense to simply require REFERENCES permission on
the table and SELECT permission on the referenced tuple (more
generally if I'm going to take a shared lock on a tuple I should need
to have SELECT permission on it).

[snip]

> Now what?  Alice's FK constraint is violated, according to the rules
> KaiGai proposes.  Shall REVOKE have to grovel through every table in the
> database looking for possible violations ... and of course locking the
> entire DB against writes while it does it?  That's not gonna fly.  I
> also note that the failure would expose knowledge of the contents of BT
> and AT to charlie, which might not be thought desirable either.

I think you have to resign yourself to the fact that a user who can
see only a subset of the rows in a table may very well see apparent
foreign-key violations.  But so what?  If the user cares to arrange
things so that this doesn't happen, it is certainly possible.  If they
don't, I'm not sure why it's our job to argue with them.  Alice's
tuples will still be in the table, and she will see them or not
according to her permissions, and will see the foreign keys they
reference or not according to those permissions.

It's already the case that I can already define a check constraint on
a table that calls a function, insert a bunch of rows into the table,
and then redefine the function to return false for some row that's
already in the table, and voila, my constraint check is violated.  It
sucks, but there's a pretty severe performance problem with rechecking
the constraint every time the function is redefined, so we don't.
This can't be any worse, and in fact I don't even think it's as bad.

Random though regarding information leakage: You might be able to find
out something about the hidden portion of a table by using EXPLAIN to
examine the plan for various queries.  The stats collector, of course,
will have seen the entire table contents.  This might not be a big
deal for basic row-level security (depending on the situation) but it
is more likely to matter for SE-PostgreSQL.

> This problem is bad enough if row visibility is defined just by role
> membership.  I shudder to think of trying to make it a general ACL.

If all you store is a role OID, you have no ability to grant
read/write access to only part of a table.  That has to be a rather
important use case.

...Robert