Re: security label support, part.2 - Mailing list pgsql-hackers

>Stephen Frost <sfrost@snowman.net> wrote:
> PG doesn't consider child tables to be independent objects when
> they're being accessed through the parent. As such, they don't
> have their own permissions checking.
I've been thinking about this from the perspective of possible
eventual use by the Wisconsin Courts, and want to throw out my
perspective on long-term direction here, without venturing any
opinion on the immediate issue.
It wouldn't be totally insane for the courts to some day use
inheritance to deal with court cases.  All court cases have much in
common and have the same basic structure, but specific case types
need to store some additional information.  If we did that, we would
want different permissions on different case types -- for example,
juvenile cases are not open to the public as many case types are. 
We would also need the ability to revoke public permissions on
specific rows, as judges can seal cases or various pieces of
information on a case (like the address of a stalker victim).
The point being, we would want a structure something like (picking a
few of our case types):
Case\_ ChargeableCase    \_ FelonyCase    \_ MisdemeanorCase    \_ CivilForfeitureCase    \_ JuvenileCase\_
NonchargableCase   \_ CivilCase    \_ SmallClaimsCase    \_ ProbateCase    \_ MentalCommitmentCase
 
Just because most of these case types are a matter of public record
and subject to open records law disclosure requests (which we
largely avoid by putting what we can on the web site), juvenile and
mental commitment cases are confidential; unless you need to handle
something related to such a case to support its progress through the
courts, you're not supposed to see anything beyond such sketchy
information as the existence of the case number, a filing date, and
a caption where names are replaced by initials (e.g., "In the
interest of E.B.") -- and even that information is held back from
the web site because of possible "data mining" attacks.
Many of the features KaiGai has discussed would fit nicely with
court requirements -- and might even be prerequisites for
considering moving security to the database level.  Mandating
identical security for all tables in a hierarchy would be a problem.
We'd want to be able to `grant select on "Case" to public` and then
`revoke select on "JuvenileCase", "MentalCommitmentCase" from
public` and have those cases disappear from selects against the
ancestor levels unless the user has the appropriate permission.  Or
less convenient, but still feasible, would be to grant nothing at
the ancestor levels, and grant what is appropriate at each child
level and have that affect the results of a query against the
ancestor.
Of course, if one was really careful, this could all be done by
adding views with appropriate permissions and blocking access to the
underlying ancestor tables, but that seems like a lot more work and
rather more error prone.
-Kevin