[PATCH] Add reloption for views to enable RLS

From: Christoph Heiss <christoph(dot)heiss(at)cybertec(dot)at>
To: pgsql-hackers(at)postgresql(dot)org
Cc: Hans-Jürgen Schönig <hs(at)cybertec(dot)at>
Subject: [PATCH] Add reloption for views to enable RLS
Date: 2021-12-17 17:31:26
Message-ID: b66dd6d6-ad3e-c6f2-8b90-47be773da240@cybertec.at
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Hi all!

As part of a customer project we are looking to implement an reloption
for views which when set, runs the subquery as invoked by the user
rather than the view owner, as is currently the case.
The rewrite rule's table references are then checked as if the user were
referencing the table(s) directly.

This feature is similar to so-called 'SECURITY INVOKER' views in other DBMS.
Although such permission checking could be implemented using views which
SELECT from a table function and further using triggers, that approach
has obvious performance downsides.

Our initial thought on implementing this was to simply add another
reloption for views, just like the already existing `security_barrier`.
With this in place, we then can conditionally evaluate in
RelationBuildRuleLock() if we need to call setRuleCheckAsUser() or not.
The new reloption has been named `security`, which is an enum currently
only supporting a single value: `relation_permissions`.

The code for fetching the rules and triggers in RelationBuildDesc() had
to be moved after the parsing of the reloptions, since with this change
RelationBuildRuleLock()now depends upon having relation->rd_options

The current behavior of views without that new reloption set is unaltered.
This is implemented as such in patch 0001.

Regression tests are included for both the new reloption of CREATE VIEW
and the row level security side of this too, contained in patch 0002.
All regression tests are passing without errors.

Finally, patch 0003 updates the documentation for this new reloption.

An simplified example on how this feature can be used could look like this:

CREATE TABLE people (id int, name text, company text);
INSERT INTO people VALUES (1, 'alice', 'foo'), (2, 'bob', 'bar');

CREATE VIEW customers_no_security
AS SELECT * FROM people;

CREATE VIEW customers
WITH (security=relation_permissions)
AS SELECT * FROM people;

-- We want carol to only see people from company 'foo'
CREATE POLICY company_foo_only
ON people FOR ALL TO carol USING (company = 'foo');

GRANT SELECT ON people TO carol;
GRANT SELECT ON customers_no_security TO carol;
GRANT SELECT ON customers TO carol;

Now using these tables as carol:

postgres=# SET ROLE carol;

For the `people` table, the policy is applied as expected:

postgres=> SELECT * FROM people;
id | name | company
1 | alice | foo
(1 row)

If we now use the view with the new relopt set, the policy is applied too:

postgres=> SELECT * FROM customers;
id | name | company
1 | alice | foo
(1 row)

But without the `security=relation_permissions` relopt, carol gets to
see data they should not be able to due to the policy not being applied,
since the rules are checked against the view owner:

postgres=> SELECT * FROM customers_no_security;
id | name | company
1 | alice | foo
2 | bob | bar
(2 rows)

Excluding regression tests and documentation, the changes boil down to this:
src/backend/access/common/reloptions.c | 20
src/backend/nodes/copyfuncs.c | 1
src/backend/nodes/equalfuncs.c | 1
src/backend/nodes/outfuncs.c | 1
src/backend/nodes/readfuncs.c | 1
src/backend/optimizer/plan/subselect.c | 1
src/backend/optimizer/prep/prepjointree.c | 1
src/backend/rewrite/rewriteHandler.c | 1
src/backend/utils/cache/relcache.c | 62
src/include/nodes/parsenodes.h | 3
src/include/utils/rel.h | 21
11 files changed, 84 insertions(+), 29 deletions(-)

All patches are against current master.

Christoph Heiss

Attachment Content-Type Size
0003-Add-documentation-for-new-security-reloption-on-view.patch text/x-patch 3.3 KB
0002-Add-regression-tests-for-new-security-reloption-for-.patch text/x-patch 11.5 KB
0001-Add-new-reloption-enum-security-to-views.patch text/x-patch 11.2 KB


Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2021-12-17 17:52:39 Re: pgsql: Revoke PUBLIC CREATE from public schema, now owned by pg_databas
Previous Message Justin Pryzby 2021-12-17 17:21:13 Re: pg_upgrade should truncate/remove its logs before running