Yes, exactly this. You can even do the user creation with default privileges as part of the transaction setup, if absolutely necessary (ie, create role if not exists…, set role …). Typically the audit is a table trigger so you can fail at that point, or even add a created_by column that looks up the role and raises an error if it’s not set (technically the role would always be set to the application’s db access role, so the check would be that the current role is a valid end user role).

I mean there are more and less efficient ways to do it, but why have a separate context setting for “user” when the (Postgres, at least) database has such great support for roles. I do find it bewildering that our tools ignore this by default.