To keep this conversation fairly modular, I'm responding to different possible solutions in different posts. This one is about Carl's comments on:
> 2. Have Django create a new *database user *for each session login, > using the user id that Django knows and that I want to record in the > last_updated_by column, and establish a new database connection with > that user as the user. Then the database would know the user and > could record it in the last_updated_by column for every update in > that session. At logout, the database user would be dropped. Do > you think a Django app could be programmed to handle the creation > and dropping of this user, and to establish a db connection for that > user? I will also put this proposal to a PostgreSQL forum to see if > they think it would work on the database end. My comments inserted below are prefaced with "Ken:" On Thursday, October 16, 2014 12:13:20 PM UTC-4, Carl Meyer wrote: I think this is probably technically possible (though I don't see why > you'd drop the database user when they logout, better to keep the > account around for their next login). I also expect it will be quite a > tough slog, take weeks or more likely months to get working reliably, > and require you to become an expert on the internals of the Django ORM. > I also think it's the only feasible path to your "universal and magic" > goal. > > (Here's a blog post where someone tried this and apparently got it > working, though I see at least three things they did along the way that > are terrible hacks and either insecure or quite likely to break: > > http://blog.everythingtastesbetterwithchilli.com/2010/02/07/per-user-database-authentication-in-django-/) > > > Ken: Thanks for this link. What the author of that post did is similar to what I had in mind, tho my requirement is more modest than his. He wants to have the database actually handle the authentication. I'm OK having Django authenticate its users; I just want to communicate their user-ids to the database with each query. 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). Ken: Thanks again! -- 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/6ccce391-951f-45c4-808b-ba11c9f6975a%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
