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
