From: David Johnston <polobo@yahoo.com>
To: Craig Ringer <ringerc@ringerc.id.au>
Cc: Brice André <brice@famille-andre.be>
Cc: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: Handling mutliple clients access with views
Date: Mon, 24 Oct 2011 23:36:33 -0400
Message-ID: <34C216F7-8D8D-4F18-A693-AAF922BF6672@yahoo.com> (raw)
In-Reply-To: <4EA62500.9070000@ringerc.id.au>
References: <CAOBG12kFTaWUDHU-puJuDK5wQUZ=hdkFBbN1Ku9HUA8_qp0BAg@mail.gmail.com>
<4EA62500.9070000@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é 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: polobo@yahoo.com, ringerc@ringerc.id.au, brice@famille-andre.be
Subject: Re: Handling mutliple clients access with views
In-Reply-To: <34C216F7-8D8D-4F18-A693-AAF922BF6672@yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox