Separation of clients' data within a database

On Thu, Nov 30, 2006 at 12:48 PM, in message

<456F2795.3070603@hardgeus.com>,
John McCawley <nospam@hardgeus.com> wrote:

4) Create views for each client that filter the underlying table data

to

only show them their data. The only database objects they would have

read permission on are these views. Come to think of it, this is
probably the best way to go.

That's what I'd be looking at. Be sure to revoke public rights on the
database and public schema, and grant back the rights you want. Think
about using the schemas to segregate the views for the various clients.

-Kevin

It seems that this approach would suffer the same problem as the one I
outlined in "1) Actually separate client data by table". I would have
to modify the logic of my web app...My web app currently handles all of
the data, regardless of company, so it would have to aggregate the data
from the different schemas when pulling data, and be smart enough to
write back to the proper schemas when writing data.

Leonel Nunez wrote:

search_path="$user" in postgresql.conf

and you create one schema for each user with the user name as name....

Rodrigo

John McCawley wrote:

Is it possible to have each user connect via different postgresql
account? if so, then you can use

alter user set search_path='common_schema','user_schema';

where common schema has the things that would be the same for each
instance of the app, and user_schema is the name of that user's schema.
Then you don't have to change your app very much, if any, and each user
can only get to their schema, assuming you've only granted them
permission on their own schemas (change rights) and the common_schema
(usage rights)

Note that my in my current code, application-level permissions are
completely detached from database permissions. The entire web app uses
one user/pass to login to the database. The web app is used both by
individual companies who can only view their data, and also the
overseeing company who is capable of viewing everything. While they
are logging in with different application-level users, they are querying
with the same database-level user. My question regarding database
user-level permission was for the purpose of the IT departments going
"under the hood" rather than for security in my web app.

As the app is currently written, I have dropdown filters for what data
the report will produce. The "lesser' companies' filter forces them to
view only their data (where tbl_foo.company_id = bar), whereas the
overseeing company runs the same report without a filter, and the data
is organized with a group by. Right now, the addition of a company is
simply an addition of a row in the client table, and the app adjusts
without modification. If I add a schema per company, every time I add a
company I would have to modify every query in the system to also pull
from this additional schema, or modify my entire application to pull
from views which must be modified every time a company is added...

Rodrigo Gonzalez wrote:

On Thu, 2006-11-30 at 13:45, John McCawley wrote:

Note that my in my current code, application-level permissions are
completely detached from database permissions. The entire web app uses
one user/pass to login to the database. The web app is used both by
individual companies who can only view their data, and also the
overseeing company who is capable of viewing everything. While they
are logging in with different application-level users, they are querying
with the same database-level user. My question regarding database
user-level permission was for the purpose of the IT departments going
"under the hood" rather than for security in my web app.

As the app is currently written, I have dropdown filters for what data
the report will produce. The "lesser' companies' filter forces them to
view only their data (where tbl_foo.company_id = bar), whereas the
overseeing company runs the same report without a filter, and the data
is organized with a group by. Right now, the addition of a company is
simply an addition of a row in the client table, and the app adjusts
without modification. If I add a schema per company, every time I add a
company I would have to modify every query in the system to also pull
from this additional schema, or modify my entire application to pull
from views which must be modified every time a company is added...

That's just the point of search_path.

For me, it can be:

alter user smarlowe set search_path='common','smarlowe';

for joe user it might be

alter user joe_user set search_path='common','joe_user';

and all you have to change is the connection statement for your app
depending on who logged in. voila!

I have been using PostgreSQL for years in my web apps, and so far in my
career I have not had to deal with database-side permissions issues.
i.e. when I have multiple clients, or hands on the data, everyone
interfaces through my web app and I handle security there. The app
enforces what data they can or can't see/modify based on their login
credentials. I have never really messed with database level permissions
other than casually.

I am faced with a very new problem for me, which is that my app is going
to be used directly by several companies utilizing one server. (i.e.
these companies will be able to go "under the hood" quite a bit more
than we typically allow with this system). There are several issues
with respective IT departments wanting to retain some level of control
of their data, and I know they are not going to be satisfied simply
using my web app frontend. Of course, I can't simply give them carte
blanche access to the database because *I* am responsible for the
integrity of the data, and also I cannot allow them to view each others'
data. Since the different clients' data is currently stored in the same
tables (separated by keys to the client table) I cannot simply do
table-level permissions. I would assume there are no row level
permissions, right? (Even the thought of it seems way too much to
maintain)

I have considered the following solutions to the problem:

1) Actually separate client data by table, and give each client a
database user only capable of read-only access to their company's
table. This seems like it would work, but it would greatly increase the
complexity of my app. Although I have heard that it is possible to
implement a writeable view, so perhaps I could make views which mimic my
current schema. Still, seems complex.

2) Do a daily dump of the data to a different database on the same
PostgreSQL server, one database for each client. The stumbling block
here is that I don't think that there's any way to use pg_dump etc. to
only dump some data. I considered dumping everything, and then
programmatically deleting data that client should not see, but if the
delete failed they have a database full of their competitor's information.

3) Similar to solution 1), except the data in the individual client
tables is simply a copy of that client's data, and gets blown away every
night by a scheduled copy of data. This way my app would simply operate
as it does currently, and I could actually give my clients full access
to "their" tables.

4) Create views for each client that filter the underlying table data to
only show them their data. The only database objects they would have
read permission on are these views. Come to think of it, this is
probably the best way to go.

5) Something I haven't thought of :)

Has anyone run into this sort of thing before? The IT guys in this
situation love using linked tables in Access over ODBC and just copy
vast quantities of data by hand, manually modifying information etc., so
there's no way in hell I'm letting them touch my data.

John

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

why don't you create a schema for every company and grant permissions to
use the shchema to only the user that needs to use that??

more info :
http://www.postgresql.org/docs/8.1/static/ddl-schemas.html

Leonel

Maybe I'm not understanding what you're getting at, so I'll throw out an
example:

--------------------------
With my current architecture, smartlowe logs in, but his login is
handled at the application layer, so his database connection is simply
"foo". He inserts a hundred records in the invoice table, which is the
public table invoice...these invoice records are automatically tagged
with the client_id associated with his login. When he runs a report,
the program forces a filter, aso by the client_id associated with his
login.

Now joe_user logs in, once again the "foo" db user, and inserts 100
records, tagged with his client_id. When he runs a report, it is
forceably filtered by his client_id. smartlowe and joe_user are writing
to the same database table, but the application forces the separation of
their data and reporting.

HOWEVER, when user big_daddy logs into the application, he just just
run a global query on the invoice table and pull all invoices. This
requires no special knowledge by the app of what clients exist in the
system...The SQL query handles the organization of the report by company.
--------------------------

I don't understand how I could implement what you're describing without
massive changes to my existing (5+ years in development) application.
Even factoring out that there are literally hundreds of people actually
logging into this system, I will just address a hypothetical if each
company only logs in with one user.

company_a logs in and inserts 100 records into tbl_invoice which
automagically becomes company_a.tbl_invoice. In his report, this also
automagically becomes company_a.tbl_invoice.

company_b logs in and inserts 100 records into tbl_invoice which
automagically becomes company_b.tbl_invoice. In his report, this also
automagically becomes company_b.tbl_invoice.

big_daddy logs in and wants to view *all* invoice data. In the invoice
report, this becomes big_daddy.tbl_invoice, which has nothing in it
because big daddy doesn't invoice. He wants the data from all of the
different companies. How would the system know to aggregate the
company_a.tbl_invoice UNION company_b.tbl_invoice UNION (etcetera)

As I said, we're talking about a pretty big system here, I don't have
the luxury of gutting the entire thing. Of course, I may just not
understand what I'm talking about with schemas, but that's why I'm asking ;)

Scott Marlowe wrote:

It seems that this approach would suffer the same problem as the one I
outlined in "1) Actually separate client data by table". I would have
to modify the logic of my web app...My web app currently handles all of
the data, regardless of company, so it would have to aggregate the data
from the different schemas when pulling data, and be smart enough to
write back to the proper schemas when writing data.

on the public schema you can have a table with the company and schema
and then select the default search path to the company's schema
and the rest of your app will be untouched

leonel

On Thu, 2006-11-30 at 14:07, John McCawley wrote:

Maybe I'm not understanding what you're getting at, so I'll throw out an
example:

I completely understand what you're saying, but I'm not quite getting
the reasons for it. For instance:

HOWEVER, when user big_daddy logs into the application, he just just
run a global query on the invoice table and pull all invoices. This
requires no special knowledge by the app of what clients exist in the
system...The SQL query handles the organization of the report by company.

Why does user big_daddy need to access everybody's data? Who is he?
What's his role? It seems like a big security problem waiting to
happen, but that's just me.

I don't understand how I could implement what you're describing without
massive changes to my existing (5+ years in development) application.
Even factoring out that there are literally hundreds of people actually
logging into this system, I will just address a hypothetical if each
company only logs in with one user.

company_a logs in and inserts 100 records into tbl_invoice which
automagically becomes company_a.tbl_invoice. In his report, this also
automagically becomes company_a.tbl_invoice.

company_b logs in and inserts 100 records into tbl_invoice which
automagically becomes company_b.tbl_invoice. In his report, this also
automagically becomes company_b.tbl_invoice.

So far, everything seems fine. No changes in your app needed but for
logins

big_daddy logs in and wants to view *all* invoice data. In the invoice
report, this becomes big_daddy.tbl_invoice, which has nothing in it
because big daddy doesn't invoice. He wants the data from all of the
different companies. How would the system know to aggregate the
company_a.tbl_invoice UNION company_b.tbl_invoice UNION (etcetera)

And again the weird requirement that your data be segregated for most
users, but then not be segregated for others. For a database to do
that, you'd need per row permissions and postgresql doesn't do that, at
least not natively. You could maybe make some kind of view / rule /
trigger system that checked each row against some master permissions
table. But performance wise you're just asking for trouble once you're
under heavy load doing that.

As I said, we're talking about a pretty big system here, I don't have
the luxury of gutting the entire thing. Of course, I may just not
understand what I'm talking about with schemas, but that's why I'm asking ;)

Well, I think if you're willing to write some extra code for the
"supervisor role" to be able to view everything, schemas make a lot of
sense. I imagine you could make some schema that has those unions you
mention above for reading all the data, and a user with usage permission
on all the schemas to run reports. you could even aggregate multiple
companies in different ways if need be (i.e. company_a has two
subdivisions, you give them a single view of the two subdivisions with a
single user log in to review all the data at once) but don't let anyone
else see their schemas.

This is one of those fundamental problems you run into when you make a
design decision up front (user perms in the app) and some change in
architecture (users in charge of web servers) changes your whole
security model.

You really don't have a lot of choice at this point. You've pretty much
GOT to put the security in some layer below the web/app server, because
you can't trust those to do the right thing anymore if you don't control
them.

I've found myself in your position before. Maybe you would be better
off writing some middleware layer that the front end hits. I.e. split
your web app in half. Front half asks back half to do something, you
maintain the back half locally. That idea might or might not be too
hare-brained depending on your situation.

On Thu, 2006-30-11 at 17:22 -0400, pgsql-general-owner@postgresql.org
wrote:

Date: Thu, 30 Nov 2006 12:48:53 -0600
From: John McCawley <nospam@hardgeus.com>
To: pgsql-general@postgresql.org
Subject: Separation of clients' data within a database
Message-ID: <456F2795.3070603@hardgeus.com>

... I would assume there are no row level
permissions, right? (Even the thought of it seems way too much to
maintain)

You could take a look at Veil http://veil.projects.postgresql.org/
which gives you row-level access controls. Whatever solution you choose
has its problems though:

1) Veil
You have to manage user permissions, implement a bunch of access
functions and secured views, and add connection functions to your
sessions.

2) Separate databases
You have to manage separate databases

3) Separate schemas
You have to manage the separate schemas, and also consider whether
access to the underlying catalogs is allowed (making it impossible for
one client to infer the existence of another may be important to you).

__
Marc

Why does user big_daddy need to access everybody's data? Who is he?
What's his role? It seems like a big security problem waiting to
happen, but that's just me.

Uncle Sam :)

This is one of those fundamental problems you run into when you make a
design decision up front (user perms in the app) and some change in
architecture (users in charge of web servers) changes your whole
security model.

Well, you're right, the security model has changed. The situation is
that the system was written for one company to manage its clients, and
the permission model was basically company/client, and the client had
pared-down access enforced by the app (the security model is quite a bit
more refined than that, but I'm simplifying)...The problem domain has
expanded for there to be many companies (clients no longer really
exist), and one over-arching super-company able to view everything.

Note that I am retaining 100% control of the Web-App server and the
database server (i.e. no one else will have superuser abilities), but I
know that the different companies will want the ability to connect to
the database under the hood. I think the most effective solution will
be to simply create a database user for each company, and for each
company create a series of views, owned by that user, which are
hard-wired to view only their data.

Of course I still have to modify my web app and schema to facilitate the
new security structure, but I was never too worried about handling it in
my app...My concern was allowing people direct access to the underlying
DB while a) blocking them from viewing others' data, and b) without
having to drastically modify the fundamental structure of my app.

On 11/30/06, John McCawley <nospam@hardgeus.com> wrote:

I am faced with a very new problem for me, which is that my app is going
to be used directly by several companies utilizing one server. (i.e.
these companies will be able to go "under the hood" quite a bit more
than we typically allow with this system).

How about web services? A simple REST based interface should offer a
decent integration point, and you wouldn't have to let anyone past the
app layer.

Isak

John McCawley wrote:

Of course I still have to modify my web app and schema to facilitate the
new security structure, but I was never too worried about handling it in
my app...My concern was allowing people direct access to the underlying
DB while a) blocking them from viewing others' data, and b) without
having to drastically modify the fundamental structure of my app.

I'd be tempted by your idea of copying the data to separate databases.
Reduces impact on your app and gives you one crucial point to test
against. The disadvantage is that it will only work for the simple
per-user split you describe, nothing more complex.

Oh, and don't forget that users can see other user and database names.

--
Richard Huxton
Archonet Ltd

How about this:

* Have one master schema that holds all physical tables. This schema
is accessible only by the superuser.

* Create a schema which contains views that mirror the master schema.
This is the schema that the customers connect to, each using a
different db role, and since it's a mirror of the master schema, it
means no change in app structure (except dropping rights management,
see below).

* Let these views pull their data from the respective master schema
table (i.e. SELECT * FROM ...) with the addition of a WHERE-clause on
client_id, that uses a function: ...WHERE client_id IN (get_client_ids
()).

* The 'get_client_ids()'-function should query a table in the master
schema that keeps the client_id's that are assigned to each db role
(e.g. SELECT client_id FROM foo WHERE role=CURRENT_USER), and return
those client_id's. For a regular customer, it would return one
client_id, for a supervisor kind of user, it would return two or
more, perhaps even all, client_id's.

* Have UPDATE and INSERT rules on the views that store the data in
the actual master schema tables. (The rules would of course have to
add client_id, this time through a function that can only return one
client_id.)

To conclude: one master schema, one mirrored customer schema that
adapts to the db role, one additional table in the master schema to
handle the rights.

Sincerely,

Niklas Johansson
Phone: +46-322-108 18
Mobile: +46-708-55 86 90

That's the first idea I've seen that looks like it might actually
work... (Not that the other ideas were bad, but I just couldn't see how
I could fit the solutions into my current app)

So what would my user setup look like? Would it look something like this:

createuser joe
grant select on schema company_a to joe
(whatever other permissions)
alter user joe set search_path='common','company_a';

createuser bob
grant select on schema company_b to bob
(whatever other permissions)
alter user bob set search_path='common','company_b';

How portable is all of this? Could a comparable structure be
implemented in MS SQL or Oracle?

Niklas Johansson wrote:

On 1 dec 2006, at 15.19, John McCawley wrote:

That's the first idea I've seen that looks like it might actually
work... (Not that the other ideas were bad, but I just couldn't see
how I could fit the solutions into my current app)

So what would my user setup look like? Would it look something
like this:

createuser joe
grant select on schema company_a to joe
(whatever other permissions)
alter user joe set search_path='common','company_a';

createuser bob
grant select on schema company_b to bob
(whatever other permissions)
alter user bob set search_path='common','company_b';

No, you wouldn't need separate schemas for each user, and the users
should *not* be allowed access to the master schema. The views in the
customer schema would, as I said, use a function (e.g. get_client_ids
()) that uses CURRENT_USER (which will evaluate to either joe or bob,
according to your example above) to lookup the actual client_ids.
This means that you can grant every user the same rights on the
customer schema views, and the rights management is done by the
function (which is better than hardcoding values into the views; if
the requirements change you just update the function), together with
an additional table in the master schema. This table could look
something like this:

role | client_id
-----+----------
joe | 100
joe | 101
bob | 102

which would mean that joe is a supervisor that can see both client
100 and client 101, while bob can see only client 102. You would
probably need some other tables to keep track of which client_id
should be used or allowed for data insertion if the user has more
than one client_id, but you get the idea.

How portable is all of this? Could a comparable structure be
implemented in MS SQL or Oracle?

As far as I know, yes. (Quite some time since I last had anything to
do with either of those. Not that I lament the fact... :-)

Sincerely,

Niklas Johansson
Phone: +46-322-108 18
Mobile: +46-708-55 86 90

Oh, I see, so there's one master schema, and one customer schema, and
the customer schema views are automatically filtered based on
login...Makes sense...I will definitely try to implement this, thanks!

Niklas Johansson wrote:

John McCawley wrote:

Oh, I see, so there's one master schema, and one customer schema, and
the customer schema views are automatically filtered based on
login...Makes sense...I will definitely try to implement this, thanks!

I've on-and-off toyed with the idea of accomplishing a similar objective
by using a temporary table (which are session specific, so different
logins would see their own temp table). Haven't worked through all the
details and so am not sure if it makes much sense this way verses using
a function to identify the current user, but here is a short script to
illustrate the idea:

CREATE SCHEMA universe;
SET search_path=universe, pg_catalog;

CREATE TABLE customer
(
customer varchar(12) NOT NULL,
CONSTRAINT customer_pkey PRIMARY KEY (customer)
);

CREATE TABLE invoice
(
customer varchar(12) NOT NULL,
invoice varchar(12) NOT NULL,
CONSTRAINT invoice_pkey PRIMARY KEY (customer, invoice),
CONSTRAINT "$1" FOREIGN KEY (customer) REFERENCES customer (customer)
);

INSERT INTO customer VALUES ('Alice');
INSERT INTO customer VALUES ('Bob');

INSERT INTO invoice VALUES ('Alice', 'inv a1');
INSERT INTO invoice VALUES ('Alice', 'inv a2');
INSERT INTO invoice VALUES ('Alice', 'inv a3');
INSERT INTO invoice VALUES ('Alice', 'inv a4');

INSERT INTO invoice VALUES ('Bob', 'inv b1');
INSERT INTO invoice VALUES ('Bob', 'inv b2');
INSERT INTO invoice VALUES ('Bob', 'inv b3');

SELECT * FROM customer NATURAL JOIN universe.invoice;
/*
customer | invoice
----------+---------
Alice | inv a1
Alice | inv a2
Alice | inv a3
Alice | inv a4
Bob | inv b1
Bob | inv b2
Bob | inv b3
(7 rows)
*/

CREATE SCHEMA customer;
SET search_path=customer, pg_catalog;

CREATE TEMPORARY TABLE customer AS SELECT * FROM universe.customer
WHERE customer = 'Alice';
SELECT * FROM customer NATURAL JOIN universe.invoice;
/*
customer | invoice
----------+---------
Alice | inv a1
Alice | inv a2
Alice | inv a3
Alice | inv a4
(4 rows)
*/

DROP TABLE customer;
CREATE TEMPORARY TABLE customer AS SELECT * FROM universe.customer
WHERE customer = 'Bob';
SELECT * FROM customer NATURAL JOIN universe.invoice;
/*
customer | invoice
----------+---------
Bob | inv b1
Bob | inv b2
Bob | inv b3
(3 rows)
*/