sql: update CREATEROLE privileges to match PG 16 changes

Postgres 16 will include [this change](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=cf5eb37c5ee0cc54c80d95c1695d7fca1f7c68cb):

> Restrict the privileges of CREATEROLE users.
>
> Previously, CREATEROLE users were permitted to make nearly arbitrary
> changes to roles that they didn't create, with certain exceptions,
> particularly superuser roles. Instead, allow CREATEROLE users to make such
> changes to roles for which they possess ADMIN OPTION, and to
> grant membership only in roles for which they possess ADMIN OPTION.
>
> When a CREATEROLE user who is not a superuser creates a role, grant
> ADMIN OPTION on the newly-created role to the creator, so that they
> can administer roles they create or for which they have been given
> privileges.
>
> With these changes, CREATEROLE users still have very significant
> powers that unprivileged users do not receive: they can alter, rename,
> drop, comment on, change the password for, and change security labels
> on roles. However, they can now do these things only for roles for
> which they possess appropriate privileges, rather than all
> non-superuser roles; moreover, they cannot grant a role such as
> pg_execute_server_program unless they themselves possess it.

This issue can be used for us to discuss the change and implement it. This has the potential to be disruptive to users, so we should be certain if we do decide to implement this change.

There's an extensive discussion of this change in the [Postgres mailing list](https://www.postgresql.org/message-id/flat/CA%2BTgmobN59ct%2BEmmz6ig1Nua2Q-_o%3Dr6DSD98KfU53kctq_kQw%40mail.gmail.com#04b136bbfca4dea42d8470f0696f6564), since making a good default experience had much debate.

Jira issue: CRDB-24081

Epic CRDB-60816