[HACKERS] Bug in pg_get_constraintdef (for deferrable constraints)
Postgresql 7.3.1 on Linux i386 - but from what I can see it is on all platforms It seems pg_get_constraintdef does not remember the setting DEFERRABLE on a constraint. This has the effect that it does not show up in psql \d commands, and it is also *not* included in backups from pg_dump. Reproduce: CREATE TABLE foo.prim(i int PRIMARY KEY); CREATE TABLE foo.for1(j int REFERENCES foo.prim(i) NOT DEFERRABLE); CREATE TABLE foo.for2(j int REFERENCES foo.prim(i) DEFERRABLE); \d foo.for1 and \d foo.for2 will then show the exact same definition of the constraint: Table foo.for2 Column | Type | Modifiers +-+--- j | integer | Foreign Key constraints: $1 FOREIGN KEY (j) REFERENCES foo.prim(i) ON UPDATE NO ACTION ON DELETE NO ACTION Seems to me like ruleutils.c at around line 600 is the place, and that is has no concept of DEFERRABLE anywhere near that, but I'm not comfortable enough in there to produce a patch myself... //Magnus ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Bug in pg_get_constraintdef (for deferrable constraints)
I can reproduce this failure here too. I am actually quite confused because: 1) I know this deferrable stuff works or used to work 2) I can't find relivant references to condeferrable/Anum_pg_constraint_condeferrable and condeferred/Anum_pg_constraint_condeferred in the code. I see the values being stored on constriant creation, but not being used anywhere: $ rgrepc condefer ./backend/catalog/pg_constraint.c: values[Anum_pg_constraint_condeferrable - 1] = BoolGetDatum(isDeferrable); ./backend/catalog/pg_constraint.c: values[Anum_pg_constraint_condeferred - 1] = BoolGetDatum(isDeferred); ./include/catalog/pg_constraint.h: boolcondeferrable; /* deferrable constraint? */ ./include/catalog/pg_constraint.h: boolcondeferred;/* deferred by default? */ ./include/catalog/pg_constraint.h:#define Anum_pg_constraint_condeferrable 4 ./include/catalog/pg_constraint.h:#define Anum_pg_constraint_condeferred 5 I am confused. --- Magnus Hagander wrote: Postgresql 7.3.1 on Linux i386 - but from what I can see it is on all platforms It seems pg_get_constraintdef does not remember the setting DEFERRABLE on a constraint. This has the effect that it does not show up in psql \d commands, and it is also *not* included in backups from pg_dump. Reproduce: CREATE TABLE foo.prim(i int PRIMARY KEY); CREATE TABLE foo.for1(j int REFERENCES foo.prim(i) NOT DEFERRABLE); CREATE TABLE foo.for2(j int REFERENCES foo.prim(i) DEFERRABLE); \d foo.for1 and \d foo.for2 will then show the exact same definition of the constraint: Table foo.for2 Column | Type | Modifiers +-+--- j | integer | Foreign Key constraints: $1 FOREIGN KEY (j) REFERENCES foo.prim(i) ON UPDATE NO ACTION ON DELETE NO ACTION Seems to me like ruleutils.c at around line 600 is the place, and that is has no concept of DEFERRABLE anywhere near that, but I'm not comfortable enough in there to produce a patch myself... //Magnus ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Bug in pg_get_constraintdef (for deferrable constraints)
Bruce Momjian [EMAIL PROTECTED] writes: I see the values being stored on constriant creation, but not being used anywhere: I believe the values that actually get inspected at runtime are the tgdeferrable and tginitdeferred fields in pg_trigger. The columns in pg_constraint are just copies of these. It is not real clear to me whether it should be allowed to alter the deferrability status of a foreign-key constraint --- is that in the spec? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Bug in pg_get_constraintdef (for deferrable constraints)
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: I see the values being stored on constriant creation, but not being used anywhere: I believe the values that actually get inspected at runtime are the tgdeferrable and tginitdeferred fields in pg_trigger. The columns in pg_constraint are just copies of these. It is not real clear to me whether it should be allowed to alter the deferrability status of a foreign-key constraint --- is that in the spec? The big problem is that while pg_dump's dump_trigger() looks at tginitdeferred and dumps accordingly, pg_get_constraintdef doesn't look at tginitdeferred, and therefore doesn't record the requirement as part of ALTER TABLE ADD CONSTRAINT. Attached is a dump of the supplied example, showing that the outputs are the same. Looks like this is a must-fix for 7.3.2. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 -- -- PostgreSQL database dump -- \connect - postgres SET search_path = public, pg_catalog; -- -- TOC entry 2 (OID 149751) -- Name: prim; Type: TABLE; Schema: public; Owner: postgres -- CREATE TABLE prim ( i integer NOT NULL ); -- -- TOC entry 3 (OID 149755) -- Name: for1; Type: TABLE; Schema: public; Owner: postgres -- CREATE TABLE for1 ( j integer ); -- -- TOC entry 4 (OID 149761) -- Name: for2; Type: TABLE; Schema: public; Owner: postgres -- CREATE TABLE for2 ( j integer ); -- -- Data for TOC entry 6 (OID 149751) -- Name: prim; Type: TABLE DATA; Schema: public; Owner: postgres -- COPY prim (i) FROM stdin; \. -- -- Data for TOC entry 7 (OID 149755) -- Name: for1; Type: TABLE DATA; Schema: public; Owner: postgres -- COPY for1 (j) FROM stdin; \. -- -- Data for TOC entry 8 (OID 149761) -- Name: for2; Type: TABLE DATA; Schema: public; Owner: postgres -- COPY for2 (j) FROM stdin; \. -- -- TOC entry 5 (OID 149753) -- Name: prim_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY prim ADD CONSTRAINT prim_pkey PRIMARY KEY (i); -- -- TOC entry 9 (OID 149757) -- Name: $1; Type: CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY for1 ADD CONSTRAINT $1 FOREIGN KEY (j) REFERENCES prim(i) ON UPDATE NO ACTION ON DELETE NO ACTION; -- -- TOC entry 10 (OID 149763) -- Name: $1; Type: CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY for2 ADD CONSTRAINT $1 FOREIGN KEY (j) REFERENCES prim(i) ON UPDATE NO ACTION ON DELETE NO ACTION; ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Bug in pg_get_constraintdef (for deferrable constraints)
On Wed, 1 Jan 2003, Bruce Momjian wrote:
Tom Lane wrote:
Bruce Momjian [EMAIL PROTECTED] writes:
I see the values being stored on constriant creation, but not being used
anywhere:
I believe the values that actually get inspected at runtime are the
tgdeferrable and tginitdeferred fields in pg_trigger. The columns in
pg_constraint are just copies of these.
It is not real clear to me whether it should be allowed to alter the
deferrability status of a foreign-key constraint --- is that in the spec?
The big problem is that while pg_dump's dump_trigger() looks at
tginitdeferred and dumps accordingly, pg_get_constraintdef doesn't look
at tginitdeferred, and therefore doesn't record the requirement as part
of ALTER TABLE ADD CONSTRAINT.
pg_get_constraintdef should probably be looking at condeferrable
and condeferred in the pg_constraint row it's looking at. Maybe something
like the attached.
*** pgsql/src/backend/utils/adt/ruleutils.c 2003-01-01 15:03:35.0 -0800
--- pgsql/src/backend/utils/adt/ruleutils.c.new 2003-01-01 15:02:32.0 -0800
***
*** 688,693
--- 688,704
}
appendStringInfo(buf, ON DELETE %s, string);
+ if (!conForm-condeferrable) {
+ appendStringInfo(buf, NOT);
+ }
+ appendStringInfo(buf, DEFERRABLE);
+ if (conForm-condeferred) {
+ appendStringInfo(buf, INITIALLY DEFERRED);
+ }
+ else {
+ appendStringInfo(buf, INITIALLY
+IMMEDIATE);
+ }
+
break;
}
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] PostgreSQL Password Cracker
Tom Lane wrote: Devrim GUNDUZ [EMAIL PROTECTED] writes: I had no time to search throug the code; but as far as I understood, it *attacks* the database servers with TCP/IP on, right? No, the program itself simply takes an MD5 hash value and does a brute-force search for a password that generates that MD5 string. The comments at the top suggest sniffing a Postgres session startup exchange in order to see the MD5 value that the user presents; which the attacker would then give to this program. (Forget it if the session is Unix-local rather than TCP, or if it's SSL-encrypted...) This is certainly a theoretically possible attack against someone who has no clue about security, but I don't put any stock in it as a practical attack. For starters, if you are talking to your database across a network that is open to hostile sniffers, you should definitely be using SSL. This is absolutely correct, shouldn't this be in the FAQ?
Re: [HACKERS] Postgresql, unicode and umlauts
The error message says all. You are trying to insert a character which is not a UTF-8. Probably you are inputting UCS-2? -- Tatsuo Ishii From: Atahualpa Jones [EMAIL PROTECTED] Subject: Postgresql, unicode and umlauts Date: Wed, 1 Jan 2003 20:17:15 +0100 Message-ID: 000d01c2b1ca$67838d60$1501000a@DonAndre Dear Sir, I have also submitted following (in slightly different manner to the postgresql newsgroup comp.databases.postgresql.general on news.postgresql.org. The posting there is of 31.12.2002 02:02 and is titled Postgresql 7.2.1 and umlauts. So far I have received no reply and since I saw that you are the person that did the coding for my problem, I write directly to you: I have a Linux Box running Postgresql 7.2.1 and php 4.2.1+apache-ssl 1.3.26+1.48. I have set up a small php form that inserts something in a postgres database called test in a table called guestbook. Client and Database Encoding is set to UNICODE, but whenever a user submits an umlaut character (for example: ö,ä,ü or sharp-s (ß)) then I get an error message that it is an invalid unicode character sequence: Warning: PostgreSQL query failed: ERROR: Invalid UNICODE character sequence found (0xe4) in /var/www/dc+da/em.inc on line 63 (thats the error message for umlaut-a) In the Warning message for umlaut-o there is also a spelling mistake: Warning: PostgreSQL query failed: ERROR: Unicode = 0x1 is not supoorted in /var/www/dc+da/em.inc on line 63 (note supoorted instead of supported) I have tried to change the encoding with php by setting pg_setclientencoding or using mp_convert_encoding, but it did not work. Either the error message came up, or there were strange or no characters written into the database. How is it possible to write umlaut characters into a postgresql database? A friend of mine running postgresql 7.2.2 on FreeBSD also tried and failed too. He tried not only with php but with perl aswell. Here is the list of databases: psql -l List of databases Name| Owner | Encoding ---+--+-- template0 | postgres | UNICODE template1 | postgres | UNICODE test | ata | UNICODE (3 rows) Here is the the SQL statement that creates the table: CREATE TABLE guestbook ( postid SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT, title TEXT NOT NULL, message TEXT NOT NULL, posted TIMESTAMP DEFAULT 'now' ); Here is postgresql version information: psql -V psql (PostgreSQL) 7.2.1 contains support for: readline, history, multibyte Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group Portions Copyright (c) 1996, Regents of the University of California Read the file COPYRIGHT or use the command \copyright to see the usage and distribution terms. the problematic line 63 of my php script looks as following: $result = pg_exec($database,INSERT INTO guestbook ...); The script works if you do not put in any umlaut characters. Sincerely, Ata ---(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: [HACKERS] PostgreSQL Password Cracker
mlw wrote: The comments at the top suggest sniffing a Postgres session startup exchange in order to see the MD5 value that the user presents; which the attacker would then give to this program. (Forget it if the session is Unix-local rather than TCP, or if it's SSL-encrypted...) This is certainly a theoretically possible attack against someone who has no clue about security, but I don't put any stock in it as a practical attack. For starters, if you are talking to your database across a network that is open to hostile sniffers, you should definitely be using SSL. This is absolutely correct, shouldn't this be in the FAQ? Well, this is a pretty rare issue, so it doesn't seem like an FAQ. People need to understand the ramifications of the various pg_hba.conf settings, and I think our documentation does that. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] PostgreSQL Password Cracker
Bruce Momjian wrote: mlw wrote: The comments at the top suggest sniffing a Postgres session startup exchange in order to see the MD5 value that the user presents; which the attacker would then give to this program. (Forget it if the session is Unix-local rather than TCP, or if it's SSL-encrypted...) This is certainly a theoretically possible attack against someone who has no clue about security, but I don't put any stock in it as a practical attack. For starters, if you are talking to your database across a network that is open to hostile sniffers, you should definitely be using SSL. This is absolutely correct, shouldn't this be in the FAQ? Well, this is a pretty rare issue, so it doesn't seem like an FAQ. People need to understand the ramifications of the various pg_hba.conf settings, and I think our documentation does that. A good DBA will probably read the docs, a bad DBA will probably not, and it is the bad DBA that needs to be guided the most. Maybe not FAQ, but is the a short page of "dos and don'ts?
Re: [HACKERS] PostgreSQL Password Cracker
What do others think? I am not sure myself. --- mlw wrote: Bruce Momjian wrote: mlw wrote: The comments at the top suggest sniffing a Postgres session startup exchange in order to see the MD5 value that the user presents; which the attacker would then give to this program. (Forget it if the session is Unix-local rather than TCP, or if it's SSL-encrypted...) This is certainly a theoretically possible attack against someone who has no clue about security, but I don't put any stock in it as a practical attack. For starters, if you are talking to your database across a network that is open to hostile sniffers, you should definitely be using SSL. This is absolutely correct, shouldn't this be in the FAQ? Well, this is a pretty rare issue, so it doesn't seem like an FAQ. People need to understand the ramifications of the various pg_hba.conf settings, and I think our documentation does that. A good DBA will probably read the docs, a bad DBA will probably not, and it is the bad DBA that needs to be guided the most. Maybe not FAQ, but is the a short page of dos and don'ts? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] PostgreSQL Password Cracker
Bruce Momjian [EMAIL PROTECTED] writes: What do others think? I am not sure myself. There should definitely be someplace that recommends using SSL across insecure networks (if there's not already). But it doesn't seem to me to qualify as a FAQ entry. Somewhere in the admin guide seems more appropriate. Perhaps under Client Authentication? Maybe someone could even put together enough material to create a whole chapter on security considerations --- this is hardly the only item worthy of mention. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] PostgreSQL Password Cracker
Yes, I have been feeling we should do that. Justin pointed out just yesterday that .pgpass is only mentioned in libpq documentation, and in fact there is lots of stuff mentioned in libpq that releates to the other interfaces, so it should be pulled out and put in one place. Does anyone want to tackle this? --- Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: What do others think? I am not sure myself. There should definitely be someplace that recommends using SSL across insecure networks (if there's not already). But it doesn't seem to me to qualify as a FAQ entry. Somewhere in the admin guide seems more appropriate. Perhaps under Client Authentication? Maybe someone could even put together enough material to create a whole chapter on security considerations --- this is hardly the only item worthy of mention. regards, tom lane -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] pg_dump.options.diff
Hello, Happy New Year everyone, Attached is an attempt to eliminate duplicate pg_dump option descriptions, and have a single description for both short and long options. For me, as for a translator, this eliminates the need to maintain the two, exactly same, sets of 24 sentences. If this is accepted, using the same approach I'll go through pg_dumpall and other tools, which suffer from the same exact problem. If it's rejected, please advise of a better way to approach the problem. Needless to say, it is a pain for a translator to properly maintain the two sets of messages for every tool PG got. After all that is settled, I'll send translation patches in. Thank you, -s pg_dump.options.diff pg_dump.options.diff Description: Binary data ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Cast your vote ...
Just got this in my mailbox: 2002 LinuxQuestions.org Members Choice Awards: http://www.linuxquestions.org/questions/showthread.php?s=78a8c06fbc1dcecd52597decd6c56ad8threadid=39870 And we are way behind MySQL (like, d'ah!): http://www.linuxquestions.org/questions/poll.php?s=78a8c06fbc1dcecd52597decd6c56ad8action=showresultspollid=168 The problem, of course, is that you have to be a registered member to vote, so its not an 'open poll' ... ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] pg_dump.options.diff
Serguei Mokhov [EMAIL PROTECTED] writes: Attached is an attempt to eliminate duplicate pg_dump option descriptions, and have a single description for both short and long options. For me, as for a translator, this eliminates the need to maintain the two, exactly same, sets of 24 sentences. Offhand, this cure strikes me as much worse than the disease. You've converted code which was understandable, if somewhat repetitious, into code that can be understood by neither programmers nor translators. The text strings have been broken into fragments that don't make any sense individually --- which probably creates translating problems, as well as opportunities for programmer error. I see your complaint, but this doesn't seem like a good way to fix it. Perhaps it would work better to do something like #ifdef HAVE_GETOPT_LONG char* f_option = _(-f, --file=FILENAME ); ... etc ... #else /* not HAVE_GETOPT_LONG */ char* f_option = _(-f FILENAME ); ... etc ... #endif /* not HAVE_GETOPT_LONG */ printf(_( %s output file name\n), f_option); ... etc ... That seems to reduce the amount of duplication without breaking things up into chunks that aren't independent concepts. However, I'm not convinced that the above is better than what we have --- it's really not obvious that the above is more maintainable than #ifdef HAVE_GETOPT_LONG printf(_( -f, --file=FILENAME output file name\n)); #else /* not HAVE_GETOPT_LONG */ printf(_( -f FILENAME output file name\n)); #endif /* not HAVE_GETOPT_LONG */ There are worse things than a little repetitiveness, and creating the opportunity to mismatch a flag with its description may well be one of them. Comments? Can anyone do better? regards, tom lane ---(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: [HACKERS] Cast your vote ...
Hmmm...I wonder if this will mean we'll be accused of ballot stuffing in the MySQL lists again... It is a newbie forum, so I'm not surprised that MySQL is way ahead. The poll is a bit of a joke anyway. The poll needs to have two questions: 1. Which of these databases have you ACTUALLY USED? 2. Which of these databases is your favourite? That'll sort it out. Chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Marc G. Fournier Sent: Thursday, 2 January 2003 2:25 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: [HACKERS] Cast your vote ... Just got this in my mailbox: 2002 LinuxQuestions.org Members Choice Awards: http://www.linuxquestions.org/questions/showthread.php?s=78a8c06fb c1dcecd52597decd6c56ad8threadid=39870 And we are way behind MySQL (like, d'ah!): http://www.linuxquestions.org/questions/poll.php?s=78a8c06fbc1dcec d52597decd6c56ad8action=showresultspollid=168 The problem, of course, is that you have to be a registered member to vote, so its not an 'open poll' ... ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] pg_dump.options.diff
- Original Message - From: Tom Lane [EMAIL PROTECTED] Sent: January 02, 2003 1:34 AM Perhaps it would work better to do something like #ifdef HAVE_GETOPT_LONG char* f_option = _(-f, --file=FILENAME ); ... etc ... #else /* not HAVE_GETOPT_LONG */ char* f_option = _(-f FILENAME ); ... etc ... #endif /* not HAVE_GETOPT_LONG */ printf(_( %s output file name\n), f_option); ... etc ... That seems to reduce the amount of duplication without breaking things up into chunks that aren't independent concepts. Thank you for your comment, Tom. A slightly more readable version of the above could be: #ifdef HAVE_GETOPT_LONG char* data_only_option = _(-f, --file=FILENAME ); char* blobs_option = _(-b, --blobs ); ... etc ... #else /* not HAVE_GETOPT_LONG */ char* data_only_option = _(-f FILENAME ); char* blobs_option = _(-b ); ... etc ... #endif /* not HAVE_GETOPT_LONG */ printf(_( %s output file name\n), data_only_option); printf(_( %s include large objects in dump\n), blobs_option); ... etc ... It loos like better than the current setup. Either way, something has to be donw about this... -s ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
