Horton

Clarification on Role Access Rights to Table Indexes

David G. Johnston <david.g.johnston@gmail.com>

View thread View on postgr.es
From: "David G. Johnston" <david.g.johnston@gmail.com>
To: Ayush Vatsa <ayushvatsa1810@gmail.com>
Cc: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Date: 2025-02-17T18:17:16Z
Lists: pgsql-hackers, pgsql-general 
On Monday, February 17, 2025, Ayush Vatsa <ayushvatsa1810@gmail.com> wrote:

> postgres=# CREATE ROLE alpha;
>
> CREATE ROLE
> postgres=# 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’t directly interact with indexes in PostgreSQL so this doesn’t
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.

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.