public inbox for [email protected]
help / color / mirror / Atom feedClarification on Role Access Rights to Table Indexes
15+ messages / 5 participants
[nested] [flat]
* Clarification on Role Access Rights to Table Indexes
@ 2025-02-17 18:01 Ayush Vatsa <[email protected]>
2025-02-17 18:17 ` Clarification on Role Access Rights to Table Indexes David G. Johnston <[email protected]>
2025-02-17 18:27 ` Re: Clarification on Role Access Rights to Table Indexes Tom Lane <[email protected]>
2025-02-17 19:58 ` Re: Clarification on Role Access Rights to Table Indexes Laurenz Albe <[email protected]>
0 siblings, 3 replies; 15+ messages in thread
From: Ayush Vatsa @ 2025-02-17 18:01 UTC (permalink / raw)
To: [email protected]
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Clarification on Role Access Rights to Table Indexes
2025-02-17 18:01 Clarification on Role Access Rights to Table Indexes Ayush Vatsa <[email protected]>
@ 2025-02-17 18:17 ` David G. Johnston <[email protected]>
2 siblings, 0 replies; 15+ messages in thread
From: David G. Johnston @ 2025-02-17 18:17 UTC (permalink / raw)
To: Ayush Vatsa <[email protected]>; +Cc: [email protected] <[email protected]>
On Monday, February 17, 2025, Ayush Vatsa <[email protected]> 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.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Clarification on Role Access Rights to Table Indexes
2025-02-17 18:01 Clarification on Role Access Rights to Table Indexes Ayush Vatsa <[email protected]>
@ 2025-02-17 18:27 ` Tom Lane <[email protected]>
2025-02-17 18:39 ` Re: Clarification on Role Access Rights to Table Indexes Ayush Vatsa <[email protected]>
2025-02-17 18:43 ` Re: Clarification on Role Access Rights to Table Indexes David G. Johnston <[email protected]>
2 siblings, 2 replies; 15+ messages in thread
From: Tom Lane @ 2025-02-17 18:27 UTC (permalink / raw)
To: Ayush Vatsa <[email protected]>; +Cc: [email protected]
Ayush Vatsa <[email protected]> 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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Clarification on Role Access Rights to Table Indexes
2025-02-17 18:01 Clarification on Role Access Rights to Table Indexes Ayush Vatsa <[email protected]>
2025-02-17 18:27 ` Re: Clarification on Role Access Rights to Table Indexes Tom Lane <[email protected]>
@ 2025-02-17 18:39 ` Ayush Vatsa <[email protected]>
1 sibling, 0 replies; 15+ messages in thread
From: Ayush Vatsa @ 2025-02-17 18:39 UTC (permalink / raw)
To: Tom Lane <[email protected]>; David G. Johnston <[email protected]>; Robert Haas <[email protected]>; +Cc: [email protected]
> 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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Clarification on Role Access Rights to Table Indexes
2025-02-17 18:01 Clarification on Role Access Rights to Table Indexes Ayush Vatsa <[email protected]>
2025-02-17 18:27 ` Re: Clarification on Role Access Rights to Table Indexes Tom Lane <[email protected]>
@ 2025-02-17 18:43 ` David G. Johnston <[email protected]>
2025-02-17 19:13 ` Re: Clarification on Role Access Rights to Table Indexes Tom Lane <[email protected]>
1 sibling, 1 reply; 15+ messages in thread
From: David G. Johnston @ 2025-02-17 18:43 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Ayush Vatsa <[email protected]>; [email protected] <[email protected]>
On Monday, February 17, 2025, Tom Lane <[email protected]> wrote:
> Ayush Vatsa <[email protected]> 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.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Clarification on Role Access Rights to Table Indexes
2025-02-17 18:01 Clarification on Role Access Rights to Table Indexes Ayush Vatsa <[email protected]>
2025-02-17 18:27 ` Re: Clarification on Role Access Rights to Table Indexes Tom Lane <[email protected]>
2025-02-17 18:43 ` Re: Clarification on Role Access Rights to Table Indexes David G. Johnston <[email protected]>
@ 2025-02-17 19:13 ` Tom Lane <[email protected]>
2025-02-17 19:42 ` Re: Clarification on Role Access Rights to Table Indexes Ayush Vatsa <[email protected]>
0 siblings, 1 reply; 15+ messages in thread
From: Tom Lane @ 2025-02-17 19:13 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Ayush Vatsa <[email protected]>; [email protected] <[email protected]>
"David G. Johnston" <[email protected]> writes:
> On Monday, February 17, 2025, Tom Lane <[email protected]> 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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Clarification on Role Access Rights to Table Indexes
2025-02-17 18:01 Clarification on Role Access Rights to Table Indexes Ayush Vatsa <[email protected]>
2025-02-17 18:27 ` Re: Clarification on Role Access Rights to Table Indexes Tom Lane <[email protected]>
2025-02-17 18:43 ` Re: Clarification on Role Access Rights to Table Indexes David G. Johnston <[email protected]>
2025-02-17 19:13 ` Re: Clarification on Role Access Rights to Table Indexes Tom Lane <[email protected]>
@ 2025-02-17 19:42 ` Ayush Vatsa <[email protected]>
2025-02-17 19:57 ` Re: Clarification on Role Access Rights to Table Indexes Tom Lane <[email protected]>
0 siblings, 1 reply; 15+ messages in thread
From: Ayush Vatsa @ 2025-02-17 19:42 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: David G. Johnston <[email protected]>; PostgreSQL Hackers <[email protected]>
> As it stands, a superuser can prewarm an index (because she bypasses all
> privilege checks including this one), but nobody else can.
That's not fully true. Any role can prewarm an index if the role has the
correct privileges.
postgres=# GRANT CREATE ON SCHEMA PUBLIC TO alpha;
GRANT
postgres=# SET ROLE alpha;
SET
postgres=> CREATE TABLE tab(id INT);
CREATE TABLE
postgres=> CREATE INDEX tab_idx ON tab(id);
CREATE INDEX
postgres=> SELECT pg_prewarm('tab_idx');
pg_prewarm
------------
1
(1 row)
Don't know what stopped it from prewarming the catalog table index.
Maybe it's checking who is the owner of the table. Although in code
it's just checking the ACL_SELECT [1]. I will be debugging more here
and maybe create a patch for the same.
postgres=# RESET ROLE;
RESET
postgres=# CREATE TABLE superuser_tab(id INT);
CREATE TABLE
postgres=# CREATE INDEX idx_superuser_tab ON superuser_tab(id);
CREATE INDEX
postgres=# GRANT SELECT ON superuser_tab TO alpha;
GRANT
postgres=# SET ROLE alpha;
SET
postgres=> SELECT pg_prewarm('superuser_tab');
pg_prewarm
------------
0
(1 row)
postgres=> SELECT pg_prewarm('idx_superuser_tab');
ERROR: permission denied for index idx_superuser_tab
postgres=> RESET ROLE;
RESET
postgres=# ALTER TABLE superuser_tab OWNER TO alpha;
ALTER TABLE
postgres=# SET ROLE alpha;
SET
postgres=> SELECT pg_prewarm('idx_superuser_tab');
pg_prewarm
------------
1
(1 row)
But I agree we should just check the table privileges even in the case of
indexes to decide whether to prewarm or not, as
*indexes don't have any privilegesof their own.*
> I think -hackers would be the appropriate location for that.
I am shifting this to -hackers mailing list instead of general.
[1]
https://github.com/postgres/postgres/blob/master/contrib/pg_prewarm/pg_prewarm.c#L108-L110
Regards,
Ayush Vatsa
SDE AWS
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Clarification on Role Access Rights to Table Indexes
2025-02-17 18:01 Clarification on Role Access Rights to Table Indexes Ayush Vatsa <[email protected]>
2025-02-17 18:27 ` Re: Clarification on Role Access Rights to Table Indexes Tom Lane <[email protected]>
2025-02-17 18:43 ` Re: Clarification on Role Access Rights to Table Indexes David G. Johnston <[email protected]>
2025-02-17 19:13 ` Re: Clarification on Role Access Rights to Table Indexes Tom Lane <[email protected]>
2025-02-17 19:42 ` Re: Clarification on Role Access Rights to Table Indexes Ayush Vatsa <[email protected]>
@ 2025-02-17 19:57 ` Tom Lane <[email protected]>
2025-02-17 21:34 ` Re: Clarification on Role Access Rights to Table Indexes Robert Haas <[email protected]>
0 siblings, 1 reply; 15+ messages in thread
From: Tom Lane @ 2025-02-17 19:57 UTC (permalink / raw)
To: Ayush Vatsa <[email protected]>; +Cc: David G. Johnston <[email protected]>; PostgreSQL Hackers <[email protected]>
Ayush Vatsa <[email protected]> writes:
>> As it stands, a superuser can prewarm an index (because she bypasses all
>> privilege checks including this one), but nobody else can.
> That's not fully true. Any role can prewarm an index if the role has the
> correct privileges.
Ah, right. An index will have null pg_class.relacl, which'll be
interpreted as "owner has all rights", so it will work for the
table owner too. Likely this explains the lack of prior complaints.
It's still a poor design IMO.
regards, tom lane
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Clarification on Role Access Rights to Table Indexes
2025-02-17 18:01 Clarification on Role Access Rights to Table Indexes Ayush Vatsa <[email protected]>
2025-02-17 18:27 ` Re: Clarification on Role Access Rights to Table Indexes Tom Lane <[email protected]>
2025-02-17 18:43 ` Re: Clarification on Role Access Rights to Table Indexes David G. Johnston <[email protected]>
2025-02-17 19:13 ` Re: Clarification on Role Access Rights to Table Indexes Tom Lane <[email protected]>
2025-02-17 19:42 ` Re: Clarification on Role Access Rights to Table Indexes Ayush Vatsa <[email protected]>
2025-02-17 19:57 ` Re: Clarification on Role Access Rights to Table Indexes Tom Lane <[email protected]>
@ 2025-02-17 21:34 ` Robert Haas <[email protected]>
2025-02-17 21:45 ` Re: Clarification on Role Access Rights to Table Indexes Ayush Vatsa <[email protected]>
0 siblings, 1 reply; 15+ messages in thread
From: Robert Haas @ 2025-02-17 21:34 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Ayush Vatsa <[email protected]>; David G. Johnston <[email protected]>; PostgreSQL Hackers <[email protected]>
On Mon, Feb 17, 2025 at 2:57 PM Tom Lane <[email protected]> wrote:
> Ayush Vatsa <[email protected]> writes:
> >> As it stands, a superuser can prewarm an index (because she bypasses all
> >> privilege checks including this one), but nobody else can.
>
> > That's not fully true. Any role can prewarm an index if the role has the
> > correct privileges.
>
> Ah, right. An index will have null pg_class.relacl, which'll be
> interpreted as "owner has all rights", so it will work for the
> table owner too. Likely this explains the lack of prior complaints.
> It's still a poor design IMO.
I'm not sure if I'd call that a "design". Sounds like I just made a
mistake here.
--
Robert Haas
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Clarification on Role Access Rights to Table Indexes
2025-02-17 18:01 Clarification on Role Access Rights to Table Indexes Ayush Vatsa <[email protected]>
2025-02-17 18:27 ` Re: Clarification on Role Access Rights to Table Indexes Tom Lane <[email protected]>
2025-02-17 18:43 ` Re: Clarification on Role Access Rights to Table Indexes David G. Johnston <[email protected]>
2025-02-17 19:13 ` Re: Clarification on Role Access Rights to Table Indexes Tom Lane <[email protected]>
2025-02-17 19:42 ` Re: Clarification on Role Access Rights to Table Indexes Ayush Vatsa <[email protected]>
2025-02-17 19:57 ` Re: Clarification on Role Access Rights to Table Indexes Tom Lane <[email protected]>
2025-02-17 21:34 ` Re: Clarification on Role Access Rights to Table Indexes Robert Haas <[email protected]>
@ 2025-02-17 21:45 ` Ayush Vatsa <[email protected]>
2025-02-17 22:02 ` Re: Clarification on Role Access Rights to Table Indexes Tom Lane <[email protected]>
0 siblings, 1 reply; 15+ messages in thread
From: Ayush Vatsa @ 2025-02-17 21:45 UTC (permalink / raw)
To: Robert Haas <[email protected]>; +Cc: Tom Lane <[email protected]>; David G. Johnston <[email protected]>; PostgreSQL Hackers <[email protected]>
> I'm not sure if I'd call that a "design". Sounds like I just made a
> mistake here.
Thanks Robert for confirming, let me submit a patch to fix the same.
Regards
Ayush Vatsa
ADE AWS
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Clarification on Role Access Rights to Table Indexes
2025-02-17 18:01 Clarification on Role Access Rights to Table Indexes Ayush Vatsa <[email protected]>
2025-02-17 18:27 ` Re: Clarification on Role Access Rights to Table Indexes Tom Lane <[email protected]>
2025-02-17 18:43 ` Re: Clarification on Role Access Rights to Table Indexes David G. Johnston <[email protected]>
2025-02-17 19:13 ` Re: Clarification on Role Access Rights to Table Indexes Tom Lane <[email protected]>
2025-02-17 19:42 ` Re: Clarification on Role Access Rights to Table Indexes Ayush Vatsa <[email protected]>
2025-02-17 19:57 ` Re: Clarification on Role Access Rights to Table Indexes Tom Lane <[email protected]>
2025-02-17 21:34 ` Re: Clarification on Role Access Rights to Table Indexes Robert Haas <[email protected]>
2025-02-17 21:45 ` Re: Clarification on Role Access Rights to Table Indexes Ayush Vatsa <[email protected]>
@ 2025-02-17 22:02 ` Tom Lane <[email protected]>
2025-02-17 22:17 ` Re: Clarification on Role Access Rights to Table Indexes David G. Johnston <[email protected]>
0 siblings, 1 reply; 15+ messages in thread
From: Tom Lane @ 2025-02-17 22:02 UTC (permalink / raw)
To: Ayush Vatsa <[email protected]>; +Cc: Robert Haas <[email protected]>; David G. Johnston <[email protected]>; PostgreSQL Hackers <[email protected]>
Ayush Vatsa <[email protected]> writes:
> Thanks Robert for confirming, let me submit a patch to fix the same.
Well, the first thing you need is consensus on what the behavior
should be instead.
I have a very vague recollection that we concluded that SELECT
privilege was a reasonable check because if you have that you
could manually prewarm by reading the table. That would lead
to the conclusion that the minimal fix is to look at the owning
table's privileges instead of the index's own privileges.
Or we could switch to using ownership, which'd keep the code
simple but some users might complain it's too restrictive.
While I mentioned built-in roles earlier, I now think those mostly
carry more privilege than should be required here, given the analogy
to SELECT.
regards, tom lane
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Clarification on Role Access Rights to Table Indexes
2025-02-17 18:01 Clarification on Role Access Rights to Table Indexes Ayush Vatsa <[email protected]>
2025-02-17 18:27 ` Re: Clarification on Role Access Rights to Table Indexes Tom Lane <[email protected]>
2025-02-17 18:43 ` Re: Clarification on Role Access Rights to Table Indexes David G. Johnston <[email protected]>
2025-02-17 19:13 ` Re: Clarification on Role Access Rights to Table Indexes Tom Lane <[email protected]>
2025-02-17 19:42 ` Re: Clarification on Role Access Rights to Table Indexes Ayush Vatsa <[email protected]>
2025-02-17 19:57 ` Re: Clarification on Role Access Rights to Table Indexes Tom Lane <[email protected]>
2025-02-17 21:34 ` Re: Clarification on Role Access Rights to Table Indexes Robert Haas <[email protected]>
2025-02-17 21:45 ` Re: Clarification on Role Access Rights to Table Indexes Ayush Vatsa <[email protected]>
2025-02-17 22:02 ` Re: Clarification on Role Access Rights to Table Indexes Tom Lane <[email protected]>
@ 2025-02-17 22:17 ` David G. Johnston <[email protected]>
2025-02-18 15:13 ` Re: Clarification on Role Access Rights to Table Indexes Robert Haas <[email protected]>
0 siblings, 1 reply; 15+ messages in thread
From: David G. Johnston @ 2025-02-17 22:17 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Ayush Vatsa <[email protected]>; Robert Haas <[email protected]>; PostgreSQL Hackers <[email protected]>
On Mon, Feb 17, 2025 at 3:02 PM Tom Lane <[email protected]> wrote:
> Ayush Vatsa <[email protected]> writes:
> > Thanks Robert for confirming, let me submit a patch to fix the same.
>
> Well, the first thing you need is consensus on what the behavior
> should be instead.
>
> I have a very vague recollection that we concluded that SELECT
> privilege was a reasonable check because if you have that you
> could manually prewarm by reading the table. That would lead
> to the conclusion that the minimal fix is to look at the owning
> table's privileges instead of the index's own privileges.
>
I feel like if you can blow up the cache by loading an entire table into
memory with just select privilege on the table we should be ok with
allowing the same person to name an index on the same table and load it
into the cache too.
David J.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Clarification on Role Access Rights to Table Indexes
2025-02-17 18:01 Clarification on Role Access Rights to Table Indexes Ayush Vatsa <[email protected]>
2025-02-17 18:27 ` Re: Clarification on Role Access Rights to Table Indexes Tom Lane <[email protected]>
2025-02-17 18:43 ` Re: Clarification on Role Access Rights to Table Indexes David G. Johnston <[email protected]>
2025-02-17 19:13 ` Re: Clarification on Role Access Rights to Table Indexes Tom Lane <[email protected]>
2025-02-17 19:42 ` Re: Clarification on Role Access Rights to Table Indexes Ayush Vatsa <[email protected]>
2025-02-17 19:57 ` Re: Clarification on Role Access Rights to Table Indexes Tom Lane <[email protected]>
2025-02-17 21:34 ` Re: Clarification on Role Access Rights to Table Indexes Robert Haas <[email protected]>
2025-02-17 21:45 ` Re: Clarification on Role Access Rights to Table Indexes Ayush Vatsa <[email protected]>
2025-02-17 22:02 ` Re: Clarification on Role Access Rights to Table Indexes Tom Lane <[email protected]>
2025-02-17 22:17 ` Re: Clarification on Role Access Rights to Table Indexes David G. Johnston <[email protected]>
@ 2025-02-18 15:13 ` Robert Haas <[email protected]>
2025-02-18 16:02 ` Re: Clarification on Role Access Rights to Table Indexes Tom Lane <[email protected]>
0 siblings, 1 reply; 15+ messages in thread
From: Robert Haas @ 2025-02-18 15:13 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Tom Lane <[email protected]>; Ayush Vatsa <[email protected]>; PostgreSQL Hackers <[email protected]>
On Mon, Feb 17, 2025 at 5:18 PM David G. Johnston
<[email protected]> wrote:
>> I have a very vague recollection that we concluded that SELECT
>> privilege was a reasonable check because if you have that you
>> could manually prewarm by reading the table. That would lead
>> to the conclusion that the minimal fix is to look at the owning
>> table's privileges instead of the index's own privileges.
>
> I feel like if you can blow up the cache by loading an entire table into memory with just select privilege on the table we should be ok with allowing the same person to name an index on the same table and load it into the cache too.
+1.
--
Robert Haas
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Clarification on Role Access Rights to Table Indexes
2025-02-17 18:01 Clarification on Role Access Rights to Table Indexes Ayush Vatsa <[email protected]>
2025-02-17 18:27 ` Re: Clarification on Role Access Rights to Table Indexes Tom Lane <[email protected]>
2025-02-17 18:43 ` Re: Clarification on Role Access Rights to Table Indexes David G. Johnston <[email protected]>
2025-02-17 19:13 ` Re: Clarification on Role Access Rights to Table Indexes Tom Lane <[email protected]>
2025-02-17 19:42 ` Re: Clarification on Role Access Rights to Table Indexes Ayush Vatsa <[email protected]>
2025-02-17 19:57 ` Re: Clarification on Role Access Rights to Table Indexes Tom Lane <[email protected]>
2025-02-17 21:34 ` Re: Clarification on Role Access Rights to Table Indexes Robert Haas <[email protected]>
2025-02-17 21:45 ` Re: Clarification on Role Access Rights to Table Indexes Ayush Vatsa <[email protected]>
2025-02-17 22:02 ` Re: Clarification on Role Access Rights to Table Indexes Tom Lane <[email protected]>
2025-02-17 22:17 ` Re: Clarification on Role Access Rights to Table Indexes David G. Johnston <[email protected]>
2025-02-18 15:13 ` Re: Clarification on Role Access Rights to Table Indexes Robert Haas <[email protected]>
@ 2025-02-18 16:02 ` Tom Lane <[email protected]>
0 siblings, 0 replies; 15+ messages in thread
From: Tom Lane @ 2025-02-18 16:02 UTC (permalink / raw)
To: Robert Haas <[email protected]>; +Cc: David G. Johnston <[email protected]>; Ayush Vatsa <[email protected]>; PostgreSQL Hackers <[email protected]>
Robert Haas <[email protected]> writes:
> On Mon, Feb 17, 2025 at 5:18 PM David G. Johnston
> <[email protected]> wrote:
>>> I have a very vague recollection that we concluded that SELECT
>>> privilege was a reasonable check because if you have that you
>>> could manually prewarm by reading the table. That would lead
>>> to the conclusion that the minimal fix is to look at the owning
>>> table's privileges instead of the index's own privileges.
>> I feel like if you can blow up the cache by loading an entire table into memory with just select privilege on the table we should be ok with allowing the same person to name an index on the same table and load it into the cache too.
> +1.
Is that a +1 for the specific design of "check SELECT on the index's
table", or just a +1 for changing something here?
regards, tom lane
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Clarification on Role Access Rights to Table Indexes
2025-02-17 18:01 Clarification on Role Access Rights to Table Indexes Ayush Vatsa <[email protected]>
@ 2025-02-17 19:58 ` Laurenz Albe <[email protected]>
2 siblings, 0 replies; 15+ messages in thread
From: Laurenz Albe @ 2025-02-17 19:58 UTC (permalink / raw)
To: Ayush Vatsa <[email protected]>; [email protected]
On Mon, 2025-02-17 at 23:31 +0530, Ayush Vatsa wrote:
> 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?
I have seen a complaint about this bug before:
https://dba.stackexchange.com/a/344603/176905
Yours,
Laurenz Albe
--
*E-Mail Disclaimer*
Der Inhalt dieser E-Mail ist ausschliesslich fuer den
bezeichneten Adressaten bestimmt. Wenn Sie nicht der vorgesehene Adressat
dieser E-Mail oder dessen Vertreter sein sollten, so beachten Sie bitte,
dass jede Form der Kenntnisnahme, Veroeffentlichung, Vervielfaeltigung oder
Weitergabe des Inhalts dieser E-Mail unzulaessig ist. Wir bitten Sie, sich
in diesem Fall mit dem Absender der E-Mail in Verbindung zu setzen.
*CONFIDENTIALITY NOTICE & DISCLAIMER
*This message and any attachment are
confidential and may be privileged or otherwise protected from disclosure
and solely for the use of the person(s) or entity to whom it is intended.
If you have received this message in error and are not the intended
recipient, please notify the sender immediately and delete this message and
any attachment from your system. If you are not the intended recipient, be
advised that any use of this message is prohibited and may be unlawful, and
you must not copy this message or attachment or disclose the contents to
any other person.
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2025-02-18 16:02 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-02-17 18:01 Clarification on Role Access Rights to Table Indexes Ayush Vatsa <[email protected]>
2025-02-17 18:17 ` David G. Johnston <[email protected]>
2025-02-17 18:27 ` Tom Lane <[email protected]>
2025-02-17 18:39 ` Ayush Vatsa <[email protected]>
2025-02-17 18:43 ` David G. Johnston <[email protected]>
2025-02-17 19:13 ` Tom Lane <[email protected]>
2025-02-17 19:42 ` Ayush Vatsa <[email protected]>
2025-02-17 19:57 ` Tom Lane <[email protected]>
2025-02-17 21:34 ` Robert Haas <[email protected]>
2025-02-17 21:45 ` Ayush Vatsa <[email protected]>
2025-02-17 22:02 ` Tom Lane <[email protected]>
2025-02-17 22:17 ` David G. Johnston <[email protected]>
2025-02-18 15:13 ` Robert Haas <[email protected]>
2025-02-18 16:02 ` Tom Lane <[email protected]>
2025-02-17 19:58 ` Laurenz Albe <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox