pg_maintain and USAGE privilege on schema

From: Fujii Masao <masao(dot)fujii(at)oss(dot)nttdata(dot)com>
To: PostgreSQL Developers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: pg_maintain and USAGE privilege on schema
Date: 2024-07-07 16:03:42
Message-ID: 56596b81-088f-4c0c-9a88-b5f27a7a62e9@oss.nttdata.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tomas Vondra 2024-07-07 16:10:49 Re: Parallel CREATE INDEX for GIN indexes
Previous Message Noah Misch 2024-07-07 14:49:44 Re: Confine vacuum skip logic to lazy_scan_skip