Re: Privileges and inheritance

On Mon, 2009-10-05 at 10:27 -0700, Josh Berkus wrote:
> Simon,
>
> >> We could use a GUC variable to ease the transition, perhaps like
> >> sql_inheritance = no | yes_without_privileges | yes
> >
> > The original way of doing things was quite useful if you wanted some
> > people to be able to see history and others just see recent data. I
> > don't think many people are aware of or take advantage of that, so your
> > proposal does simplify things for many people.
> >
> > Would it not be better to offer this as a table-level option, with
> > default of check-permission-on-parent-only?
>
> No, I don't think so. The original way *wasn't* actually useful if you
> wanted to differentiate between which partitions people could see. Example:
>
> You partition the sales table geographically. You want salespeople to
> only be able to see their own geo, but management to be able to see all:
>
> role staff
> manager inherits staff
> sales_USA inherits staff
> sales_CAN inherits staff
> sales_EUR inherits staff
>
> master table sales grant SELECT: staff
> sales_CAN inherits sales grant SELECT: manager, sales_CAN
> sales_USA inherits sales grant SELECT: manager, sales_USA
> sales_EUR inherits sales grant SELECT: manager, sales_EUR
>
> So, then a USA-role salesperson does "SELECT sum(gross) FROM sales;".
> What happens? A permissions error. *Not* the desired seeing only the
> USA data.

> In order for a user which privs on only some partitions to see the data
> in those partitions, that user needs to query the partitions directly.
> The proposed patch would not change that. The only thing it would
> change is that DBAs would need to be careful to be restrictive about
> permissions granted on the master table.

OK, make the change.

A small addition though, please. This functionality has been available
since 8.1 and changing things could cause existing people's scripts to
fail when they upgrade. If we make this change then we should make sure
that explicitly GRANTing a permission on the child tables does not fail.

--
Simon Riggs www.2ndQuadrant.com