> 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
