Thread: Grants not working on partitions
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?
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?
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?
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
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
On Sat, Sep 28, 2024 at 8:46 PM Adrian Klaver <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
"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?
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