Sample Header Ad - 728x90

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