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

Am 27.07.2017 um 12:05 schrieb vinny:
> On 2017-07-27 10:27, Thomas Güttler wrote:
>> 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)
>>>>
>
>>
>>>
>>> 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.
>
> I understand the feeling, but realistically; you're doing exactly the same, just in a different syntax.
>
>>
>>> 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.
>>
>> But maybe I misunderstood what you mean with "error-response from the
>> database".
>
> Indeed you did :-)
>
> row-level security will cause the database to start returning permission-denied messages,
> for example:
> (taken from the manual)
>
> postgres=> update passwd set shell = '/bin/xx';
> ERROR: new row violates WITH CHECK OPTION for "passwd"
>
> Your application will have to be able to translate that error into something that the user can understand.
> In this case it should be something like "Sorry, this password is not allowed".

I see. I could just pass the error unchanged to the user ... Would look ugly ...

>
>
>>
>> My current concer: I want a SELECT statement wich returns all rows a
>> user is allowed to see.
>
> Sure, but the permissions in your application are not just like "John can see rows 1-4 and Pete can see rows that have
> isVisible=True"
> In a forum you may have a rule that says that posting new topics is only allowed if you have posted more than ten replies,
> and the forum allows now posts and the user is not banned. So the row-level permission has to check the user and the forum
> to decide what to do, and those rulings cannot be done using row-level security so you will have to write pgsql functions
> that do those checks on those records and.... well the whol row-level thing turns into a group of functions very quickly.

Yes, you write what I had in mind. Since it is easy to extend the permission checks in a programming language (example
you are allowed if you have posted more than ten replies) you soon have many functions in your programming language.

This is easy and gets done everywhere.

As soon as you need the permission check in a SQL WHERE the whole "I love programming and typing" way of doing things
get a very complex SQL WHERE condition. Yes in theory you can translate every if-then-else checking from your
code to SQL.

I don't want to change my existing systems, which I currently develop and maintain.

But if I start from scratch, then why not put all that stuff into SQL ...

>
>>
>> This is brainstorming and I am just trying to widen my horizont.
>> Feedback welcome!
>
> Ditto, I'd love to hear input from others!

Yes

Regards,
Thomas Güttler

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