Grants not working on partitions

From: Lok P <loknath(dot)73(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Grants not working on partitions
Date: 2024-09-28 11:02:44
Message-ID: CAKna9VZgEyV8yXA1iugYOD_enRpQEwRRPhPYv4VS1AqEw4PmcQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

Regards
Lok

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Greg Sabino Mullane 2024-09-28 15:00:34 Re: Regarding publish_via_partiton_root with pglogical
Previous Message Durgamahesh Manne 2024-09-28 05:39:51 Re: Regarding publish_via_partiton_root with pglogical