Horton

Thread

  1. Re: Inserting data of two other tables [Now deleting ...]

    papapep <papapep@gmx.net> — 2003-05-28T16:59:08Z

    Well, at last I've been able (with some help ;-D) to do it.

The query has been:

SELECT tarneto FROM detalltrajectes d WHERE d.journey = (SELECT c.pkey 
FROM CAPTRAJECTES WHERE c.pkey = d.journey AND fecha = 20030423);

With this one I've been able to "see" what was I going to remove, and 
with this one:

DELETE FROM DETALLTRAJECTES WHERE journey = (SELECT pkey FROM 
CAPTRAJECTES WHERE pkey = journey AND fecha = 20030423);

I have removed the rows. I have to mention that I first tried to "clone" 
the query, changing SELECT tarneto for DELETE obviously, but it didn't 
work. The parser said:

	ERROR:  parser: parse error at or near "d"

So what I did was to remove all the alias and it worked.... is it normal??

Thanks to all for your help, and specially to Nabil.

Josep Sànchez
   [papapep]

  2. Re: Inserting data of two other tables [Now deleting ...]

    Nabil Sayegh <postgresql@e-trolley.de> — 2003-05-28T19:47:10Z

    Am Mit, 2003-05-28 um 18.59 schrieb papapep:

> So what I did was to remove all the alias and it worked.... is it normal??

Yes, it has some problems with aliases.

> Thanks to all for your help, and specially to Nabil.

np
-- 
 e-Trolley Sayegh & John, Nabil Sayegh
 Tel.: 0700 etrolley /// 0700 38765539
 Fax.: +49 69 8299381-8
 PGP : http://www.e-trolley.de

  3. MD5 salt

    M. Bastin <marcbastin@mindspring.com> — 2003-05-29T11:10:53Z

    How do I send an MD5 password to pgsql?  (I'm programming my own front-end)

Pgsql provides a 4-byte 'salt', that you must somehow use with your 
password for MD5.  The trouble is, I don't know how.

I have been trying sending the MD5 digest from (password + salt), or 
from (salt + password), but it doesn't work.  How exactly does the 
salt fit in the picture?

Thanks,

Marc

  4. Re: MD5 salt

    Tom Lane <tgl@sss.pgh.pa.us> — 2003-05-29T14:04:50Z

    "M. Bastin" <marcbastin@mindspring.com> writes:
> How do I send an MD5 password to pgsql?  (I'm programming my own front-end)
> Pgsql provides a 4-byte 'salt', that you must somehow use with your 
> password for MD5.  The trouble is, I don't know how.

Step 1: compute 32-byte MD5 checksum of cleartext password concatenated
with username.  (BTW this checksum, with "md5" on the front, is what is
actually stored in pg_shadow.)

Step 2: compute 32-byte MD5 checksum of the 32-byte result of step 1
concatenated with the 4-byte salt from the server.  Stick "md5" on the
front and send it to the server.

			regards, tom lane

  5. Re: MD5 salt

    M. Bastin <marcbastin@mindspring.com> — 2003-05-29T14:49:59Z

    Thanks Tom, You're my hero!

However I must be doing something wrong.  This is what I do:

"md5" + MD5( MD5(Password + UserName) + Salt)

Is this a correct interpretation of your explanation?  (To this I 
still need to add the zero byte for termination, isn't it?  That's 
what I'm doing now anyway.)


>"M. Bastin" <marcbastin@mindspring.com> writes:
>>  How do I send an MD5 password to pgsql?  (I'm programming my own front-end)
>>  Pgsql provides a 4-byte 'salt', that you must somehow use with your
>>  password for MD5.  The trouble is, I don't know how.
>
>Step 1: compute 32-byte MD5 checksum of cleartext password concatenated
>with username.  (BTW this checksum, with "md5" on the front, is what is
>actually stored in pg_shadow.)
>
>Step 2: compute 32-byte MD5 checksum of the 32-byte result of step 1
>concatenated with the 4-byte salt from the server.  Stick "md5" on the
>front and send it to the server.
>
>			regards, tom lane

  6. Re: MD5 salt

    Tom Lane <tgl@sss.pgh.pa.us> — 2003-05-29T15:06:26Z

    "M. Bastin" <marcbastin@mindspring.com> writes:
> However I must be doing something wrong.  This is what I do:
> "md5" + MD5( MD5(Password + UserName) + Salt)
> Is this a correct interpretation of your explanation?

Looks right to me.  Do you have the MD5 algorithm correct?

> (To this I 
> still need to add the zero byte for termination, isn't it?

Yeah, IIRC the contents of the Password message are a zero-terminated
string.  Check the protocol document.

You might try testing with plain-text password auth method to make sure
you have the basic Password-message mechanics down, before you go on
with MD5.

			regards, tom lane

  7. Re: MD5 salt

    M. Bastin <marcbastin@mindspring.com> — 2003-05-29T15:40:30Z

    At 11:06 AM -0400 5/29/03, Tom Lane wrote:
>"M. Bastin" <marcbastin@mindspring.com> writes:
>>  However I must be doing something wrong.  This is what I do:
>>  "md5" + MD5( MD5(Password + UserName) + Salt)
>>  Is this a correct interpretation of your explanation?
>
>Looks right to me.  Do you have the MD5 algorithm correct?

I'm using the one provided with my development tool.  Is there some 
way I could calculate a MD5 digest with a known good tool and compare 
it with my result?

>You might try testing with plain-text password auth method to make sure
>you have the basic Password-message mechanics down, before you go on
>with MD5.

Exactly, that's what I did and it works, so I'm pretty sure there 
must be something wrong with the MD5 algorithm I use, (or else I 
don't extract the salt properly out of the data stream but I'm quite 
sure I've got that covered).

...

Mmmm...  I've just done some testing and my MD5 function gives me 16 
bytes instead of 32.  I'll look into this.

  8. Re: MD5 salt

    Tom Lane <tgl@sss.pgh.pa.us> — 2003-05-29T15:48:02Z

    "M. Bastin" <marcbastin@mindspring.com> writes:
>> Looks right to me.  Do you have the MD5 algorithm correct?

> I'm using the one provided with my development tool.  Is there some 
> way I could calculate a MD5 digest with a known good tool and compare 
> it with my result?

Well, you could compute just MD5(Password + User) and compare that to
what's stored in pg_shadow.  Another possibility is to add some
debugging printouts to libpq and see what it computes (look at 
pg_password_sendauth() in src/interfaces/libpq/fe-auth.c).

It could be something silly like including trailing nulls into what's
processed by MD5 --- I'm pretty sure you should *not* do that, for
either password or user name.

			regards, tom lane

  9. Re: MD5 salt

    Joe Conway <mail@joeconway.com> — 2003-05-29T16:40:10Z

    Tom Lane wrote:
> "M. Bastin" <marcbastin@mindspring.com> writes:
>>I'm using the one provided with my development tool.  Is there some 
>>way I could calculate a MD5 digest with a known good tool and compare 
>>it with my result?
> 
> Well, you could compute just MD5(Password + User) and compare that to
> what's stored in pg_shadow.  Another possibility is to add some
> debugging printouts to libpq and see what it computes (look at 
> pg_password_sendauth() in src/interfaces/libpq/fe-auth.c).
> 

You can find test vectors for MD5 here:
ftp://ftp.rfc-editor.org/in-notes/rfc1321.txt

See near the bottom, A.5 Test suite. For convenience, here is that section:

A.5 Test suite

    The MD5 test suite (driver option "-x") should print the following
    results:

MD5 test suite:
MD5 ("") = d41d8cd98f00b204e9800998ecf8427e
MD5 ("a") = 0cc175b9c0f1b6a831c399e269772661
MD5 ("abc") = 900150983cd24fb0d6963f7d28e17f72
MD5 ("message digest") = f96b697d7cb7938d525a2f31aaf161d0
MD5 ("abcdefghijklmnopqrstuvwxyz") = c3fcd3d76192e4007dfb496cca67e13b
MD5 ("ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789") =
d174ab98d277d9f5a5611c2c9f419d9f
MD5 ("123456789012345678901234567890123456789012345678901234567890123456
78901234567890") = 57edf4a22be3c955ac49da2e2107b67a

Note that there is no '\n' in that last line-wrapped example. If you can 
produce hashes matching these, you should be OK.

Joe

  10. MD5 different standards?

    M. Bastin <marcbastin@mindspring.com> — 2003-05-29T17:24:19Z

    Are there different MD5 standards?

The rfc (http://www.faqs.org/rfcs/rfc1321.html) says:

The algorithm takes as input a message of arbitrary length and 
produces as output a 128-bit [= 16 bytes] "fingerprint" or "message 
digest" of the input.

My IDE's MD5 function produces 16 bytes.  Yet for pgsql MD5 should 
produce 32 bytes.

Can I find a human language description of this 32 bytes MD5 
somewhere, so that I can implement it myself.  I'm not good at 
reading C.  (Where did you guys get your info to implement it for 
pgsql?)

Thanks,

Marc

  11. Re: MD5 different standards?

    Joe Conway <mail@joeconway.com> — 2003-05-29T17:30:53Z

    M. Bastin wrote:
> Are there different MD5 standards?
> 
> The rfc (http://www.faqs.org/rfcs/rfc1321.html) says:
> 
> The algorithm takes as input a message of arbitrary length and produces 
> as output a 128-bit [= 16 bytes] "fingerprint" or "message digest" of 
> the input.
> 
> My IDE's MD5 function produces 16 bytes.  Yet for pgsql MD5 should 
> produce 32 bytes.

16 binary bytes == 32 bytes in hex

You need to convert IDE's MD5 function output to hex.

Joe

  12. MD5 done. Thanks!

    M. Bastin <marcbastin@mindspring.com> — 2003-05-29T21:07:26Z

    >You need to convert IDE's MD5 function output to hex.

Thank you for this epiphany!

I had a few other minor hurdles to take (padding leading zeros and 
converting to lowercase, while converting to hex), but now I can log 
in using MD5 too.

You guys made my day!  Thanks again for your support,

Marc

  13. Re: MD5 done. Thanks!

    ghaverla@freenet.edmonton.ab.ca — 2003-05-29T23:20:22Z

    On Thu, 29 May 2003, M. Bastin wrote:

> >You need to convert IDE's MD5 function output to hex.
> 
> Thank you for this epiphany!
> 
> I had a few other minor hurdles to take (padding leading zeros and 
> converting to lowercase, while converting to hex), but now I can log 
> in using MD5 too.
> 
> You guys made my day!  Thanks again for your support,

Not that I need it, but perhaps you could write up what you
did, with sample code, and submit it to be included in the
docs?

Gord