[SQL] Thoughts on a Isolation/Security problem.
Hi, i have run into the following problem. Let me describe the context first. When i joined the company(ies) i work for (a group of Shipping Mgmt/ Owenship/Agent companies), the only thing i was told when i started designing the DB/Apps was just one company. So i built everything into one single DB, and i wrote the apps having one company in mind. Our architecture is based on jboss3/postgresql (curenctly 7.4.12). There is one .ear file, which authenticates users against a lotus notes ldap server. At the time, the corporate organisational model was a little bit wierd: - Many Indepentent ownership companies - Many Independent Mgmg companies (but all busines was with one company in mind). Each App user is a member of one or more ldap groups, each group mapping to a mgmt company. So i ended up with - one DB with 173 tables - one DB user (postgres) - one .EAR application with 148,827 lines of code. Now the requirements start to change. The boss now bought some other types of vessels too. So virtually there must be N separate distinct apps, where N is the number of Mgmt companies (roughly one for every type of vessel), where each app sees and manages only its data. Moreover there are some apps that should see global data for some specific tables. (like the crew data, people in the crew move from one type of vessel to the other so they are not tied to a Mgmt company). These new requirements are of legal nature, as well as of operational. (People managing a type of vessels dont want to mess with another type, and auditors must see each company completely separated from the rest). Doing it with extra code would be a real pain, since i would have to refine all security/authentication based on the groups ([EMAIL PROTECTED]) that a person belongs to. Also this way no inherent isolation/security would hold. Now i am thinking of restructuring the whole architecture as: - Create one EAR app for every mgmt company - Create one DB USER for every mgmg company - Create one SCHEMA (same as the USER) for every mgmt company (mgmtcompany1,mgmtcompany2,etc...) - Find a way (links/xdoclet/eclipse?) to have *one* common code base for the N EAR apps. - Tweak with jboss*.xml to map java:comp/env/jdbc/DB to java:/pgsql, where pgsql authenticates with the corresponding DB USER. - Classify the tables into - The ones that apply to ALL mgmt companies (and leave them in the public schema) - The ones that apply *only* to a mgmt company and so create one under each SCHEMA - Load the data in *each* SCHEMA, except the tables that apply to all. - Define a process of "mgmt company"fying the tables in each schema (e.g. delete from mgmtcompany1.vessels the vessels that dont belong to mgmtcompany1, and so forth) - Resolve FK constraint issues - The default search_path in psql (whats the the equivalent in jdbc?) is $user,public, so effectively *each* EAR will hit automagically the correct mgmtcompanyN.* tables, or the public.* tables if these tables apply to all mgmt companies. With this way, the hard work is DB oriented, and not APP oriented. However i wonder whether someone else has gone thru a similar process, or if someone finds some assumption conceptually flawed. Thanx for reading, and for any possible thoughts. -- -Achilleus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [JDBC] Thoughts on a Isolation/Security problem.
Hi, Achilleus, Achilleus Mantzios wrote: > Now i am thinking of restructuring the whole architecture as: > - Create one EAR app for every mgmt company > - Create one DB USER for every mgmg company > - Create one SCHEMA (same as the USER) for every mgmt company > (mgmtcompany1,mgmtcompany2,etc...) We're doing a very similar thing here for one of our legacy apps, which luckily does not know anything about schemas, and so the search_path trick does work. However, for most "global" tables we have views with insert/update/ delete rules in the specific schemas, and such shield the application from directly accessing the global data. We even need to mere local and global data this way in some cases. It is ugly, but it works fine and is manageable. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Thoughts on a Isolation/Security problem.
how about having a company table, and company_code column across all relevant table, although you'll have to modify your application also, which would be an additional clause in the WHERE condition e.g where company_code = 'which company user has logged in'. The user has to specify while logging under which company he's going to work on. This way would be ideal even for your Global financial consolidations if the mgmt requires in the due course. other option would be of two tables, Company , Organization, where you can have company1, org1, org2 etc., this can also be applied in the same pattern as stated above. On 4/18/06, Achilleus Mantzios <[EMAIL PROTECTED]> wrote: Hi, i have run into the following problem.Let me describe the context first.When i joined the company(ies) i work for (a group of Shipping Mgmt/ Owenship/Agent companies), the only thing i was told when i starteddesigning the DB/Apps was just one company.So i built everything into one single DB, and i wrote the appshaving one company in mind. Our architecture is based on jboss3/postgresql (curenctly 7.4.12).There is one .ear file, which authenticates users against a lotusnotes ldap server.At the time, the corporate organisational model was a little bit wierd: - Many Indepentent ownership companies- Many Independent Mgmg companies(but all busines was with one company in mind).Each App user is a member of one or more ldap groups, each groupmapping to a mgmt company. So i ended up with- one DB with 173 tables- one DB user (postgres)- one .EAR application with 148,827 lines of code.Now the requirements start to change.The boss now bought some other types of vessels too. So virtually there must be N separate distinct apps, where N is the numberof Mgmt companies (roughly one for every type of vessel), where each appsees and manages only its data.Moreover there are some apps that should see global data for some specific tables. (like the crew data, people in the crew move from one type ofvessel to the other so they are not tied to a Mgmt company).These new requirements are of legal nature, as well as ofoperational. (People managing a type of vessels dont want to mess with another type,and auditors must see each company completely separated from the rest).Doing it with extra code would be a real pain, since i would have torefineall security/authentication based on the groups ( [EMAIL PROTECTED])that a person belongs to. Also this way no inherent isolation/securitywould hold.Now i am thinking of restructuring the whole architecture as:- Create one EAR app for every mgmt company - Create one DB USER for every mgmg company- Create one SCHEMA (same as the USER) for every mgmt company(mgmtcompany1,mgmtcompany2,etc...)- Find a way (links/xdoclet/eclipse?) to have *one* common code base for the N EAR apps.- Tweak with jboss*.xml to map java:comp/env/jdbc/DB tojava:/pgsql, where pgsql authenticateswith the corresponding DB USER.- Classify the tables into - The ones that apply to ALL mgmt companies (and leave them in thepublic schema)- The ones that apply *only* to a mgmt company and so create one undereach SCHEMA- Load the data in *each* SCHEMA, except the tables that apply to all. - Define a process of "mgmt company"fying the tables in each schema (e.g.delete from mgmtcompany1.vessels the vessels that dont belong tomgmtcompany1, and so forth)- Resolve FK constraint issues - The default search_path in psql (whats the the equivalent in jdbc?) is$user,public, so effectively *each* EAR will hit automagically the correctmgmtcompanyN.* tables, or the public.* tables if these tables apply to all mgmt companies.With this way, the hard work is DB oriented, and not APP oriented.However i wonder whether someone else has gone thru a similar process,or if someone finds some assumption conceptually flawed. Thanx for reading, and for any possible thoughts.---Achilleus---(end of broadcast)---TIP 4: Have you searched our list archives? http://archives.postgresql.org-- regards, Luckys...
Re: [SQL] [JDBC] Thoughts on a Isolation/Security problem.
O Markus Schaber έγραψε στις Apr 18, 2006 : > Hi, Achilleus, > > Achilleus Mantzios wrote: > > > Now i am thinking of restructuring the whole architecture as: > > - Create one EAR app for every mgmt company > > - Create one DB USER for every mgmg company > > - Create one SCHEMA (same as the USER) for every mgmt company > > (mgmtcompany1,mgmtcompany2,etc...) > > We're doing a very similar thing here for one of our legacy apps, which > luckily does not know anything about schemas, and so the search_path > trick does work. > > However, for most "global" tables we have views with insert/update/ > delete rules in the specific schemas, and such shield the application > from directly accessing the global data. We even need to mere local and > global data this way in some cases. > > It is ugly, but it works fine and is manageable. If no exotic/contrib code is to be used then i think splitting into separate Schemas (versus separate DBs) will make future consolidation/stats/accounting (global data) code easy to write. (Unless ofcourse some real cross-db sql join features appear which is not the case at the moment). Why do you think its ugly after all? > > HTH, > Markus > -- -Achilleus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Thoughts on a Isolation/Security problem.
O Luckys έγραψε στις Apr 18, 2006 : > how about having a company table, and company_code column across all > relevant table, although you'll have to modify your application also, which > would be an additional clause in the WHERE condition e.g where company_code > = 'which company user has logged in'. > The user has to specify while logging under which company he's going to work > on. > This way would be ideal even for your Global financial consolidations if the > mgmt requires in the due course. > other option would be of two tables, Company , Organization, where you can > have company1, org1, org2 etc., this can also be applied in the same pattern > as stated above. Thanx for your thoughts, but this would require touching 173 tables + 2,594 SQL statements in a sum of 324 programs (which sum into 125,085 lines of code) > > > > On 4/18/06, Achilleus Mantzios <[EMAIL PROTECTED]> wrote: > > > > > Hi, i have run into the following problem. > > Let me describe the context first. > > > > When i joined the company(ies) i work for (a group of Shipping Mgmt/ > > Owenship/Agent companies), the only thing i was told when i started > > designing the DB/Apps was just one company. > > > > So i built everything into one single DB, and i wrote the apps > > having one company in mind. > > > > Our architecture is based on jboss3/postgresql (curenctly 7.4.12). > > There is one .ear file, which authenticates users against a lotus > > notes ldap server. > > > > At the time, the corporate organisational model was a little bit wierd: > > - Many Indepentent ownership companies > > - Many Independent Mgmg companies > > (but all busines was with one company in mind). > > > > Each App user is a member of one or more ldap groups, each group > > mapping to a mgmt company. > > > > So i ended up with > > - one DB with 173 tables > > - one DB user (postgres) > > - one .EAR application with 148,827 lines of code. > > > > Now the requirements start to change. > > The boss now bought some other types of vessels too. > > So virtually there must be N separate distinct apps, where N is the number > > of Mgmt companies (roughly one for every type of vessel), where each app > > sees and manages only its data. > > > > Moreover there are some apps that should see global data for some specific > > tables. (like the crew data, people in the crew move from one type of > > vessel to the other so they are not tied to a Mgmt company). > > > > These new requirements are of legal nature, as well as of > > operational. (People managing a type of vessels dont want to mess with > > another type, > > and auditors must see each company completely separated from the rest). > > > > Doing it with extra code would be a real pain, since i would have to > > refine > > all security/authentication based on the groups ([EMAIL PROTECTED]) > > that a person belongs to. Also this way no inherent isolation/security > > would hold. > > > > Now i am thinking of restructuring the whole architecture as: > > - Create one EAR app for every mgmt company > > - Create one DB USER for every mgmg company > > - Create one SCHEMA (same as the USER) for every mgmt company > > (mgmtcompany1,mgmtcompany2,etc...) > > - Find a way (links/xdoclet/eclipse?) to have *one* common code base for > > the N EAR apps. > > - Tweak with jboss*.xml to map java:comp/env/jdbc/DB to > > java:/pgsql, where pgsql authenticates > > with the corresponding DB USER. > > - Classify the tables into > > - The ones that apply to ALL mgmt companies (and leave them in the > > public schema) > > - The ones that apply *only* to a mgmt company and so create one under > > each SCHEMA > > - Load the data in *each* SCHEMA, except the tables that apply to all. > > - Define a process of "mgmt company"fying the tables in each schema (e.g. > > delete from mgmtcompany1.vessels the vessels that dont belong to > > mgmtcompany1, and so forth) > > - Resolve FK constraint issues > > - The default search_path in psql (whats the the equivalent in jdbc?) is > > $user,public, so effectively *each* EAR will hit automagically the correct > > mgmtcompanyN.* tables, or the public.* tables if these tables apply to all > > mgmt companies. > > > > With this way, the hard work is DB oriented, and not APP oriented. > > However i wonder whether someone else has gone thru a similar process, > > or if someone finds some assumption conceptually flawed. > > > > Thanx for reading, and for any possible thoughts. > > > > -- > > -Achilleus > > > > > > > > ---(end of broadcast)--- > > TIP 4: Have you searched our list archives? > > > > http://archives.postgresql.org > > > > > > -- > regards, Luckys... > -- -Achilleus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] [JDBC] Thoughts on a Isolation/Security problem.
Hi, Achilleus, Achilleus Mantzios wrote: [schema trickery] > Why do you think its ugly after all? It is not ugly to split such things via schemas per se, but it is ugly to use this schema trick together with a bunch of views, rules and triggers to "upgrade" a legacy single-deployment application into a multi-deployment application with some limited cooperation / data sharing. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Migrating a Database to a new tablespace
Hello, What is the easiest way to migrate a complete database from one tablespace to another? ALTER DATABASE only allows to set the default tablespace, but not migrating the existing database. Thanks, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Looking for some help with cascading updates...
Here is the question:I have a situation where I need to create triggers to cascade an insert operation to many tables to maintain foreign key constraints. So at a high levelINSERT INTO myTable (Name, Address, Zip) VALUES ('Mark', '3 Dingle St.', '01832')
myTable has a relation to two other tables, which contain Zip and Address. Before I insert into myTable I want to:Check if '3 Dingle St.' exists in table Address, if not insert itCheck if '01832' exists in table Zip and if not insert it
I want to generalize this so that for any insert into myTable, I guarantee that if Zip and Address are not populated they will be. My insert into myTable always contains full information required for doing the inserts into the other related tables.
Help?--Mark
Re: [SQL] Looking for some help with cascading updates...
Mark True <[EMAIL PROTECTED]> schrieb:
>
> Here is the question:
>
> I have a situation where I need to create triggers to cascade an insert
> operation to many tables to maintain foreign key constraints.
>
> So at a high level
>
> INSERT INTO myTable (Name, Address, Zip) VALUES ('Mark', '3 Dingle St.',
> '01832')
>
> myTable has a relation to two other tables, which contain Zip and Address.
> Before I insert into myTable I want to:
>
> Check if '3 Dingle St.' exists in table Address, if not insert it
> Check if '01832' exists in table Zip and if not insert it
For such task i write a function (plpgsql). This obtains all parameters,
do the checks and insert into the other tables and the table with the fk
constraints.
HTH, Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."(unknow)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Looking for some help with cascading updates...
I guess I should have clarified. I am having trouble figuring out how to construct the IF statement to see if that item exists in the foreign table something like:count=select count(*) from table;if count=0 {
insert fkey into myOtherTable}On 4/18/06, Andreas Kretschmer <[EMAIL PROTECTED]> wrote:
Mark True <[EMAIL PROTECTED]> schrieb:
>> Here is the question:>> I have a situation where I need to create triggers to cascade an insert> operation to many tables to maintain foreign key constraints.>> So at a high level
>> INSERT INTO myTable (Name, Address, Zip) VALUES ('Mark', '3 Dingle St.',> '01832')>> myTable has a relation to two other tables, which contain Zip and Address.> Before I insert into myTable I want to:
>> Check if '3 Dingle St.' exists in table Address, if not insert it> Check if '01832' exists in table Zip and if not insert itFor such task i write a function (plpgsql). This obtains all parameters,
do the checks and insert into the other tables and the table with the fkconstraints.HTH, Andreas--Really, I'm not out to destroy Microsoft. That will just be a completelyunintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."(unknow)Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°---(end of broadcast)---
TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Looking for some help with cascading updates...
Mark True <[EMAIL PROTECTED]> schrieb:
> I guess I should have clarified. I am having trouble figuring out how to
> construct the IF statement to see if that item exists in the foreign table
> something like:
>
> count=select count(*) from table;
>
> if count=0 {
> insert fkey into myOtherTable
> }
a real example:
(table harz_ufpos references harz_uf)
create or replace function gh_insert(text,int,int,text,int,float) returns int
as $$
declare c int;
begin
select into c count(*) from harz_uf where (uf,ab) = ($1,$2);
if c = 0 then
insert into harz_uf (uf,ab,beschreibung,aktiv) values
($1,$2,$4,'t');
end if;
insert into harz_ufpos (uf,pos,anzahl,rest,flaeche) values
($1,$3,$5,$5,$6);
return 1;
end;
$$ language 'plpgsql';
HTH, Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."(unknow)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] Looking for some help with cascading updates...
Next silly question, how do you get your database to like plpgsql...I do CREATE LANGUAGE 'plpgsql' and it says it is not defined?--MarkOn 4/18/06,
Andreas Kretschmer <[EMAIL PROTECTED]> wrote:
Mark True <[EMAIL PROTECTED]> schrieb:> I guess I should have clarified. I am having trouble figuring out how to> construct the IF statement to see if that item exists in the foreign table
> something like:>> count=select count(*) from table;>> if count=0 {> insert fkey into myOtherTable> }a real example:(table harz_ufpos references harz_uf)
create or replace function gh_insert(text,int,int,text,int,float) returns int as $$declare c int;beginselect into c count(*) from harz_uf where (uf,ab) = ($1,$2);if c = 0 then
insert into harz_uf (uf,ab,beschreibung,aktiv) values ($1,$2,$4,'t');end if;insert into harz_ufpos (uf,pos,anzahl,rest,flaeche) values ($1,$3,$5,$5,$6);return 1;
end;$$ language 'plpgsql';HTH, Andreas--Really, I'm not out to destroy Microsoft. That will just be a completelyunintentional side effect. (Linus Torvalds)"If I was god, I would recompile penguin with --enable-fly."(unknow)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°---(end of broadcast)---TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] create temp table in rule
Is it possible to create a temporary table within a rule? I get a syntax error near 'CREATE TEMPORARY TABLE' if I try to do the following: CREATE RULE id_insert_rule AS ON INSERT TO id_insert DO INSTEAD ( CREATE TEMPORARY TABLE id_temp ( LIKE id ) ON COMMIT DELETE ROWS; INSERT INTO id_temp ( username, pass_md5, pass_sha1 ) VALUES new.username, encode( digest( new.password, 'md5' ), 'hex' ), encode( digest( new.password, 'sha1' ), 'hex' ); DELETE FROM id USING id_temp WHERE id.username = id_temp.username; INSERT INTO id SELECT * FROM id_temp; ); As you can see, I'm trying to create a simple 'insert or update' rule. -- Ron Peterson Network & Systems Manager Mount Holyoke College http://www.mtholyoke.edu/~rpeterso ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Looking for some help with cascading updates...
Mark True <[EMAIL PROTECTED]> schrieb: > Next silly question, how do you get your database to like plpgsql... - createlang on the command line - create language plpgsql; (in psql) > > I do CREATE LANGUAGE 'plpgsql' and it says it is not defined? Strange... > > --Mark > > > On 4/18/06, AAnnddrreeaass KKrreettsscchhmmeerr <[EMAIL > PROTECTED]> wrote: > Mark True <[EMAIL PROTECTED]> schrieb: Please, without HTML and fullquote... HTH, Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly."(unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Looking for some help with cascading updates...
"Mark True" <[EMAIL PROTECTED]> writes: > I do CREATE LANGUAGE 'plpgsql' and it says it is not defined? Should work (in 8.1 anyway). Could we see the *exact* command and error message? regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] create temp table in rule
Ron Peterson <[EMAIL PROTECTED]> writes: > Is it possible to create a temporary table within a rule? No, sorry. From memory, rules only allow SELECT/INSERT/UPDATE/DELETE plus NOTIFY (and there are restrictions on the latter). regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Complex Informix OUTER
Hi all. I have a fairly complicated Informix OUTER needs to be converted to PostgreSQL and I cannot get around it. For me it is fairly complicated Problem is inv_contracts OUTERS from three different tables and two out of these three (invention, 'dbuser as con') needs to SELECT for data (invention.name as inv_name, con.lastname as con_lastname, con.firstname as con_firstname) Thanks for help. First thought would be below but not allowed, inv_contracts needs to be renamed and that wont be the correct conversion. ,invention LEFT OUTER JOIN inv_contracts ON inv_contracts.inv_id = invid ,dbuser as con LEFT OUTER JOIN inv_contracts ON inv_contracts.con_id = con.id ,contractDef LEFT OUTER JOIN inv_contracts ON inv_contracts.contract_id IN (select id FROM contractDef WHERE phase_id = 2); Possible sub-queries may not be the solution because I need to be able to some selects from 'dbuser as con' and invention. Thanks. Any help appriciated. --ORIGINAL INFORMIX OUTER QUERY SELECT user.username, user.firstname, user.lastname, add1, add2, city, stateDef.abbreviation as state, zip, invention.inv_number as invnum, invention.name as inv_name, con.lastname as con_lastname, con.firstname as con_firstname FROM user, ascpDef AS stateDef, address, invention, user as con , OUTER inv_contracts WHERE address.type = 'User' AND address_id = 1 AND user.id = address.type_id AND state_id = stateDef.id AND invention.user_id = user.id AND invention.inv_number = '1994376-A' AND inv_contracts.inv_id = invention.id AND inv_contracts.con_id = con.id AND inv_contracts.contract_id IN (select id FROM contractDef WHERE phase_id = 2); - This mail sent through IMP: www.resolution.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
