Horton

Re: Clarification on Role Access Rights to Table Indexes

Ayush Vatsa <ayushvatsa1810@gmail.com>

View thread View on postgr.es
From: Ayush Vatsa <ayushvatsa1810@gmail.com>
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
Date: 2025-02-17T18:39:33Z
Lists: pgsql-hackers, pgsql-general 
> 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

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.