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

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

"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.
"


> 
> In other databases(say like Oracle) we use to create standard 
> "roles"(Read_role, Write_role etc..) and then provide grants to the user 
> through those roles. And the objects were given direct grants to those 
> roles. Similarly here in postgres we were granting "read" or "write" 
> privileges on objects to the roles and letting the users login to the 
> database using those roles and thus getting all the read/write 
> privileges assigned to those roles. Are we doing anything wrong?
> 
> Regards
> Lok

-- 
Adrian Klaver
adrian.klaver@aklaver.com