Posgres Row Security Policy - Why Array faster than Bitstring?
1
vote
1
answer
97
views
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:
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?
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:

Asked by supotko
(11 rep)
Sep 6, 2019, 10:50 AM
Last activity: Sep 6, 2019, 12:36 PM
Last activity: Sep 6, 2019, 12:36 PM