Re: pg_maintain and USAGE privilege on schema

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