Re: Handling mutliple clients access with views

On Oct 24, 2011, at 22:54, Craig Ringer  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.