Hi Ken, On 10/17/2014 05:16 PM, Ken Winter wrote: > Ken: My idea for doing this goes something like: > > 1. For every session, let's say that the Django app's database > connection is with a user I'll call "myapp" here. User "myapp" has > the CREATEUSER privilege. > 2. Someone requests a page from the app. They're not logged in yet, so > they get the login page. > 3. The app handles authentication in usual Django fashion. > 4. When the user has been authenticated, the app: > 1. Checks whether a database user already exists named <user_id>, > where <user_id> is the user just authenticated by the app, and > hence is that value that we want to have recorded in the > last_update_by columns. > 2. If not, creates a <user_id> user. > 3. Issues the SQL command: SET SESSION AUTHORIZATION '<user_id>'. > 5. In the database, all user tables have an ON UPDATE trigger that > assigns the PostgreSQL system variable /current_user > /to/last_updated_by/ for each row that was updated. > 6. Because the SET SESSION AUTHORIZATION command was executed, > /current_user/ evaluates to <user_id> throughout this session. > So /last_updated_by/ gets set as required. An added benefit is that > the permissions applicable throughout the session are those of > <user_id>, and <user_id> is an ordinary end user without any admin > permissions. So a certain kind of possible destructive mischief or > bumbling is prevented. > > Ken: Here are the aspects of this scheme that I'm more and less > confident about: > > 1. I'm fairly confident that steps 1-3 are OK, because I think that's > normal Django process. > 2. I'm less confident about step 4, because I'm not very fluent with > how Django handles actions like this. > 3. I'm confident that this will work at the DB level, i.e. that steps > 5-6 will work fine once step 4 has been accomplished. I'm fluent > with PostgreSQL, and I've unit-tested these bits. > > Ken: I'd appreciate comments and suggestions about any and all parts of > this approach. > > Ken: If it /is/ feasible, this does satisfy my criteria of being > "universal" (all DB updates thru the Django app, whether thru object > model save() or raw SQL or whatever, would appropriately set > last_updated_by) and "magic" (once the login process is programmed to do > step 4, last_updated_by would be properly set without any action of the > developer who programs each update action).
Yes, I think this approach looks quite feasible, and probably simpler than I'd first thought to implement, given that you don't need the database to perform authentication for you and don't mind using a "master user" and then SET SESSION AUTHORIZATION. There may be devils in the details I'm not thinking of, but I think step 4 _should_ be relatively straightforward with a custom authentication backend and some raw SQL via cursor.execute(). If using Django 1.6 or later, you'll want to ensure your CONN_MAX_AGE (https://docs.djangoproject.com/en/1.6/ref/databases/#connection-management) is set to 0, to avoid the possibility of database session authorization leaking from one request to the next via a reused database connection. Reconnecting on every request imposes some performance penalty, but this may not be a problem for you (until recently this was a penalty paid by every Django project that didn't use an external connection pool). I would definitely pursue this approach over "patch the DB connector and rewrite SQL". I'd love to see some code if you get it working and can share! Carl -- You received this message because you are subscribed to the Google Groups "Django users" group. To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscr...@googlegroups.com. To post to this group, send email to django-users@googlegroups.com. Visit this group at http://groups.google.com/group/django-users. To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/5441A6C7.6050302%40oddbird.net. For more options, visit https://groups.google.com/d/optout.
