1

I have a question: I have two row security policies to restrict the access to a table. The first policy uses an Array-Column "rights". The "rights" column is type 16-bit bit/integer array and each user is assigned an index. If the array has the value 1 at an index, the user with that index can read the line. The policy for looks like this:

CREATE POLICY policy_testdata_select ON testdata
        FOR SELECT TO PUBLIC
        USING (rights[(select array_pos from account where username = current_user)] > 0);

The second policy uses bitstrings with the same logic so for example:

Value in the table: 1001010

User1 bit string: 0001000

User2 bit string: 0000100

1001010 & 0001000 = 0001000 -> User1 has access

1001010 & 0000100 = 0000000 -> User2 has no access

The policy looks like this:

CREATE POLICY policy_testdata_select ON testdata
        FOR SELECT TO PUBLIC
        USING ((bitstring & (select user_bitstring from account where username = current_user)) <> B'0'::BIT(16));

Intuitively, I would argue that the second option is more performant as a bit string does not consume a lot of memory and binary operations don't cost much, but if one compares the performance, the array version is even a little faster. How come? Here are the two evaluation plans with the same test data (2 million lines and user has access to half):

Array:
                                                                 QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=10000000008.16..10000526114.16 rows=666667 width=1160) (actual time=0.025..2733.524 rows=1000000 loops=1)
   InitPlan 1 (returns $0)
     ->  Index Scan using username2_index on account2  (cost=0.14..8.16 rows=1 width=4) (actual time=0.014..0.014 rows=1 loops=1)
           Index Cond: (username = CURRENT_USER)
   ->  Seq Scan on testdata  (cost=10000000000.00..10000526106.00 rows=666667 width=1160) (actual time=0.025..2660.927 rows=1000000 loops=1)
         Filter: (rights[$0] > 0)
         Rows Removed by Filter: 1000000
 Planning Time: 0.181 ms
 Execution Time: 2768.561 ms
(9 rows)

Bitstring:
                                                                  QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=10000000008.16..10000266895.60 rows=1000000 width=1160) (actual time=0.026..2759.098 rows=1000000 loops=1)
   InitPlan 1 (returns $0)
     ->  Index Scan using username2_index on account2  (cost=0.14..8.16 rows=1 width=7) (actual time=0.013..0.014 rows=1 loops=1)
           Index Cond: (username = CURRENT_USER)
   ->  Seq Scan on testdata  (cost=10000000000.00..10000531106.00 rows=1990000 width=1160) (actual time=0.025..2685.145 rows=1000000 loops=1)
         Filter: ((bitstring & $0) <> '0000000000000000'::bit(16))
         Rows Removed by Filter: 1000000
 Planning Time: 0.167 ms
 Execution Time: 2794.719 ms
(9 rows)

Some comparison between the two versions (yellow and purple - blue without any policy) x-Axis: Count of Rows in thousand, y-Axis: Execution Time of the query - averaged: Some comparison between the two versions (yellow and purple - blue without any policy)

And another question: is there a better way to filter access. It should be possible that the rights to a line are changeable. Would it be possible, for example, to build an index that speeds up the filtering process?

  • To my eyes the execution times are basically equal (well, they who a 1% difference, which is possibly not statistically significant). – dezso Sep 6 '19 at 10:58
  • yes but thats what bothers me. Shouldn't the bit-version be way faster as it uses less storage and only binary operations? – supotko Sep 6 '19 at 11:06
1

It doesn't matter very much how big the data structure is, as long as the row is small enough not to get TOASTed (stored out of line, in which case access becomes more expensive).

The performance difference you measure might as well be the cost of the different operators (& vs. array element access).

I don't believe that you can do that more efficiently than you did. Row level security comes with a performance hit that is ideally proportional to the number of rows checked, and the performance impact will be most visible with a cheap access path like a sequential scan.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.