Hello everyone, I am developping a web service where some tables are handling the data of different clients. Depending on configured rights, one client can have no access, or read access, or read and write access to other clients data.
In order to handle that, I am using views and, to ensure that a client cannot access data outside the view, all clients info is stored in a table where its postgresql user name is also stored. So, in order to limit access of view, I am using the postgresql special function "current_user()" and I am retrieving the id of my client like this. My problem is that I am not sure this is a good idea in term of performance (which is a big issue in my case). My main fears are that : - The use of the"current_user()" function would slow the complete request - My method requests that each user has its own postgresql user. But, in this case, my web server needs to establish a postgresql connection for each user, which will maybe cause problems. So, I was guessing if I was not completely wrong by doing like that. Maybe is there a simpler way of doing what I try to do ? Or maybe am I a little bit too paranoïde, and maybe should I handle all my clients with a single postgresql user, handling all safety aspect in my php script ? The advantage of my method is that, even if there is a bug in my php script, if the db views are properly configured, a malicious client cannot alter the data ofanother client for which he does not have write access. But maybe is it not a good idea to handle this at db level ? Do you have any suggestion on those aspects? Regards, Brice André