Обсуждение: Handling mutliple clients access with views

Hello everyone,<br /><br />I am developping a web service where some tables are handling the data of different clients.
Dependingon configured rights, one client can have no access, or read access, or read and write access to other clients
data.<br/><br />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
ofview, I am using the postgresql special function "current_user()" and I am retrieving the id of my client like
this.<br/><br />My problem is that I am not sure this is a good idea in term of performance (which is a big issue in my
case).My main fears are that :<br />   - The use of the"current_user()" function would slow the complete request<br />
  - My method requests that each user has its own postgresql user. But, in this case, my web server needs to establish
apostgresql connection for each user, which will maybe cause problems.<br /><br />So, I was guessing if I was not
completelywrong by doing like that. Maybe is there a simpler way of doing what I try to do ? Or maybe am I a little bit
tooparanoïde, and maybe should I handle all my clients with a single postgresql user, handling all safety aspect in my
phpscript ?<br /><br />The advantage of my method is that, even if there is a bug in my php script, if the db views are
properlyconfigured, a malicious client cannot alter the data ofanother client for which he does not have write access.
Butmaybe is it not a good idea to handle this at db level ?<br /><br />Do you have any suggestion on those aspects?<br
/><br/>Regards,<br /><br />Brice André<br /> 

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

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
providethe protected data a function can be used to process data (via RAISE NOTICE) that would otherwise be filtered
outin the end result.  This is because views are simply re-write rules. 

David J.

On 25/10/11 11:36, David Johnston wrote:

> Except database roles cannot strictly enforce row-level security (i.e., multiple-tenant) which is the goal of this
setup.

Ah, yes, if theyr'e relying on _row_ level security then that's very
much the case. I misread their post as suggesting that they had
different tables for different clients, rather than tables that mix
different clients' data.

Declarative row-level security (row ownership) would be really nice...
here's hoping the SELinux work can be extended to support a simpler,
OS-agnostic non-SELinux-based row-level RBAC mechanism.

I'd still use SET ROLE where possible, and rely on SECURITY DEFINER
stored procs in cases where clients' data is mixed in a table so you
need to filter it programmatically. Then I'd wrap those procs in a view
so they were transparent and looked like tables, so once row-level
security is implemented you could switch to that without the app caring.

--
Craig Ringer

On Mon, Oct 24, 2011 at 8:50 PM, Craig Ringer <ringerc@ringerc.id.au> wrote:
>
> Declarative row-level security (row ownership) would be really nice...
> here's hoping the SELinux work can be extended to support a simpler,
> OS-agnostic non-SELinux-based row-level RBAC mechanism.
> --
> Craig Ringer

Veil might do what you mention above.

http://veil.projects.postgresql.org/curdocs/index.html

Thanks very much for those info.

From what I read, I think Veil will do the trick.

I already use ROLES, but there are a few probems with them :
   - first, as I mix the data from different users in the same table, ROLES do not solve the complete problem.
   - second, this forces me to use a dedicated postgresql connection for each user, which on a large accessed database, could be a big problem.

If what I read from Veil is exact, it allows determining access rules from SELECT statements, which is what I am currently performing with the "current_user()". A typical view in my system is declared like this :
CREATE VIEW "AgendaCurrentlyDisplayedReadableView" AS
    SELECT "AgendaAccessRights"."AgendaID" FROM "AgendaAccessRights" WHERE (("AgendaAccessRights"."ClientID" = (SELECT "Clients"."ID" FROM "Clients" WHERE "Clients"."Login" = "current_user"())) AND ("AgendaAccessRights"."IsDisplayed" = true));

My understanding is that veil will allow me to perform stuff like that, but without using the "current_user()" stuff. And, if I still understand correctly, the Veil serialisation functions will allow me to quickly switch from one user to the other with keeping the same database connection.

Am I right or is there something I did not understood ?

Second question : does Veil has a big impact on database performances ?

Regards,

Brice