Control access to raw query and level across multiple models in Django

Question

I am trying to provide a user interface for writing custom database queries. I need to make sure that they can only request records that are allowed to them. For this, I decided to apply row-based access control using django-guardian .

This is how my circuits look

class BaseClass(models.Model): somefield = models.TextField() class Meta: permissions = ( ('view_record', 'View record'), ) class ClassA(BaseClass): # some other fields here classb = models.ForeignKey(ClassB) class ClassB(BaseClass): # some fields here classc = models.ForeignKey(ClassC) class ClassC(BaseClass): # some fields here 

I would like to be able to use get_objects_for_group as follows:

 >>> group = Group.objects.create('some group') >>> class_c = ClassC.objects.create('ClassC') >>> class_b = ClassB.objects.create('ClassB', classc=class_c) >>> class_a = ClassA.objects.create('ClassA', classb=class_b) >>> assign_perm('view_record', group, class_c) >>> assign_perm('view_record', group, class_b) >>> assign_perm('view_record', group, class_a) >>> get_objects_for_group(group, 'view_record') 

This gives me a QuerySet. Is it possible to use the base class that I defined above and write a raw query for other related classes?

 >>> qs.intersection(get_objects_for_group(group, 'view_record'), \ BaseClass.objects.raw('select * from table_a a' 'join table_b b on a.id=b.table_a_id ' 'join table_c c on b.id=c.table_b_id ' 'where some conditions here')) 

Does this approach make sense? Is there a better way to solve this problem?

Thanks!

Edit:

Another way to solve the problem could be to create a separate table for each user. I understand that this can add to my application, but:

• The number of users will not be more than 100 seconds for a long time. Not a consumer app.
• In our use case, it is rather unlikely that I will need to query these tables. I will not write a query that should aggregate something from table1, table2, table3, which belongs to the same model.
• Maintaining a separate table for each client can be an advantage.

Do you think this is a viable approach?

Answer 1

I think you already know what you need to do. The word you are looking for is multithreading. Although this is not one table for each client. The best option for you would be one scheme for each client. Unfortunately, the best article I've had on multiplayer work is no longer available. See if you can find the cached version: https://msdn.microsoft.com/en-us/library/aa479086.aspx , otherwise there are many articles on the Internet.

Another viable approach is to view user managers . You can write one user manager for each model client and request it accordingly. But all this will lead to complexity of applications and will soon come out of your hands. Any mistake in the security level of the application is a nightmare for you.

Weighing both, I would be inclined to say that the solution for a multiplayer game, as you said in your editing, is by far the best approach.

Answer 2

After exploring many options, it turned out that I can solve this problem at the database level using Row Level Security on PostgreSQL. It becomes the simplest and most elegant.

This article has helped me greatly simplify application-level users with PostgreSQL policies.

What I learned by doing my research:

• There may still be separate tables in the future when clients can potentially affect each other's query performance, as they are allowed to execute arbitrary queries.

• Trying to solve the problem at the ORM level is almost impossible if you plan to use raw or special requests.

Answer 3

First, you must provide us with more detailed information on how your architecture is installed and built using django so that we can help you. Have you implemented the API? using a django template is not really a good idea if you are building a large-scale application consuming a lot of data. Because it can affect the loading of requests massively. I can suggest extracting your frontend from the backend.