Message-ID: <56596b81-088f-4c0c-9a88-b5f27a7a62e9@oss.nttdata.com>
Date: Mon, 8 Jul 2024 01:03:42 +0900
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
To: PostgreSQL Developers <pgsql-hackers@lists.postgresql.org>
Content-Language: en-US
From: Fujii Masao <masao.fujii@oss.nttdata.com>
Subject: pg_maintain and USAGE privilege on schema
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/56596b81-088f-4c0c-9a88-b5f27a7a62e9%40oss.nttdata.com>
Precedence: bulk

Hi,

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?

This issue also affects other maintenance commands covered by pg_maintain.

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?

If this has already been discussed and the current behavior is deemed proper,
I'm sorry for bringing it up again. Even in that case, it would be helpful
to document that USAGE privilege on the schema may be necessary in addition
to pg_maintain to perform the maintenance command.

Regards,

-- 
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION