MIME-Version: 1.0
From: Lok P <loknath.73@gmail.com>
Date: Sat, 28 Sep 2024 16:32:44 +0530
Message-ID: <CAKna9VZgEyV8yXA1iugYOD_enRpQEwRRPhPYv4VS1AqEw4PmcQ@mail.gmail.com>
Subject: Grants not working on partitions
To: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000fbcd4606232be905"
Archived-At: <https://www.postgresql.org/message-id/CAKna9VZgEyV8yXA1iugYOD_enRpQEwRRPhPYv4VS1AqEw4PmcQ%40mail.gmail.com>
Precedence: bulk

--000000000000fbcd4606232be905
Content-Type: text/plain; charset="UTF-8"

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

--000000000000fbcd4606232be905
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hi,<div>While we are creating any new tables, we used to g=
ive SELECT privilege on the newly created tables using the below command. B=
ut we are seeing now , in case of partitioned tables even if we had given t=
he privileges in the same fashion, the user is not able to query specific p=
artitions but only the table. Commands like &quot;select * from schema1.&lt=
;partition_name&gt; &quot; are erroring out with the &quot;insufficient pri=
vilege&quot; error , even if the partition belongs to the same table. <br><=
br>Grant SELECT ON &lt;table_name&gt; to &lt;user_name&gt;;<br><br>Grant wa=
s 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 gra=
nt , all the inherited partitions will be automatically applied to those gr=
ants. but it seems it&#39;s not working that way. So is there any other way=
 to handle this situation?<br><br>In other databases(say like Oracle) we us=
e to create standard &quot;roles&quot;(Read_role, Write_role etc..) and the=
n provide grants to the user through those roles. And the objects were give=
n direct grants to those roles. Similarly here in postgres we were granting=
 &quot;read&quot; or &quot;write&quot; privileges on objects to the roles a=
nd letting the users login to the database using those roles and thus getti=
ng all the read/write privileges assigned to those roles. Are we doing anyt=
hing wrong?<br></div><div><br></div><div>Regards</div><div>Lok</div></div>

--000000000000fbcd4606232be905--