Message-ID: <e4462067-96ac-4c52-8402-d8de54c4e5d2@oss.nttdata.com>
Date: Wed, 10 Jul 2024 17:13:58 +0900
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: pg_maintain and USAGE privilege on schema
To: Nathan Bossart <nathandbossart@gmail.com>
Cc: PostgreSQL Developers <pgsql-hackers@lists.postgresql.org>
References: <56596b81-088f-4c0c-9a88-b5f27a7a62e9@oss.nttdata.com>
 <ZotLWGf1O86n9XRx@nathan>
Content-Language: en-US
From: Fujii Masao <masao.fujii@oss.nttdata.com>
In-Reply-To: <ZotLWGf1O86n9XRx@nathan>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/e4462067-96ac-4c52-8402-d8de54c4e5d2%40oss.nttdata.com>
Precedence: bulk


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