Re: CREATE ROLE bug?

On Wed, Jan 25, 2023 at 8:29 AM Bruce Momjian <bruce@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=oKei1Dkw@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