DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=DKIM-Signature:X-Yahoo-Newman-Property:X-YMail-OSG:X-Yahoo-SMTP:Received:References:Mime-Version:In-Reply-To:X-Apple-Yahoo-Original-Message-Folder:Content-Type:Content-Transfer-Encoding:Message-Id:Cc:X-Mailer:From:X-Apple-Yahoo-Replied-Msgid:Subject:Date:To;
	b=3FzEI2DJT+jPLspEglRWtZWiZJI2KETdPGHYOJNp9Vy506F1X0nNiOLYpEqUEar56Ufo2HBedCG/itA9Ryi4oYfw+4svJVaHphTkcDua8iUWh6Chzf0y8I9pigOzaRftfvBE7/uLD8jyEtc6f9DOi4YI3Q0CR1B8RDj4lPTmlUs=
	;
References: 
 <CAOBG12kFTaWUDHU-puJuDK5wQUZ=hdkFBbN1Ku9HUA8_qp0BAg@mail.gmail.com>
	<4EA62500.9070000@ringerc.id.au>
Mime-Version: 1.0 (iPad Mail 8J2)
In-Reply-To: <4EA62500.9070000@ringerc.id.au>
Content-Type: text/plain;
	charset=utf-8
Content-Transfer-Encoding: quoted-printable
Message-Id: <34C216F7-8D8D-4F18-A693-AAF922BF6672@yahoo.com>
Cc: =?utf-8?Q?Brice_Andr=C3=A9?= <brice@famille-andre.be>,
	"pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
From: David Johnston <polobo@yahoo.com>
Subject: Re: Handling mutliple clients access with views
Date: Mon, 24 Oct 2011 23:36:33 -0400
To: Craig Ringer <ringerc@ringerc.id.au>

On Oct 24, 2011, at 22:54, Craig Ringer <ringerc@ringerc.id.au> wrote:

> On 25/10/11 03:23, Brice Andr=C3=A9 wrote:
>> Hello everyone,
>>=20
>> 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.
>>=20
>> 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.
>=20
> That sounds ... roundabout.
>=20
> 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.
>=20
>>   - 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.
>=20
> Connect as a single user, then SET ROLE to the user you want in order to
> control access.
>=20
> Instead of using current_user() and programmatic security checking, use
> GRANT and REVOKE for declarative access checking where possible.
>=20
>> 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=C3=AFde, and maybe should I handle all my clients w=
ith
>> a single postgresql user, handling all safety aspect in my php script ?
>=20
> Nope, I heartily approve of doing security in-database, especially if
> you can do it declaratively.
>=20
> --
> Craig Ringer
>=20
> --=20
> 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., mult=
iple-tenant) which is the goal of this setup.

Views are not fool-proof in providing row-level security, for that you need f=
unctions. 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 filt=
ered out in the end result.  This is because views are simply re-write rules=
.

David J.