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 = " 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

-- 
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/e81651b5-3363-4b86-83b5-dd72d8a75196%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Incremental inspectdb, anyone?

[Ken Winter]

Is there a Django utility or add-on that can read database schemas and turn 
them into Django data models *incrementally*? 

By "incrementally", I mean that the utility would be able to update a set 
of already existing Django models so they match the current DB schema.  In 
other words, the utility would incorporate changes to the schema that have 
been made at the DB level into the Django model set.

It seems to me that this utility would have some or all of the following 
level of functionality, which I list in increasing order of difficulty:

1. Inspect a DB schema and create a Django model for every table in it.  
This is what inspectdb does.  It's fine for initializing a Django app that 
doesn't already contain any data models.  Level 1 functionality is easiest 
to implement, because the utility doesn't have to look at the existing 
model set at all.

2. Inspect a DB schema and create a Django model for user-selected tables 
in the schema.  This extension seems easy to implement, as the utility 
still doesn't have to inspect the existing model set.  Adding level 2 
functionality would be a big help for those of us who need to evolve our DB 
schema directly at the DB level and then create-modify Django models to fit 
the DB, rather than vice-versa.  For us, a level 1 utility is good only for 
a one-time run when initializing the model set of a brand-new app.  A level 
2 utility would spare us the error-prone and labor-intensive effort of 
creating subsequent models by hand.  (As someone suggested elsewhere in 
this forum, it is possible to run inspectdb and then lop out the 
pre-existing models from its output file before adding the rest of them to 
the model set, but this is at best rather clunky.)  

3. Inspect a DB schema and create a Django model for every table in the 
schema that doesn't already have a model in the existing model set.  Harder 
to implement, because now the utility has to compare the schema with the 
model set - albeit at a quite coarse level:  all it needs to ask is "Does 
this table already have a model or not?"  Level 3 functionality would shave 
off some additional work, leaving only the altering and deleting of 
pre-existing models (and the impact of those changes on code) to be done by 
hand.

4. Inspect a DB schema and modify the Django model set so that they match 
completely.  Much harder, because it involves (a) comparing every detail of 
each existing model with the DB schema, (b) altering and deleting models as 
well as just creating them, and (c) dealing with the problems that these 
alterations and deletions may cause for existing app code.  The last point 
in particular is a big hurdle.  So level 4 functionality may not be worth 
the effort - but if someone has already gone to the trouble of building it, 
great to have!

So my question is:  Has anybody built something that provides functionality 
beyond level 1?  If so, where is it and what can it do?

~ TIA, Ken

-- 
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/f0572b3c-39ae-4f59-829e-bc5d382bac4e%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

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

[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




On Tuesday, October 14, 2014 11:46:40 AM UTC-4, Collin Anderson 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. (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
>
>

-- 
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/465118e1-7fd0-4501-8a56-92332e142bd8%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

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

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


-- 
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/b68fba53-11be-47e1-b02f-7740c1647f9c%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

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

[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 , where 
   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.  
  3. Issues the SQL command: SET SESSION AUTHORIZATION ''. 
  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  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 , and 
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://grou

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

[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

-- 
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/2d560d97-335c-4b3a-99b3-e72818491337%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

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

[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()", or is there a better way?

~ Tx again, Ken

On Friday, October 17, 2014 5:46:05 PM UTC-6, Ken Winter wrote:
>
> 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
>

-- 
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/9a61f435-5e94-4f64-99d9-9b7cb44891d2%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.