Re: Privileges and inheritance

Was it uncertain what I wanted to say for the proposition?

In my understanding, here are two different perspectives to handle
table inheritances.

The one handles the inherited definitions of child tables as a part
of the child table. The current implementation uses this perspective.
Thus, it checks user's privilege on all the children when he tries to
select data from the parent table. In constrast, it does not need to
check permission on the parent tables, when he tries to select data
from the child table, because the child table does not contain any
part of the parent table.

The other is newly proposed one. It handles the inherited definitions
of child tables as a part of parent table. Thus, it does not need to
check user's privilege on the children when he tries to select data
from the parent table, because this query can select only columns
inherited from the parent table and we can consider these are a part
of the parent table in this perspective.
In constrast, if we consider inherited columns are a part of the parent
tables, it should check permission on the parent relation and columns
when he tries to select data from the child table.

If a table tbl_c(c int) inherits a table tbl_p(a int, b int), ...

The first perspective considers as follows:

+--- physical location of the data
|
--V---+---------------+
tbl_p | data stored |
| within tbl_p |
------+---------------+-------+
tbl_c | data stored |
| within tbl_c |
------+-------+-------+-------|
| a | b | c |

In this perspective, "SELECT * FROM tbl_p" accesses data within both of
tbl_p and tbl_c, so it needs to check privileges to tbl_p and tbl_c.
But "SELECT * FROM tbl_c" does not see any data within tbl_p.

The later perspective considers as follows:

+--- physical location of the data
|
--V---+---------------+
tbl_p | | +----- data stored within tbl_c
| | |
------+ data stored +---|---+
tbl_c | within tbl_p | V |
| | |
------+-------+-------+-------+
| a | b | c |

In this perspective, "SELECT * FROM tbl_p" access data within only tbl_p,
so it does not needs to check privilege on the tbl_c.
But, "SELECT * FROM tbl_c" also means accesses data within both of tbl_p
and tbl_c concurrently in this perspective, so I think it is quite natural
to check privileges to both of tbl_p and tbl_c.

In my understanding, your proposition enables DBA to choose which perspective
can be applied on his system. It seems to me quite cool.
However, the behavior when we select from a child table seems to me inconsistent.
If it does not check privileges on the parent table when selecting from the child,
it means we can adopt different perspectives concurrently.

Am I missing something?
I'm still unclear which perspective does it tries to provide.

Thanks,

BTW, I uses the term of "perspective" to mean "point of view" or "philosophy".
Is it confusable? Is there any more appropriate terms?

KaiGai Kohei wrote:
> Peter Eisentraut wrote:
>> On Mon, 2009-10-05 at 16:27 +0900, KaiGai Kohei wrote:
>>> CREATE TABLE tbl_p (int a, int b);
>>> CREATE TABLE tbl_c (int x) INHERITS(tbl_p);
>>>
>>> SELECT a,b FROM tbl_p; --> It selects data from only tbl_p.
>>> It is reasonable to bypass checks on tbl_c.
>>> SELECT b,x FROM tbl_c; --> It selects data from tbl_p and tbl_c concurrently,
>>> if we consider the inherited columns are a part of
>>> the parent table.
>> I think you need to distinguish between the definition of columns and
>> the data in the columns. tbl_c has inherited the definition of the
>> columns from tbl_p, but the data is part of tbl_c, not tbl_p. So there
>> is not reason for this second query to ask tbl_p for permission, because
>> it does not touch data in tbl_p at all.
>
> Yes, I can understand the second query selects data stored within only
> tbl_c in this case, not tbl_p, even if tbl_c inherits its definitions
> from the parent.
> However, this perspective may be inconsistent to the idea to bypass
> permission checks on the child (tbl_c) when we select data from the
> parent (tbl_p), because the first query also fetches data stored
> within the tbl_c, not only the tbl_p.
>
> IMO, if we adopt the perspective which considers the access control
> depends on the physical location, the current implementation works fine.
> However, this idea proposed a different perspective.
> It allows to bypass permission checks on the child tables, because
> the child has identical definition with its parent and these are a part
> of the parent table.
> If so, I think this perspective should be ensured without any exception.
>
> BTW, I basically think this perspective change is better.
>
> Thanks,

--
OSS Platform Development Division, NEC
KaiGai Kohei <kaigai(at)ak(dot)jp(dot)nec(dot)com>