Install script requires CREATEROLE, blocking non-superuser install even with superuser = false

[jnasbyupgrade]

Summary

cat_tools's install script requires the CREATEROLE privilege, which blocks non-superuser
installation even when superuser = false is set in cat_tools.control.

Details

The install script unconditionally runs:

CREATE ROLE cat_tools__usage NOLOGIN;

superuser = false is the standard PostgreSQL 13+ mechanism for allowing an extension to be
installed by a non-superuser role (distinct from, and safer than, trusted = true — it doesn't
grant any privilege elevation, the install script simply runs as whatever role called
CREATE EXTENSION). But because the script itself contains a CREATE ROLE, setting
superuser = false isn't sufficient on its own: CREATE EXTENSION cat_tools still fails for
any ordinary non-superuser role with:

ERROR:  permission denied to create role
DETAIL:  Only roles with the CREATEROLE attribute may create roles.

Reproduction

Confirmed directly: a plain NOINHERIT role, granted only CREATE privilege on the target
database (no other grants, no membership in any privileged role), logged in as itself
(see methodology note below), fails CREATE EXTENSION cat_tools exactly this way even with
superuser = false added to the control file.

Methodology note

Testing this requires a genuine non-superuser login, not SET ROLE/SET SESSION AUTHORIZATION from a superuser session. Role-creation privilege checks (this one included)
are based on session_user, not current_user, so testing via SET ROLE from a superuser
can give false negatives/positives specifically for checks like this one.

Same pattern elsewhere

This isn't unique to cat_tools — the same pattern shows up in two other extensions that
build on similar conventions:

• object_reference's install script runs CREATE ROLE object_reference__usage and
  CREATE ROLE object_reference__dependency
• test_factory's install script runs CREATE ROLE test_factory__owner

Both hit the identical CREATEROLE requirement for the same reason. Filing here since it's
the same root cause; a fix approach worked out for cat_tools would likely apply to those too.

Question for the maintainer

Does cat_tools__usage need to be created fresh by the extension's own install script every
time, or could it instead be created once out-of-band — e.g. by a deploy process that already
has CREATEROLE, separately from CREATE EXTENSION itself? Or is there a reason the role
creation needs to be self-contained in the install script that isn't obvious from the outside?
Not proposing a specific fix, just flagging the constraint and asking whether there's a reason
it's structured this way.