public inbox for [email protected]
help / color / mirror / Atom feedFrom: Fujii Masao <[email protected]>
To: Nathan Bossart <[email protected]>
Cc: PostgreSQL Developers <[email protected]>
Subject: Re: pg_maintain and USAGE privilege on schema
Date: Wed, 10 Jul 2024 17:13:58 +0900
Message-ID: <[email protected]> (raw)
In-Reply-To: <ZotLWGf1O86n9XRx@nathan>
References: <[email protected]>
<ZotLWGf1O86n9XRx@nathan>
On 2024/07/08 11:13, Nathan Bossart wrote:
> On Mon, Jul 08, 2024 at 01:03:42AM +0900, Fujii Masao wrote:
>> I've noticed an issue with non-superusers who have the pg_maintain role.
>> When they run VACUUM on a specific table within a specific schema,
>> like "VACUUM mynsp.mytbl", it fails if they don't have the USAGE privilege
>> on the schema. For example, the error message logged is
>> "ERROR: permission denied for schema mynsp". However, running VACUUM
>> without specifying the table name, such as "VACUUM",
>> completes successfully and vacuums all tables, including those in schemas
>> where the user lacks the USAGE privilege.
>>
>> Is this behavior intentional?
>
> I'd consider it intentional because it matches the database owner behavior.
> If the database owner does not have USAGE on a schema, they'll similarly be
> unable to VACUUM a specific table in that schema while being able to VACUUM
> it via a database-wide command.
Yes, you're right.
> That's admittedly a little weird, but IMHO
> any changes in this area should apply to both pg_maintain and the database
> owner.
However, unlike the database owner, pg_maintain by definition should
have *all* the rights needed for maintenance tasks, including MAINTAIN
rights on tables and USAGE rights on schemas? ISTM that both
pg_read_all_data and pg_write_all_data roles are defined similarly,
with USAGE rights on all schemas. So, granting USAGE rights to
pg_maintain, but not the database owner, doesn't seem so odd to me.
>> I assumed that a pg_maintain user could run VACUUM on specific tables
>> in any schema without needing additional privileges. So, shouldn't
>> pg_maintain users be able to perform maintenance commands as if they have
>> USAGE rights on all schemas?
>
> It might be reasonable to give implicit USAGE privileges on all schemas
> during maintenance commands to pg_maintain roles. I would be a little
> hesitant to consider this v17 material, though.
That's a valid concern. I'd like hear more opinions about this.
> There are some other inconsistencies that predate MAINTAIN that I think we
> ought to clear up at some point. For example, the privilege checks for
> REINDEX work a bit differently than VACUUM, ANALYZE, and CLUSTER. I doubt
> that's causing anyone too much trouble in the field, but since we're
> grouping these commands together as "maintenance commands" now, it'd be
> nice to make them as consistent as possible.
+1
Regards,
--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION
view thread (5+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: pg_maintain and USAGE privilege on schema
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox