Postgres Pro
Downloads
Home   >   mailing lists

Thread: Clarification on Role Access Rights to Table Indexes

Clarification on Role Access Rights to Table Indexes

From
"David G. Johnston"
Date:
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.

Re: Clarification on Role Access Rights to Table Indexes

From
Tom Lane
Date:
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

Re: Clarification on Role Access Rights to Table Indexes

From
Ayush Vatsa
Date:
> 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

Re: Clarification on Role Access Rights to Table Indexes

From
"David G. Johnston"
Date:
On Monday, February 17, 2025, Tom Lane <tgl@sss.pgh.pa.us> wrote:
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.

This is our contrib module so this seems like the expected place to ask such a question.  It’s neither a bug nor a topic for -hackers.  FTR, Robert Haas is the author from 2013.  Not sure he monitors -general though.

David J.

Re: Clarification on Role Access Rights to Table Indexes

From
Tom Lane
Date:
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Monday, February 17, 2025, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> You'd really have to take that up with the author of pg_prewarm.

> This is our contrib module so this seems like the expected place to ask
> such a question.  It’s neither a bug nor a topic for -hackers.  FTR, Robert
> Haas is the author from 2013.  Not sure he monitors -general though.

Ah, you are right, I was thinking it was a third-party extension.

If we're talking about changing the behavior of a contrib module,
I think -hackers would be the appropriate location for that.
And it does seem like this deserves a fresh look.  As it stands,
a superuser can prewarm an index (because she bypasses all
privilege checks including this one), but nobody else can.
Seems weird.

            regards, tom lane