Hello everyone,
I am attempting to set up a row level security policy based on geo-location
(and the PostGIS extension). I am struggling to have it make use of column
indexes.
The following example defines a table with geography points and aims to
restrict access to it based on distance to another set of points in a
secondary table. It has been tested on 11.2.
CREATE EXTENSION postgis;
-- This is the table we want to secure with RLS
DROP TABLE IF EXISTS example1;
CREATE TABLE example1 (
id serial NOT NULL,
geo geography NULL,
CONSTRAINT example1_pk PRIMARY KEY (id)
) with ( OIDS=FALSE );
-- Seed the table with 100k random points
INSERT INTO example1(geo)
SELECT ST_SetSRID(
ST_MakePoint(
(random()*360.0) - 180.0,
(random()*180.0) - 90.0),
4326) as geom
FROM generate_series(1, 100000);
CREATE INDEX example1_spx ON example1 USING GIST (geo);
-- This table will hold points for the row level policy
DROP TABLE IF EXISTS example_acl;
CREATE TABLE example_acl (
geo geography NULL
) with ( OIDS=FALSE );
INSERT INTO example_acl(geo)
SELECT ST_SetSRID(
ST_MakePoint(
(random()*360.0) - 180.0,
(random()*180.0) - 90.0),
4326) as geom
FROM generate_series(1, 100);
-- Simple query that performs an index scan
EXPLAIN ANALYZE VERBOSE SELECT count(*) from example1
INNER JOIN example_acl on st_dwithin(example_acl.geo, example1.geo, 1000)
Aggregate (cost=12364.11..12364.12 rows=1 width=8) (actual
time=4.802..4.802 rows=1 loops=1)
Output: count(*)
-> Nested Loop (cost=0.41..12364.00 rows=45 width=0) (actual
time=4.797..4.797 rows=0 loops=1)
-> Seq Scan on public.example_acl (cost=0.00..23.60 rows=1360
width=32) (actual time=0.034..0.066 rows=100 loops=1)
Output: example_acl.geo
-> Index Scan using example1_spx on public.example1
(cost=0.41..9.06 rows=1 width=32) (actual time=0.044..0.044 rows=0
loops=100)
Output: example1.id, example1.geo
Index Cond: (example1.geo && _st_expand(example_acl.geo,
'1000'::double precision))
Filter: ((example_acl.geo && _st_expand(example1.geo,
'1000'::double precision)) AND _st_dwithin(example_acl.geo, example1.geo,
'1000'::double precision, true))
Planning time: 60.690 ms
Execution time: 5.006 ms
-- Setting up the policy
CREATE ROLE example_role;
GRANT SELECT ON TABLE example1 to example_role;
GRANT SELECT ON TABLE example_acl to example_role;
ALTER TABLE example1 ENABLE ROW LEVEL SECURITY;
CREATE POLICY example_location_policy ON example1
AS permissive
FOR SELECT
TO example_role
USING (
EXISTS (
SELECT 1
FROM example_acl
WHERE (
st_dwithin(example_acl.geo, example1.geo, 1000)
)
)
);
SET ROLE example_role;
EXPLAIN ANALYZE VERBOSE SELECT count(*) from example1;
Aggregate (cost=5251959.00..5251959.01 rows=1 width=8) (actual
time=9256.606..9256.606 rows=1 loops=1)
Output: count(*)
-> Seq Scan on public.example1 (cost=0.00..5251834.00 rows=50000
width=0) (actual time=9256.601..9256.601 rows=0 loops=1)
Output: example1.id, example1.geo
Filter: (SubPlan 1)
Rows Removed by Filter: 100000
SubPlan 1
-> Seq Scan on public.example_acl (cost=0.00..52.50 rows=1
width=0) (actual time=0.089..0.089 rows=0 loops=100000)
Filter: ((example_acl.geo && _st_expand(example1.geo,
'1000'::double precision)) AND (example1.geo && _st_expand(example_acl.geo,
'1000'::double precision)) AND _st_dwithin(example_acl.geo, example1.geo,
'1000'::double precision, true))
Rows Removed by Filter: 100
Planning time: 67.601 ms
Execution time: 9256.812 ms
As you can see, the policy does not use the index example1_spx on the
geography column.
Is there a way to rewrite that policy so that it would make use of the
index?
Thank you in advance.
Best regards,
Grégory El Majjouti
