Horton

Re: Clarification on Role Access Rights to Table Indexes

Tom Lane <tgl@sss.pgh.pa.us>

View thread View on postgr.es
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ayush Vatsa <ayushvatsa1810@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Date: 2025-02-17T18:27:30Z
Lists: pgsql-hackers, pgsql-general 
Ayush Vatsa <ayushvatsa1810@gmail.com> writes:
> postgres=> SELECT pg_prewarm('pg_class_oid_index');
> ERROR:  permission denied for index pg_class_oid_index

You'd really have to take that up with the author of pg_prewarm.
It's not apparent to me why checking SQL access permissions is
the right mechanism for limiting use of pg_prewarm.  It seems
like ownership of the table would be more appropriate, or maybe
access to one of the built-in roles like pg_maintain.

> 1. Can a role have access rights to a table without having access to its
> index?

Indexes do not have access rights of their own, which is why
access rights are a poor gating mechanism for something that
needs to be applicable to indexes.  Ownership could work,
because we make indexes inherit their table's ownership.

			regards, tom lane

Commits

  1. Fix privilege checks for pg_prewarm() on indexes.

  2. Fix lookup code for REINDEX INDEX.

  3. Fix redefinition of typedef RangeVar.

  4. Fix lookups in pg_{clear,restore}_{attribute,relation}_stats().

  5. dblink: Avoid locking relation before privilege check.