Hi Ken,
On 10/15/2014 05:58 PM, Ken Winter wrote:
Is this a reasonable summary of what you have said so far?:
1. You've suggested two solutions, which I'll call "middleware" (from
Collin) and "save() argument" (from Carl and Tom).
2. A limitation of both is that they aren't what I'll call
"universal". That is, they only work when the update is done with
the save() method of a Django data model object (or perhaps with
other methods that unleash DB actions, such as update(), that could
be customized)in a similar way). Other ways of doing a DB update
action - ways that don't go thru save()or other such methods - would
fail to set last_updated_by. Questions: What are these "other
ways"? Do they include "raw SQL" as discussed in
https://docs.djangoproject.com/en/dev/topics/db/sql/, using
Manager.raw() or cursor.execute()? Are there any other "other ways"?
Yes, all of those are possible. Someone could also access the database
directly without going through Django (or even Python) at all.
3. The difference between the "middleware" and "save() argument"
solutions is that "middleware" is "magic", i.e totally invisible to
the developer, where "save() argument" is not.
The middleware solution is not invisible to the developer, because as
soon as a developer tries to do any database access outside of an HTTP
request context, they have to think about it again.
In general, "magic" (or abstraction) is more problematic the more leaky
it is. If you have a bulletproof system that always does the right thing
automatically in every case, it's fine for it to be invisible to the
developer. But if the system has limitations that the developer will
need to understand anyway to avoid writing broken code, it's often
better if it's more explicit and visible to them from the beginning.
Assuming that my understanding isn't too far off, let me say that I'm
looking for a solution that is "universal" and "magic". (And I'm
willing to live with the risks of "magic"). I think this requires a
solution at a deeper level of middleware ("deeper" meaning closer to the
DB and farther from app code), some level that /all /DB update actions
go through.
Since someone could connect to the database without going through your
Django app at all, the only solution that can possibly meet your
"universal" criterion is a database-level trigger. A database-level
trigger can record which _database_ user updated the row, but it doesn't
know anything about Django users. So that means the only feasible
solution is one that gives each Django user their own database user, as
you outline below.
Let me float two more not-even-half-baked ideas for your comments:
1. Stick some code into the database connector, as I described in
possibility 1 of my original post. I guess the connector would be
the "connection" class?
I don't see any way this is feasible, if you want it to cover raw SQL
executed through ``cursor.execute``. Are you planning to parse the SQL
for every raw statement, figure out if its an INSERT or UPDATE, and then
figure out which row(s) it might affect? At this point you're well into
re-implementing chunks of PostgreSQL in your app code.
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.
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-/)
I don't know your specific requirements, but unless you are in unusual
circumstances I think you would be better advised to abandon the
"universal and magic" goal and accept an app-level solution that
requires some level of awareness from app developers.
Carl
