The following bug has been logged online:

Bug reference: 1905
Logged by: Trevor Tingey
Email address: ttingey (AT) gmail (DOT) com
PostgreSQL version: 8.1 beta 2
Operating system: Windows XP Home Edition SP 2
Description: When a column with the chkpass datatype is used in a
where clause it returns invalid results
Details:

My schema is as follows:

CREATE TABLE account
(
"AccountId" int4 NOT NULL DEFAULT
nextval('public."account_AccountId_seq"'::text),
"AccountName" varchar(255) NOT NULL,
"Password" chkpass,
"Description" varchar(255),
CONSTRAINT account_pkey PRIMARY KEY ("AccountId"),
CONSTRAINT "account_Username_key" UNIQUE ("AccountName")
)
WITHOUT OIDS;


I ran the following queries and got unexpected results:

UPDATE Account
SET "Password" = 'password1'
WHERE "AccountName" = 'Trevor'

SELECT *
FROM Account
WHERE "AccountName" = 'Trevor'
AND "Password" = 'password2'

(I ran them individually)

The Select query unexpectedly returned a result. I also added another digit
on the end of 'password2' i.e. 'password21' and that returned the same
result. I also changed it to 'password3' etc. and got the same result and
the same with 'password'. When I changed it to 'passwor1' it did not return
a result.

Running SELECT * FROM Account returns two rows, one with the accountname of
'administrator' and the other 'Trevor'.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

"Trevor Tingey" <ttingey (AT) gmail (DOT) com> writes:
This is not a bug --- chkpass limits passwords to 8 significant
characters, so the above two values are in fact equal so far as
the datatype is concerned.

(The documentation probably ought to point that out...)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org