Multi-credential ORM configuration support

[Warren Smith]

If this question would be more appropriate for django-users, please
let me know. I ask it here because I suspect that the answer to my
question may require someone with a fairly deep understanding of the
ORM internals.

I have a legacy oracle database that I would like to use with the
Django ORM. Unfortunately, the security for this database is
implemented using oracle users and roles, making it necessary for each
user to have their connection to the database use their own
credentials, which translates into a connection for each user.
Although I can retrieve the credentials from the user and keep them in
session, it is not apparent to me how to configure the ORM to
appropriately use them. Unfortunately, changing the database security
model is not an option.

The research I have done seems to indicate that this mode of operation
is not supported.

Am I correct or am I missing something?

If it is not officially supported, is there a way it could be made to
work safely? We are already using a custom version of django 1.1.1,
so I'm not opposed to making some additional custom changes, though I
prefer to avoid them.

Alex and Russell, would the work you have done in the multi-db branch
have any bearing on this use case?

--
Warren Smith

[Russell Keith-Magee]

On Thu, Nov 12, 2009 at 6:20 AM, Warren Smith <war...@wandrsmith.net> wrote:
>
> If this question would be more appropriate for django-users, please
> let me know.  I ask it here because I suspect that the answer to my
> question may require someone with a fairly deep understanding of the
> ORM internals.
>
> I have a legacy oracle database that I would like to use with the
> Django ORM.  Unfortunately, the security for this database is
> implemented using oracle users and roles, making it necessary for each
> user to have their connection to the database use their own
> credentials, which translates into a connection for each user.
> Although I can retrieve the credentials from the user and keep them in
> session, it is not apparent to me how to configure the ORM to
> appropriately use them.  Unfortunately, changing the database security
> model is not an option.
>
> The research I have done seems to indicate that this mode of operation
> is not supported.
>
> Am I correct or am I missing something?

You're pretty much correct. Django assumes that it has a single
trusted login to the database.

> If it is not officially supported, is there a way it could be made to
> work safely?  We are already using a custom version of django 1.1.1,
> so I'm not opposed to making some additional custom changes, though I
> prefer to avoid them.

I can't think of an easy way - connection is essentially a singleton
in Django 1.1. However...

> Alex and Russell, would the work you have done in the multi-db branch
> have any bearing on this use case?

Multi-db allows you to set up multiple database credentials; the usual
reason for this is to split DB load across multiple database servers,
but I don't see any reason you couldn't also use it to provide
multiple connections to a single server, each with different
credentials. So - you should be able to use the using() query operator
to force a particular query to use a particular set of credentials
(and thus a particular database connection).

I haven't given this particular use case enough thought to make any
comment on how the UI could be made cleaner. So far, we've been
concentrating on the 'user accounts are on db X', sharding model X
based on PK, and supporting master/slave replication as the end-user
use cases that we want to satisfy. We'd certainly be interested in any
input on ways to improve usability for the multi-credential case.

Yours
Russ Magee %-)

[Warren Smith]

On Wed, Nov 11, 2009 at 5:08 PM, Russell Keith-Magee
<freakb...@gmail.com> wrote:
> Multi-db allows you to set up multiple database credentials; the usual
> reason for this is to split DB load across multiple database servers,
> but I don't see any reason you couldn't also use it to provide
> multiple connections to a single server, each with different
> credentials. So - you should be able to use the using() query operator
> to force a particular query to use a particular set of credentials
> (and thus a particular database connection).
>

So, if understand you correctly, with multi-db I could do the
following for each request:

1. Retrieve the credentials from the session.
2. Create a new, or update an existing, entry for those credentials in
the database configuration.
3. Somehow ensure that the using() operator is used on all ORM operations.

Would this work?

If so, I think it could be implemented as middleware.

My only concern is #3. Could the ORM be made to check some
request-specific place for a default using() argument if one is not
passed explicitly?

> I haven't given this particular use case enough thought to make any
> comment on how the UI could be made cleaner. So far, we've been
> concentrating on the 'user accounts are on db X', sharding model X
> based on PK, and supporting master/slave replication as the end-user
> use cases that we want to satisfy.

Understood. Those use cases should take priority.

>We'd certainly be interested in any
> input on ways to improve usability for the multi-credential case.
>

Excellent. I'll do some experimentation and attempt to come up with
some recommendations.

Thanks. Your brief consideration of this is exactly what I am seeking.

--
Warren Smith

[Russell Keith-Magee]

On Fri, Nov 13, 2009 at 2:28 AM, Warren Smith <war...@wandrsmith.net> wrote:
> On Wed, Nov 11, 2009 at 5:08 PM, Russell Keith-Magee
> <freakb...@gmail.com> wrote:
>> Multi-db allows you to set up multiple database credentials; the usual
>> reason for this is to split DB load across multiple database servers,
>> but I don't see any reason you couldn't also use it to provide
>> multiple connections to a single server, each with different
>> credentials. So - you should be able to use the using() query operator
>> to force a particular query to use a particular set of credentials
>> (and thus a particular database connection).
>
> So, if understand you correctly, with multi-db I could do the
> following for each request:
>
> 1. Retrieve the credentials from the session.
> 2. Create a new, or update an existing, entry for those credentials in
> the database configuration.
> 3. Somehow ensure that the using() operator is used on all ORM operations.
>
> Would this work?

This sounds a little more dynamic that I was anticipating.

Multi-DB changes the way you define your database - instead of
DATABASE_HOST, DATABASE_NAME etc, you have a single DATABASES
dictionary, with each key-value pair representing a full database
configuration, including signin credentials. The process of actually
opening a connection is handled by a ConnectionHandler class in
django.db.utils.

Although we haven't provided any way to override this, I suppose it
could be replaced by an alternate implementation that creates
connections based on credentials obtained from a session or something
similar. I haven't given this much thought though.

> If so, I think it could be implemented as middleware.
>
> My only concern is #3.  Could the ORM be made to check some
> request-specific place for a default using() argument if one is not
> passed explicitly?

The details aren't quite sorted out yet, but I expect the answer will
be yes. We will need a similiar sort of approach will be needed to
handle master/slave replication (i.e., write to database X if it's a
write, or a read of something recently written).

Yours,
Russ Magee %-)

[Warren Smith]

On Thu, Nov 12, 2009 at 5:17 PM, Russell Keith-Magee
<freakb...@gmail.com> wrote:
>> 1. Retrieve the credentials from the session.
>> 2. Create a new, or update an existing, entry for those credentials in
>> the database configuration.
>> 3. Somehow ensure that the using() operator is used on all ORM operations.
>

> This sounds a little more dynamic that I was anticipating.
>
> Multi-DB changes the way you define your database - instead of
> DATABASE_HOST, DATABASE_NAME etc, you have a single DATABASES
> dictionary, with each key-value pair representing a full database
> configuration, including signin credentials.

I've looked through the code in the SVN multdb branch and Alex's
GitHub and thought about this a little more.

I realize now that I don't need to store the credentials in session,
just the default db alias.

The dynamic population of settings.DATABASES can happen at login time.
I can use a statically configured alias as a template, just replacing
the username and password. This new alias can be stored with a name
like '<username>@<template_alias_name>' and set in the session for use
as the default db alias in subsequent requests.

The new multi-credential middleware would just have to take care of
getting the using() value from the session and putting it somewhere
for the ORM to use (perhaps a thread-local?).

Is there an existing thread-local instance that would be appropriate
for storing this value?

> The process of actually
> opening a connection is handled by a ConnectionHandler class in
> django.db.utils.
>
> Although we haven't provided any way to override this, I suppose it
> could be replaced by an alternate implementation that creates
> connections based on credentials obtained from a session or something
> similar. I haven't given this much thought though.
>

Though django.db.utils.ConnectionHandler is the broker of all database
connections, I'm concerned that overriding connection selection here
would overrule explicit alias declaration such as Model.Meta.using,
Model.save(using=), etc. Though it would work in my case (I'm not
using those features), it feels heavy-handed. However, since the
multi-credential use case adds an extra dimension to an already
complex problem, perhaps it does make sense for it to be mutually
exclusive with the other multi-db enabled use cases.

What I originally envisioned was injecting the check of the request
specific default into the connection selection logic just ahead of
DEFAULT_DB_ALIAS. Unfortunately, the connection selection logic
exists in several places in the code and is not all the same (for
reasons I haven't taken the time to fully understand).

To avoid changing any of the existing connection-selection code, I
suppose I could change DEFAULT_DB_ALIAS to a custom string subclass
that was smart enough to check for the request-specific value when
asked for his own value, but that seems like an overly complex and
counter-intuitive solution to a relatively simple logic problem.

Would it make sense to factor out the connection selection logic into
a utility function with parameters that make it usable in all
contexts, thus yielding a single place to inject the check for the
request-specific default db alias (and perhaps other logic to support
master/slave, etc.)?

--
Warren Smith

[Russell Keith-Magee]

I'm not sure this will work as you expect. Django can be served in
multi-process environments, and on multiple-server environments. As a
result, there is no such thing as an in-process or in-thread store
that is shared between all serving instances of a Django application.
If you want to share data between requests, you need to use a resource
that is persistent across requests that might be served on completely
different machines - like a cookie, or the database itself.

As a mental model, you need to assume that each individual request
will be served by a system process that will be created for that
request, and destroyed as soon as the request is completed. Of course,
this isn't how Django apps will be served in practice, but it
accurately reflects the constraints that you need to adhere to in the
general case.

That's the eventual intent - a callback or plugin that will be
registered, probably with a model manager, that will provide an answer
to the "which database should I be using for this query" question.
Different implementations of this API will then exist for
master/slave, sharding, etc. The exact form of this API is yet to be
determined.

I mentioned ConnectionHandler because you were talking about creating
new database connections at runtime based on the user's credentials,
and at some level, this will still need to happen. Tweaking
ConnectionHandler is the alternative to trying to dynamically rewrite
settings.DATABASE, not a solution to the 'find which connection to
use' problem.

Yours,
Russ Magee %-)

[Warren Smith]

On Fri, Nov 13, 2009 at 8:06 PM, Russell Keith-Magee
<freakb...@gmail.com> wrote:
>> The dynamic population of settings.DATABASES can happen at login time.
>>  I can use a statically configured alias as a template, just replacing
>> the username and password.  This new alias can be stored with a name
>> like '<username>@<template_alias_name>' and set in the session for use
>> as the default db alias in subsequent requests.
>>
>> The new multi-credential middleware would just have to take care of
>> getting the using() value from the session and putting it somewhere
>> for the ORM to use (perhaps a thread-local?).
>>
>> Is there an existing thread-local instance that would be appropriate
>> for storing this value?
>
> I'm not sure this will work as you expect. Django can be served in
> multi-process environments, and on multiple-server environments. As a
> result, there is no such thing as an in-process or in-thread store
> that is shared between all serving instances of a Django application.

Point taken. Although it can be expected that session information is
shared across processes, changes made in the settings module of one
process will not be reflected in the other. So, in order to
accomplish my goal, I will need to store the credentials in session.
The middleware will need to ensure that the database config contains
the alias, and create it if it doesn't. Once that is done, it can
store the session-based default db alias in a thread local somewhere
for the ORM to use for the duration of the request-response cycle.

> If you want to share data between requests, you need to use a resource
> that is persistent across requests that might be served on completely
> different machines - like a cookie, or the database itself.
>

Understood. Sessions are driven by cookies.

> As a mental model, you need to assume that each individual request
> will be served by a system process that will be created for that
> request, and destroyed as soon as the request is completed. Of course,
> this isn't how Django apps will be served in practice, but it
> accurately reflects the constraints that you need to adhere to in the
> general case.
>

Thats how I was picturing it, except when I was thinking about
dynamically manipulating settings.DATABASES. Thanks for pointing out
the flaw in my reasoning.

>>
>> Would it make sense to factor out the connection selection logic into
>> a utility function with parameters that make it usable in all
>> contexts, thus yielding a single place to inject the check for the
>> request-specific default db alias (and perhaps other logic to support
>> master/slave, etc.)?
>
> That's the eventual intent - a callback or plugin that will be
> registered, probably with a model manager, that will provide an answer
> to the "which database should I be using for this query" question.
> Different implementations of this API will then exist for
> master/slave, sharding, etc. The exact form of this API is yet to be
> determined.
>

Is developing this API in scope for 1.2?

> I mentioned ConnectionHandler because you were talking about creating
> new database connections at runtime based on the user's credentials,
> and at some level, this will still need to happen. Tweaking
> ConnectionHandler is the alternative to trying to dynamically rewrite
> settings.DATABASE, not a solution to the 'find which connection to
> use' problem.
>

Ok. I think I understand now. I was leaning on the current
implementation of ConnectionHandler to do the right thing if I
dynamically manipulated settings.DATABASES. Although it appears that
it would work, I now see that depending on it would be a bad idea.

Perhaps a better solution would be for ConnectionHandler to directly
support the concept of dynamic database configuration. I suppose
there could be a structure similar to settings.DATABASES that lived on
a thread-local instance and potentially manipulated by middleware at
the beginning of each request. ConnectionHandler.__getitem__ could
look in that structure for database config if the requested alias did
not exist in self.databases.
The "which database should I be using for this query" logic could
still be in control of which connection is requested, using a separate
mechanism to be aware of request-specific default db alias.

My impression is that my use case is not very common. Therefore, it
may not make sense for it to be officially supported by django.

I'm going to run with some of these ideas in my custom version of
django and see what I come up with.

Thanks for your help.

--
Warren Smith

[Russell Keith-Magee]

On Tue, Nov 17, 2009 at 3:19 AM, Warren Smith <war...@wandrsmith.net> wrote:
> On Fri, Nov 13, 2009 at 8:06 PM, Russell Keith-Magee
> <freakb...@gmail.com> wrote:
>>> Would it make sense to factor out the connection selection logic into
>>> a utility function with parameters that make it usable in all
>>> contexts, thus yielding a single place to inject the check for the
>>> request-specific default db alias (and perhaps other logic to support
>>> master/slave, etc.)?
>>
>> That's the eventual intent - a callback or plugin that will be
>> registered, probably with a model manager, that will provide an answer
>> to the "which database should I be using for this query" question.
>> Different implementations of this API will then exist for
>> master/slave, sharding, etc. The exact form of this API is yet to be
>> determined.
>>
>
> Is developing this API in scope for 1.2?

It's certainly in scope. Multi-db is in scope, and APIs to use
multi-db cleanly will be required.

However, realistically, this particular feature still needs some
design work, and the 1.2 feature deadline is just over a month away. I
suspect what will end up landing for v1.2 is the simple interface that
allows users to manually direct query X at database Y; 1.3 will then
include the pretty interface that makes it easy to control the use of
the simple interface.

That said, it's still possible that this could be done for v1.2. If
you want to help out, Alex's multi-db branch on github [1] contains
the working base of code, and it is getting close to being complete
for the simple case. Any feedback or development assistance is most
welcome.

[1] http://github.com/alex/django/tree/multiple-db

> My impression is that my use case is not very common.  Therefore, it
> may not make sense for it to be officially supported by django.

It may not be supported out of the box, but that doesn't mean we don't
want it to be possible. Suggestions welcome.

> I'm going to run with some of these ideas in my custom version of
> django and see what I come up with.

Excellent. If you discover anything interesting, let us know.

Yours
Russ Magee %-)