Обсуждение: grant with hierarchy option

There is a gap in the permission scheme for inheritance setups.  Say you
have this:

CREATE TABLE persons (...);
CREATE TABLE employees (...) INHERITS (persons);

GRANT SELECT ON persons TO foo;

Then user foo can extract who the employees are using

SELECT * FROM persons EXCEPT SELECT * FROM ONLY persons;

With UPDATE permissions one could then even do moderately evil things
like only changing the employees.

Proper encapsulation would require that using ONLY is only permitted
when specifically allowed.  That is what GRANT SELECT WITH HIERARCHY
OPTION does, per SQL standard.  It is basically a funny spelling for
another privilege type that means "may use ONLY".

I think this would be the proper and useful thing to do, especially in
conjunction with the new recursive grant behavior.  There would probably
be some upgrading issues.  For example, GRANTs imported via pg_dump from
8.4 would probably need to change SELECT to SELECT WITH HIERARCHY
OPTION, and even that technically wouldn't cover all cases.

Comments?

Peter Eisentraut <peter_e@gmx.net> writes:
> There is a gap in the permission scheme for inheritance setups.  Say you
> have this:

> CREATE TABLE persons (...);
> CREATE TABLE employees (...) INHERITS (persons);

> GRANT SELECT ON persons TO foo;

> Then user foo can extract who the employees are using

> SELECT * FROM persons EXCEPT SELECT * FROM ONLY persons;

And this is a problem why exactly?  It's entirely likely that
employee-ness can be determined just from what is visible in
the persons view, anyway.  Not to mention tableoid.

> I think this would be the proper and useful thing to do, especially in
> conjunction with the new recursive grant behavior.  There would probably
> be some upgrading issues.  For example, GRANTs imported via pg_dump from
> 8.4 would probably need to change SELECT to SELECT WITH HIERARCHY
> OPTION, and even that technically wouldn't cover all cases.

That sounds like "this will break everything in sight, especially
pre-existing dump files" :-(
        regards, tom lane

On fre, 2009-10-30 at 00:49 -0400, Tom Lane wrote:
> Peter Eisentraut <peter_e@gmx.net> writes:
> > There is a gap in the permission scheme for inheritance setups.  Say you
> > have this:
> 
> > CREATE TABLE persons (...);
> > CREATE TABLE employees (...) INHERITS (persons);
> 
> > GRANT SELECT ON persons TO foo;
> 
> > Then user foo can extract who the employees are using
> 
> > SELECT * FROM persons EXCEPT SELECT * FROM ONLY persons;
> 
> And this is a problem why exactly?  It's entirely likely that
> employee-ness can be determined just from what is visible in
> the persons view, anyway.  Not to mention tableoid.

Yeah, tableoid is a deal-breaker.  But perhaps using ONLY should at
least require SELECT privilege, because it effectively allows you to
select a subset of the table's rows.

Peter Eisentraut <peter_e@gmx.net> writes:
> On fre, 2009-10-30 at 00:49 -0400, Tom Lane wrote:
>> And this is a problem why exactly?  It's entirely likely that
>> employee-ness can be determined just from what is visible in
>> the persons view, anyway.  Not to mention tableoid.

> Yeah, tableoid is a deal-breaker.  But perhaps using ONLY should at
> least require SELECT privilege, because it effectively allows you to
> select a subset of the table's rows.

By that argument, WHERE clauses are a security hazard.  It's still
not apparent to me why it would be essential, or even a good idea,
to prevent people from figuring out which rows belong to which
subtable.

Or do you mean that ONLY should be treated as requiring column
select privilege on TABLEOID?  Perhaps that's sensible.
        regards, tom lane