Clarification on Role Access Rights to Table Indexes

From: Ayush Vatsa <ayushvatsa1810(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Clarification on Role Access Rights to Table Indexes
Date: 2025-02-17 18:01:46
Message-ID: CACX+KaMz2ZoOojh0nQ6QNBYx8Ak1Dkoko=D4FSb80BYW+o8CHQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2025-02-17 18:17:16 Clarification on Role Access Rights to Table Indexes
Previous Message Tom Lane 2025-02-17 17:33:36 Re: Calling set-returning functions in a non-set-expecting context

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2025-02-17 18:08:29 Re: revamp row-security tracking
Previous Message Peter Geoghegan 2025-02-17 17:56:12 Re: Showing primitive index scan count in EXPLAIN ANALYZE (for skip scan and SAOP scans)