CREATE EXTENSION fails on PG16+ without a superuser (RDS/Aurora): must be able to SET ROLE "test_factory__owner"

[jnasbyupgrade]

Summary

CREATE EXTENSION test_factory fails on PostgreSQL 16+ when installed by a role that is
not a true superuser (e.g. AWS RDS / Aurora and other managed Postgres, where no
rolsuper role is available):

ERROR:  must be able to SET ROLE "test_factory__owner"

It works fine when installed by a real superuser (the usual local-dev case), which is why it
hasn't shown up before.

Root cause

sql/test_factory.sql (generated test_factory--0.5.0.sql) creates the owner role and then
switches to it:

DO $body$ BEGIN
    CREATE ROLE test_factory__owner;
EXCEPTION WHEN duplicate_object THEN NULL;
END $body$;
...
SET LOCAL ROLE test_factory__owner;

On PostgreSQL 16+, creating a role no longer automatically grants the creator a
SET-enabled membership in the new role — the automatic self-grant is governed by the
createrole_self_grant GUC, which defaults to empty. SET ROLE x is only permitted if the
current role is a member of x WITH the SET option, or is a real superuser (superusers
bypass the check).

• Local dev installs as a superuser → the check is bypassed → works.
• Managed Postgres (RDS/Aurora) has no true superuser → after CREATE ROLE, the installing
  role has ADMIN OPTION on the new role but not a SET-enabled membership → SET ROLE is
  rejected.

Reproduction

On PostgreSQL 16 or 17, as a non-superuser role with CREATEROLE (or membership that permits
role creation):

CREATE EXTENSION test_factory;
-- ERROR: must be able to SET ROLE "test_factory__owner"

Suggested fix

Immediately after creating test_factory__owner, grant it back to the installing role with the
SET option, so the subsequent SET ROLE is permitted. The creator holds ADMIN OPTION on the
role it just created, so the grant succeeds:

DO $body$ BEGIN
    CREATE ROLE test_factory__owner;
EXCEPTION WHEN duplicate_object THEN NULL;
END $body$;

-- PG16+: creating a role no longer implies SET-membership; grant it explicitly so
-- SET ROLE works without a superuser. Safe under a superuser install too.
GRANT test_factory__owner TO current_user WITH SET TRUE;

(WITH SET TRUE is PG16+ syntax. If older PostgreSQL must be supported, gate on
current_setting('server_version_num')::int >= 160000, since pre-16 GRANT ... TO already
confers the ability to SET ROLE.)

Verified: with this grant added, CREATE EXTENSION test_factory and
CREATE EXTENSION test_factory_pgtap CASCADE both succeed on PostgreSQL 17 managed Postgres
installed by a non-superuser role.

The same pattern likely applies to any other extension in this project that creates an owner
role and SET ROLEs to it during install.