I don’t do this kind of stuff anymore but if I did I would do something similar. Maybe add a before trigger that caused an error if no role was set. That way all connecting applications must set a user to use the db. Then put the database in charge of audit logs (who did what and when) and data access control with standard permissions and row level permissions. I’m sure you can balance that against an identity provider too.
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.
