Re: Separation of clients' data within a database

> 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