Re: Grants not working on partitions

On 9/28/24 08:56, Lok P wrote:
>
>
> On Sat, Sep 28, 2024 at 8:46 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)com>> wrote:
>
> On 9/28/24 04:02, Lok P wrote:
> > Hi,
> > While we are creating any new tables, we used to give SELECT
> privilege
> > on the newly created tables using the below command. But we are
> seeing
> > now , in case of partitioned tables even if we had given the
> privileges
> > in the same fashion, the user is not able to query specific
> partitions
> > but only the table. Commands like "select * from
> > schema1.<partition_name> " are erroring out with the "insufficient
> > privilege" error , even if the partition belongs to the same table.
> >
> > Grant SELECT ON <table_name> to <user_name>;
> >
> > Grant was seen as a one time command which needed while creating the
> > table and then subsequent partition creation for that table was
> handled
> > by the pg_partman extension. But that extension is not creating or
> > copying any grants on the table to the users. We were expecting ,
> once
> > the base table is given a grant , all the inherited partitions
> will be
> > automatically applied to those grants. but it seems it's not working
> > that way. So is there any other way to handle this situation?
>
>
> The docs are there for a reason:
>
> https://github.com/pgpartman/pg_partman/blob/master/doc/pg_partman.md#child-table-property-inheritance <https://github.com/pgpartman/pg_partman/blob/master/doc/pg_partman.md#child-table-property-inheritance>
>
> "Privileges & ownership are NOT inherited by default. If enabled by
> pg_partman, note that this inheritance is only at child table creation
> and isn't automatically retroactive when changed (see
> reapply_privileges()). Unless you need direct access to the child
> tables, this should not be needed. You can set the inherit_privileges
> option if this is needed (see config table information below)."
>
>
> And:
>
> "reapply_privileges(
>      p_parent_table text
> )
> RETURNS void
>
>      This function is used to reapply ownership & grants on all child
> tables based on what the parent table has set.
>      Privileges that the parent table has will be granted to all child
> tables and privileges that the parent does not have will be revoked
> (with CASCADE).
>      Privileges that are checked for are SELECT, INSERT, UPDATE,
> DELETE,
> TRUNCATE, REFERENCES, & TRIGGER.
>      Be aware that for large partition sets, this can be a very long
> running operation and is why it was made into a separate function to
> run
> independently. Only privileges that are different between the parent &
> child are applied, but it still has to do system catalog lookups and
> comparisons for every single child partition and all individual
> privileges on each.
>      p_parent_table - parent table of the partition set. Must be
> schema
> qualified and match a parent table name already configured in
> pg_partman.
> "
>
>
>
> Thank you. I was not aware about this function which copies the grants
> from parent to child ,so we can give a call to this function at the end
> of the pg_partman job call which is happening through the cron job. But
> I see , the only issue is that this function only has one parameter
> "p_parent_table" but nothing for "child_table" and that means it will
> try to apply grants on all the childs/partitions which have been created
> till today and may already be having the privileges already added in them.
>
> And we have just ~60 partitions in most of the table so hope that will
> not take longer but considering we create/purge one partition daily for
> each partition table using the pg_partman, every time we give it a call,
> it will try to apply/copy the grants on all the partitions(along with
> the current day live partition), will it cause the existing running
> queries on the live partitions to hard parse? or say will it cause any
> locking effect when it will try to apply grant on the current/live
> partitions , which must be inserted/updated/deleted data into or being
> queries by the users?
>

1) You seem to have missed the first part of the answer:

"Privileges & ownership are NOT inherited by default. If enabled by
pg_partman, note that this inheritance is only at child table creation
and isn't automatically retroactive when changed (see
reapply_privileges()). Unless you need direct access to the child
tables, this should not be needed. **You can set the
inherit_privileges** option if this is needed (see config table
information below)."

Read ** ...** part.

2) This is open source the code is available for you to see what is
actually going on:

https://github.com/pgpartman/pg_partman/blob/master/sql/functions/reapply_privileges.sql

which in turn uses:

https://github.com/pgpartman/pg_partman/blob/master/sql/functions/apply_privileges.sql

3) This is something that is easily tested on you end.

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com