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

We think that privilege only affect the accessibility of the table, not which index is accessed (the same applies to column-level privilge,and we have no such scenario). If I really don't have UPDATE privilege on the partition, then I should get an error message,right?

We grant privilege like this:
1.create table
2.create index
3.grant table-level privilege
4.create table partition of

then,\dp+ partition_name shows nothing
query is executable,
if revoke UPDATE partition from appuser1 apparently
query is still executable.

________________________________
发件人: David Rowley <dgrowleyml(at)gmail(dot)com>
发送时间: 2023年7月2日 5:11
收件人: liuzhilong62(at)outlook(dot)com <liuzhilong62(at)outlook(dot)com>; pgsql-bugs(at)lists(dot)postgresql(dot)org <pgsql-bugs(at)lists(dot)postgresql(dot)org>
主题: Re: BUG #18011: Declarative partition privilege problem cause incorrect execution plans

On Sun, 2 Jul 2023 at 03:17, PG Bug reporting form
<noreply(at)postgresql(dot)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