Thread: [GENERAL] Row based permissions: at DB or at Application level?
I am thinking about rewriting an existing application which uses PostgreSQL via Django (ORM and Web-Framework written in Python). Up to now the permission checks are done at the application level. Up to now queries like: "Show all items which the current user is allowed to modify" result in complicated SQL and this leads to slow queries. Up to now there is one db-user and the application does the filtering of rows to prevent application users to see items which they are not allowed to see. I guess most web applications work like this. I would like to reduce the "ifing and elsing" in my python code (less conditions, less bugs, more SQL, more performance) How could an application which gets written from scratch use PostgreSQL to implement row based permissions? Regards, Thomas Güttler -- Thomas Guettler http://www.thomas-guettler.de/
>How could an application which gets written from scratch use PostgreSQL to implement
>row based permissions?
Are you looking for this?
>row based permissions?
Are you looking for this?
https://www.postgresql.org/docs/9.6/static/ddl-rowsecurity.html
Regards
Daniel
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. 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... 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. 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.
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/
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/
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/
[GENERAL] Logging in Code vs SQL-WHERE was: Row based permissions: at DB or atApplication level?
From
Thomas Güttler
Date:
I have been thinking about this again.
Doing perm checking in code (I use Python) has the advantage that you can do logging:
def has_perm(item, user):
if user.is_superuser:
logger.debug('Access to %s for %s allowed, since user is superuser' % (item, user))
return True
if ...:
logger.debug('Access to %s for %s allowed, since ...' % (item, user))
logger.debug('Access to %s for %s not allowed.' % (item, user))
return False
We use this sometimes for debugging. This way I can see why a user is allowed to access an object or not.
This is an argument for permission checking in code.
On the other side I still think perm checking in SQL WHERE has more benefits.
Regards,
Thomas Güttler
--
Thomas Guettler http://www.thomas-guettler.de/