Re: RLS Design

Robert, all,

* Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
> I think we're converging, but it might be a good idea to summarize a
> specific proposal before you start implementing.

Alright, apologies for it being a bit later than intended, but here's
what I've come up with thus far.

-- policies defined at a table scope
-- allows using the same policy name for different tables
-- with quals appropriate for each table
ALTER TABLE t1 ADD POLICY p1 USING p1_quals;
ALTER TABLE t1 ADD POLICY p2 USING p2_quals;

-- used to drop a policy definition from a table
ALTER TABLE t1 DROP POLICY p1;

-- cascade required when references exist for the policy
-- from roles
ALTER TABLE t1 DROP POLICY p1 CASCADE;

ALTER TABLE t1 ALTER POLICY p1 USING new_quals;

-- Controls if any RLS is applied to this table or not
-- If enabled, all users must access through some policy
ALTER TABLE table_name [ NO ] ROW LEVEL SECURITY;

-- Associates roles to policies
ALTER TABLE table_name GRANT ROW ACCESS TO role_name USING p1;
ALTER TABLE table_name REVOKE ROW ACCESS FROM role_name USING p1;

-- "all" provides a policy which equates to full access (eg: 'true' or
-- 'direct' access). Used to explicitly state when RLS can be bypassed
-- and therefore a GUC can be set which says "bypass-RLS-or-error" and
-- not have an error if this policy is granted to the role.
ALTER TABLE table_name GRANT ROW ACCESS TO role_name USING all;

-- Per-command-type control
ALTER TABLE table_name GRANT SELECT ROW ACCESS TO role_name USING all;
ALTER TABLE table_name GRANT UPDATE ROW ACCESS TO role_name USING all;

Policies for a table are checked against pg_has_role() and all which
apply are OR'd together.

Added to pg_class:

relrlsenabled boolean

pg_rowsecurity
oid oid
rlsrel oid
rlspol name
rlsquals text
rlsacls aclitem[]..? cmdtype(s) + role

If relrlsenabled then scan pg_rowsecurity for the policies associated
with the table, testing each to see if any apply for the current role
based on pg_has_role() against the aclitem array. Any which apply are
added and OR'd together.

Thoughts?

Thanks,

Stephen