MIME-Version: 1.0
From: Ayush Vatsa <ayushvatsa1810@gmail.com>
Date: Mon, 17 Feb 2025 23:31:46 +0530
Message-ID: <CACX+KaMz2ZoOojh0nQ6QNBYx8Ak1Dkoko=D4FSb80BYW+o8CHQ@mail.gmail.com>
Subject: Clarification on Role Access Rights to Table Indexes
To: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000001c509062e5a5247"
Archived-At: <https://www.postgresql.org/message-id/CACX%2BKaMz2ZoOojh0nQ6QNBYx8Ak1Dkoko%3DD4FSb80BYW%2Bo8CHQ%40mail.gmail.com>
Precedence: bulk

--00000000000001c509062e5a5247
Content-Type: text/plain; charset="UTF-8"

Hi PostgreSQL Community,
I am currently exploring the behavior of pg_prewarm and encountered an
issue related to role
access rights that I was hoping you could help clarify.

Here is the scenario I observed:

postgres=# CREATE ROLE alpha;
CREATE ROLE
postgres=# GRANT SELECT ON pg_class TO alpha;
GRANT
postgres=# SET ROLE alpha;
SET
postgres=> SELECT pg_prewarm('pg_class');
 pg_prewarm
------------
         14
(1 row)

postgres=> SELECT pg_prewarm('pg_class_oid_index');
ERROR:  permission denied for index pg_class_oid_index
postgres=> RESET ROLE;
RESET

postgres=# GRANT SELECT ON pg_class_oid_index TO alpha;
ERROR:  "pg_class_oid_index" is an index

Based on this, I have few questions:
1. Can a role have access rights to a table without having access to its
index?
2. If yes, how can we explicitly grant access to the index?
3. If no, and the role inherently gets access to the index when granted
access to the table, why
does the pg_prewarm call fail [1] in the above scenario?

[1]
https://github.com/postgres/postgres/blob/master/contrib/pg_prewarm/pg_prewarm.c#L108-L110


Regards,
Ayush Vatsa
SDE AWS

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

<div dir=3D"ltr"><p>Hi PostgreSQL Community,<br>I am currently exploring th=
e behavior of <code>pg_prewarm</code> and encountered an issue related to r=
ole<br>access rights that I was hoping you could help clarify.</p><p>Here i=
s the scenario I observed:</p><p>postgres=3D# CREATE ROLE alpha;<br>CREATE =
ROLE<br>postgres=3D# GRANT SELECT ON pg_class TO alpha;<br>GRANT<br>postgre=
s=3D# SET ROLE alpha;<br>SET<br>postgres=3D&gt; SELECT pg_prewarm(&#39;pg_c=
lass&#39;);<br>=C2=A0pg_prewarm <br>------------<br>=C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A014<br>(1 row)<br><br>postgres=3D&gt; SELECT pg_prewarm(&#39;pg=
_class_oid_index&#39;);<br>ERROR: =C2=A0permission denied for index pg_clas=
s_oid_index<br>postgres=3D&gt; RESET ROLE;<br>RESET<br><br>postgres=3D# GRA=
NT SELECT ON pg_class_oid_index TO alpha;<br>ERROR: =C2=A0&quot;pg_class_oi=
d_index&quot; is an index</p><p>Based on this, I have=C2=A0few questions:<b=
r>1. Can a role have access rights to a table without having access to its =
index?<br>2. If yes, how can we explicitly grant access to the index?<br>3.=
 If no, and the role inherently gets access to the index when granted acces=
s to the table, why<br>does the <code>pg_prewarm</code> call fail [1] in th=
e above scenario?<br><br>[1]=C2=A0<a href=3D"https://github.com/postgres/po=
stgres/blob/master/contrib/pg_prewarm/pg_prewarm.c#L108-L110">https://githu=
b.com/postgres/postgres/blob/master/contrib/pg_prewarm/pg_prewarm.c#L108-L1=
10</a></p><p><br>Regards,<br>Ayush Vatsa<br>SDE AWS</p></div>

--00000000000001c509062e5a5247--