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

[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()", 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

-- 
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/544550E7.7070408%40oddbird.net.
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.

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

[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 ,
> 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).

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.

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 

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

[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

-- 
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/54417535.3000800%40oddbird.net.
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

[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

On 17/10/2014 3:12 AM, Carl Meyer wrote:

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 

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

[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

-- 

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

[Tom Evans]

On Tue, Oct 14, 2014 at 4:46 PM, 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.

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

-- 
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/CAFHbX1%2BNDumAWpswGNOk%3DA%3DT4Ymwc9Oy4FtBo-E%2B6sgKQKUY6w%40mail.gmail.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

[Carl Meyer]

On 10/14/2014 09:46 AM, Collin Anderson wrote:
> 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).

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

-- 
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/543D4B2C.3070204%40oddbird.net.
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

[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

-- 
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/bcf744ef-17a4-4665-90f4-9611b7842dce%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.