public inbox for [email protected]
help / color / mirror / Atom feedFrom: Adrian Klaver <[email protected]>
To: Lok P <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Grants not working on partitions
Date: Sat, 28 Sep 2024 09:58:34 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAKna9VZi8_ZG_j-kC7b=geq0sMzDnGzdTati7g0PCv45enfEqA@mail.gmail.com>
References: <CAKna9VZgEyV8yXA1iugYOD_enRpQEwRRPhPYv4VS1AqEw4PmcQ@mail.gmail.com>
<[email protected]>
<CAKna9VZi8_ZG_j-kC7b=geq0sMzDnGzdTati7g0PCv45enfEqA@mail.gmail.com>
On 9/28/24 08:56, Lok P wrote:
>
>
> On Sat, Sep 28, 2024 at 8:46 PM Adrian Klaver <[email protected]
> <mailto:[email protected]>> 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-inheritan... <https://github.com/pgpartman/pg_partman/blob/master/doc/pg_partman.md#child-table-property-inheritan...;
>
> "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
[email protected]
view thread (3+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Grants not working on partitions
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox