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.
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.
That seems like something that would have to be designed in now,
because it's difficult to see how you could add it later.
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? I think that can
be made more manageable by making policies top-level objects that
exist independently of any particular tables. So you might do
something like:
\c - alice
CREATE POLICY policy1;
CREATE POLICY policy2;
ALTER TABLE t1 SET POLICY policy1 TO t1_quals;
ALTER TABLE t2 SET POLICY policy1 TO t2_quals;
...
GRANT SELECT ON TABLE t1, t2 TO bob USING policy1;
GRANT SELECT ON TABLE t1, t2 TO manager; -- Can use any policy, or
bypass all policies
Then a particular user would typically only have to set their policy
once per session, for accessing multiple tables:
\c - bob
SET rls_policy = policy1;
SELECT * FROM t1 JOIN t2; -- OK
SET rls_policy = policy2;
SELECT * FROM t1; -- ERROR: no permission to access t1 using policy2
or you'd be able to set a default policy for users, so that they
wouldn't need to explicitly choose one:
ALTER ROLE bob SET rls_policy = policy1;
Note that the syntax proposed elsewhere --- GRANT SELECT (polname) ON
TABLE tab TO role --- doesn't work because it conflicts with the
syntax for granting column privileges, so there needs to be a distinct
syntax for this, and I think it ought to ultimately allow things like
GRANT SELECT (col1, col2), UPDATE (col1) ON t1 TO bob USING policy1;
Regards,
Dean
