Unrecognized configuration parameter when using set_config(text, text, boolean) in CTE
0
votes
1
answer
5331
views
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 **PREPARE**d and **EXECUTE**d 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:
- Is there any elegant way to ensure running set_config
before the main query (the one in result
) is executed?
- Is there any better way to construct queries for the application side, to avoid using WITH
, but keeping them as a single SQL statement?
Thank you!
Asked by Ivan C.
(1 rep)
Nov 7, 2020, 09:49 AM
Last activity: Mar 21, 2025, 09:06 PM
Last activity: Mar 21, 2025, 09:06 PM