Re: BUG #18011: Declarative partition privilege problem cause incorrect execution plans

On Sun, 2 Jul 2023 at 03:17, PG Bug reporting form
<noreply@postgresql.org> wrote:
>   ->  Index Scan using TABLE_RECORD_202304_date_created_idx on
> TABLE_RECORD_202304 TABLE_RECORD_6  (cost=0.44..5.47 rows=1 width=481)
>         Index Cond: ((date_created > (now() - '31 days'::interval day)) AND
> (date_created < now()))

> The planner(or executor) should trim partitions —just access partitions of
> 202305 and 202306,it does.

Going by what you've shown above, it does *not* prune that partition.
There's a note in the documents [1] about execution time partition
pruning not working for modify table:

"Execution-time partition pruning currently only occurs for the Append
and MergeAppend node types. It is not yet implemented for the
ModifyTable node type, but that is likely to be changed in a future
release of PostgreSQL."

> And the executor should use the
> idx_rms_reject_reason(appl_no, is_deleted)  index,it doesn't.Indexes on
> DATE_CREATED are used

When is the last time the statistics were updated on
TABLE_RECORD_202306? Does a manual ANALYZE on that table cause the
plan to change?

> We collected statistics and killed sessions because of plan cache,but that
> didn't work.
> Then,We found that the user does not have permissions on the partitions.So
> we did some authorization operations:
> grant select,update,delete,insert  on TABLE_RECORD_202305 to appuser1;
> grant select,update,delete,insert  on TABLE_RECORD_202306 to appuser1;

When you query a partitioned table, the permissions of each partition
are not checked. Effectively these inherit from the partitioned table.
If you want to access the partitions directly from appuser1, then
you'll need to grant permissions.

David

[1] https://www.postgresql.org/docs/13/ddl-partitioning.html#DDL-PARTITION-PRUNING