Re: RLS Design the rewriter into the planner?

Robert, all,

Changing the thread topic to match the other one, and adding Dean in
explicitly since we're talking about the design discussed with him.

* Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
> I think role is good enough. That's the primary identifier for all
> access-control related decisions, so it should be good enough here,
> too.

Alright. That works for me.

> I don't really understand your concerns about overlapping policies
> being complex. If you've got a couple of WHERE clauses, combining
> them with OR is not hard. Now, the query optimizer may have trouble
> with it, but on the whole I expect to win more than we lose, by
> entirely excluding some branches of an OR for users for whom entirely
> policies can be excluded.

On the thread with Dean we're proposing some specific catalog designs
and part of that included (fleshing it out a bit more) something like:

CREATE TABLE pg_relrlspolicy (-- relation RLS policy table
ptblrelid oid, -- Relation/table
ptblaction text, -- SELECT, INSERT, UPDATE, DELETE
ptblpolid oid, -- Policy
ptblquals text, -- Quals to add
ptblacl aclitem[], -- Rights to use this policy on the table

primary key (ptblrelid, ptblaction)
);

And note that I had expected aclitem to only include one entry per role.

To support overlapping policies, we could add 'ptblpolid' into the
primary key and then simply extract out all of the entries for the
relation and action that we're currently running and step through each
to find which of the policies apply to the current_role...?

If a role has policyA with 'INSERT' rights, but no rights to SELECT, but
they also have an entry for policyB with 'SELECT' rights, we would use
only policyB for a SELECT query? Does that approach mean we don't need
'ptblaction' after all? I'm thinking this approach would also toss out
the "pick your policy" concept that Dean had proposed up-thread.

How would these interact with the existing table-level rights? For
column-level rights, if you have access to SELECT the column then you
don't need any table-level rights (and the table-level rights mean you
can SELECT from any column), are we thinking the same would apply here,
such that having 'USING POLICY' rights means you can SELECT from the
table and the table-level rights end up being the 'DIRECT' rights which
had been discussed up-thread? Not sure that I like that approach,
though I understand some others might find it appealing.. As we're
integrating this with the GRANT command, perhaps it'd be alright.

> > Overall, while I'm interested in defining where this is going in a way
> > which allows us implement an initial RLS capability while avoiding
> > future upgrade issues, I am perfectly happy to say that the 9.5 RLS
> > implementation may not be exactly syntax-compatible with 9.6 or 10.0.
>
> Again, I think that's completely non-viable. Are you going to tell
> people they can't pg_upgrade, and they can't dump-and-reload either,
> without manual fiddling? There's no way that's going to be accepted.

I don't understand what you're getting at here. We dump the catalog
using the newer version of pg_dump for pg_upgrade and we could handle
any *syntax* change required during that process to ensure that the same
access is granted in the new cluster as existed in the old cluster.

We do the exact same thing every time we add a new reserved keyword-
anything which used that keyword before ends up getting double-quoted by
the new version of pg_dump and both pg_dump and pg_upgrade work just
fine. We routinly break some syntax compatibility between major
versions, address those changes in the newer version of pg_dump, and
move on.

I am not proposing that users won't be able to upgrade from 9.5 to 9.6
if they have RLS and agree that it'd be a non-starter.

Thanks,

Stephen