EBR is also handy for some structural changes, but requires that the application uses editioning views and clean API packages (smart/pink DB).
But usually in reality you will encounter direct table access from applications. 😅

The biggest takeaway imho is to break deployments into small steps (expand/contract), even if they belong to the same feature. This makes it easier to "undo" stuff or even spot the problem.

Instead of undoing all prior steps, repairing the order, and then doing everything again,

you would just go forward by repairing the order of the remaining steps.

Also, rollback scripts in automated fashion will usually undo every part of the deployment, which might consist of several changes.
A forward fix could focus only on the part which went wrong. Maybe your script just tried to create a FK and the table was not yet existing due to wrong ordering.

So you could just run the failed update script again at a later point as the same version.

Whereas a forward fix is a new deployment (with usual syntax). So you could not run the original patch again and would have to create a new unique changeset to retry the original change.

I think from DB perspective (DDL/data state) the achieved end result of "undoing" is the same.
The difference is the execution with the tools (ie Flyway/Liquibase). Rollback scripts have a special syntax/tags and I think the tools then treat the initial script as "has not been applied".

Maybe proxy user would be a good option for the deployment user. Then it doesn't need powerful permissions, we can just grant connect through, maybe even limited to a specific role with only the necessary subset of the schema user permissions.

I often need to do DML as part of deployments, i.e. data migration, or data "fixes".
I think for audit it is important then that the developers don't have access to the schema users, but only the deployment user, so they can't "go around".

I totally agree with the application user. But could you explain how you would setup the deployment user? Wouldn't it need some CREATE/ALTER/DROP ANY privileges? Then it would be an almighty all-in-one schema-user. How is that better than the schema users directly?

Next time I will do that!

For now I came up with... *drumroll*

DBledore!

I even created a fancy dress as suggested by @kibeha.dk
I made sure to leave a lot of room for improvement.