Re: Separation of clients' data within a database - Mailing list pgsql-general

> 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