Feedback-ID: i76984098:Fastmail
Message-ID: <af088eb6-92d7-4651-baef-c97fbf8172a9@aklaver.com>
Date: Sat, 28 Sep 2024 09:58:34 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Grants not working on partitions
To: Lok P <loknath.73@gmail.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
References: <CAKna9VZgEyV8yXA1iugYOD_enRpQEwRRPhPYv4VS1AqEw4PmcQ@mail.gmail.com>
 <4a17cf81-3fdd-4661-a0b4-1ad135f85511@aklaver.com>
 <CAKna9VZi8_ZG_j-kC7b=geq0sMzDnGzdTati7g0PCv45enfEqA@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAKna9VZi8_ZG_j-kC7b=geq0sMzDnGzdTati7g0PCv45enfEqA@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/af088eb6-92d7-4651-baef-c97fbf8172a9%40aklaver.com>
Precedence: bulk

On 9/28/24 08:56, Lok P wrote:
> 
> 
> On Sat, Sep 28, 2024 at 8:46 PM Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.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.klaver@aklaver.com