Re: [SQL] [PHP] Secure DB Systems - How to
On Thu, Jul 08, 2004 at 11:49:36 -0400, Sarah Tanembaum <[EMAIL PROTECTED]> wrote: > I was wondering if it is possible to create a secure database system > usingPostgreSQL/PHP combination? > > I have the following in mind: > > I wanted to store all my( and my brothers and sisters) important document > information such as birth certificate, SSN, passport number, travel > documents, insurance(car, home, etc) document, and other important documents > imagined in the database. > > The data will be entered either manually and/or scanned(with OCR). I need to > be able to search on all the fields in the database. > > We have 10 computers(5bros, 4sisters, and myself) plus 1 server with I > maintained. The data should be synchronize/replicate between those > computers. > > Well, so far it is easy, isn't it? > > Here's my question: > > a) How can I make sure that it secure so only authorized person can > modify/add/delete the information? Beside transaction logs, are there any > other method to trace any transaction(kind of paper trail)? Keeping the system administrator from seeing the data while making it searchable is difficult. To do this you need to encrypt the data on the client side using a key the client has (and this key has to be protected from loss) and the only searches you can do are equality searches using a hash or encrypted value. The system administrator can always delete the data. If you store which user has access to a row in the row, you can use views to control access to the rows for people other than the system administrator. > b) How can I make sure that no one can tap the info while we are entering > the data in the computer? (our family are scattered within US and Canada) Use SSL. > c) Is it possible to securely synchronize/replicate between our computers > using VPN? Does PostgreSQL has this functionality by default? Probably the best thing here is to run one live server and make backups of the system that you store at your relatives along with instructions for recovering them if something happens to you. Probably the backups should be encrypted with either the keys in your safe deposit box or using a system where something like 3 out of 5 keys can be used to recover the backup files. Be sure to test the backup recovery. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Constraint->function dependency and dump in 7.3
Hi! Why don't you use pg_restore. You can set the order of restoring with parameters. (I haven't tried) By, Gabor -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Együd Csaba Sent: Friday, July 09, 2004 7:15 AM To: 'SZŰCS Gábor'; [EMAIL PROTECTED] Subject: Re: [SQL] Constraint->function dependency and dump in 7.3 Hi Gábor! I had the same problem and someone pointed me the right direction. I tried to define a table default clause refering a function. Reloading the dump file an error messaged raised up saying that the referred function doesn't exist. This is because dumping out the schema pg_dump pushes out the table definitions first and then the functions (I don't know why can not realize these issues.). You can keep the schema dump in a separete file and move the referred functions in front of the tble definitions. After that regulary dump out only the data. Restoing the db start with the schema file. I hope I was clear. Another advance of this method is that it is absolutely Y3K safe. :) Bye, -- Csaba Együd > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Behalf Of SZŰCS Gábor > Sent: 2004. július 8. 13:10 > To: [EMAIL PROTECTED] > Subject: [SQL] Constraint->function dependency and dump in 7.3 > > > Dear Gurus, > > Just recently realized that our daily dump from a 7.3 > (production) db to a > 7.4 (development) server has problems. I have no idea where > to search for an > answer so please feel free to point me to the appropriate > thread, doc or TFM > :) > > Below is two portions of the dump, which seems to be in the > wrong order (at > least for me). I'm not sure, and not in a position to easily > test it, that > it's wrong order in 7.3; but 7.4 has problems creating the > table without the > function (which is logical): > > %--- cut here ---% > CREATE TABLE cim ( > -- etc etc ... > orszag_kod integer, > CONSTRAINT cim_orszag_kod CHECK ((hely_fajta(orszag_kod) = 7)) > ); > > -- ... several lines later: > > CREATE FUNCTION hely_fajta (integer) RETURNS integer > AS ' ... ' > LANGUAGE sql; > %--- cut here ---% > > Checked pg_depend, and constraint cim_orszag_kod refers to function > hely_fajta, but noone (not even the table) refers to the > constraint. I'm > just wondering if it's ok... > > 1) is it normal that the table does not refer to its constraints? > 2) if not, do you have the idea of the possible cause? > 3) if so, is it normal for pg_dump to dump in this order? > 4) if so, how may I change it? > 5) may inserting into pg_depend solve the problem? > > TIA, > G. > %--- cut here ---% > \end > > > ---(end of > broadcast)--- > TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faqs/FAQ.html > > --- > Incoming mail is certified Virus Free. > Checked by AVG anti-virus system (http://www.grisoft.com). > Version: 6.0.712 / Virus Database: 468 - Release Date: 2004. 06. 27. > --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.712 / Virus Database: 468 - Release Date: 2004. 06. 27. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] [JDBC] Error in DatabaseMetaData.getColumns() with Views
On Sat, 3 Jul 2004, Dario V. Fassi wrote: > In the sample adjunct, you can see that error arise at the time when the > view's sql text is parsed and saved in database catalog. > Then generic NUMERIC type is forced for every calculated column without > regard or precision. > And at execute time the f2 column has varying type decimals (in row 2 > you can see 4 decimals and in other rows has 3 decimals), this is not a > behavior , this is an ERROR. It isn't clear that an operation like + should retain the same size restrictions as it's arguments. Consider adding two numeric(6,2) values of .99, how do you handle the overflow? Your other arguments about the sizing of derived columns may make sense for your application, but it is unlikely that they make sense for all users. Note that you can put a cast into your view definition like so: CREATER VIEW v AS SELECT (a+b)::numeric(6,2) FROM tab; Kris Jurka ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] [BUGS] [JDBC] Error in DatabaseMetaData.getColumns() with Views
Stephan Szabo wrote: On Sun, 4 Jul 2004, Kris Jurka wrote: On Sat, 3 Jul 2004, Dario V. Fassi wrote: In the sample adjunct, you can see that error arise at the time when the view's sql text is parsed and saved in database catalog. Then generic NUMERIC type is forced for every calculated column without regard or precision. And at execute time the f2 column has varying type decimals (in row 2 you can see 4 decimals and in other rows has 3 decimals), this is not a behavior , this is an ERROR. [Jumping in, because this was the first message of the thread I've seen] Technically, the correct behavior by spec would be an implementation-defined precision and a particular scale based on the argument scales. So, having numeric(6,2)+numeric(6,2) return numeric(65535, 2) is fine. Returning numeric(65535, 65531) isn't technically, but I don't think this is an issue in the jdbc metadata getting as much as an issue in the database proper. I agree with all your spech but a numeric data type like numeric(65535, 2) isn't a very serious return value. A value like this could be usefull to eliminate the need of presicion specification in numeric data type too. Of course it's a dabase proper issue , but impact in my needs in jdbc usability. No matter what's technically or not, a behavior at this point like those of db2/oracle would be very nice ;-)
Re: [SQL] [JDBC] Error in DatabaseMetaData.getColumns() with Views
kris, Next you can see how the same test-case is handled by DB2. -- IBM DB2 V6.1 for Linux CREATE TABLE userid.t ( f1 numeric(6,3), f2 numeric(6,4), f3 numeric(8,2) ); create view userid.v as select (f1+f2+f3) as fsum, coalesce(f1,f2,f3) as fcoal, (f1*f2*f3) as fprod, (f1/f2) as fdiv from userid.t; insert into userid.t values ( 123.123, 12.1234, 12345.12); insert into userid.t values ( null, 12.1234, 12345.12); insert into userid.t values ( 123.123, null, 12345.12); insert into userid.t values ( 123.123, 12.1234, null); insert into userid.t values ( 999.999, 99., 9.99); select * from userid.v FSUM | FCOAL | FPROD | FDIV | -- 12480.3664 | 123.1230 | 18427182.594204384 | 10.155814375505221307553986 | -- | 12.1234 | | | -- | 123.1230 | | | -- | 123.1230 | | 10.155814375505221307553986 | -- 101099.9889 | 999.9990 | 979000.01199 | 10. | -- METADATA RETURNED BY DatabaseMetaData.getColumns() Tabla: USERID.V # Campo Tipo Anulable ReadOnly Writable AutoInc CaseSens Currency 1 FSUM [0] DECIMAL(11,4) . . WA . . . 2 FCOAL [0] DECIMAL(10,4) . . WA . . . 3 FPROD [0] DECIMAL(20,9) . . WA . . . 4 FDIV [0] DECIMAL(31,24) . . WA . . . 4 Campos. -- # Campo Tipo nTy Nulable Descripcion 1 FSUM DECIMAL(11,4) B10 3 2 FCOAL DECIMAL(10,4) B10 3 3 FPROD DECIMAL(20,9) B10 3 4 FDIV DECIMAL(31,24) B10 3 -- The work-around is posible , if you are working from start and you are warned about the problem. But if you are porting a data model you must to reconstuct all views. A better solution to return (-1 or 65535) as the length or decs of a numeric field could be to do dataType scalation to a DOUBLE data type. This is not a good solution but better than now. The optimal solution es make presicion calculation for every column or almost the trivial cases like columns formed by "coalesce", "case then", etc. The overflow or "data truncation" exception , for views, it's a going back , but almost put the problem in programmer's hands. Regards, Dario. Kris Jurka wrote: On Sat, 3 Jul 2004, Dario V. Fassi wrote: In the sample adjunct, you can see that error arise at the time when the view's sql text is parsed and saved in database catalog. Then generic NUMERIC type is forced for every calculated column without regard or precision. And at execute time the f2 column has varying type decimals (in row 2 you can see 4 decimals and in other rows has 3 decimals), this is not a behavior , this is an ERROR. It isn't clear that an operation like + should retain the same size restrictions as it's arguments. Consider adding two numeric(6,2) values of .99, how do you handle the overflow? Your other arguments about the sizing of derived columns may make sense for your application, but it is unlikely that they make sense for all users. Note that you can put a cast into your view definition like so: CREATER VIEW v AS SELECT (a+b)::numeric(6,2) FROM tab; Kris Jurka -- Dario V. Fassi SISTEMATICA ingenieria de software srl Ituzaingo 1628 (2000) Rosario, Santa Fe, Argentina. Tel / Fax: +54 (341) 485.1432 / 485.1353
Re: [SQL] [BUGS] [JDBC] Error in DatabaseMetaData.getColumns() with Views
Stephan, look at the samples I send in previous posts , from PgSql and Db2. I know it's a no ease task to change all that behavior , but you must agree that a Numeric column with the fractional part varing from row to row are different data types and break relational rules. If this is the case the returning data type must be a floating point data type like a double, but never 2 decimals for a row and 3, 4 or whatever for others rows. Stephan Szabo wrote: On Sun, 4 Jul 2004, Dario V. Fassi wrote: Stephan Szabo wrote: On Sun, 4 Jul 2004, Kris Jurka wrote: On Sat, 3 Jul 2004, Dario V. Fassi wrote: In the sample adjunct, you can see that error arise at the time when the view's sql text is parsed and saved in database catalog. Then generic NUMERIC type is forced for every calculated column without regard or precision. And at execute time the f2 column has varying type decimals (in row 2 you can see 4 decimals and in other rows has 3 decimals), this is not a behavior , this is an ERROR. [Jumping in, because this was the first message of the thread I've seen] Technically, the correct behavior by spec would be an implementation-defined precision and a particular scale based on the argument scales. So, having numeric(6,2)+numeric(6,2) return numeric(65535, 2) is fine. Returning numeric(65535, 65531) isn't technically, but I don't think this is an issue in the jdbc metadata getting as much as an issue in the database proper. I agree with all your spech but a numeric data type like numeric(65535, 2) isn't a very serious return value. Why not? If 65535 were the maximal numeric precision (also implementation-defined IIRC) then it's a reasonable answer, although it doesn't give a user much information about the expected result range, but AFAICS that isn't one of the expected properties. A value like this could be usefull to eliminate the need of presicion specification in numeric data type too. Of course it's a dabase proper issue , but impact in my needs in jdbc usability. Since I'm not on -jdbc, I didn't see how it was intended to be used, but I think it'd be a portability bug to expect it to return only the precision that the result could take. Now, the current results are fairly broken because the precision value has no connection to reality (it's not the maximum precision, and might in fact be smaller than the actual precision in some absurd cases) and the scale is wrong if one follows spec. I'm not sure the jdbc driver can do much better given the backend right now. No matter what's technically or not, a behavior at this point like those of db2/oracle would be very nice ;-) Probably true. But I don't think it's likely to happen any time soon unless someone steps up and takes responsibility for making it happen. I think it'd also be non-trivial for the general case since I think it'd have to mean that arbitrary functions would have to be able to have some sort of way of specifying the values for its output.
[SQL] What is PG best practice for storing temporary data in functions?
Title: Message There is a very common technique used in other RDBMS (e.g. Sybase) stored procedure programming which I am not sure how best to replicate in Postgres functions. A Sybase stored procedure can do select into #temp1 create table #temp2 (…) call some proc which also uses #temp1 and #temp2 etc where #temp1 and #temp2 are temporary tables magically created by the server for the duration of the procedure call only (the procedure can be safely executed in parallel since each execution sees only its own data). Under the hood, it does this by mangling the names of the temp tables with a unique identifier for the procedure context. What are the cleanest and most performant ways to do this in Postgres (7.4.2)? I am aware of temporary tables but they are globally visible to other invocations in the same session. We need the equivalent of local method variables I guess.
[SQL] How to filter on timestamps?
I have a table where one of the columns is of type 'TIMESTAMP' How can I do a query to filter on the TIMESTAMP value, e.g. to obtain all rows earlier than a certain time stamp? Regards, ---(end of broadcast)--- TIP 3: 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] How to filter on timestamps?
B.W.H. van Beest wrote: > > > I have a table where one of the columns is of type 'TIMESTAMP' > > How can I do a query to filter on the TIMESTAMP value, e.g. to obtain > all rows earlier than a certain time stamp? Think of the math opperators '<' and '>' as 'before' and 'after', respectively. ie: SELECT * FROM table WHERE begin_date > '2004-07-06'; You can also use BETWEEN: SELECT * FROM table WHERE update_timestamp BETWEEN '2004-07-01' AND '2004-07-06'; Remember that when timestamps are cast to dates, they are cast with 00:00:00.0 as the time part. See the docs on this at http://www.postgresql.org/docs/7.4/interactive/datatype-datetime.html and http://www.postgresql.org/docs/7.4/interactive/functions-datetime.html . --miker > > Regards, ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Triggers - need help !!!
"Richard Huxton" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Pradeepkumar, Pyatalo (IE10) wrote: > > IF UPDATE(CreateTime) THEN > > > > > > END IF; > > > > Is this syntax correct. > > No, and I don't recall seeing anything like it in the manuals. ... and it's always an excellent time to read them ... > IF OLD.CreateTime <> NEW.CreateTime THEN > ... ... being mindful of the case where CreateTime might be NULL ... ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] append columns that are null
THX! didn't know that one yet :) nobody wrote: "Gerwin Philippo" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] I have function that appends 2 columns, like "new.field1 = new.field2||new.field3", but when field 3 is null, then the entire outcome is null, while I expected just field2, since when I append nothing to field2, I get field2, right? Is this standard behaviour, and if so, is there another append function that I can use that doesn't behave like this? COALESCE is your friend... ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Help with sql statement grouping and distinct
I want to be able to select a distinct group of rows from 2 tables and then once selected to group and total the results from the previous sql statement I have been trying the following select a.id_fk_accommodation, a.id_fk_selection,a.type,sum(1) from acommodationlog a, log l where (id_fk_accommodation) in (select distinct a.id_fk_accommodation, a.id_fk_selection, a.type, l.sessionid from accommodationlog a, log l where a.id_fk_log = l.id_pk and id_fk_accommodation = 159 ) group by a.id_fk_accommodation,a.id_fk_selection,a.type I know this won't work due to incorrect number of columns in the in statement but how do I achieve the above Any help appreciated Thanks Regards David Inglis This message was sent using IMP, the Internet Messaging Program. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] append columns that are null
I have function that appends 2 columns, like "new.field1 = new.field2||new.field3", but when field 3 is null, then the entire outcome is null, while I expected just field2, since when I append nothing to field2, I get field2, right? Is this standard behaviour, and if so, is there another append function that I can use that doesn't behave like this? ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] append columns that are null
You are welcome. Only if all problems were that simple ;-)... "Gerwin Philippo" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > THX! didn't know that one yet :) > > nobody wrote: > > "Gerwin Philippo" <[EMAIL PROTECTED]> wrote in message > > news:[EMAIL PROTECTED] > > > >>I have function that appends 2 columns, like > >>"new.field1 = new.field2||new.field3", > >>but when field 3 is null, then the entire outcome is null, while I > >>expected just field2, since when I append nothing to field2, I get > >>field2, right? > >>Is this standard behaviour, and if so, is there another append function > >>that I can use that doesn't behave like this? > > > > > > COALESCE is your friend... > > > > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] append columns that are null
I'd be out of work very soon :( nobody wrote: You are welcome. Only if all problems were that simple ;-)... "Gerwin Philippo" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] THX! didn't know that one yet :) nobody wrote: "Gerwin Philippo" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] I have function that appends 2 columns, like "new.field1 = new.field2||new.field3", but when field 3 is null, then the entire outcome is null, while I expected just field2, since when I append nothing to field2, I get field2, right? Is this standard behaviour, and if so, is there another append function that I can use that doesn't behave like this? COALESCE is your friend... ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] append columns that are null
"Gerwin Philippo" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > I have function that appends 2 columns, like > "new.field1 = new.field2||new.field3", > but when field 3 is null, then the entire outcome is null, while I > expected just field2, since when I append nothing to field2, I get > field2, right? > Is this standard behaviour, and if so, is there another append function > that I can use that doesn't behave like this? COALESCE is your friend... ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Secure DB Systems - How to
I was wondering if it is possible to create a secure database system usingPostgreSQL/PHP combination? I have the following in mind: I wanted to store all my( and my brothers and sisters) important document information such as birth certificate, SSN, passport number, travel documents, insurance(car, home, etc) document, and other important documents imagined in the database. The data will be entered either manually and/or scanned(with OCR). I need to be able to search on all the fields in the database. We have 10 computers(5bros, 4sisters, and myself) plus 1 server with I maintained. The data should be synchronize/replicate between those computers. Well, so far it is easy, isn't it? Here's my question: a) How can I make sure that it secure so only authorized person can modify/add/delete the information? Beside transaction logs, are there any other method to trace any transaction(kind of paper trail)? Assuming there are 3 step process to one enter the info e.g: - One who enter the info (me) - One who verify the info(the owner of info) - One who verify and then commit the change! How can I implement such a process in PostgreSQL and/or PHP or any other web language? b) How can I make sure that no one can tap the info while we are entering the data in the computer? (our family are scattered within US and Canada) c) Is it possible to securely synchronize/replicate between our computers using VPN? Does PostgreSQL has this functionality by default? d) Other secure method that I have not yet mentioned. Anyone has good ideas on how to implement such a systems? Thanks ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] append columns that are null
nobody wrote: "Gerwin Philippo" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] I have function that appends 2 columns, like "new.field1 = new.field2||new.field3", but when field 3 is null, then the entire outcome is null, while I expected just field2, since when I append nothing to field2, I get field2, right? Is this standard behaviour, and if so, is there another append function that I can use that doesn't behave like this? 3 + unknown value = unknown value its also unknown if 3 is bigger, smaller or equal to an unknown value. -- Hans de Bruin http://eratosthenes.xs4all.nl ---(end of broadcast)--- TIP 3: 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] Problem in Stored Procedures
Hi all, I m using Postgresql version 7.1.3-2. I have written a function which accepts 2 arguments and returns matching tuples from a table based on the arguments passed...but i am having problems in getting it work. This is my function - CREATE TYPE PointType AS(ParamId INTEGER,ParamName VARCHAR(5),Is_FixEnum BIT,Is_ExpandEnum BIT); CREATE FUNCTION PP_ReadParameter(integer,varchar) RETURNS setof PointType AS ' DECLARE rec PointType; BEGIN IF $1 IS NOT NULL THEN FOR rec IN SELECT ParamId, ParamName, Is_FixEnum, Is_ExpandEnum AttributeId, AttributeName,IsFixEnum,IsExpandEnum FROM Attributes WHERE AttributeId = $1 ORDER BY AttributeId LOOP RETURN NEXT rec; END LOOP; RETURN; END IF; ELSE IF $2 IS NOT NULL THEN FOR rec IN SELECT ParamId, ParamName, Is_FixEnum, Is_ExpandEnum AttributeId, AttributeName,IsFixEnum,IsExpandEnum FROM Attributes WHERE AttributeId = $2 ORDER BY AttributeId LOOP RETURN NEXT rec; END LOOP; RETURN; ELSE FOR rec IN SELECT ParamId, ParamName, Is_FixEnum, Is_ExpandEnum AttributeId, AttributeName,IsFixEnum,IsExpandEnum FROM Attributes ORDER BY AttributeId LOOP RETURN NEXT rec; END LOOP; RETURN; END IF; END IF; END; ' language 'plpgsql'; I get the error... psql:Procedures.sql:2: ERROR: parse error at or near "AS" (for CREATE TYPE command) psql:Procedures.sql:40: NOTICE: return type 'pointtype' is only a shell CREATE WHEN I EXECUTE THE FUNCTION USING SELECT (pp_readparameter(42,null)); ERROR: fmgr_info: function 0: cache lookup failed. any value inputs on why this is happening. > With Best Regards > Pradeep Kumar P J > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
