MIME-Version: 1.0
In-Reply-To: <CACX+KaMz2ZoOojh0nQ6QNBYx8Ak1Dkoko=D4FSb80BYW+o8CHQ@mail.gmail.com>
References: <CACX+KaMz2ZoOojh0nQ6QNBYx8Ak1Dkoko=D4FSb80BYW+o8CHQ@mail.gmail.com>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Mon, 17 Feb 2025 11:17:16 -0700
Message-ID: <CAKFQuwaACqunNiK5Xo6qDYK5O-w-6od0xfN4kdu5Z3Bmp1GuDQ@mail.gmail.com>
Subject: Clarification on Role Access Rights to Table Indexes
To: Ayush Vatsa <ayushvatsa1810@gmail.com>
Cc: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000c994c3062e5a8823"
Archived-At: <https://www.postgresql.org/message-id/CAKFQuwaACqunNiK5Xo6qDYK5O-w-6od0xfN4kdu5Z3Bmp1GuDQ%40mail.gmail.com>
Precedence: bulk

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

On Monday, February 17, 2025, Ayush Vatsa <ayushvatsa1810@gmail.com> wrote:

> postgres=3D# CREATE ROLE alpha;
>
> CREATE ROLE
> postgres=3D# GRANT SELECT ON pg_class TO alpha;
>
This is pointless, everyone (i.e. the PUBLIC pseudo-role) can already read
pg_class.

1. Can a role have access rights to a table without having access to its
> index?
>
Roles don=E2=80=99t directly interact with indexes in PostgreSQL so this do=
esn=E2=80=99t
even make sense.  But if you need a yes/no answer, then yes.

>
> 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
>
It fails because AFAICS there is no way for it to work on an index, only
tables.

David J.

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

On Monday, February 17, 2025, Ayush Vatsa &lt;<a href=3D"mailto:ayushvatsa1=
810@gmail.com" target=3D"_blank">ayushvatsa1810@gmail.com</a>&gt; wrote:<br=
><blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1=
px #ccc solid;padding-left:1ex"><div dir=3D"ltr"><p>postgres=3D# CREATE ROL=
E alpha;<br></p><p>CREATE ROLE<br>postgres=3D# GRANT SELECT ON pg_class TO =
alpha;</p></div></blockquote><div>This is pointless, everyone (i.e. the PUB=
LIC pseudo-role) can already read pg_class.</div><div><br></div><blockquote=
 class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc soli=
d;padding-left:1ex"><div dir=3D"ltr"><p>1. Can a role have access rights to=
 a table without having access to its index?<br></p></div></blockquote><div=
>Roles don=E2=80=99t directly interact with indexes in PostgreSQL so this d=
oesn=E2=80=99t even make sense.=C2=A0 But if you need a yes/no answer, then=
 yes.=C2=A0<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0 0 =
0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir=3D"ltr"><p><br=
>3. If no, and the role inherently gets access to the index when granted ac=
cess to the table, why<br>does the <code>pg_prewarm</code> call fail [1] in=
 the above scenario?<br><br>[1]=C2=A0<a href=3D"https://github.com/postgres=
/postgres/blob/master/contrib/pg_prewarm/pg_prewarm.c#L108-L110" target=3D"=
_blank">https://github.com/postgre<wbr>s/postgres/blob/master/contrib<wbr>/=
pg_prewarm/pg_prewarm.c#L108-<wbr>L110</a></p></div></blockquote><div>It fa=
ils because AFAICS there is no way for it to work on an index, only tables.=
</div><div><br></div><div>David J.</div><div><br></div><blockquote class=3D=
"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding=
-left:1ex">
</blockquote>

--000000000000c994c3062e5a8823--