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

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

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

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

Ditto, I'd love to hear input from others!

>
> Regards,
> Thomas Güttler
>
>
>
>
>
>
> --
> Thomas Guettler http://www.thomas-guettler.de/