Re: API change advice: Passing plan invalidation info from the rewriter into the planner? - Mailing list pgsql-hackers

Dean,

* Dean Rasheed (dean.a.rasheed@gmail.com) wrote:
> On 17 June 2014 20:19, Robert Haas <robertmhaas@gmail.com> wrote:
> > On Fri, Jun 13, 2014 at 3:11 AM, Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
> >> Yeah, I was thinking something like this could work, but I would go
> >> further. Suppose you had separate GRANTable privileges for direct
> >> access to individual tables, bypassing RLS, e.g.
> >>
> >>   GRANT DIRECT SELECT|INSERT|UPDATE|DELETE ON table_name TO role_name
> >
> > So, is this one new privilege (DIRECT) or four separate new privileges
> > that are variants of the existing privileges (DIRECT SELECT, DIRECT
> > INSERT, DIRECT UPDATE, DIRECT DELETE)?
>
> I was thinking it would be 4 new privileges, so that a user could for
> example be granted DIRECT SELECT permission on a table, but not DIRECT
> UPDATE.

Ok.

> On reflection though, I think I prefer the approach of allowing
> multiple named security policies per table, because it gives the
> planner more opportunity to optimize queries against specific RLS
> quals, which won't work if the ACL logic is embedded in functions.

Having more than one policy for the purpose of performance really
doesn't make a huge amount of sense to me.  Perhaps someone could
explain the use-case with specific example applications where they would
benefit from this?  Based on the discussion, they would have to be OR'd
together in the query as built with any result being marked as success.
One could build an SQL function which could be in-lined potentially
which does the same if their case is that simple.

Being able to define the policy based on some criteria may allow it to
be simpler (eg: policy 'a' applies for certain roles, while policy 'b'
applies for other roles), but I'm not enthusiastic about that approach
because there could be a huge number of permutations to allow.

How about another approach- what about having a function which is called
(as the table owner, I'm thinking..) that then returns the qual to be
included, instead of having to define a specific qual which is included
in the catalog?  That function could take into consideration the user,
table, etc, and return a qual which includes constants to compare rows
against for planning purposes.  This would have to be done early enough,
of course, which might be difficult.  For my part, having that
capability would be neat, but nothing we're trying to do here would
preclude us from adding it later either.

> That seems like something that would have to be designed in now,
> because it's difficult to see how you could add it later.

I don't follow this at all.  Going from supporting one qual to
supporting multiple seems like it'd be quite straight-forward to add
in later?  Going the other way would be difficult.

> Managing policy names becomes an issue though, because if you have 2
> tables each with 1 policy, but you give them different names, how can
> the user querying the data specify that they want policy1 for table1
> and policy2 for table2, possibly in the same query?

From my experience, users don't pick the policy any more than they get
to pick which set of permissions get applied to them when querying
tables (modulo roles, of course, but that's a mechanism for changing
users, not for saying which set of permissions you get).  All that you
describe could be done for regular permissions also, but we don't, and
I don't think we get complaints about that because we have roles-
which would work just the same for RLS (assuming the RLS policy defined
has a role component).

Having a function be able to be called to return a qual to be used would
be a way to have per-role RLS also, along with providing the flexibility
to have per-source-IP, per-connection-type, etc, RLS policies also.
Thanks,
    Stephen