How to "automatically " populate "last_updated_by" DB columns with the current user's id

[Ken Winter]

I'm building a Django app that will work against a pre-existing PostgreSQL database.  Every user data table in my DB has an auditing column called last_updated_by.  The requirement I need to meet is that every time an UPDATE is run against a table, that column is automatically set to the id of the logged-in user.

By "automatically", I mean that this is done without any effort (or choice) on the part of the Django developer who writes an UPDATE (either as a SQL query or as an update on a Django object representing a row of that table) on a particular user data table.

For example (from https://docs.djangoproject.com/en/1.7/topics/db/queries/), the declarer of the Blog class, which corresponds to the blog table, can declare the class as:

class Blog(models.Model):
    name = models.CharField(max_length=100)
    tagline = models.TextField()

and a developer can write an update as:

>>> b5.name = 'New name'
>>> b5.save()

Neither the declarer or the developer gave a thought to the last_updated_by column.  But when logged-in user 12345 causes this code to execute, the blog.last_updated_by column of that row will be set to 12345.

The nub of this problem is that when the database receives an UPDATE query, that query does not identify the Django end user that caused the query to be submitted.  The DB contains a list of the users (auth_user), and my DB also contains a list of the currently active user sessions (and the logged-in user of each session).  But multiple users can be logged in at once, and there is no way for the DB to deduce which of them issued this particular update.  The fact that this update came from user 12345 is known only within the Django app. 

So the problem is:  How to get this info from the app to the database, without requiring the developer to put this bit of housekeeping explicitly into every update?

Three possibilities have occurred to me:

1. A decade ago, when I was using the Zope framework, I found the Zope module (called a database connector) that actually formulates the SQL query strings en route from Zope apps to the DB.  I wrote a routine that inserts "last_updated_by = <the Zope function that returns the user id>" into the SET clause of each UPDATE query, and called this routine from the Zope database connector code.  This worked.  But it's an ugly hack and, worse, involves tinkering with the framework's DNA, so it would not have survived the next version of the database connector module.

2. Could this be done by defining last_updated_by in an abstract base class, as in https://docs.djangoproject.com/en/1.7/topics/db/models/, that includes an update method that automatically assigns the user id to last_update_by for each update?

3. Or could it be done with a modified Manager, with a modified QuerySet (https://docs.djangoproject.com/en/1.7/topics/db/managers/)?

Not sure if this is relevant, but let me add that in the PostgreSQL database, the last_updated_by column is in all user data tables because their CREATE TABLE definitions all include an "INHERITS FROM when_and_who" clause, where when_and_who is the table that actually contains the last_updated_by column.  I'm not sure if Django's inspectdb utility makes any use of INHERITS FROM.

Thanks in advance for any suggestions you have for meeting this requirement!

~ Ken

[Collin Anderson]

Hi Ken,

If you _really_ want it to be automatic, I think your best bet is to use a middleware to store the user or request object in a "thread local" variable, and then have a pre_save signal set the last_updated_by field based on that. That method is frowned upon, but should work. (If you're doing "green" threading, it may break.) The pre_save signal could error, or something if it is not able to determine the request (like in the command-line case).

Thanks,

Collin

[Carl Meyer]

I've done this while avoiding thread-locals (and signals) by just
overriding the model save method to add a ``user`` argument, and giving
the model a custom manager/queryset whose ``update`` method also takes a
``user`` argument. This just means anywhere you call ``save`` or
``update`` you have to pass in a ``user``. It's a bit more typing than
doing it via thread-local and signals, but I much prefer making it
explicit. Also makes it simpler for the cases (like a management
command) where there is no request, but you may still have a way to
identify the responsible user.

If you use the admin, it means you have to customize the ModelAdmin
slightly too so that it passes in the ``user`` kwarg, but that's not hard.

Carl

[Tom Evans]

On Tue, Oct 14, 2014 at 4:46 PM, Collin Anderson <cmawe...@gmail.com> wrote:
> Hi Ken,
>
> If you _really_ want it to be automatic, I think your best bet is to use a
> middleware to store the user or request object in a "thread local" variable,
> and then have a pre_save signal set the last_updated_by field based on that.
> That method is frowned upon, but should work.

To expand on this, it is frowned on because it uses magic to achieve
the desired outcome. Since it is magic, the developer stops providing
anything, if the magic stops working or doesn't happen, the
developer's code starts doing the wrong thing without any changes to
their code.

Collin suggested the usual method of providing the magic - middleware
that pulls the current user in to a global variable. The model can
then only be saved within the context of a request. Outside of that
context, there is no middleware populating the global, so either the
developer is required to do extra work outside of the context to set
up the magic global so that everything works correctly, or it will
fail, perhaps silently.

A "better", but more involved solution would be to override save() on
that model, require that an additional keyword argument "current_user"
is provided. This model will now fail when you use it with code which
does not supply the user, which strictly forces developers to fill in
that information when using that model.

Its not perfect because not everything goes through save() (although
the same issue applies to using a global variable), and because you
will need to customize things like ModelForms and FormSets so that
they can provide the current user.

Django is a web framework, you might argue that this is fine, in a web
framework almost everything is in the context of a request. In very
small sites this might be true, but good testing usually involves
testing small blocks of code, not normally within a request context.

Cheers

Tom

[Ken Winter]

Thanks, Collin, Carl, & Tom ~

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"?
   
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.

I'm a relative newbie to Django, so I'm trying to get clear that I'm understanding you.

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. 

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?
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.
   

~ Tx, Ken

[Carl Meyer]

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

[Mike Dewhirst]

+1

Well said Carl.

Ken, if you do "stuff" outside Django it means you need to maintain code
in more than one place. I was warned against such as a youngster so I
have never done it but I suspect it will either drive you mad or cost
you dearly.

For the external updaters, how will you keep their changes when Django
users are possibly subverting them? And vice versa?

I would suggest you keep everything in the ORM and highly visible.
Choose the save(self, request.user) method and keep your costs down.

Good luck

Mike

[Ken Winter]

To keep this conversation fairly modular, I'm responding to different possible solutions in different posts.  This one is about Carl's comments on:

>  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?

My commenst inserted below are prefaced with "Ken:"

On Thursday, October 16, 2014 12:13:20 PM UTC-4, Carl Meyer wrote:

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,

Ken: Yes.
 

figure out if its an INSERT or UPDATE,

Ken: Yes.
 

and then
figure out which row(s) it might affect?

Ken: No; at this point my code inserts an element that assigns the user id to "last_updated_by" into the SET clause of an UPDATE query or the VALUES clause of an INSERT query, then hands the modified query back to the connector, which passes it on to the DB.

At this point you're well into
re-implementing chunks of PostgreSQL in your app code.

Ken: No, just a modest bit of parsing and tweaking of SQL queries.  My code doesn't have to execute the queries in any way.

Ken: Here's how I did it back in the dark ages, when Zope rather than Django was my framework: https://drive.google.com/file/d/0B-thboqjuKZTNG9DR3lqalJaOWM/view?usp=sharing.  The db.py module is the query pathway of the ZPsycopgDA adapter.  I have changed this out-of-the-box Zope module only by adding the lines I have enclosed here in "ADDED: << ... >>" delimiters.  I have added the whole _insert_when_who_updates() function, three lines invoking it from the query() function, and a few import lines.

Ken: So to implement this in Django, one would have to find the Django counterpart of ZPsycopgDA.db.py and insert this code in the same way.

Ken: Whaddya think?

[Carl Meyer]

Hi Ken,

On 10/17/2014 01:09 PM, Ken Winter wrote:
> On Thursday, October 16, 2014 12:13:20 PM UTC-4, Carl Meyer wrote:
>
> 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,
>

> /Ken: Yes./

>
> figure out if its an INSERT or UPDATE,
>

> /Ken: Yes./

>
> and then
> figure out which row(s) it might affect?
>
> Ken: No; at this point my code inserts an element that assigns the user
> id to "last_updated_by" into the SET clause of an UPDATE query or the
> VALUES clause of an INSERT query, then hands the modified query back to
> the connector, which passes it on to the DB.
>
> At this point you're well into
> re-implementing chunks of PostgreSQL in your app code.
>
>
> Ken: No, just a modest bit of parsing and tweaking of SQL queries. My
> code doesn't have to execute the queries in any way.

I admire your intrepidity!

But on a project I'm responsible for, I'd never let this pass code
review. SQL is a complex language; are you using a battle-tested SQL
parser that you are confident handles every possible syntax variation
correctly? Or are you hand-rolling a regex-based "parser" that will work
correctly for the simplest queries, but break when it encounters
anything you didn't anticipate?

To name a few examples selected randomly from a brief perusal of the
Postgres docs:

- Does your code handle an INSERT with DEFAULT VALUES but no VALUES clause?

- Does your code handle an UPDATE that touches multiple tables using a
FROM clause?

- Does your code handle writable CTEs?

You may say "I just won't use any of those SQL features"; at that point
you don't have a solution that is safe to hide from your developers, and
you may as well .

> Ken: So to implement this in Django, one would have to find the Django
> counterpart of ZPsycopgDA.db.py and insert this code in the same way.

Yes, you'll have to do this either by patching Django, or monkeypatching
it at runtime. I think you'll be happier in the long run if you avoid
doing either of those things.

You'll also have to install a "current user ID" threadlocal for this to
work, which introduces another way in which this solution won't really
be transparent to your developers - they'll have to always make sure
they install a threadlocal user ID before doing anything with the
database. And if they install the wrong one, rather than being a clear
local bugfix (as in the case of passing the wrong user to model.save()),
it's a much more mysterious action-at-a-distance bug to track down.

And, lastly, this solution still isn't really "universal" because it
won't help if someone connects to the database directly instead of
through your app.

> Ken: Whaddya think?

I think that you can almost certainly get this working for simple cases,
and it might even be a fun exercise, but if it were my production
project that I needed to maintain for the next several years, I wouldn't
touch this solution with a ten-foot pole.

Carl

[Ken Winter]

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!

[Carl Meyer]

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

[Ken Winter]

Thanks again Carl (and others) ~

I agree wıth you that the "DB users" approach seems the most promısıng.  It's the one I will pursue, and certainly glad to share the code when I have some worth sharing - and quite likely I'll have some more questions before that, as I try to implement this idea. 

As it happens, I'm leaving on a trip tomorrow, so it may be a while for my next communiqué on this matter.  But I'll be back!

~ Ken

[Ken Winter]

OK, I've written and unit-tested the database components - see https://drive.google.com/file/d/0B-thboqjuKZTSjRHTXhqMlo3RlU/view?usp=sharing.  Now I could use a little advice on the Django part.

I believe that all that is needed is to have the app call the DB function set_session_user() while logging in the user, passing it the user id.  I guess my questions are:

1. Where to put this call? - i.e. in what Django module and function?
2. How to make the call? - Is it just executing a SQL statement like "SELECT set_session_user(<userid>)", or is there a better way?

~ Tx again, Ken

[Carl Meyer]

Hi Ken,

On 10/18/2014 12:55 PM, Ken Winter wrote:
> OK, I've written and unit-tested the database components - see
> https://drive.google.com/file/d/0B-thboqjuKZTSjRHTXhqMlo3RlU/view?usp=sharing.
> Now I could use a little advice on the Django part.
>
> I believe that all that is needed is to have the app call the DB
> function set_session_user() while logging in the user, passing it the
> user id. I guess my questions are:
>

> 1. Where to put this call? - i.e. in what Django module and function?

I would not edit any module or function in Django. Editing Django
directly is just asking for trouble when you want to upgrade Django in
the future.

Fortunately, as in many cases, Django provides the extension hooks
necessary to do what you want without editing any Django code. There are
two techniques that come to mind:

1) You can write your own login view to replace (or wrap)
`django.contrib.auth.views.login`, which does the necessary database query.

2) You could write your own authentication backend (see
https://docs.djangoproject.com/en/1.7/topics/auth/customizing/),
probably just subclassing the default ModelBackend and adding your
database query in an overridden `authenticate()` method after
`ModelBackend.authenticate()` successfully authenticates the user.

Option 1 is simpler if your app has a typical login flow that always
goes through a single login view. Option 2 is lower-level and perhaps
more comprehensive if your app has multiple login flows/views.

> 2. How to make the call? - Is it just executing a SQL statement like

> "SELECT set_session_user(<userid>)", or is there a better way?

I'm not sure what a "better way" would be; Django does not provide any
built-in API to call arbitrary database stored procedures, so you will
have to do this using raw SQL with `cursor.execute()`.

Carl