foreign
have a wonderful outside and a sunshine
and a lunch
okay well you're back here to see
another wonderful talk we are talking
about what are we talking about
multi-strategies for multi-tenancy
strategies with Django and postgres you
seem to know what you're talking about
I'll let you take it away
good afternoon Jenga nuts
so the focus of my talk today is going
to be
we have decided to use Django and
postgres on our website
what can we do to install to ensure that
data doesn't leak from one user to
another user and in particular what can
we do about developer mistakes so that
developers don't make it possible for
users to leak data between the between
systems now I'm going to be talking a
lot about tenants and tenancies in this
in this talk if you're not aware of a
tenancy or a tenant the idea is it's an
isolated set of data that is using
shared infrastructure so a really good
example of that would be Gmail where you
might have millions of users that are
all using Google's computers they're all
stored in the same servers
but I as a Gmail user should not be able
to see the data from another Gmail user
in the system I have my own mailbox and
those things should be kept completely
separate
in most situations a tenant will
correspond directly with the user but
that's not always going to be the case
if you think about something like
Salesforce a company will have an
account on Salesforce and they might
have a hundred users that are attached
to that company within that company
those hundred users can all see data
relating to those customers but they
shouldn't be able to see data from a
different company that is also using the
Salesforce platform
so I'm going to talk about three major
strategies today
I'm going to spend most of the time on
the last one because I think that's
probably the most interesting
and my assumptions here are that you are
python developers who've used Django and
in particular use the Django orm before
if you've never used the Django orm but
you have used postgres or SQL databases
of some sort you'll probably get about
70 but there will be some that may go
over your head
there's also many setup variations on
these
um like little tweaks you can make them
just could be covering the broad the
broad outlines here
hopefully by the end you'll have picked
up one or two new strategies
so the first major strategy is that
every tenant in your system gets their
own app server and gets their own
database on completely separate domains
in a sense every single tenant has a
complete new copy of your system
this is great for code Simplicity it's
very difficult for devs to accidentally
make a mistake and expose data from a
different tenant and in fact it's quite
difficult for them to do that
it's great for data isolation and not
just data isolation but also performance
isolation if you have one particular
tenant who is hammering the database a
lot that's not going to affect any of
the other tenants because they're all
using completely separate database
service
and
depending on your cost model it also
makes it possible to pass through the
costs of Hosting directly to a tenant if
you have some big corporates and you say
well we're going to give this system to
you but you're also going to pay for the
hosting costs associated with your
tenancy then it's possible to do that
where it becomes painful is when you
have lots of different tenants in the
system every time you bring a new tenant
on means you have to stand up new app
servers you have to stand up new
databases you can automate that process
but every time you add moving parts to a
system there's something that's going to
go wrong and let's say for example you
create a new AWS server well what
happens if something goes wrong in that
and it actually takes 40 minutes rather
than the expected three minutes then you
have a support call there's lots of
things that can go wrong
it's also not possible to easily run
cross-tenancy queries if as a software
developer I want to know how many users
are across all of the tenants in the
system I have to query every single one
of those databases one at a time bring
it back to Python and then aggregate the
data together
from an Administration perspective it's
also a bit of a pain when I want to take
backups I have to back up every single
database one at a time
and when I want to run when I want to
push out new migrations I can't just
push it out to one server I have to push
out my new Django migrations to each
server
one at a time or in parallel but you
still have to do it many times
so usually this is a bad idea why do I
mention it all
in some circumstances it can actually be
a good idea for example if you have a
very small number of tenants I had a
client once who had a custom written CRM
that was written just for them there was
no Assumption of any tenancy information
whatsoever it was it was one company
using that one system they want they
decided they wanted to license it to
another company well we could have spent
months going back through the system and
saying okay every single thing now needs
to have an account ID attached to it or
what we end up doing we spend a couple
of days
standing up a new database server a new
application server and we were done with
it
it's also sometimes the only option if
you have corporates with an I.T
Department that has particularly strict
security policies
the next option is where we have a
single Django app server backed by
multiple databases the idea here is that
we have custom middleware that looks at
the incoming requests decides which
tendency is this particular request
relating to and then in your custom
Django database router you remember you
go back and you look at the middleware
and say well what tenancy are we
currently dealing with okay we'll route
that now through to the correct database
the nice thing about this is that hooks
to do it are already there in Django you
have to write a bit of code but you're
not messing around with any deep
internals or anything
this inherits almost all of the
advantages and the disadvantages of
having multiple app servers but you do
save on app server hosting costs
and while the code is generally simple
there are a few gotchas so for example
Django transactions by default only
apply to the default database which may
not be the database they're actually
working with and you think everything's
working fine until you discover that
hang on the data is inconsistent here
because transactions weren't being
applied to the correct database at the
time
the biggest problem in it it has though
is what are you going to do with the
situation where you push out a new
update which includes migrations
and you start migrating each of those
databases one at a time and you've got
three thousand databases to do and on
database 1 500 something fails and you
realize there's a problem with the
migrations there's no rollback here now
you have a single set of app server code
that is somehow having to deal with 1500
databases on the old migration schema
and 1500 databases in the new schema
that's going to be a bit of a pain to
deal with
a nicer variation of that is to use a
schema per tenant now if you're not
familiar with postgres schemas think of
them as postgres equivalent of
namespaces within a postgres database
you have many little smaller databases
that we call schemas instead those
schemas are all completely independent
of each other and in fact the table
definitions inside each schema could be
different now we probably don't want to
do that it's theoretically possible
instead we want to have the same
database definitions across all of the
schemas but instead inside each schema
is just the data for one particular
tenant
and again the idea here is that
middleware will look at the incoming
request and on the current Django
database connection it will set the
current schema to use
again the majority of your code can
simply pretend that tenancies do not
exist the middleware has already taken
care of it for you
um and unlike the last setup because all
of your databases sorry because all of
your schemers sit within the one
database transactions work as you would
expect transactions cover across schemas
it's
a bit better for very large numbers of
schemas you can dump the database as a
whole
sort of the big problem here is that
when you use some standard postgres
tools PG dump you'll quickly discover
that postgres has some internal limits
on the number of table locks that can be
taken at once now that can be increased
by changing the postgres settings but
you're forever going to be chasing your
tail if you have to increase this and
increase this and increase this every
time you do that you're creating fixed
you're setting aside fixed memory
buffers to deal with that
and cross-tenancy queries are now
possible
because this because your data all sits
in the one database you can actually
query multiple schemas at once
it's a bit ugly because what you
effectively do is Select from this
tenancy Union this tenancy Union this
one Union this one Union this one you
end if you've got 10 000 tenants that's
a really really big query you're going
to be running
and the administration deployment
situation is a little better however
again as I mentioned there are
transaction limits when you try and run
your migrations you think okay I'll put
everything in one big transaction and
I'll run the migrations across all my
schemas until you get halfway through
and then postgres says I've run out of
transaction locks so I've run out of
table locks for your transaction
if you try and do this without
transaction at all then we're back to
the previous problem where you could get
halfway through half your schemes have
been updated half your schemers haven't
been updated
if you've not used it before connection
pooling is a way of sharing database
connections between multiple requests so
normally in the simplest configuration
you have a request come in Django
creates a connection to the database
when the request is finished processing
it tears down that connection that
creating connections and tearing down
connections that has overhead that can
add up over times so the idea behind
connection pulling is you generally have
a proxy sitting between your application
and the actual postgres database and it
will hold those connections open forever
so that the postgres overhead of
starting and stopping connections
doesn't exist
depending on your connection pooling
setup it's also possible to share
connections between requests
concurrently
it's not a common setup but it is a
possible setup
because we are setting the current
schema on the connection our connections
are now stateful if you try an
interspersed connection interspersed
requests to the database from multiple
requests simultaneously they're going to
clobber each other and you'll end up
using the wrong schema
there is a third-party package called
Django tenants which basically does all
of this for you all of the middleware
for routing towards schemas it looks at
the subdomain and says well based on the
subdomain this is the schema you want to
use the fact that there's third-party
code to do this is quite nice it also
has the ability to run your migrations
in parallel so rather than each
upgrading each schema one at a time
it'll paralyze them and do 30 at a time
probably the most interesting setup
though and the one that you've probably
used before and most developers would
use intuitively is where you have a
single app server you have a single
database you have a single set of tables
and you share those tables between the
different tenants within each table you
record who does this particular piece of
data belong to
now in order to keep the data separate
between the tenants there's a bunch of
strategies that we can follow so these
are the three things that we're going to
be looking at today and the nice thing
about these three is they're somewhat
independent of each other and you can
adopt them incrementally so if you had
an existing system you can actually add
these onto an existing system and get a
bit more security
there are some common characteristics
that they all have firstly the code is
not as simple as the previous methods
you always have to bear in the back of
your mind that there are multiple
tenants in the database and all these
things can help avoid make mistakes it's
still possible to make mistakes and
accidentally expose data
the isolation is okay but it's not great
again coding mistakes can have a problem
however if you have thousands of tenants
then it's basically the only option
you're going to have
cross-tenancy queries are possible in
fact the problem is accidentally doing
cross-tenancy queries
and the administration and deployment
situation is fine everything sits within
the one database your the standard
Django workflows that you would follow
um apply here
so here's an example database diagram
for a task tracking system
and down the bottom is some Associated
model code the idea here is that we're
going to have custom model managers and
query sets which have a standardized
interface on every single model in the
system that says limit the data that I'm
selecting out of this table to just one
particular tenant
now one of the nice consequences of
standardizing on one thing across all of
the tables in your system is that if
you're using class-based views The
class-based View doesn't need to know
what model it's dealing with it can
always call filter tenant and the code
will just work so you can have your base
classes which apply this tenancy
filtering automatically and it for most
of the system it just comes out for free
if a table doesn't join directly to your
table to to the account table which is
the tenant table in this particular
example
then we just use standard orm joins in
order to get back to that table
now as a practical matter
the middleware has a copy of the request
and from that request it's able to
figure out which tenant we're supposed
to be dealing with or maybe it looks at
the user in the request and from that
figures out which tenant we're supposed
to be dealing with and since the view
has access to the request it now has
access to the tenant ID
but not everything else in the system is
going to have the tenant ID and where
now we could pass that tenant ID down
the system down the call chains that's
going to get really painful really
quickly
um just as a pragmatic thing you
probably want to store the current
tenant ID in the middleware so that you
don't have to pass it everywhere through
the system
and keep track of that in thread local
storage now if you've not used it before
thread local storage is
essentially global data that only
applies to the current thread so if
you're running on a multi-threaded
server you won't clobber data that
relates to it to the wrong thread the
reason we want to do this is that if
you're not aware Django only creates one
copy of each middleware object on
Startup so you can't store the data in
the middleware object itself or it will
get clobbered by whatever the whatever
the next request that happens to be
simultaneously processed is
so there are some caveats about this
whoops wrong direction
number one if you write custom SQL
you're on your own there's no help you
need to make sure that you don't make
mistakes with any custom or irm code
sorry any custom SQL code
having the joins automatically applied
to every single table can actually
change the meaning of some of your
aggregate queries
it's not a common circumstance but you
will run into it when you when you when
you write your queries and say where's
the data not coming back how I expected
it to which means that sometimes you
will need to bypass and not call
filtered tenant and in fact apply the
tenant filtering manually even though
you are using the orm
a more subtle problem is that by default
if I have a model with a foreign key
that refers to another model
that second model also creates a reverse
foreign key in Django back to the
original model
now what this means is that if I have a
shared table so for example I have a
list of countries I don't need to
duplicate that between the all the
tenants because the list of countries is
fixed
and I have users who all have a country
then by following user.country.user
underscore set that user underscore set
is the reverse foreign key that jenko
creates automatically I'm not just
accessing users that relate to that
country for the tenant that I started
with I'm accessing users from the entire
system that relate to that country so
you need to be careful about that
if you want to be strict then what you
can do is turn off the reverse current
foreign Keys you need to add on a
foreign Key by Foreign key basis or user
base class that does that by default
and again if you want to be strict
there's a system of checks called Django
system checks if you've not used those
before go look up the manual they're
fantastic the code that will run on
every Django server startup what you can
do is introspect all of the apps in the
system get the models for those apps
look at the fields in each of those
models and make sure that every single
foreign key field has had the reverse
foreign key disabled that way you can't
accidentally Escape out of it
but the biggest problem of all is that
the develop that is that the developer
could simply forget to call filter
tenant and nothing's going to stop them
from doing that
so oops what can we do about that
what we can do is we can remember in
each query set where the filter tenant
was actually called and if it wasn't
called then we will log a warning or
raise an exception when the query set
actually goes to fetch data back from
the database
um or you do something else like in
debug you log a warning but in
production and on CR you actually throw
a hard error so that it can't escape
it's a bit nicer if you're doing
refactoring because it can be a real
pain sometimes when you have to
constantly stop and start
if you do this though you'll quickly run
into the difference between Django's
base manager and its default manager if
you've ever used soft deletes in Django
you'll have run into the same situation
before
the default manager in Django is the
manager that you normally use
Django also has the notion of a base
manager which is you can think of that
as the master manager that has access to
everything in the system
Django uses this for things like refresh
from DB if you want to get the data back
from a record it uses it for migrations
it uses for fetching foreign key
relationships and you need to provide a
way for Django to
fetch data from the database without
having called this because Django is not
going to call this
call filter tenant I mean
now normally when we design a database
we like to normalize everything
so if we have a look at our task
management task management system again
each of those tables has a reference to
another table but only project and user
have a reference back to the account
that is the tenant table
if we needed to filter a subtask
according to the current account we join
the subtask to task join task to project
join project to account and
and then check that we're using the
correct account
what we can also do is denormalize that
we can copy the tenant ID into every
single table in the system
why would we want to do that well
firstly it simplifies our code there's
no joins what you have to do anymore in
order to filter by tenant which means
that's aggregate query problems that I
was talking about before no longer reply
and it's also desirable for row level
security which we'll talk about next
it doesn't meaningfully
affect anything else
there may be a performance impact but
that performance impact could be
positive or could be negative depending
on the ratio of rights to reads in the
system
it's harder to move data between tenants
no longer are we just updating so let's
assume for example we wanted to merge
two accounts no longer are we taking all
the projects for one account and
pointing those to the to the new merged
account instead we have to do the
projects and the sub projects and the
tasks sorry the projects and the tasks
and the subtasks there's a lot more
records that we're going to have to
update there's a potential to introduce
errors here
let's say we forgot to do a transaction
and we're doing that merge and we got
halfway through then now we have data
that's half
changed and data that's half not changed
and some of those foreign keys are not
going to be consistent with the other
data that's in the system
so it's possible for coding mistakes to
introduce problems and you have your
standard denormalization caveats um
your database will be bigger because
we've duplicated data in particular your
indexes you can have a lot more indexes
in the system
that means that every time that you
update an account record
every single record in the system that
has an index associated with it that
points to the account is potentially
also going to have to be Rewritten by
postgres which means no longer is
postgres just going to have to update
the projects that relate to an account
for their indexes it's also going to
have to update the tasks and the
subtasks
there's a third-party package called
Django multi-tenant which assumes that
you have already denormalized your data
and it automatically adds where Clauses
onto every irm query that you do so that
it assumes well the tenant ID is already
in here rather than you having to
manually add the where Clauses I'll just
do it for you
it also provides a replacement foreign
key class that avoids the problem I
spoke about before about escaping from
your current tenancy via the foreign
keys
and also worth mentioning the reason for
the existence of that project is that
the company behind it cytus they make a
postgres extension which will
automatically look at the tenant ID and
use that to Shard your data between
multiple servers so from your
perspective it looks like a single
postgres server but in fact it could
have 20 or 30 servers behind it and your
software doesn't have to change at all
even better Microsoft bought that
company and now it's like a couple of
Clicks in Azure when you set up a new
database to get that you also pay a lot
more because you're paying for many more
servers which is why Microsoft want you
to do that
and finally posters row level security
so postgres has the ability to add
Security checks on a row by row basis
now that's not just select that's also
updates and inserts and deletes and it
will validate that every time you run a
query against a table that some check
the check being the SQL code that you
define that condition is met
now that check itself could do selects
you probably don't want to do that
because it's really easy to blow up the
number of queries that you're going to
do in your um
in your system and have Major
Performance issues so you probably want
to keep it to a relatively simple
expression
if you look at row level security
tutorials on the internet you'll see
that almost all of them use postgres
users
that's probably something that I think
is a bad idea the problem with postgres
users is that they're not specific to a
database they're Global across all
databases on a postgres server which
means if you have your database and
someone else has another database that's
using the same server and you're both
creating and deleting users there's a
really good chance you're going to be
clobbering each other and you're going
to end up having a bad time
and that assumes you even have
permission to create users if you have
CIS admins that are managing your
databases they're probably not going to
give you those permissions they're going
to say no we gave you a user use that
one
so what we can do instead is that again
middleware is a common theme running
through this here we can have some
middleware set the current tenant ID on
the connection and then in the row level
security checks we can look at what the
current tenant ID is and make sure that
that matches the data that's we're
operating against now in the example
here we've set this variable called
django.accountid
and note that the dot in that name is
very important what we're actually doing
here is we're abusing postgres settings
postgres thinks that we're trying to set
a configuration item for a postgres
extension called Django that we just
happen not to have loaded yet
and it says well that's fine you can
temporarily override that configuration
setting
and later on we then pull that back out
again and use that for our row level
check
because it's a configuration option it's
always going to be a string which is why
we have that cast from int there
now there are some caveats for postgres
row level security
again because we're setting some State
on the connection you need to make sure
that you're not interleaving requests
simultaneously across the same
connection
there are potential performance impacts
if you run an explain analyze on your
query after activating row level
security you'll see all of these extra
row level security checks that have
basically been tacked onto your queries
by postgres most of the time that should
be okay as long as you were already
filtering by the tenant ID in your
Django code but if you weren't then the
row level checks are now going to add
these extra filters which may not have
indexes associated with them and now you
may have performance problems so you'll
need to
just keep an eye on things once you've
activated to make sure that there's no
unexpected effects and if there are you
may need to look at the indexes that you
that you need to apply and again the
postgres query Optimizer is pretty good
but the more complicated the query is
the more chance that it's going to
choose a query plan that's not the one
that you expected it to use and you may
have to jig around your queries a bit in
order to get it to operate performantly
if you're doing your local development
postgres you probably just have one one
user that user is actually a super user
that user will completely bypass any row
level checks so your row level checks
will appear not to work at all you need
to make sure that when you are doing
your development and when you get to
production that you didn't accidentally
connect to the database with the super
user
and conversely when you're taking your
backups from the database you need to
make sure that you are using a super
user if you were just using PG dump and
that was not a super user and that user
had row level security checks applied
and was not seeing all of the data you
will not get all of your data in your
backup and you'll have a very bad day
when you go to restore from backups
so in summary
multiple app servers and databases very
simple to code but can be costly and
painful with a large number of tenants
using a single lap server with multiple
databases and multiple schemas the big
problem you're going to have here is
partially applied migrations
uh Django managers
they're very nice at detecting missing
filters and they can provide consistency
to your code
denormalizing tenancy data can simplify
your code at the expense of database
size and postgres row levels Security
checks are a really nice fail safe in
case you forgot something in Django the
database is there got you back just to
make sure that you don't accidentally
mess up something
and I have code samples for most of
these
pinned in my repository so if you'd like
to have a look at some of these things
with a proof of concept then feel free
questions or comments
[Applause]
who has questions
or comments
thanks for for running through all those
there's sort of there's a lot of things
to keep in mind and a lot of places you
could accidentally trip over your own
feet and accidentally show the world
things that aren't going to be seen
uh can you are there any ways that you
can systematically avoid those problems
or give any confidence that you haven't
hit those problems or is this just a
matter of you need to make sure you've
got test coverage and things yeah test
coverage the thing the thing that I
found we actually used mandatory
filtering on a project once and I had so
much pushback from developers who say oh
it's constantly complaining that I
forgot to filter and I'd ask them or
should you be filtering yes
um
it is it is like the Nazi Checker of
making sure that you've put filtering in
but I've actually personally I like it
the most as something that forces you to
consider should I be filtering this or
not and you always have the escape hatch
you can use all objects but it also
means it's really easy to audit your
code and say find me all of the
instances of all objects because I know
that those ones aren't going to have
filters applied and I can go and double
check those
oh we have more questions
I'm getting my steps in
sorry
um watching this talk uh I I saw myself
I went through this process at work a
couple years ago and all the things you
talked about were all the things that we
dealt with
um so I'll ask a high specific question
uh using thread local storage to store
the context the to the tenant context
I've always felt kind of uneasy about
that partially because of globals but
partially because I don't
I don't trust my knowledge of threads
and whatever else
are they common pitfalls that someone
like me should be looking out for or
edge cases that I might not have
considered
um I'm not sure of any common pitfalls
specifically I would just say test and
make sure you test multiple requests
simultaneously
um
I actually had some test cases that
would
just do tests over and over and over and
over again in two different threads it's
kind of a pain and it's
non-deterministic unless you have pauses
and sleeps that you insert just to make
sure that but but even that assumes that
you put the paws in the sleep in the
correct in the correct location
on thread local storage though I can't
say Django uses it internally I was a
bit wary of it first and was like well
Django's doing it so if I'd know Django
async doesn't work with it we'll then
say there's a bunch of other core Django
code that's going to break so that I
don't know if that makes you feel better
about using it
the recording that was a little bit
um sadly we are out of time so let's
thank our speaker again for the great
talk
[Applause]