On Aug 3, 2017 1:08 AM, "guettli" <guettl...@gmail.com> wrote:
First I asked a similar question on the postgresql-general list. The
discussion[1] has settled there.
Now I would love the hear what you think.
I am thinking about rewriting an existing application which uses
PostgreSQL via Django.
Up to now the permission checks are done at the application level.
Up to now queries like: "Show all items which the current user is
allowed to modify" result in complicated SQL and
this leads to slow queries.
Up to now there is one db-user and the application does the filtering
of rows to prevent application users to see
items which they are not allowed to see.
I guess most web applications work like this.
I would like to reduce the "ifing and elsing" in my python code (less
conditions, less bugs, more SQL, more performance)
One important intention for me: I would like to avoid the redundancy.
As soon as I want to query for
"Show all items which the current user is allowed to modify" I need
the permission checking in a SQL WHERE condition.
If I implement this. Then my code which might look like this is redundant:
{{{
def has_perm(obj, user):
if user.is_superuser:
return True
...
}}}
Yes, I feel farewell pain. I love Python, but I guess I will use perm
checking via SQL WHERE for new projects in the future.
What do you think?
Regards,
Thomas Güttler
[1]:
https://www.postgresql.org/message-id/e662fd8a-6001-514c-71e8-01718444f338%40thomas-guettler.de
I thumbed through the threads on the PG list, and the best summary of the
answers you received is:
A. Yes you can probably do that, but it would likely be extremely
complicated and Django won't necessarily make it easy.
B. The "problem" you are trying to solve is more of a "preference" with a
questionable motive of speed increases.
There wasn't a great amount of support for what you are trying to do, and I
doubt you'll find much on this list.
The DB should not be responsible for business logic (in this case,
authorization rules). DB's are only good at storing, searching, and
returning data. Data doesn't care who you are, nor does the DBMS past the
table level in most cases.
There are several other points to consider.
- By moving this logic in to the DB, you are almost certainly vendor
locking yourself to the DB. If your project is not distributed to others,
then it may not be an issue. If you do, abstract away the function calls as
much as possible to build an internal Python API. If you need to change the
underlying database functionality later, your application code should
remain relatively untouched, and you can send the bundle of money in labor
you saved to me. ;-)
- You'll almost certainly take a performance hit when connecting to the
database. If each connection to the DB is using a different user, then you
likely cannot take advantage of things like DB connection pooling. Every
request would require that a connection be built, utilized, and then torn
down. Those operations have a cost in both time and resources.
- It likely won't scale well. Assuming you have a fair number of unique
users with concurrent connections (depending on the resources available and
tuning, this could be as little as a few dozen), your DB now has to manage
at least a single connection or
--
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 https://groups.google.com/group/django-users.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/CA%2Be%2BciV1obpdOUpLdMQ9jwYgLOjisUcUmVbFfHOv8qKE4EQPmA%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.
