Message-ID: <3f4c6e5ce54d8871ab915dc4f5c270a940f628d6.camel@j-davis.com>
Subject: Re: pg_maintain and USAGE privilege on schema
From: Jeff Davis <pgsql@j-davis.com>
To: Fujii Masao <masao.fujii@oss.nttdata.com>, Nathan Bossart
	 <nathandbossart@gmail.com>
Cc: PostgreSQL Developers <pgsql-hackers@lists.postgresql.org>
Date: Wed, 10 Jul 2024 12:29:00 -0700
In-Reply-To: <e4462067-96ac-4c52-8402-d8de54c4e5d2@oss.nttdata.com>
References: <56596b81-088f-4c0c-9a88-b5f27a7a62e9@oss.nttdata.com>
	 <ZotLWGf1O86n9XRx@nathan>
	 <e4462067-96ac-4c52-8402-d8de54c4e5d2@oss.nttdata.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.44.4-0ubuntu2 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/3f4c6e5ce54d8871ab915dc4f5c270a940f628d6.camel%40j-davis.com>
Precedence: bulk

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