Re: [HACKERS] [BUGS] BUG #14682: row level security not work with partitioned table

On Wed, Jun 7, 2017 at 9:49 AM, Mike Palmiotto
<mike.palmiotto@crunchydata.com> wrote:
> One thing that concerns me is the first EXPLAIN plan from regress_rls_dave:
> +EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
> +                                             QUERY PLAN
> +----------------------------------------------------------------------------------------------------
> + Append
> +   InitPlan 1 (returns $0)
> +     ->  Index Scan using uaccount_pkey on uaccount
> +           Index Cond: (pguser = CURRENT_USER)
> +   ->  Seq Scan on part_document_fiction
> +         Filter: ((cid <> 55) AND (cid <> 55) AND (cid < 99) AND
> (dlevel <= $0) AND f_leak(dtitle))
> +   ->  Seq Scan on part_document_satire
> +         Filter: ((cid <> 55) AND (cid <> 55) AND (cid < 99) AND
> (dlevel <= $0) AND f_leak(dtitle))
> +(8 rows)
>
> I would expect that both part_document_satire (cid == 55) and
> part_document_nonfiction (cid == 99) would be excluded from the
> explain, but only cid < 99 seems to work. Interestingly, when I change
> policy pp1r to cid < 55, I see the following:
>
> +EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
> +                                            QUERY PLAN
> +---------------------------------------------------------------------------------------------------
> + Append
> +   InitPlan 1 (returns $0)
> +     ->  Index Scan using uaccount_pkey on uaccount
> +           Index Cond: (pguser = CURRENT_USER)
> +   ->  Seq Scan on part_document_fiction
> +         Filter: ((cid < 55) AND (cid <> 55) AND (cid < 99) AND
> (dlevel <= $0) AND f_leak(dtitle))
> +(6 rows)
>
> Is this a demonstration of a non-immutable function backing the
> operator and thus not being able to filter it from the planner, or is
> it a bug?

Assuming my digging is correct, there's some other explanation for
this not working as expected...
postgres=> select po.oprname, pp.proname, pp.provolatile from pg_proc
pp join pg_operator po on pp.proname::text = po.oprcode::text where
po.oprname = '<>' and pp.proname like 'int%ne';oprname |   proname   | provolatile
---------+-------------+-------------<>      | int4ne      | i<>      | int2ne      | i<>      | int24ne     | i<>
|int42ne     | i<>      | int8ne      | i<>      | int84ne     | i<>      | int48ne     | i<>      | interval_ne | i<>
   | int28ne     | i<>      | int82ne     | i
 
(10 rows)

Thoughts?

Thanks,
-- 
Mike Palmiotto
Software Engineer
Crunchy Data Solutions
https://crunchydata.com