public inbox for [email protected]  

help / color / mirror / Atom feed

From: Nathan Bossart <[email protected]>
To: Fujii Masao <[email protected]>
Cc: PostgreSQL Developers <[email protected]>
Subject: Re: pg_maintain and USAGE privilege on schema
Date: Sun, 7 Jul 2024 21:13:44 -0500
Message-ID: <ZotLWGf1O86n9XRx@nathan> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

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.  That's admittedly a little weird, but IMHO
any changes in this area should apply to both pg_maintain and the database
owner.

> 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.

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.

-- 
nathan

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: <ZotLWGf1O86n9XRx@nathan>

* 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