Thread: 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
>>> 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
> 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
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: >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 > > > >
search_path="$user" in postgresql.conf and you create one schema for each user with the user name as name.... Rodrigo John McCawley 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. > > Leonel Nunez wrote: > >> 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 >> >> >> >> > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings >
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: > search_path="$user" in postgresql.conf > > and you create one schema for each user with the user name as name.... > > Rodrigo > > > John McCawley 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. >> >> Leonel Nunez wrote: >> >>> 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 >>> >>> >>> >>> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 5: don't forget to increase your free space map settings >> > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
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) On Thu, 2006-11-30 at 13:35, Rodrigo Gonzalez 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: > > 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: > > > >> 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 > >> > >> > >> > >> > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 5: don't forget to increase your free space map settings > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
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!
> 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
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: >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! > >
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.
>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: > 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 > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
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: > > 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 > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
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) */