Row based permissions: In DB or App? ... farewell pain

[guettli]

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

[guettli]

Hello this post is now four days old. I would like to hear from other people.

Something like:

 "I have no clue what you are talking about" or
 "I understand your concerns, but I have no clue, too" or
 "Thank you about talking about this, this raised my awareness"

would make me happy.

Thank you.

[Andréas Kühne]

Hi,

I understand your concern, however I would like to learn more about how you intend to solve the problem. The only way I could see a solution would be to change the database user depending on which application user is logged in. That would mean updating users and permissions in the database every time it changes on the application level. I think this would add a lot of complexity that you really don't want. 

You could of course have one role for each group you intend to have in the application and then apply the permissions accordingly. That would however mean that your user permissions solution is very rigid. One of the things that I like about django is the ability to add and delete permissions easily and swiftly. I'm wondering if you instead couldn't create mixins to make sure that the permissions are correct for each object. That way the code will be easily testable and also less error prone. However it won't create great SQL queries though.

Also - I find that working in Python is a lot easier than working in raw SQL :-) but you will of course need to adapt the queries to make them faster (which can be done by prefetching for example).

That's just my 2 cents.

Regards,

Andréas

--
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+unsubscribe@googlegroups.com.
To post to this group, send email to django...@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/b2f0f560-9e83-4a90-9277-23c19f992c0b%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Mike Morris]

I have no expertise in the field, but I've chosen not to let that stop me from making a suggestion :-)

How about splitting the difference:

1. Assign & track permissions on the application side, then
2. Pass the permission level/parameters into a Stored Procedure in the database

If the Stored Procedure accepted permission info, it would not even need to know who the user was, just their permissions. (So no need for DB user switching, etc) ACLs would be enforced 100% in the DB, while rights management stayed in the app.

You'd just have to figure out a flexible enough set or parameters to pass into the stored procedure to allow all the permission permutations you need... ever... ;-)

To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.

To post to this group, send email to django...@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/CAK4qSCfd8Yqo%3DhPKqU_49nhW-6ma-G4QTpo%3DVFzdyRc1F7c7Yw%40mail.gmail.com.

[Mike Dewhirst]

On 7/08/2017 4:43 PM, guettli wrote:
> Hello this post is now four days old. I would like to hear from other
> people.
>
> Something like:
>
> "I have no clue what you are talking about" or
> "I understand your concerns, but I have no clue, too" or
> "Thank you about talking about this, this raised my awareness"

I have a friend who invested heavily in a project (not Django) and went
broke. The reason is he put some of the business logic in the database
and some in the app. Because of that split, the project became
unmaintainable. New developer hires didn't really understand.

I decided to do everything in the app. We retain flexibility to adjust
the entire system provided you can see all the business logic in one
place. Otherwise we would be relying on unseen magic. If performance
becomes a problem and caching has not helped and schema redesign hasn't
helped you can move logic to the database layer but only (sensibly) if
the requirements have settled completely.

With migrations, Django is fabulous. It is possible you can migrate the
IDML but we haven't reached that stage. Our project does depend on
auth.permissions but also we run queries to display records based on
licences which exist between users. I guess that is row-level
"permissions". I investigated all the object-permissions apps with a
view to implement one but our requirements would have needed complex
code. Easier to do our own.

Cheers

Mike

>
> would make me happy.
>
> Thank you.
>
> Am Donnerstag, 3. August 2017 10:07:53 UTC+2 schrieb guettli:
>
> 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

> <https://www.postgresql.org/message-id/e662fd8a-6001-514c-71e8-01718444f338%40thomas-guettler.de>
>
> --
> 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...@googlegroups.com
> <mailto:django-users...@googlegroups.com>.

> To post to this group, send email to django...@googlegroups.com

> <mailto:django...@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/b2f0f560-9e83-4a90-9277-23c19f992c0b%40googlegroups.com

> <https://groups.google.com/d/msgid/django-users/b2f0f560-9e83-4a90-9277-23c19f992c0b%40googlegroups.com?utm_medium=email&utm_source=footer>.

[Antonis Christofides]

Hello,

This is a tricky issue and we need to start from the basics. You already know the basics, but they bear repeating. (Related questions are relatively common, which is why this is something like the third time I'm pasting this information here).

As you know, RDBMS's keep their own list of users and have sophisticated permissions systems with which different users have different permissions on different tables. This is particularly useful in desktop applications that connect directly to the database. Web applications changed that. Instead of the RDBMS managing the users and their permissions, we have a single RDBMS user as which Django connects to the RDBMS, and this user has full permissions on the database. The actual users and their permissions are managed by Django itself (more precisely, by the included Django app django.contrib.auth), using database tables created by Django. What a user can or cannot do is decided by Django, not by the RDBMS. This is a pity because django.contrib.auth (or the equivalent in other web frameworks) largely duplicates functionality that already exists in the RDBMS, and because having the RDBMS check the permissions is more robust and more secure. I believe that the reason web frameworks were developed this way is independence from any specific RDBMS, but I don't really know.

So the canonical way of working is to have a single database user as which Django logs on to the database, with full permissions on the database (including permission to create and delete tables), and many Django users, each one with different permissions. Typically only one Django superuser is created. I call the superuser "admin", which I believe is the common practice.

You can probably do things differently, and maybe there exist custom database backends that would allow you to switch the database user on login, but if there's no compelling reason you should really stick to the canonical way.

So, I agree with you that, in theory, it would be more elegant (and more robust and more secure) to do this thing at the db level, and that it is a pity that Django and similar systems have moved this functionality from the db to the app. However, as I said, without a compelling reason you should really stick to the canonical way. The canonical way is more maintainable, because it's widely used. It will be easier for other programmers to understand what you are doing. Elegance of solution is not compelling enough. A compelling reason would be, for example, if you also have a desktop application that is controlled by the user and connects directly to the database.

Regards,

Antonis

Antonis Christofides
http://djangodeployment.com

--
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...@googlegroups.com.
To post to this group, send email to django...@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/b2f0f560-9e83-4a90-9277-23c19f992c0b%40googlegroups.com.

[Vijay Khemlani]

I use django-guardian for object level permissions and it works surprisingly well

To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscribe@googlegroups.com.

To post to this group, send email to django...@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/b2f0f560-9e83-4a90-9277-23c19f992c0b%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
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+unsubscribe@googlegroups.com.

To post to this group, send email to django...@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/45859506-8dc4-09b9-8386-5a995917a247%40djangodeployment.com.

[James Schneider]

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

[Antonis Christofides]

Hi,

- 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

Not necessarily. Django could connect to the db as a PostgreSQL superuser and middle that runs very early could use "set session authorization" to switch user.

Regards,

Antonis

Antonis Christofides
http://djangodeployment.com

--
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...@googlegroups.com.

To post to this group, send email to django...@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.

[Antonis Christofides]

middle that runs very early

middleware

Antonis Christofides
http://djangodeployment.com

[James Schneider]

Sorry for the duplicate, accidently hit send before I was done, finished below.

On Aug 3, 2017 1:08 AM, "guettli" <guet...@gmail.com> wrote:

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. 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. Under load, that cost will likely be noticeable.

- 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 per user. DB's may or may not fare well under those conditions. I haven't tried that model, though, so I may be mistaken. Managing dozens, hundreds, or thousands of threads/processes in the DB all attacking the same data set does not sound desirable, especially with locking and/or transactions in the mix.

- It will likely be very rigid. DB's are not necessarily good at what it is you are trying to do, and may lack features. Any set of rules you establish will probably be more basic than you are anticipating/hoping, or will be near impossible to modify once in production. Again, this is a guess.

- Performance gains may be marginal, especially weighted against the amount of complexity introduced. Your claim that the DB queries are "slow" is probably somewhat accurate due to the GFK usage by the contrib.auth package. However, the GFK's provide a great deal of flexibility, which usually offsets the marginal performance hit they introduce. I'd rather run two reasonably fast queries than rely on a custom rolled set of triggers, DB views, stored procedures, and black magic offered by the DB.

- Maintenance and sync will be a nightmare. Users who are disabled, modified, or deleted may pose a risk of synch issues, as now you have to keep that data in sync in two locations (ie the users' password).

Your argument of "if/elif" statements being undesirable for auth is likely not based on any decent production system, granted they can get very hairy in complex scenarios. I can chalk that up to personal preference, but I think it unwise to discard an awesome tool in your chest in liueu of a hard dependency on the DB.

I would suggest that rather than looking to optimize at the DB level, start with rolling your own authentication/authorization system instead of pushing the work out. Your queries can be tweaked and tuned to keep them simple and fast without having to track things in the DB.

https://docs.djangoproject.com/en/dev/topics/auth/customizing/

-James

[guettli]

Am Montag, 7. August 2017 09:43:00 UTC+2 schrieb Andréas Kühne:

Hi,

I understand your concern, however I would like to learn more about how you intend to solve the problem. The only way I could see a solution would be to change the database user depending on which application user is logged in. That would mean updating users and permissions in the database every time it changes on the application level. I think this would add a lot of complexity that you really don't want. 

Using the postgres users would be possible, but like you I think this makes things more complicated than necessary.


 

You could of course have one role for each group you intend to have in the application and then apply the permissions accordingly. That would however mean that your user permissions solution is very rigid. One of the things that I like about django is the ability to add and delete permissions easily and swiftly. I'm wondering if you instead couldn't create mixins to make sure that the permissions are correct for each object. That way the code will be easily testable and also less error prone. However it won't create great SQL queries though.

Also - I find that working in Python is a lot easier than working in raw SQL :-) but you will of course need to adapt the queries to make them faster (which can be done by prefetching for example).

Yes, writing python is more easy, but if you need the conditions in SQL WHERE to get a queryset, you don't have python. You have a nice ORM MyModel.objects.filter(...) which
is nice and helps to create the needed SQL statements.


 

To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.

[guettli]

Am Montag, 7. August 2017 09:55:45 UTC+2 schrieb Mike Morris:

I have no expertise in the field, but I've chosen not to let that stop me from making a suggestion :-)

How about splitting the difference:

1. Assign & track permissions on the application side, then
2. Pass the permission level/parameters into a Stored Procedure in the database

If the Stored Procedure accepted permission info, it would not even need to know who the user was, just their permissions. (So no need for DB user switching, etc) ACLs would be enforced 100% in the DB, while rights management stayed in the app.

You'd just have to figure out a flexible enough set or parameters to pass into the stored procedure to allow all the permission permutations you need... ever... ;-)

Yes, sounds good. Thank you for your feedback.

 

To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
To post to this group, send email to django...@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/b2f0f560-9e83-4a90-9277-23c19f992c0b%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[guettli]

Am Montag, 7. August 2017 14:48:54 UTC+2 schrieb Vijay Khemlani:

I use django-guardian for object level permissions and it works surprisingly well

Good to hear this. I will have a look at it.
 

To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.

To post to this group, send email to django...@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/b2f0f560-9e83-4a90-9277-23c19f992c0b%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
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...@googlegroups.com.

To post to this group, send email to django...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.

[guettli]

 

My concern is that this python code can't return a queryset with all items where a given permission+user tuple match.

def has_perm(obj, user):
    if user.is_superuser:
        return True

This means I need a SQL WHERE condition

For example MyModel.objects.filter(Q(...)|Q(....))  

I never wanted business logic in the database. Sorry, if you misunderstood me.

[guettli]

Vendor locking? Yes, that's true. My solution is fixed to PostgreSQL. But for me this is not a problem.

- 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. Under load, that cost will likely be noticeable.

I think our servers can handle the load well. Somewhere the permission checks need to be done. I think the db is faster than python if there are many rows.

 

- 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 per user. DB's may or may not fare well under those conditions. I haven't tried that model, though, so I may be mistaken. Managing dozens, hundreds, or thousands of threads/processes in the DB all attacking the same data set does not sound desirable, especially with locking and/or transactions in the mix.

- It will likely be very rigid. DB's are not necessarily good at what it is you are trying to do, and may lack features. Any set of rules you establish will probably be more basic than you are anticipating/hoping, or will be near impossible to modify once in production. Again, this is a guess.

- Performance gains may be marginal, especially weighted against the amount of complexity introduced. Your claim that the DB queries are "slow" is probably somewhat accurate due to the GFK usage by the contrib.auth package. However, the GFK's provide a great deal of flexibility, which usually offsets the marginal performance hit they introduce. I'd rather run two reasonably fast queries than rely on a custom rolled set of triggers, DB views, stored procedures, and black magic offered by the DB.

- Maintenance and sync will be a nightmare. Users who are disabled, modified, or deleted may pose a risk of synch issues, as now you have to keep that data in sync in two locations (ie the users' password).

Your argument of "if/elif" statements being undesirable for auth is likely not based on any decent production system, granted they can get very hairy in complex scenarios. I can chalk that up to personal preference, but I think it unwise to discard an awesome tool in your chest in liueu of a hard dependency on the DB.

I would suggest that rather than looking to optimize at the DB level, start with rolling your own authentication/authorization system instead of pushing the work out. Your queries can be tweaked and tuned to keep them simple and fast without having to track things in the DB.

https://docs.djangoproject.com/en/dev/topics/auth/customizing/

-James

 thank you for your suggestions. I will think about it again.

[Vijay Khemlani]

https://django-guardian.readthedocs.io/en/stable/userguide/check.html#get-objects-for-user

projects = get_objects_for_user(request.user, 'projects.view_project')

--
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+unsubscribe@googlegroups.com.

To post to this group, send email to django...@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/64d929fa-ea5c-49ca-8b9f-5ff872616435%40googlegroups.com.

[guettli]

Am Mittwoch, 9. August 2017 18:03:44 UTC+2 schrieb Vijay Khemlani:

https://django-guardian.readthedocs.io/en/stable/userguide/check.html#get-objects-for-user

projects = get_objects_for_user(request.user, 'projects.view_project')

Thank you for providing this link. I guess it is new to many developers.

I worked with this method some years ago and it was slow. It was slow since the permission checking
gets done at several ways (via is_superuser, via perms for user, via perms for group, ...) But this is old and outdated
information. I guess this is not true any more.

 

[Vijay Khemlani]

It's still implemented like that in the background, but I'm not sure why do you call it slow.

Did you run benchmarks? Profiling?

Usually your own business logic will be the bottleneck of your application, not your permission checking.

--

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+unsubscribe@googlegroups.com.
To post to this group, send email to django...@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/afc9ac15-8e61-400a-a36c-c62b5bac22dc%40googlegroups.com.

[James Schneider]

My concern is that this python code can't return a queryset with all items where a given permission+user tuple match.

def has_perm(obj, user):
    if user.is_superuser:
        return True

This means I need a SQL WHERE condition

For example MyModel.objects.filter(Q(...)|Q(....))  

I never wanted business logic in the database. Sorry, if you misunderstood me.

has_perm() is not used for that purpose, so it isn't a fair comparison.

If you want items specific to a permission gradient, you'll need to determine the logic to emulate it yourself. In most cases, with a correct model design, it isn't terribly difficult. 

For example, if user Joey can only see red widgets, then you would need to specifically filter for red widgets in your view query set. The color (or list of colors) may need to be pulled from a different table, causing a second query. The ORM does have some advanced usages for filtering a model based on values in an related model, in most cases using a JOIN to keep it as a single cross-table query. If that's all you're trying to do, I doubt any custom database procedure will be much faster than a good query set.

Do you have evidence of such queries being slow in the past? Profiling them will lead you to the specific operations that may be slowing you down and can likely be customized through more specific queries in a custom authentication back end, or better direct querying within the views.

Guardian is a good package, but frankly it is a polar opposite implementation (all Python, use of GFK's incurring multiple queries, etc.) from the one you are proposing.

-James

[guettli]

Am Donnerstag, 10. August 2017 14:30:31 UTC+2 schrieb Vijay Khemlani:

It's still implemented like that in the background, but I'm not sure why do you call it slow.

Did you run benchmarks? Profiling?

Usually your own business logic will be the bottleneck of your application, not your permission checking.

I can't provide details any more. It was long ago. Performance of PostgreSQL SQL optimizer increased a lot.

[guettli]

Am Freitag, 11. August 2017 10:05:00 UTC+2 schrieb James Schneider:

My concern is that this python code can't return a queryset with all items where a given permission+user tuple match.

def has_perm(obj, user):
    if user.is_superuser:
        return True

This means I need a SQL WHERE condition

For example MyModel.objects.filter(Q(...)|Q(....))  

I never wanted business logic in the database. Sorry, if you misunderstood me.

has_perm() is not used for that purpose, so it isn't a fair comparison.

If you want items specific to a permission gradient, you'll need to determine the logic to emulate it yourself. In most cases, with a correct model design, it isn't terribly difficult. 

For example, if user Joey can only see red widgets, then you would need to specifically filter for red widgets in your view query set. The color (or list of colors) may need to be pulled from a different table, causing a second query. The ORM does have some advanced usages for filtering a model based on values in an related model, in most cases using a JOIN to keep it as a single cross-table query. If that's all you're trying to do, I doubt any custom database procedure will be much faster than a good query set.

I learned something during the last days. The question is not "Should permission checking be done at application level?" the question is "How to do the permission checking?"

Yes, I think like you. I guess using a database procedure or a django ORM filter() does not matter much. I guess it will have the same speed.




 

Do you have evidence of such queries being slow in the past? Profiling them will lead you to the specific operations that may be slowing you down and can likely be customized through more specific queries in a custom authentication back end, or better direct querying within the views.

Yes, here is the related issue: https://code.djangoproject.com/ticket/27260#comment:10

You can work-around this be using union() instead of sql-joins.

I like python and in we did a lot of permission checking with code like this in the past "if ...: return True" - It needs some time to discover that this is a dead end if you want
a queryset of all items sooner or later.

don't get me wrong. I am not writing this because I complain.

I write here because I want to learn more, and I write here because I want to spread the things I learned.

This time, this topic is really ambivalent. Up to now I liked SQL but preferred python. Now I see that more and more source code moves to this simple set operations engine :-)

With "moves to .." I mean this:

My permission checks in the past: "if ...: return True"
My permission checks in the future: SomeModel.objects.filter(Q(...)|Q(...)).exists()




 

Guardian is a good package, but frankly it is a polar opposite implementation (all Python, use of GFK's incurring multiple queries, etc.) from the one you are proposing.

I want a get_objects_for_user(). That's my goal. Up to now I am unsure which strategy fits best.

Regards,
  Thomas

[guettli]

Am Donnerstag, 10. August 2017 14:30:31 UTC+2 schrieb Vijay Khemlani:

It's still implemented like that in the background, but I'm not sure why do you call it slow.

Here are more docs about guardian being slow:

http://django-guardian.readthedocs.io/en/stable/userguide/performance.html


 

Did you run benchmarks? Profiling?

Yes, we profiled this. The above link explains it better than I can do it :-)

Regards,
  Thomas Güttler

[Vijay Khemlani]

So, please share the specific numbers you got from your profiling.

--

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+unsubscribe@googlegroups.com.
To post to this group, send email to django...@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/5b892553-dacf-46f6-81e1-6004924cc474%40googlegroups.com.

[guettli]

Am Mittwoch, 16. August 2017 13:39:53 UTC+2 schrieb Vijay Khemlani:

So, please share the specific numbers you got from your profiling.

Why should I share the numbers? How would this help?

[Vijay Khemlani]

How can we propose faster alternatives if we don't know how fast must it be?

Of course it is up to you in the end, but if you are seriously considering exotic options such as using stored procedures at least I would like to know what specific bottlenecks did you stumble upon as I also use object-level permissions extensively in my own project and haven't had any performance issues yet.

If nothing else, your results would prove useful to the community.

--

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+unsubscribe@googlegroups.com.
To post to this group, send email to django...@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/7e009200-1a25-4609-93c3-49117e509e6d%40googlegroups.com.

[guettli]

Am Donnerstag, 17. August 2017 16:09:42 UTC+2 schrieb Vijay Khemlani:

How can we propose faster alternatives if we don't know how fast must it be?

Of course it is up to you in the end, but if you are seriously considering exotic options such as using stored procedures at least I would like to know what specific bottlenecks did you stumble upon as I also use object-level permissions extensively in my own project and haven't had any performance issues yet.

First I had stored procedures on my mind, but I talked to a postgreSQL expert and he said that the django ORM would be fast enough in most cases. This means
 "has_perm" checking would be finally SQL, something like MyModel.objects.filter(...).exists()

The bottle neck I had with guardian is explained on the page below. It is not new, it is well known. My case is not different. Up to now we use generic foreign keys. That's why I think
numbers won't help here.

http://django-guardian.readthedocs.io/en/stable/userguide/performance.html

 

If nothing else, your results would prove useful to the community.

Yes, if I find a solution, I will publish it. This is important for me, since the solutions I provide to our customers are mostly
the work of open source libraries. I just put these great peaces together with some lines of python code.

I ask myself how to be most useful for the community?