Database Administrators Asked by Ivan C. on December 28, 2020
I’m trying to implement a simple ABAC system using row level security, with main policy defined as following:
CREATE policy resource_access ON resource
USING (
(
org_id::varchar = current_setting('scope.org.id', true)
)
AND
(
acl_read && regexp_split_to_array(current_setting('scope.acl'), ',')::varchar[]
)
)
Issuing queries akin to:
WITH
acl AS (SELECT set_config('scope.acl', 'ACL', true) "__acl"),
result AS ( ... )
SELECT * FROM acl, result
With the main reason to use WITH is to avoid multiple statements when queries are later PREPAREd and EXECUTEd by the Postgres driver I’m using.
The result in example above can contain any arbitrary queries required by the application. To ensure that set_config is executed in the query, it’s also added to the final SELECT.
However, I still do consistently encounter the following error:
QueryFailedError: unrecognized configuration parameter "scope.acl"
Which appears to be caused by executing the subquery from WITH
in isolation from the result query.
So the main questions are:
set_config
before the main query (the one in result
) is executed?WITH
, but keeping them as a single SQL statement?Thank you!
We've ended up using the JOINs over CTEs, which implicitly applies the expected order, and queries looking like:
WITH
acl AS (SELECT set_config('scope.acl', 'VALUE', TRUE) "__acl"),
result AS (${query})
SELECT * FROM bypass FULL OUTER JOIN result ON 1 = 1
In which case, acl
CTE runs before the result
and ensures that RLS could pick up a correct configuration.
Whilst it might very well be sub-optimal for a larger scale table, it worked perfectly for our scale (10k-100k rows) with no noticeable performance degradation compared to using CTEs without the resulting FULL OUTER JOIN
. More importantly, our correctness tests haven't highlighted any deviations from the expected order, despite this behavior (of result
running after acl
in CTEs block) is implicit.
Answered by Ivan Cherepanov on December 28, 2020
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP