BUG #15251: query plan affected by grant select on partition - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      15251
Logged by:          Peter
Email address:      pgsqlpl@gmail.com
PostgreSQL version: 10.4
Operating system:   Ubuntu 14.04
Description:

Description:
Plan of SELECT query on a parent table, which actually get data from an
inherited table (partition) depends on grants for a partition.
- When current_user is missing a grant to SELECT from a partition then
planner incorrectly decides to use BitmapAnd on two GIN Indexes (see "Bad
plan" below).
- When current_user has grant to SELECT from a partition then planner
correctly decides to use one of two GIN Indexes which is more reductive.


Reproducible on:
- Ubuntu 10.4-2.pgdg14.04+1
- Ubuntu 9.6.9-2.pgdg14.04+1
- Ubuntu 9.5.13-2.pgdg14.04+1


Code:

CREATE EXTENSION IF NOT EXISTS pg_trgm; -- for gin_trgm_ops

-- base table
DROP TABLE IF EXISTS c CASCADE;
CREATE TABLE c
(
   id                   bigserial   NOT NULL PRIMARY KEY
 , ts                   timestamptz NOT NULL                --
partitioning
 , p                    text        NOT NULL                -- trigram based
search
 , t                    int[]       NOT NULL DEFAULT '{0}'  -- row level
security
);
CREATE INDEX g3_c_p ON c USING GIN (p gin_trgm_ops);
CREATE INDEX gx_c_t ON c USING GIN(t);

-- create partition
CREATE TABLE cc201806
(
   CHECK (ts >= '2018-06-01 00:00:00'
     AND  ts <  '2018-07-01 00:00:00')
) INHERITS (c);

CREATE EXTENSION IF NOT EXISTS pgcrypto; -- gen_random_bytes
-- populate partition
INSERT INTO cc201806 (id, ts, p) SELECT i, clock_timestamp(),
encode(gen_random_bytes(11), 'hex') FROM generate_series(1, 1000000) AS
gs(i);
-- make indexes after instert to speedup above insert
CREATE INDEX g3_cc201806_p ON cc201806 USING GIN (p gin_trgm_ops);
CREATE INDEX gx_cc201806_t ON cc201806 USING GIN(t);

ANALYZE; -- have good statistics

-- use restricted role (in actual DB, it is an owner of SECURITY DEFINER
function, executing following query as a dynamic statement)
DROP ROLE IF EXISTS u;
CREATE ROLE u;
GRANT SELECT ON c TO u;
;
-- exec once to cache data
EXPLAIN (ANALYZE, BUFFERS)
   SELECT *
      FROM c
      WHERE p ~ 'bbbb'
        AND t && '{1, 2, 0}'::int[] -- this array is actually a function
call
;

-- missing GRANT SELECT ON partition
REVOKE SELECT ON cc201806 FROM u;
SET ROLE u;
EXPLAIN (ANALYZE, BUFFERS)
   SELECT *
      FROM c
      WHERE p ~ 'bbbb'
        AND t && '{1, 2, 0}'::int[] -- this array is actually a function
call
;

RESET ROLE;
GRANT SELECT ON cc201806 TO u;
SET ROLE u;
EXPLAIN (ANALYZE, BUFFERS)
   SELECT *
      FROM c
      WHERE p ~ 'bbbb'
        AND t && '{1, 2, 0}'::int[] -- this array is actually a function
call
;

SELECT version();
SHOW work_mem;             -- 4MB
SHOW effective_cache_size; -- 4GB
SHOW shared_buffers;       -- 256MB


Examples of resulting plans:

Bad plan:

-------------------------------------------------------------------------------------------------------------------------------------------------
 Append  (cost=0.00..2233.50 rows=120 width=64) (actual
time=157.626..167.316 rows=276 loops=1)
   Buffers: shared hit=3866
   ->  Seq Scan on c  (cost=0.00..0.00 rows=1 width=80) (actual
time=0.001..0.001 rows=0 loops=1)
         Filter: ((p ~ 'bbbb'::text) AND (t && '{1,2,0}'::integer[]))
   ->  Bitmap Heap Scan on cc201806  (cost=1792.25..2233.50 rows=119
width=64) (actual time=157.624..167.284 rows=276 loops=1)
         Recheck Cond: ((p ~ 'bbbb'::text) AND (t &&
'{1,2,0}'::integer[]))
         Rows Removed by Index Recheck: 4212
         Heap Blocks: exact=3723
         Buffers: shared hit=3866
         ->  BitmapAnd  (cost=1792.25..1792.25 rows=119 width=0) (actual
time=157.019..157.019 rows=0 loops=1)
               Buffers: shared hit=143
               ->  Bitmap Index Scan on g3_cc201806_p  (cost=0.00..84.00
rows=8000 width=0) (actual time=1.608..1.608 rows=4488 loops=1)
                     Index Cond: (p ~ 'bbbb'::text)
                     Buffers: shared hit=7
               ->  Bitmap Index Scan on gx_cc201806_t  (cost=0.00..1707.94
rows=14925 width=0) (actual time=155.163..155.163 rows=1000000 loops=1)
                     Index Cond: (t && '{1,2,0}'::integer[])
                     Buffers: shared hit=136
 Planning time: 0.316 ms
 Execution time: 167.372 ms

Good plan:

-----------------------------------------------------------------------------------------------------------------------------------
 Append  (cost=0.00..390.13 rows=101 width=64) (actual time=2.192..11.944
rows=276 loops=1)
   Buffers: shared hit=3730
   ->  Seq Scan on c  (cost=0.00..0.00 rows=1 width=80) (actual
time=0.001..0.001 rows=0 loops=1)
         Filter: ((p ~ 'bbbb'::text) AND (t && '{1,2,0}'::integer[]))
   ->  Bitmap Heap Scan on cc201806  (cost=16.77..390.13 rows=100 width=64)
(actual time=2.189..11.912 rows=276 loops=1)
         Recheck Cond: (p ~ 'bbbb'::text)
         Rows Removed by Index Recheck: 4212
         Filter: (t && '{1,2,0}'::integer[])
         Heap Blocks: exact=3723
         Buffers: shared hit=3730
         ->  Bitmap Index Scan on g3_cc201806_p  (cost=0.00..16.75 rows=100
width=0) (actual time=1.565..1.565 rows=4488 loops=1)
               Index Cond: (p ~ 'bbbb'::text)
               Buffers: shared hit=7
 Planning time: 0.413 ms
 Execution time: 11.996 ms