system: centos 7 PostgreSQL 12.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit

-- DB set steps
CREATE SCHEMA IF NOT EXISTS partman;
CREATE EXTENSION IF NOT EXISTS pg_partman SCHEMA partman;

CREATE ROLE  test_owner LOGIN;
CREATE ROLE  test_user LOGIN;
CREATE SCHEMA  test  AUTHORIZATION test_owner;

GRANT USAGE on SCHEMA partman to test_owner;
GRANT USAGE on SCHEMA test  to test_user;


set role test_owner;

-- create the qtest_position table and its partitions
CREATE TABLE IF NOT EXISTS test.qtest_position (
    position_id         bigint NOT NULL,
    tier                smallint,
    toi                 timestamptz(0) NOT NULL,
    track_id            bigint,
    security_tag        varchar(33),
    posit               public.geometry(Point)
) PARTITION BY LIST (tier);

CREATE TABLE IF NOT EXISTS test.qtest_posit_t1 PARTITION OF test.qtest_position
    FOR VALUES IN (1) PARTITION BY RANGE (toi);

-- now prepare to create the partitions for tier=1 position data
-- Note: in practice there are other tier=x partitons but they are not needed to replicate the problem

CREATE TABLE test.qtest_posit_t1_template ( LIKE test.qtest_posit_t1 INCLUDING ALL );

ALTER TABLE test.qtest_posit_t1_template ADD PRIMARY KEY (position_id);

CREATE INDEX ON test.qtest_posit_t1_template (track_id, toi);

CREATE INDEX ON test.qtest_posit_t1_template USING GIST (posit, toi) include (security_tag);

-- define a simple function as a standin for the complicated one used for access control

CREATE OR REPLACE FUNCTION test.user_has_access(code varchar)
RETURNS text
AS $$
BEGIN
    return '1'::text;
END;
$$ LANGUAGE plpgsql STABLE STRICT;

reset role;

-- create some partitons
SELECT partman.create_parent('test.qtest_posit_t1', 'toi', 'native', '1 days',
  p_premake := 20, p_start_partition := '2022-09-01', p_template_table := 'test.qtest_posit_t1_template');

set role test_owner;

grant select on test.qtest_position to test_owner;
grant select on test.qtest_position to test_user;

CREATE POLICY sec_select_on_qtest_position ON test.qtest_position 
    FOR ALL USING (test.user_has_access(security_tag) = '1') with check (test.user_has_access(security_tag) = '1');

ALTER TABLE  test.qtest_position ENABLE ROW LEVEL SECURITY;


CREATE POLICY sec_select_on_qtest_posit_t1 ON test.qtest_posit_t1 
   FOR ALL USING (test.user_has_access(security_tag) = '1') with check (test.user_has_access(security_tag) = '1');
 
ALTER TABLE  test.qtest_posit_t1 ENABLE ROW LEVEL SECURITY;

-- now insert some data into this table 

insert into test.qtest_position 
   with  trks  as (select (s::BIGINT * 100::BIGINT) id from generate_series(200001,600000,1) s),
         pos   as (select s::BIGINT id from generate_series(1,90, 1) s)
select (t.id + p.id), 1,
        ('2022-09-02'::timestamptz + (trunc(random() * 160000)::text || ' seconds')::interval),
        t.id,
        (case when random() <= .5 then 'ok' else 'other' end),
        ST_SetSRID(ST_MakePoint((-179.9 + 359.9 * random()), -60.0 * 120.0 * random()), 4326)
   from trks t, pos p;

INSERT 0 36000000

-- Run analyze againts all the tables of concern

analyze test.qtest_position;
analyze test.qtest_posit_t1_p2022_09_01;
analyze test.qtest_posit_t1_p2022_09_02;
analyze test.qtest_posit_t1_p2022_09_03;
analyze test.qtest_posit_t1_p2022_09_03;

-- ===========================================================================
-- now run a spatial query a user might run

set role test_user;
SET
explain analyze SELECT  
        position_961.SECURITY_TAG,
        position_961.TRACK_ID,
        position_961.POSIT,
        position_961.POSITION_ID,
        position_961.TOI  
    FROM
        test.qtest_position position_961  
    WHERE
        (ST_Intersects( position_961.POSIT, ST_SETSRID(ST_GeomFromGeoJSON('{"type":"Polygon","coordinates":[[[ 67.729806, 15.734972],[49.077444,-9.989],[39.995417,-3.982083],[51.616528,17.977861],[67.729806,15.734972]]]}')  , 4326)  )   
            )  AND
 position_961.TOI BETWEEN '2022-09-02T20:28:42.753Z'::TIMESTAMPTZ AND '2022-09-03T20:28:42.753Z'::TIMESTAMPTZ
  and test.user_has_access(security_tag) = '1';
                                                                                                                                                                                                        
                                        QUERY PLAN                                                                                                                                                      
                                                                                          
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
 Append  (cost=0.42..495598323.78 rows=2 width=60) (actual time=7246.524..31614.944 rows=699 loops=1)
   ->  Index Scan using qtest_posit_t1_p2022_09_02_posit_toi_security_tag_idx on qtest_posit_t1_p2022_09_02 position_961  (cost=0.42..72721949.89 rows=1 width=60) (actual time=7246.523..9560.695 rows=
121 loops=1)
         Index Cond: ((toi >= '2022-09-02 20:28:42.753+00'::timestamp with time zone) AND (toi <= '2022-09-03 20:28:42.753+00'::timestamp with time zone))
         Filter: ((test.user_has_access(security_tag) = '1'::text) AND (test.user_has_access(security_tag) = '1'::text) AND st_intersects(posit, '0103000020E610000001000000050000002C9B3924B5EE504091F3
FE3F4E782F40BEA25BAFE9894840EE7C3F355EFA23C0F47002D369FF434019A9F7544EDB0FC0BE88B663EACE4940AB08371955FA31402C9B3924B5EE504091F3FE3F4E782F40'::geometry))
         Rows Removed by Filter: 2851335
   ->  Seq Scan on qtest_posit_t1_p2022_09_03 position_961_1  (cost=0.00..422876373.88 rows=1 width=60) (actual time=84.624..22054.050 rows=578 loops=1)
         Filter: ((toi >= '2022-09-02 20:28:42.753+00'::timestamp with time zone) AND (toi <= '2022-09-03 20:28:42.753+00'::timestamp with time zone) AND (test.user_has_access(security_tag) = '1'::tex
t) AND (test.user_has_access(security_tag) = '1'::text) AND st_intersects(posit, '0103000020E610000001000000050000002C9B3924B5EE504091F3FE3F4E782F40BEA25BAFE9894840EE7C3F355EFA23C0F47002D369FF434019A9
F7544EDB0FC0BE88B663EACE4940AB08371955FA31402C9B3924B5EE504091F3FE3F4E782F40'::geometry))
         Rows Removed by Filter: 16561241
 Planning Time: 0.369 ms
 Execution Time: 31615.459 ms
(10 rows)


-- run the same query as test_owner


set role test_owner;
SET
explain analyze SELECT  
        position_961.SECURITY_TAG,
        position_961.TRACK_ID,
        position_961.POSIT,
        position_961.POSITION_ID,
        position_961.TOI  
    FROM
        test.qtest_position position_961  
    WHERE
        (ST_Intersects( position_961.POSIT, ST_SETSRID(ST_GeomFromGeoJSON('{"type":"Polygon","coordinates":[[[ 67.729806, 15.734972],[49.077444,-9.989],[39.995417,-3.982083],[51.616528,17.977861],[67.729806,15.734972]]]}')  , 4326)  )   
            )  AND
 position_961.TOI BETWEEN '2022-09-02T20:28:42.753Z'::TIMESTAMPTZ AND '2022-09-03T20:28:42.753Z'::TIMESTAMPTZ
  and test.user_has_access(security_tag) = '1';
                                                                                                                                                                                       QUERY PLAN       
                                                                                                                                                                                 
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Append  (cost=0.42..88190.82 rows=16 width=60) (actual time=0.167..6.259 rows=699 loops=1)
   ->  Index Scan using qtest_posit_t1_p2022_09_02_posit_toi_security_tag_idx on qtest_posit_t1_p2022_09_02 position_961  (cost=0.42..10309.24 rows=2 width=60) (actual time=0.167..1.320 rows=121 loops
=1)
         Index Cond: ((posit && '0103000020E610000001000000050000002C9B3924B5EE504091F3FE3F4E782F40BEA25BAFE9894840EE7C3F355EFA23C0F47002D369FF434019A9F7544EDB0FC0BE88B663EACE4940AB08371955FA31402C9B3
924B5EE504091F3FE3F4E782F40'::geometry) AND (toi >= '2022-09-02 20:28:42.753+00'::timestamp with time zone) AND (toi <= '2022-09-03 20:28:42.753+00'::timestamp with time zone))
         Filter: ((test.user_has_access(security_tag) = '1'::text) AND st_intersects(posit, '0103000020E610000001000000050000002C9B3924B5EE504091F3FE3F4E782F40BEA25BAFE9894840EE7C3F355EFA23C0F47002D36
9FF434019A9F7544EDB0FC0BE88B663EACE4940AB08371955FA31402C9B3924B5EE504091F3FE3F4E782F40'::geometry))
         Rows Removed by Filter: 201
   ->  Index Scan using qtest_posit_t1_p2022_09_03_posit_toi_security_tag_idx on qtest_posit_t1_p2022_09_03 position_961_1  (cost=0.42..77881.49 rows=14 width=60) (actual time=0.484..4.876 rows=578 lo
ops=1)
         Index Cond: ((posit && '0103000020E610000001000000050000002C9B3924B5EE504091F3FE3F4E782F40BEA25BAFE9894840EE7C3F355EFA23C0F47002D369FF434019A9F7544EDB0FC0BE88B663EACE4940AB08371955FA31402C9B3
924B5EE504091F3FE3F4E782F40'::geometry) AND (toi >= '2022-09-02 20:28:42.753+00'::timestamp with time zone) AND (toi <= '2022-09-03 20:28:42.753+00'::timestamp with time zone))
         Filter: ((test.user_has_access(security_tag) = '1'::text) AND st_intersects(posit, '0103000020E610000001000000050000002C9B3924B5EE504091F3FE3F4E782F40BEA25BAFE9894840EE7C3F355EFA23C0F47002D36
9FF434019A9F7544EDB0FC0BE88B663EACE4940AB08371955FA31402C9B3924B5EE504091F3FE3F4E782F40'::geometry))
         Rows Removed by Filter: 1151
 Planning Time: 0.525 ms
 Execution Time: 6.324 ms
(11 rows)

-- Note this example has relatively few rows in each table and returns results. I have also culled over 70 columns from the qtest_position table to simplify the example.
-- In the real case there are too many rows and the plan users get never returns any results for users that aren't the owner of the table.
-- I have tried making the spatial index just contain the posit column but when I do that the planner picks the track_toi index instead and again uses the toi range condition.
-- I have also granted select access to all of the table partitions but that doesn't help either.
-- Desire to know if there is anything that can be done to get the planner to use the correct spatial condition for the spatial index.