On Wed, Jan 25, 2023 at 8:29 AM Bruce Momjian <br...@momjian.us> wrote: > This works in PG 15: > > CREATE ROLE service CREATEROLE; > CREATE ROLE service1 WITH LOGIN IN ROLE service; > SET SESSION AUTHORIZATION service; > CREATE ROLE service2 WITH LOGIN IN ROLE service; > > but generates an error in git master: > > CREATE ROLE service CREATEROLE; > CREATE ROLE service1 WITH LOGIN IN ROLE service; > SET SESSION AUTHORIZATION service; > CREATE ROLE service2 WITH LOGIN IN ROLE service; > --> ERROR: must have admin option on role "service" > > If I make 'service' a superuser, it works: > > CREATE ROLE service SUPERUSER; > CREATE ROLE service1 WITH LOGIN IN ROLE service; > SET SESSION AUTHORIZATION service; > CREATE ROLE service2 WITH LOGIN IN ROLE service; > > It is probably related to this discussion and change: > > > https://www.postgresql.org/message-id/flat/CA+TgmobGds7oefDjZUY+k_J7p1sS=pTq3sZ060qdb=okei1...@mail.gmail.com > > I am not sure if the behavior is wrong, the error message is wrong, or > it is working as expected.
It is indeed related to that discussion and change. In existing released branches, a CREATEROLE user can make any role a member of any other role even if they have no rights at all with respect to that role. This means that a CREATEROLE user can create a new user in the pg_execute_server_programs group even though they have no access to it. That allows any CREATEROLE user to take over the OS account, and thus also superuser. In master, the rules have been tightened up. CREATEROLE no longer exempts you from the usual permission checks about adding a user to a group. This means that a CREATEROLE user now needs the same permissions to add a user to a group as any other user would need, i.e. ADMIN OPTION on the group. In your example, the "service" user has CREATEROLE and is therefore entitled to create new roles. However, "service" can only add those new roles to groups for which "service" possesses ADMIN OPTION. And "service" does not have ADMIN OPTION on itself, because no role ever possesses ADMIN OPTION on itself. I wrote a blog about this yesterday, which may or may not be of help: http://rhaas.blogspot.com/2023/01/surviving-without-superuser-coming-to.html I think that the new behavior will surprise some people, as it has surprised you, and it will take some time to get used to. However, I also think that the changes are absolutely essential. We've been shipping major releases for years and just pretending that it's OK that having CREATEROLE lets you take over the OS account and the superuser account. That's a security hole -- and not a subtle one. I could have easily exploited it as a teenager. My goals in doing this project were to (1) fix the security holes, (2) otherwise change as little about the behavior of CREATEROLE as possible, and (3) make CREATEROLE do something useful. We could have accomplished the first goal by just removing CREATEROLE or making it not do anything at all, but meeting the second and third goals at the same time require letting CREATEROLE continue to work but putting just enough restrictions on its power to keep it from being used as a privilege-escalation attack. I hope that what's been committed accomplishes that goal. -- Robert Haas EDB: http://www.enterprisedb.com
