MIME-Version: 1.0
References: <CACX+KaMz2ZoOojh0nQ6QNBYx8Ak1Dkoko=D4FSb80BYW+o8CHQ@mail.gmail.com>
 <855988.1739816850@sss.pgh.pa.us>
In-Reply-To: <855988.1739816850@sss.pgh.pa.us>
From: Ayush Vatsa <ayushvatsa1810@gmail.com>
Date: Tue, 18 Feb 2025 00:09:33 +0530
Message-ID: <CACX+KaNQ9ahSb-TP-N2RELD4wFNGuXHx7C7t+Dtf_zeGSLDybw@mail.gmail.com>
Subject: Re: Clarification on Role Access Rights to Table Indexes
To: Tom Lane <tgl@sss.pgh.pa.us>, "David G. Johnston" <david.g.johnston@gmail.com>, 
	Robert Haas <robertmhaas@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000250807062e5ad97a"
Archived-At: <https://www.postgresql.org/message-id/CACX%2BKaNQ9ahSb-TP-N2RELD4wFNGuXHx7C7t%2BDtf_zeGSLDybw%40mail.gmail.com>
Precedence: bulk

--000000000000250807062e5ad97a
Content-Type: text/plain; charset="UTF-8"

> This is pointless, everyone (i.e. the PUBLIC pseudo-role) can already
read pg_class.
True, Just checked that.

> It fails because AFAICS there is no way for it to work on an index, only
tables.
pg_prewarm extension works on index if we have right (SELECT) privileges
postgres=# CREATE TABLE x(id INT);
CREATE TABLE
postgres=# CREATE INDEX idx ON x(id);
CREATE INDEX

postgres=# INSERT INTO x SELECT * FROM generate_series(1,10000);
INSERT 0 10000
postgres=# SELECT pg_prewarm('x');
 pg_prewarm
------------
         45
(1 row)

postgres=# SELECT pg_prewarm('idx');
 pg_prewarm
------------
         30
(1 row)

> It seems like ownership of the table would be more appropriate, or maybe
> access to one of the built-in roles like pg_maintain.
True, adding Robert Haas (author) to this thread for his opinion.

Regards,
Ayush Vatsa
SDE AWS

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

<div dir=3D"ltr">&gt;=C2=A0<span class=3D"gmail-im" style=3D"color:rgb(80,0=
,80)"><span style=3D"color:rgb(34,34,34)">This is pointless, everyone (i.e.=
 the PUBLIC pseudo-role) can already read pg_class.<br>True, Just checked t=
hat.</span></span><div><span class=3D"gmail-im" style=3D"color:rgb(80,0,80)=
"><span style=3D"color:rgb(34,34,34)"><br></span></span></div><div><span cl=
ass=3D"gmail-im" style=3D"color:rgb(80,0,80)"><span style=3D"color:rgb(34,3=
4,34)">&gt;=C2=A0</span></span>It fails because AFAICS there is no way for =
it to work on an index, only tables.<br>pg_prewarm extension works on index=
 if we have right (SELECT) privileges</div><div>postgres=3D# CREATE TABLE x=
(id INT);<br>CREATE TABLE<br>postgres=3D# CREATE INDEX idx ON x(id);<br>CRE=
ATE INDEX</div><div><br>postgres=3D# INSERT INTO x SELECT * FROM generate_s=
eries(1,10000);<br>INSERT 0 10000<br>postgres=3D# SELECT pg_prewarm(&#39;x&=
#39;);<br>=C2=A0pg_prewarm <br>------------<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A045<br>(1 row)<br><br>postgres=3D# SELECT pg_prewarm(&#39;idx&#39;);<b=
r>=C2=A0pg_prewarm <br>------------<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A030=
<br>(1 row)</div><div><br></div><div>&gt; It seems like ownership of the ta=
ble would be more appropriate, or maybe</div>&gt; access to one of the buil=
t-in roles like pg_maintain.<br>True, adding Robert Haas (author) to this t=
hread for his opinion.<div><br></div><div>Regards,<br>Ayush Vatsa</div><div=
>SDE AWS</div></div>

--000000000000250807062e5ad97a--