Using indexes in RLS policies (sub)queries

[Grégory EL MAJJOUTI <gelmajjouti@xxxxxxxxx>]

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