pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHandling mutliple clients access with views
6+ messages / 4 participants
[nested] [flat]
* Handling mutliple clients access with views
@ 2011-10-24 19:23 Brice André <brice@famille-andre.be>
0 siblings, 1 reply; 6+ messages in thread
From: Brice André @ 2011-10-24 19:23 UTC (permalink / raw)
To: pgsql-sql
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.
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 :
- The use of the"current_user()" function would slow the complete request
- 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.
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 ?
The advantage of my method is that, even if there is a bug in my php script,
if the db views are properly configured, a malicious client cannot alter the
data ofanother client for which he does not have write access. But maybe is
it not a good idea to handle this at db level ?
Do you have any suggestion on those aspects?
Regards,
Brice André
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Handling mutliple clients access with views
@ 2011-10-25 02:54 Craig Ringer <ringerc@ringerc.id.au>
parent: Brice André <brice@famille-andre.be>
0 siblings, 1 reply; 6+ messages in thread
From: Craig Ringer @ 2011-10-25 02:54 UTC (permalink / raw)
To: Brice André <brice@famille-andre.be>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Handling mutliple clients access with views
@ 2011-10-25 03:36 David Johnston <polobo@yahoo.com>
parent: Craig Ringer <ringerc@ringerc.id.au>
0 siblings, 1 reply; 6+ messages in thread
From: David Johnston @ 2011-10-25 03:36 UTC (permalink / raw)
To: Craig Ringer <ringerc@ringerc.id.au>; +Cc: Brice André <brice@famille-andre.be>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Handling mutliple clients access with views
@ 2011-10-25 03:50 Craig Ringer <ringerc@ringerc.id.au>
parent: David Johnston <polobo@yahoo.com>
0 siblings, 1 reply; 6+ messages in thread
From: Craig Ringer @ 2011-10-25 03:50 UTC (permalink / raw)
To: David Johnston <polobo@yahoo.com>; +Cc: Brice André <brice@famille-andre.be>; pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Handling mutliple clients access with views
@ 2011-10-25 03:52 bricklen <bricklen@gmail.com>
parent: Craig Ringer <ringerc@ringerc.id.au>
0 siblings, 1 reply; 6+ messages in thread
From: bricklen @ 2011-10-25 03:52 UTC (permalink / raw)
To: Craig Ringer <ringerc@ringerc.id.au>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Handling mutliple clients access with views
@ 2011-10-25 06:02 Brice André <brice@famille-andre.be>
parent: bricklen <bricklen@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Brice André @ 2011-10-25 06:02 UTC (permalink / raw)
To: pgsql-sql
Thanks very much for those info.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2011-10-25 06:02 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-10-24 19:23 Handling mutliple clients access with views Brice André <brice@famille-andre.be>
2011-10-25 02:54 ` Craig Ringer <ringerc@ringerc.id.au>
2011-10-25 03:36 ` David Johnston <polobo@yahoo.com>
2011-10-25 03:50 ` Craig Ringer <ringerc@ringerc.id.au>
2011-10-25 03:52 ` bricklen <bricklen@gmail.com>
2011-10-25 06:02 ` Brice André <brice@famille-andre.be>
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