Re: Privileges and inheritance

Peter Eisentraut wrote:
> I would like to propose a change in the way privilege checking is done
> with inheritance hierarchies.  Currently, selecting from a table that
> has children requires explicit privileges on all the children.  This is
> inconsistent with all other commands, which treat children as implicitly
> part of the parent table.  (Arguably, it exposes an implementation
> detail, since you could just as well implement inheritance by keeping
> all the children's data for the inherited columns in the parent's heap.)
> 
> As inheritance has now found new popularity as a partitioning mechanism,
> this exacerbates the annoyance because you have to copy the privilege
> sets to possibly dozens or hundreds of subtables in cumbersome ways for
> really no good reason.

I think it is a matter of perspectives.
(So, we will not have a perfectly correct answer.)

If we consider that inherited columns are a part of parent table,
it is odd to apply checks on both of parent and child tables when
we select data from a table with its children, as you mentioned.

On the other hand, it also needs to check permission both of child
table and its parents when we select data from a table with its
parents, because the selected columns are inherited from other tables.

The current implementation handles the parent/children as an individual
relations. It does not consider the inherited columns are a part of
parent tables.
For example, ALTER TABLE ADD COLUMN statement checks ownership on
the target table and all the children to add a new column. It is
equivalent to the iteration of ALTER TABLE on the children.

> If you use inheritance for data modeling (the original purpose), you
> face another problem.  Either you grant table privileges on all the
> child tables, thus giving users access to more information than they
> were supposed to have, or you grant column privileges on only those
> columns that were inherited, being careful to keep that set updated
> whenever table definitions are altered.  (Before 8.4 you couldn't even
> do that.)  It's messy.

I think we should check permission on the parent tables/columns when
a user tries to select inherited columns on the child table.
It stands on the perspective that the inherited columns are owned by
the parent (source) table.

The matter is a case when we cannot identify where is the source of
the inherited column if the child table has multiple parents.
An idea is to check permissions on all the parents in this case.

> We could use a GUC variable to ease the transition, perhaps like
> sql_inheritance = no | yes_without_privileges | yes

I think the GUC should toggle which table owns the inherited columns.

If DBA considers the inherited columns are a part of the parent table,
individual checks can be bypassed. Otherwise, we can keep the compatible
bahavior.

Thanks,
-- 
OSS Platform Development Division, NEC
KaiGai Kohei <kaigai@ak.jp.nec.com>