Thread: pg_maintain and USAGE privilege on schema
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
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
On Mon, Jul 08, 2024 at 01:03:42AM +0900, Fujii Masao wrote: > 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. If we don't proceed with the implicit-USAGE-privilege idea, +1 for documenting. -- nathan
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
On Wed, Jul 10, 2024 at 05:13:58PM +0900, Fujii Masao wrote: > 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. It doesn't seem so odd to me, either. But there are other things that could prevent a role with privileges of pg_maintain from being able to VACUUM a table. For example, the role might not have LOGIN, or it might not have CONNECT on the database. I think the argument for giving pg_maintain roles implicit USAGE on all schemas for only maintenance commands is that we already do that in some cases (e.g., a database-wide VACUUM). > I'd like hear more opinions about this. +1 -- nathan
On Wed, 2024-07-10 at 17:13 +0900, Fujii Masao wrote:
> ISTM that both
> pg_read_all_data and pg_write_all_data roles are defined similarly,
> with USAGE rights on all schemas.
I'm not so sure that was a great idea to begin with. If you create a
private schema with a SECURITY DEFINER function in it, it's a bit odd
to allow someone with pg_read_all_data to execute it. Granted, that's
documented behavior, but I'm not sure the privileges should be bundled
in that fashion.
> > It might be reasonable to give implicit USAGE privileges on all
> > schemas
> > during maintenance commands to pg_maintain roles.
That's an even more specific exception: having USAGE only in the
context of a maintenance command. I think that's a new concept, right?
Regards,
Jeff Davis
On Wed, Jul 10, 2024 at 12:29:00PM -0700, Jeff Davis wrote: >> > It might be reasonable to give implicit USAGE privileges on all >> > schemas >> > during maintenance commands to pg_maintain roles. > > That's an even more specific exception: having USAGE only in the > context of a maintenance command. I think that's a new concept, right? AFAIK yes. -- nathan