On Mon, 2022-09-19 at 15:32 -0400, Robert Haas wrote: > One could take the view that the issue here is that > pg_read_all_settings shouldn't have the right to create objects in > the > first place, and that this INHERIT vs. SET ROLE distinction is just a > distraction. However, that would require accepting the idea that it's > possible for a role to lack privileges granted to PUBLIC, which also > sounds pretty unsatisfying. On the whole, I'm inclined to think it's > reasonable to suppose that if you want to grant a role to someone > without letting them create objects owned by that role, it should be > a > role that doesn't own any existing objects either. Essentially, > that's > legislating that predefined roles should be minimally privileged: > they > should hold the ability to do whatever it is that they are there to > do > (like read all settings) but not have any other privileges (like the > ability to do stuff to objects they own).
I like this approach -- the idea that you can create a role that can't own anything, can't create anything, and to which nobody else can "SET ROLE". Creating a "virtual" role like that feels much more declarative and easy to document: "this isn't a real user, it's just a collection of inheritable privileges". Even superusers couldn't "SET ROLE pg_read_all_settings" or "OWNER TO pg_signal_backend". I wouldn't call it "minimally privileged" (which feels wrong because it wouldn't even have privileges on PUBLIC, as you say); I'd just say that it's a type of role where those things just don't make sense. -- Jeff Davis PostgreSQL Contributor Team - AWS
