postgresql-socket md5 auth
Jason Melbye <[email protected]> Tue, 11 Aug 2015 23:37:31 -0500
| Newsgroups | gmane.lisp.clsql.general |
|---|---|
| Message-ID | <CAEBpdtmmWvYT1w3EqbdLepYFKXM3Q+kPsEHRSTk9fv9XBzHdqw@mail.gmail.com> |
--089e011771a9e704ca051d15c688
Content-Type: multipart/alternative; boundary=089e011771a9e704c5051d15c686
--089e011771a9e704c5051d15c686
Content-Type: text/plain; charset=UTF-8
Hello,
I recently started playing with clsql. I was unable to connect to a
postgres database using md5 auth and the postgresql-socket backend.
I'm aware that there is a postgresql-socket3 backend, which works for me,
but I took a look and think I see the issue with postgresql-socket.
The issue appears to with how md5sum-string converts from a string to
bytes. In the backend, the 4-byte salt is converted to a string and used
with md5sum-string. md5sum-string appears to convert the string back into
bytes, but may insert additional unicode bytes, causing the hash to not
match what the database is expecting.
The following example illustrates the issue:
; This is how a 4 byte sequence would hash:
CL-USER> (md5:md5sum-sequence (map '(vector (unsigned-byte 8)) #'identity
'(187 112 167 74)))
#(217 139 160 179 42 115 72 240 130 71 253 219 50 17 29 151)
; Convert the salt to a string and hash - not the same result
CL-USER> (md5:md5sum-string (map 'string #'code-char '(187 112 167 74)))
#(244 107 54 239 235 17 167 205 4 178 45 142 88 127 81 237)
; Convert the salt to a string, then back to bytes assuming unicode:
CL-USER> (trivial-utf-8:string-to-utf-8-bytes (map 'string #'code-char
'(187 112 167 74)))
#(194 187 112 194 167 74)
; This is actually what md5sum-string is doing:
CL-USER> (md5:md5sum-sequence (map '(vector (unsigned-byte 8))
#'identity '(194 187 112 194 167 74)))
#(244 107 54 239 235 17 167 205 4 178 45 142 88 127 81 237)
Below is a patch that works for me locally. For the second "pass" of
the md5 hashing, I keep the salt as bytes and use md5sum-sequence
instead of -string.
Jason
--- postgresql-socket-api.lisp 2015-08-11 23:08:27.882999506 -0500
+++ postgresql-socket-api.lisp.original 2015-08-11 22:26:44.145820416 -0500
@@ -234,21 +234,10 @@
(int32 pid)
(int32 key))
-; Plucked from cl-postgres
-(defun read-bytes (socket length)
- "Read a byte array of the given length from a stream."
- ;(declare (type stream socket)
- ; (type fixnum length)
- ; #.*optimize*)
- (let ((result (make-array length :element-type '(unsigned-byte 8))))
- (read-sequence result socket)
- result))
-
(defun read-socket-sequence (stream length &optional (allow-wide t))
- (break)
- ;(declare (stream stream)
- ; (optimize (speed 3) (safety 0)))
+ (declare (stream stream)
+ (optimize (speed 3) (safety 0)))
#-(or sb-unicode ccl)
(let ((result (make-string length)))
(dotimes (i length result)
@@ -265,8 +254,6 @@
(let ((bytes (make-array length :element-type '(unsigned-byte 8))))
(declare (type (simple-array (unsigned-byte 8) (*)) bytes))
(read-sequence bytes stream)
- (format t "allow-wide: ~a~%" allow-wide)
- (format t "bytes: ~a~%" bytes)
(if allow-wide
(sb-ext:octets-to-string bytes)
(map 'string #'code-char bytes))))
@@ -500,21 +487,10 @@
:database database :user user
:password (or password ""))))
-;(defun encrypt-md5 (plaintext salt)
-; (string-downcase
-; (format nil "~{~2,'0X~}"
-; (coerce (md5sum-string (concatenate 'string plaintext
salt)) 'list))))
-
-(defun byte-sequence-to-hex-string (sequence)
- (string-downcase (format nil "~{~2,'0X~}" (coerce sequence 'list))))
-
-(defun encrypt-password-md5 (password user salt)
- (let ((pass1 (byte-sequence-to-hex-string
- (md5sum-string (concatenate 'string password user)))))
- (byte-sequence-to-hex-string
- (md5sum-sequence (concatenate '(vector (unsigned-byte 8))
- (map '(vector (unsigned-byte 8)) #'char-code pass1)
- salt)))))
+(defun encrypt-md5 (plaintext salt)
+ (string-downcase
+ (format nil "~{~2,'0X~}"
+ (coerce (md5sum-string (concatenate 'string plaintext
salt)) 'list))))
(defun reopen-postgresql-connection (connection)
"Reopen the given PostgreSQL connection. Closes any existing
@@ -556,11 +532,11 @@
(postgresql-connection-password connection) salt)))
(force-output socket))
(5
- (let ((salt (read-bytes socket 4)))
- (let ((pwd (encrypt-password-md5
- (postgresql-connection-password connection)
- (postgresql-connection-user connection)
- salt)))
+ (break)
+ (let ((salt (read-socket-sequence socket 4 nil)))
+ (let* ((pwd2 (encrypt-md5
(postgresql-connection-password connection)
+
(postgresql-connection-user connection)))
+ (pwd (encrypt-md5 pwd2 salt)))
(send-encrypted-password-message
socket
(concatenate 'string "md5" pwd))))
--089e011771a9e704c5051d15c686
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
<div dir=3D"ltr"><div><div><div><div><span style=3D"font-family:arial,helve=
tica,sans-serif">Hello,<br><br></span></div><span style=3D"font-family:aria=
l,helvetica,sans-serif">I recently started playing with clsql.=C2=A0 I was =
unable to connect to a postgres database using md5 auth and the postgresql-=
socket backend.<br><br></span></div><span style=3D"font-family:arial,helvet=
ica,sans-serif">I'm aware that there is a postgresql-socket3 backend, w=
hich works for me, but I took a look and think I see the issue with postgre=
sql-socket.<br><br>The issue appears to with how md5sum-string converts fro=
m a string to bytes.=C2=A0 In the backend, the 4-byte salt is converted to =
a string and used with md5sum-string.=C2=A0 md5sum-string appears to conver=
t the string back into bytes, but may insert additional unicode bytes, caus=
ing the hash to not match what the database is expecting.<br><br></span></d=
iv><span style=3D"font-family:arial,helvetica,sans-serif">The following exa=
mple illustrates the issue:<br><br></span></div><span style=3D"font-family:=
arial,helvetica,sans-serif">; This is how a 4 byte sequence would hash:</sp=
an><br>CL-USER> (md5:md5sum-sequence (map '(vector (unsigned-byte 8)=
) #'identity '(187 112 167 74)))<br>#(217 139 160 179 42 115 72 240=
130 71 253 219 50 17 29 151)<br><div><pre class=3D"paste-area"><span style=
=3D"font-family:arial,helvetica,sans-serif">; Convert the salt to a string =
and hash - not the same result<br>CL-USER> (md5:md5sum-string (map '=
string #'code-char '(187 112 167 74)))
#(244 107 54 239 235 17 167 205 4 178 45 142 88 127 81 237)<br><br>; Conver=
t the salt to a string, then back to bytes assuming unicode:<br>CL-USER>=
(trivial-utf-8:string-to-utf-8-bytes (map 'string #'code-char '=
;(187 112 167 74)))<br>#(194 187 112 194 167 74)<br><br>; This is actually =
what md5sum-string is doing:<br>CL-USER> (md5:md5sum-sequence (map '=
(vector (unsigned-byte 8)) #'identity '(194 187 112 194 167 74)))
#(244 107 54 239 235 17 167 205 4 178 45 142 88 127 81 237)<br><br>Below is=
a patch that works for me locally. For the second "pass" of the=
md5 hashing, I keep the salt as bytes and use md5sum-sequence instead of -=
string.<br><br>Jason<br><br><br>--- postgresql-socket-api.lisp 2015-08-11 2=
3:08:27.882999506 -0500<br>+++ postgresql-socket-api.lisp.original 2015-08-=
11 22:26:44.145820416 -0500<br>@@ -234,21 +234,10 @@<br> (int32 pid)<br> =
(int32 key))<br> <br>-; Plucked from cl-postgres<br>-(defun read-bytes (s=
ocket length)<br>- "Read a byte array of the given length from a stre=
am."<br>- ;(declare (type stream socket)<br>- ; (type fixnum=
length)<br>- ; #.*optimize*)<br>- (let ((result (make-array leng=
th :element-type '(unsigned-byte 8))))<br>- (read-sequence result so=
cket)<br>- result))<br>-<br> <br> (defun read-socket-sequence (stream le=
ngth &optional (allow-wide t))<br>- (break)<br>- ;(declare (stream st=
ream)<br>- ; (optimize (speed 3) (safety 0)))<br>+ (declare (stre=
am stream)<br>+ (optimize (speed 3) (safety 0)))<br> #-(or sb-u=
nicode ccl)<br> (let ((result (make-string length)))<br> (dotimes (i =
length result)<br>@@ -265,8 +254,6 @@<br> (let ((bytes (make-array length=
:element-type '(unsigned-byte 8))))<br> (declare (type (simple-arr=
ay (unsigned-byte 8) (*)) bytes))<br> (read-sequence bytes stream)<br>-=
(format t "allow-wide: ~a~%" allow-wide)<br>- (format t &q=
uot;bytes: ~a~%" bytes)<br> (if allow-wide<br> (sb-ext:oct=
ets-to-string bytes)<br> (map 'string #'code-char bytes))))=
<br>@@ -500,21 +487,10 @@<br> :database data=
base :user user<br> :password (or password &=
quot;"))))<br> <br>-;(defun encrypt-md5 (plaintext salt)<br>-; (strin=
g-downcase<br>-; (format nil "~{~2,'0X~}"<br>-; (=
coerce (md5sum-string (concatenate 'string plaintext salt)) 'list))=
))<br>-<br>-(defun byte-sequence-to-hex-string (sequence)<br>- (string-dow=
ncase (format nil "~{~2,'0X~}" (coerce sequence 'list))))=
<br>-<br>-(defun encrypt-password-md5 (password user salt)<br>- (let ((pas=
s1 (byte-sequence-to-hex-string<br>- (md5sum-string (concatenate 'stri=
ng password user)))))<br>- (byte-sequence-to-hex-string<br>- (md5sum=
-sequence (concatenate '(vector (unsigned-byte 8))<br>- (map '=
;(vector (unsigned-byte 8)) #'char-code pass1)<br>- salt)))))<br>=
+(defun encrypt-md5 (plaintext salt)<br>+ (string-downcase<br>+ (format =
nil "~{~2,'0X~}"<br>+ (coerce (md5sum-string (conca=
tenate 'string plaintext salt)) 'list))))<br> <br> (defun reopen-po=
stgresql-connection (connection)<br> "Reopen the given PostgreSQL co=
nnection. Closes any existing<br>@@ -556,11 +532,11 @@<br> =
(postgresql-connection-password connection) salt)))<br> =
(force-output socket))<br> (5<br>- =
(let ((salt (read-bytes socket 4)))<br>- =
(let ((pwd (encrypt-password-md5<br>- (postgresql-connection-password=
connection)<br>- (postgresql-connection-user connection)<br>- =
salt)))<br>+ (break)<br>+ (let ((salt (read-socke=
t-sequence socket 4 nil)))<br>+ (let* ((pwd2 (encrypt=
-md5 (postgresql-connection-password connection)<br>+ =
(postgresql-connection-user connection)))<br>+ =
(pwd (encrypt-md5 pwd2 salt)))<br> =
(send-encrypted-password-message<br> =
socket<br> (concatenate 'string "md5&qu=
ot; pwd))))<br></span></pre><span style=3D"font-family:arial,helvetica,sans=
-serif"><br></span></div></div>
--089e011771a9e704c5051d15c686--
--089e011771a9e704ca051d15c688
Content-Type: text/plain; charset=US-ASCII; name="postgresql-socket-api.diff"
Content-Disposition: attachment; filename="postgresql-socket-api.diff"
Content-Transfer-Encoding: base64
X-Attachment-Id: f_id89vuor0
LS0tIHBvc3RncmVzcWwtc29ja2V0LWFwaS5saXNwCTIwMTUtMDgtMTEgMjM6MDg6MjcuODgyOTk5
NTA2IC0wNTAwCisrKyBwb3N0Z3Jlc3FsLXNvY2tldC1hcGkubGlzcC5vcmlnaW5hbAkyMDE1LTA4
LTExIDIyOjI2OjQ0LjE0NTgyMDQxNiAtMDUwMApAQCAtMjM0LDIxICsyMzQsMTAgQEAKICAgKGlu
dDMyIHBpZCkKICAgKGludDMyIGtleSkpCiAKLTsgUGx1Y2tlZCBmcm9tIGNsLXBvc3RncmVzCi0o
ZGVmdW4gcmVhZC1ieXRlcyAoc29ja2V0IGxlbmd0aCkKLSAgIlJlYWQgYSBieXRlIGFycmF5IG9m
IHRoZSBnaXZlbiBsZW5ndGggZnJvbSBhIHN0cmVhbS4iCi0gIDsoZGVjbGFyZSAodHlwZSBzdHJl
YW0gc29ja2V0KQotICA7ICAgICAgICAgKHR5cGUgZml4bnVtIGxlbmd0aCkKLSAgOyAgICAgICAg
ICMuKm9wdGltaXplKikKLSAgKGxldCAoKHJlc3VsdCAobWFrZS1hcnJheSBsZW5ndGggOmVsZW1l
bnQtdHlwZSAnKHVuc2lnbmVkLWJ5dGUgOCkpKSkKLSAgICAocmVhZC1zZXF1ZW5jZSByZXN1bHQg
c29ja2V0KQotICAgIHJlc3VsdCkpCi0KIAogKGRlZnVuIHJlYWQtc29ja2V0LXNlcXVlbmNlIChz
dHJlYW0gbGVuZ3RoICZvcHRpb25hbCAoYWxsb3ctd2lkZSB0KSkKLSAgKGJyZWFrKQotICA7KGRl
Y2xhcmUgKHN0cmVhbSBzdHJlYW0pCi0gIDsgICAgICAgICAob3B0aW1pemUgKHNwZWVkIDMpIChz
YWZldHkgMCkpKQorICAoZGVjbGFyZSAoc3RyZWFtIHN0cmVhbSkKKyAgICAgICAgICAgKG9wdGlt
aXplIChzcGVlZCAzKSAoc2FmZXR5IDApKSkKICAgIy0ob3Igc2ItdW5pY29kZSBjY2wpCiAgIChs
ZXQgKChyZXN1bHQgKG1ha2Utc3RyaW5nIGxlbmd0aCkpKQogICAgIChkb3RpbWVzIChpIGxlbmd0
aCByZXN1bHQpCkBAIC0yNjUsOCArMjU0LDYgQEAKICAgKGxldCAoKGJ5dGVzIChtYWtlLWFycmF5
IGxlbmd0aCA6ZWxlbWVudC10eXBlICcodW5zaWduZWQtYnl0ZSA4KSkpKQogICAgIChkZWNsYXJl
ICh0eXBlIChzaW1wbGUtYXJyYXkgKHVuc2lnbmVkLWJ5dGUgOCkgKCopKSBieXRlcykpCiAgICAg
KHJlYWQtc2VxdWVuY2UgYnl0ZXMgc3RyZWFtKQotICAgIChmb3JtYXQgdCAiYWxsb3ctd2lkZTog
fmF+JSIgYWxsb3ctd2lkZSkKLSAgICAoZm9ybWF0IHQgImJ5dGVzOiB+YX4lIiBieXRlcykKICAg
ICAoaWYgYWxsb3ctd2lkZQogICAgICAgICAoc2ItZXh0Om9jdGV0cy10by1zdHJpbmcgYnl0ZXMp
CiAgICAgICAgIChtYXAgJ3N0cmluZyAjJ2NvZGUtY2hhciBieXRlcykpKSkKQEAgLTUwMCwyMSAr
NDg3LDEwIEBACiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgOmRhdGFiYXNlIGRhdGFi
YXNlIDp1c2VyIHVzZXIKICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICA6cGFzc3dvcmQg
KG9yIHBhc3N3b3JkICIiKSkpKQogCi07KGRlZnVuIGVuY3J5cHQtbWQ1IChwbGFpbnRleHQgc2Fs
dCkKLTsgIChzdHJpbmctZG93bmNhc2UKLTsgICAoZm9ybWF0IG5pbCAifnt+MiwnMFh+fSIKLTsg
ICAgICAgICAgIChjb2VyY2UgKG1kNXN1bS1zdHJpbmcgKGNvbmNhdGVuYXRlICdzdHJpbmcgcGxh
aW50ZXh0IHNhbHQpKSAnbGlzdCkpKSkKLQotKGRlZnVuIGJ5dGUtc2VxdWVuY2UtdG8taGV4LXN0
cmluZyAoc2VxdWVuY2UpCi0gIChzdHJpbmctZG93bmNhc2UgKGZvcm1hdCBuaWwgIn57fjIsJzBY
fn0iIChjb2VyY2Ugc2VxdWVuY2UgJ2xpc3QpKSkpCi0KLShkZWZ1biBlbmNyeXB0LXBhc3N3b3Jk
LW1kNSAocGFzc3dvcmQgdXNlciBzYWx0KQotICAobGV0ICgocGFzczEgKGJ5dGUtc2VxdWVuY2Ut
dG8taGV4LXN0cmluZwotCQkobWQ1c3VtLXN0cmluZyAoY29uY2F0ZW5hdGUgJ3N0cmluZyBwYXNz
d29yZCB1c2VyKSkpKSkKLSAgICAoYnl0ZS1zZXF1ZW5jZS10by1oZXgtc3RyaW5nCi0gICAgICht
ZDVzdW0tc2VxdWVuY2UgKGNvbmNhdGVuYXRlICcodmVjdG9yICh1bnNpZ25lZC1ieXRlIDgpKQot
CQkJCSAgIChtYXAgJyh2ZWN0b3IgKHVuc2lnbmVkLWJ5dGUgOCkpICMnY2hhci1jb2RlIHBhc3Mx
KQotCQkJCSAgIHNhbHQpKSkpKQorKGRlZnVuIGVuY3J5cHQtbWQ1IChwbGFpbnRleHQgc2FsdCkK
KyAgKHN0cmluZy1kb3duY2FzZQorICAgKGZvcm1hdCBuaWwgIn57fjIsJzBYfn0iCisgICAgICAg
ICAgIChjb2VyY2UgKG1kNXN1bS1zdHJpbmcgKGNvbmNhdGVuYXRlICdzdHJpbmcgcGxhaW50ZXh0
IHNhbHQpKSAnbGlzdCkpKSkKIAogKGRlZnVuIHJlb3Blbi1wb3N0Z3Jlc3FsLWNvbm5lY3Rpb24g
KGNvbm5lY3Rpb24pCiAgICJSZW9wZW4gdGhlIGdpdmVuIFBvc3RncmVTUUwgY29ubmVjdGlvbi4g
IENsb3NlcyBhbnkgZXhpc3RpbmcKQEAgLTU1NiwxMSArNTMyLDExIEBACiAgICAgICAgICAgICAg
ICAgICAgICAgICAgKHBvc3RncmVzcWwtY29ubmVjdGlvbi1wYXNzd29yZCBjb25uZWN0aW9uKSBz
YWx0KSkpCiAgICAgICAgICAgICAgICAgICAgICAoZm9yY2Utb3V0cHV0IHNvY2tldCkpCiAgICAg
ICAgICAgICAgICAgICAgICg1Ci0gICAgICAgICAgICAgICAgICAgICAobGV0ICgoc2FsdCAocmVh
ZC1ieXRlcyBzb2NrZXQgNCkpKQotICAgICAgICAgICAgICAgICAgICAgICAobGV0ICgocHdkIChl
bmNyeXB0LXBhc3N3b3JkLW1kNQotCQkJCSAgIChwb3N0Z3Jlc3FsLWNvbm5lY3Rpb24tcGFzc3dv
cmQgY29ubmVjdGlvbikKLQkJCQkgICAocG9zdGdyZXNxbC1jb25uZWN0aW9uLXVzZXIgY29ubmVj
dGlvbikKLQkJCQkgICBzYWx0KSkpCisJCSAgICAgKGJyZWFrKQorICAgICAgICAgICAgICAgICAg
ICAgKGxldCAoKHNhbHQgKHJlYWQtc29ja2V0LXNlcXVlbmNlIHNvY2tldCA0IG5pbCkpKQorICAg
ICAgICAgICAgICAgICAgICAgICAobGV0KiAoKHB3ZDIgKGVuY3J5cHQtbWQ1IChwb3N0Z3Jlc3Fs
LWNvbm5lY3Rpb24tcGFzc3dvcmQgY29ubmVjdGlvbikKKyAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAocG9zdGdyZXNxbC1jb25uZWN0aW9uLXVzZXIgY29u
bmVjdGlvbikpKQorICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgKHB3ZCAoZW5jcnlwdC1t
ZDUgcHdkMiBzYWx0KSkpCiAgICAgICAgICAgICAgICAgICAgICAgICAgKHNlbmQtZW5jcnlwdGVk
LXBhc3N3b3JkLW1lc3NhZ2UKICAgICAgICAgICAgICAgICAgICAgICAgICAgc29ja2V0CiAgICAg
ICAgICAgICAgICAgICAgICAgICAgIChjb25jYXRlbmF0ZSAnc3RyaW5nICJtZDUiIHB3ZCkpKSkK
--089e011771a9e704ca051d15c688
Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: base64
Content-Disposition: inline
X19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX18KQ0xTUUwgbWFp
bGluZyBsaXN0CkNMU1FMQGI5LmNvbQpodHRwOi8vbGlzdHMuYjkuY29tL2NnaS1iaW4vbWFpbG1h
bi9saXN0aW5mby9jbHNxbAo=
--089e011771a9e704ca051d15c688--