security - Login system using physical postgres users not "logical" users -

i'm building application has conform sox auditing requirements. 1 of these, inserts, updates , deletes (but delete can ignore), need leave trail difficult, if not impossible standard user (or non-dba) change.

this means, need enforce auditing @ database level via triggers on insert, update , delete.

my problem is; webapp... typical design pattern store users "logical", example; in "users" table. need, application run logged in user after initial login.

my thinking (which not best) following:

1. load login page via standard username (webapp)
2. check table called "stored_users" logical username/password.
3. if enter correct user/pass; retrieve db username, generate session password (stored in kvsession on redis), update user on postgres db , login it.
4. after defined time of inactivity, destroy password session, reset db password user , log them out.

does sound safe way ensure following?

1. my users using postgres users; can enforce triggers via current_user etc..
2. security regenerating postgres user password random, temporary password

i'd hear others have on matter; can't find on google (or i'm not searching right terms). seems prevailing mindset user logins store them logical records , have global connection user.

to achieve goal

all inserts, updates , deletes, need leave trail difficult, if not impossible standard user change.

you can create:

1) 2 schemas: 1 common tables, 1 security stuff login/pass(hash) table, user session log, change log table, etc.

2) 2 users: 1 common user, can use dml on common schema (no ddl), 1 superuser.

3) login function check provided user/pass against login/pass table , log successful/failed attempts user session log (you need security definer function)

4) set of audit triggers on common schema tables check user privileges , log changes, made user (security definer functions here too).