public inbox for [email protected]  

help / color / mirror / Atom feed

From: Lok P <[email protected]>
To: pgsql-general <[email protected]>
Subject: Grants not working on partitions
Date: Sat, 28 Sep 2024 16:32:44 +0530
Message-ID: <CAKna9VZgEyV8yXA1iugYOD_enRpQEwRRPhPYv4VS1AqEw4PmcQ@mail.gmail.com> (raw)

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

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]
  Subject: Re: Grants not working on partitions
  In-Reply-To: <CAKna9VZgEyV8yXA1iugYOD_enRpQEwRRPhPYv4VS1AqEw4PmcQ@mail.gmail.com>

* 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