subject:"Re\: Row based permissions\: In DB or App\? ... farewell pain"

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

2017-08-18 Thread 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?


-- 
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/eae520bb-6c06-44dc-a0e9-9e616575c6fe%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

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

2017-08-17 Thread 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.

On Thu, Aug 17, 2017 at 10:18 AM, guettli  wrote:

>
>
> 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?
>
> --
> 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/7e009200-1a25-4609-93c3-49117e509e6d%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+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/CALn3ei0H27DaZhZgLX8hqshhkDOaaYxVFB2k7WmpOLhsJZMjqg%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

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

2017-08-17 Thread 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? 

-- 
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/7e009200-1a25-4609-93c3-49117e509e6d%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

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

2017-08-16 Thread Vijay Khemlani

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

On Wed, Aug 16, 2017 at 6:10 AM, guettli  wrote:

>
>
> 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
>
> --
> 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/5b892553-dacf-46f6-81e1-6004924cc474%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+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/CALn3ei00s5MPZRktpgrF8amdDhHCoBJMPgMnDXio9wnf5jcAcg%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

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

2017-08-16 Thread 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

-- 
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/5b892553-dacf-46f6-81e1-6004924cc474%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

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

2017-08-11 Thread 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

-- 
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/6381461b-ac40-41d2-bfe6-47fcb0acf64e%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

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

2017-08-11 Thread 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.

-- 
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/0faa4721-86f8-4fef-9934-280e08521bec%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

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

2017-08-11 Thread 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

-- 
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%2BciUUiVsat3Pwb%3DaJ7FSC_oUxJ1gv6dApGi-fOnOo6nq-VA%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

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

2017-08-10 Thread 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.

On Thu, Aug 10, 2017 at 5:15 AM, guettli 
wrote:

>
>
> 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.
>
>
>
> --
> 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/afc9ac15-8e61-400a-a36c-c62b5bac22dc%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+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/CALn3ei3u%3Dk4Js%3DknDkWVAwPSfYVbx5fL78BeFabkAkD1mOT%2Biw%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

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

2017-08-10 Thread 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.

 

-- 
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/afc9ac15-8e61-400a-a36c-c62b5bac22dc%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

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

2017-08-09 Thread 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')


On Wed, Aug 9, 2017 at 10:55 AM, guettli 
wrote:

>
>
> Am Mittwoch, 9. August 2017 10:04:25 UTC+2 schrieb James Schneider:
>
>>
>>
>> On Aug 3, 2017 1:08 AM, "guettli"  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
>>
>>
>>
>
>
> 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.
>
>
> --
> 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/64d929fa-ea5c-49ca-8b9f-5ff872616435%40googlegroups.com
> 
> .
>
> For more options, visit https://groups.google.com/d/optout.
>

-- 
You received this m

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

2017-08-09 Thread guettli



Am Mittwoch, 9. August 2017 10:46:10 UTC+2 schrieb James Schneider:
>
> Sorry for the duplicate, accidently hit send before I was done, finished 
> below.
>
> On Aug 3, 2017 1:08 AM, "guettli" > 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. 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. ;-)
>
>

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 c

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

2017-08-09 Thread guettli



Am Mittwoch, 9. August 2017 10:04:25 UTC+2 schrieb James Schneider:
>
>
>
> On Aug 3, 2017 1:08 AM, "guettli" > 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
>
>
>
 

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.


-- 
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/64d929fa-ea5c-49ca-8b9f-5ff872616435%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

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

2017-08-09 Thread 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.
 

>
> On Mon, Aug 7, 2017 at 4:59 AM, Antonis Christofides <
> ant...@djangodeployment.com > wrote:
>
>> 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 Christofideshttp://djangodeployment.com
>>
>>
>> On 2017-08-07 09:43, 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"
>>
>> 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 gu

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

2017-08-09 Thread 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.

 

> On 08/07/2017 12:42 AM, Andréas Kühne wrote:
>
> 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
>
> 2017-08-07 8:43 GMT+02:00 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.
>>
>> 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
>>>
>>> -- 
>> 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
>>  
>> 
>> .
>> For more options, visit https://groups.google.com

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

2017-08-09 Thread 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.


 

> That's just my 2 cents.
>
> Regards,
>
> Andréas
>
> 2017-08-07 8:43 GMT+02:00 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.
>>
>> 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
>>>
>>> -- 
>> 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
>>  
>> 
>> .
>> 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+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/17c3c627-6cce

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

2017-08-09 Thread James Schneider

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

On Aug 3, 2017 1:08 AM, "guettli" 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. 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 i

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

2017-08-09 Thread Antonis Christofides

> middle that runs very early
middleware

Antonis Christofides
http://djangodeployment.com

On 2017-08-09 11:15, Antonis Christofides wrote:
>
> 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
> On 2017-08-09 11:03, James Schneider wrote:
>>
>>
>> On Aug 3, 2017 1:08 AM, "guettli" > > 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

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

2017-08-09 Thread 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

On 2017-08-09 11:03, James Schneider wrote:
>
>
> On Aug 3, 2017 1:08 AM, "guettli"  > 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
> .
> V

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

2017-08-09 Thread James Schneider

On Aug 3, 2017 1:08 AM, "guettli" 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.

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.

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 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.

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

2017-08-07 Thread Vijay Khemlani

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

On Mon, Aug 7, 2017 at 4:59 AM, Antonis Christofides <
anto...@djangodeployment.com> wrote:

> 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 Christofideshttp://djangodeployment.com
>
>
> On 2017-08-07 09:43, 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"
>
> 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
>>
>> --
> You received this mes

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

2017-08-07 Thread 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


On 2017-08-07 09:43, 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"
>
> 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
> 
> 
>
> -- 
> Y

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

2017-08-07 Thread 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

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+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/b3c424de-e0cc-9125-eef2-4321cf0d3ad0%40dewhirst.com.au.
For more options, visit https://groups.google.com/d/optout.

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

2017-08-07 Thread 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... ;-)

On 08/07/2017 12:42 AM, Andréas Kühne wrote:

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

2017-08-07 8:43 GMT+02:00 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.

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

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

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

2017-08-07 Thread 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

2017-08-07 8:43 GMT+02:00 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.
>
> 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
>>
>> --
> 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/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+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/CAK4qSCfd8Yqo%3DhPKqU_49nhW-6ma-G4QTpo%3DVFzdyRc1F7c7Yw%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

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

2017-08-06 Thread 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.

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
>
>

-- 
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/b2f0f560-9e83-4a90-9277-23c19f992c0b%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

26 matches

Site Navigation

Mail list logo

Footer information