Re: Row based permissions: at DB or at Application level?

Am 25.07.2017 um 12:59 schrieb vinny:
> On 2017-07-25 11:40, Thomas Güttler wrote:
>> I would like to reduce the "ifing and elsing" in my python code (less
>> conditions, less bugs, more SQL, more performance)
>>
>> Regards,
>> Thomas Güttler
>>
>
> A quick brainstorm:
>
> You could, probably...
> but you'd have to create a separate database user for every Django user,
> get Django to connect to the database as that user
> and setup policies for each of those users, for every use-case.

Yes, this could be done. ... I am unsure

>
> When I look at an example policy from the manual:
>
> CREATE POLICY fp_u ON information FOR UPDATE
> USING (group_id <= (SELECT group_id FROM users WHERE user_name = current_user));
>
> I'm not sure if this is any less bug-sensitive than an IF in Python...

Somehow I trust set operations more then "if" and "else" in a programming language.

> And don't forget you have to interpret any error-response from the database into
> something that Django can make understandable to the end-user.

Yes? An internal server error is an internal server error. I don't think that you
can create anything understandable. You can reply "We are sorry".

But maybe I misunderstood what you mean with "error-response from the database".

> I'm not saying row-level security is bad, far from it, but I doubt that using it
> to replace Django's own security is going to magically make life much easier.

My current concer: I want a SELECT statement wich returns all rows a user is allowed to see.

This mean all conditions in my python/django code won't help me. I need a way to
create a WHERE clause for this. If I need this in a WHERE clause, then I don't want
to have two implementations (once in python, once in SQL-WHERE clause).

How to create the WHERE clause is a different topic. I like the django ORM filter methods very much.

Next thing is where to apply the WHERE.

I could create it in django, or use PG feature "Row Security Policies" ...

Using Django-ORM-Filter-methods in "Row Security Policies" would be cool ...

This is brainstorming and I am just trying to widen my horizont. Feedback welcome!

Regards,
Thomas Güttler

--
Thomas Guettler http://www.thomas-guettler.de/