Обсуждение: 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