Andrew Gierth <andrew@tao11.riddles.org.uk> writes: > "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes: > Tom> Meh. If the things aren't actually roles, I think this'd just add > Tom> confusion. Or were you proposing to implement them as roles? I'm > Tom> not sure if that would be practical in every case.
> In fact my original suggestion when this idea was discussed on IRC was > to remove the current superuser flag and turn it into a role; but the > issue then is that role membership is inherited and superuserness > currently isn't, so that's a more intrusive change.
To cover the proposed functionality, you'd still need some way to select not-superuser. So I don't think this fully answers the need even if we wanted to do it.
Sorry- why do we need that..? The first match for a pg_hba line wins, so you can define all the access methods that superuser accounts are allowed to use first, then a “reject” line for superuser accounts, and then whatever else you want after that.
More generally, allowing inheritance of superuser scares me a bit from a security standpoint. I wouldn't mind turning all the other legacy role properties into grantable roles, but I *like* the fact that that one is special.
Requiring an extra “set role whatever;” is good to make sure the user really understands they’re running as superuser, but it doesn’t really improve actual security at all since there’s no way to require a password or anything. That superuser-ness isn’t inherited but membership in the “postgres” or other role-that-owns-everything role is actually strikes me as less than ideal... the whole allow system table mods thing kinda helps with that since you need that extra step to actually change most things but it’s still not great imv. I can’t get too excited about trying to improve this though since I’d expect material changes to improve security to be beat back with backwards incompatibility concerns.