AnswerBun.com

Unrecognized configuration parameter when using set_config(text, text, boolean) in CTE

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:

  • 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!

Database Administrators Asked by Ivan C. on December 28, 2020

1 Answers

One Answer

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

Add your own answers!

Related Questions

SHOW INNODB STATUS averages calculated

1  Asked on November 28, 2021 by antonios

   

IDE Access to Hive without user password?

1  Asked on November 28, 2021 by mayak

     

Daily Value of Stock Portfolio Postgres

1  Asked on November 28, 2021 by danields

     

How to remove dbcreator server role?

2  Asked on November 28, 2021 by andrea-antonangeli

       

why binlog doesn’t log the DML statement?

1  Asked on November 25, 2021 by lawrenceli

     

How to promote hidden mongo node to primary?

2  Asked on November 25, 2021 by sweeti-bharti

 

SQL Get Users By One Group But Exclude Others

1  Asked on November 25, 2021 by dangerman1988

 

Can’t find missing packages Oracle Linux 8

2  Asked on November 23, 2021 by cute-cake

     

Cassandra nodes are not equal

1  Asked on November 21, 2021 by ekrem-gurdal

 

MySQL sql_mode for remote access

1  Asked on November 21, 2021 by lakshmikantha

   

Error: MySQL shutdown unexpectedly

2  Asked on November 21, 2021 by md-maruf

   

Ask a Question

Get help from others!

© 2022 AnswerBun.com. All rights reserved.