Raw query and row level access control over multiple models in Django

Question

I'm trying to provide an interface for the user to write custom queries over the database. I need to make sure they can only query the records they are allowed to. In order to do that, I decided to apply row based access control using django-guardian.

Here is how my schemas look like

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. Can I use the BaseClass that I defined above and write a raw query over 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 tackle this problem?

Thanks!

Edit:

Another way to tackle the problem might be creating a separate table for each user. I understand the complexity this might add to my application but:

• The number of users will not be more than 100s for a long time. Not a consumer application.
• Per our use case, it's quite unlikely that I'll need to query across these tables. I won't write a query that needs to aggregate anything from table1, table2, table3 that belongs to the same model.
• Maintaining a separate table per customer could have an advantage.

Do you think this is a viable approach?

Answer 1

After researching many options I found out that I can solve this problem at the database level using Row Level Security on PostgreSQL. It ends up being the easiest and the most elegant.

This article helped me a lot to bridge the application level users with PostgreSQL policies.

What I learned by doing my research is:

• Separate tables could still be an option in the future when customers can potentially affect each others' query performances since they are allowed to run arbitrary queries.

• Trying to solve it at the ORM level is almost impossible if you are planning to use raw or ad-hoc queries.