Re: Handling mutliple clients access with views

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