Read-only connection to database. How to?
Julien
Is it possible to force database access to be read-only when executing
custom SQL?
I want to make a simple admin application that lets my clients execute
some SQL code (only "SELECT" queries, so they can pull out some data
when they need). I don't want them to mess up the database so I'd like
to force the read-only access for whatever they execute.
My model is:
class Query(models.Model):
name = models.CharField(max_length=255)
sql = models.TextField()
description = models.TextField(blank=True)
def execute(self):
from django.db import connection
cursor = connection.cursor()
cursor.execute(self.sql)
row = cursor.fetchall()
return row
class Admin:
pass
What should I change to force the read-only access?
Thanks a lot!
Julien
James Bennett
> What should I change to force the read-only access?
Create a new database-level user, and grant that user SELECT but
nothing else. Then fill in those credentials in the settings file used
by the site. You can always set up a read/write "real" administrative
area for yourself with a different settings file and different
database credentials.
--
"Bureaucrat Conrad, you are technically correct -- the best kind of correct."
Julien
I thought about that, but how could I use that read-only user just in
that Query.execute() function?
Cheers,
Julien
On Feb 3, 9:11 pm, "James Bennett" <ubernost...@gmail.com> wrote:
Julien
I found a trick that works for my use case. I just don't execute if
it's not a SELECT request. I do the test like so:
def execute(self):
if self.sql.split()[0].lower() != 'select':
return 'You can only execute SELECT queries.'
....
James Bennett
> I found a trick that works for my use case. I just don't execute if
> it's not a SELECT request. I do the test like so:
Things your filter doesn't catch:
* PostgreSQL's table-creating SELECT INTO statement.
* Any "query" which consists of multiple statements separated by
semicolons (e.g., "SELECT * from foo; DROP TABLE auth_user;").
* etc., etc.
Your database already knows how to handle a read-only user. Either set
up a separate settings file and run the client-facing part of your
site on that (most secure) or have this method obtain its own separate
database connection as a read-only user (not as good, but still
decent). Trying to invent a security system which prevents any and all
data-altering queries will almost certainly end in pain and suffering,
especially when you already have easy access to a proven system that
does the same thing.
Tim Chase
> it's not a SELECT request. I do the test like so:
>
> def execute(self):
> if self.sql.split()[0].lower() != 'select':
> return 'You can only execute SELECT queries.'
> ....
So the user puts in
SELECT * FROM app_whatever; DROP TABLE auth_users;
...
You *really* *do* want a RO DB connection. Anything else is just
asking for trouble. Push come to shove, you can create a second
connection to the DB using the RO credentials, and then do your
execute() through that connection.
-tim
Julien
Thanks, guys, for spotting the mistake.
I totally understand what you suggest, having a RO user at the
database (in this case MySQL) level.
But I am fairly new to Django and Python, and I am unsure how to
implement that dual-setting option.
I was thinking, maybe to add a DATABASE_USER_RO value in setting.py.
But I don't see how I can force connection object to use that user
instead of the regular "DATABASE_USER".
Could you please indicate how to do this?
Thanks a lot!
Julien
James Bennett
> Could you please indicate how to do this?
Once again, either:
1. Set up a second Django settings file, fill in the read-only user
there, and use that settings file for the site that your clients use.
2. Write your custom query method so that it manually imports the
database adapter and creates its own completely separate database
connection, without ever using the one provided by Django.
The first option is the better of the two.
David Reynolds
On 4 Feb 2008, at 1:59 am, Julien wrote:
> I totally understand what you suggest, having a RO user at the
> database (in this case MySQL) level.
> But I am fairly new to Django and Python, and I am unsure how to
> implement that dual-setting option.
In the devlopment server you can do
./manage.py runserver --settings=yoursite.readonlysettings # for the
display side of the site
and
./manage.py runserver # for the admin side of the site
When you deploy the site you can set up which settings file is used too.
--
David Reynolds
da...@reynoldsfamily.org.uk
Julien
However, what I'd like to have is that the site runs as per usual with
a root user (with all priviledges), and that only one particular apps
runs with a read-only user. Do I have to create a new connection
object within my app code to override Django's, is it possible to
ovveride the parameter via a setting file?
In PHP, since you create a connection object yourself, you can select
which user (and also, which database, host, etc.) to execute a
particular request. It would be great to make it easy in Django to
allow that sort of flexibility, at least for custom made hard-coded
SQL queries.
koenb
ticket #4747). It is a bit behind on trunk, but the basics should
work. It allows you to define multiple connections, so you should be
able to connect to the same db using different users.
Koen
James Bennett
> You could take a look at the multidb branch (specifically check out
> ticket #4747). It is a bit behind on trunk, but the basics should
> work. It allows you to define multiple connections, so you should be
> able to connect to the same db using different users.
If all he wants is a single method doing raw queries on a different
connection, that's easy enough to do without needing a massive rewrite
of Django -- he can just import the correct DB adapter module, set up
a connection and go.