On Oct 24, 2011, at 22:54, Craig Ringer <ring...@ringerc.id.au> wrote:
> On 25/10/11 03:23, Brice André wrote: >> 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. > > That sounds ... roundabout. > > Why not use roles and role inheritance? You can use SET ROLE to > temporarily change roles, log in as different roles, have one role be a > member of other roles, have role access permissions on tables/views at > the column or table level, etc. > >> - 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. > > Connect as a single user, then SET ROLE to the user you want in order to > control access. > > Instead of using current_user() and programmatic security checking, use > GRANT and REVOKE for declarative access checking where possible. > >> 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 ? > > Nope, I heartily approve of doing security in-database, especially if > you can do it declaratively. > > -- > Craig Ringer > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql Except database roles cannot strictly enforce row-level security (i.e., multiple-tenant) which is the goal of this setup. Views are not fool-proof in providing row-level security, for that you need functions. While a view itself will not provide the protected data a function can be used to process data (via RAISE NOTICE) that would otherwise be filtered out in the end result. This is because views are simply re-write rules. David J. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
