PostgreSQL采用MD5密码认证时密码和pg_authid里rolpassword的关系


一、PostgreSQL用户密码以MD5方式加密

PostgreSQL里用户创建密码后,是把密码按照SCRAM-SHA-256或MD5等算法处理后的值写入数据库,等到用户登陆的时候,再把输入的密码进行相应的算法处理,把两个值进行比较来判断是否密码输入正确,具体如下:

客户端收到输入的密码后,会使用密码和用户做拼接,把用户作为salt,然后对拼接好的字符串做md5。即md5(密码+用户(salt))。然后把这个值加上md5前缀与存储在pg_authid里的rolpassword做比较,如果一致,则通过认证,如果不一致,则报密码错误。

MD5算法属于单向散列算法,无法通过反推获得原始输入数据,但是MD5不算严格意义上的加密算法,可用暴力穷举法破解。SCRAM-SHA-256生成的密文长度为256位,MD5生成的密文长度为128位。SCRAM-SHA-256算法的碰撞概率比MD5更小,因为SCRAM-SHA-256使用了更复杂的哈希算法和更长的输出长度。


二、测试验证使用md5认证时密码和pg_authid里rolpassword的关系

//session1

postgres=# show password_encryption ;

password_encryption
---------------------

md5
(1 row)

一个session用gdb来Attach这个进程,然后在encrypt_password函数这里打上断点。

//session2

(gdb) b encrypt_password
Breakpoint 1 at 0xaaaabc4f7a40: file crypt.c, line 118.
(gdb) info b
Num Type Disp Enb Address What
1 breakpoint keep y 0x0000aaaabc4f7a40 in encrypt_password at crypt.c:118

然后原本的这个数据库连接里执行创建用户的操作

//session1
执行后处于一直卡着的状态
postgres=# create user ysla with password '1qaz!QAZ';

然后去gdb端查看堆栈

(gdb) c
Continuing.

Breakpoint 1, encrypt_password (target_type=PASSWORD_TYPE_MD5, role=0xaaaacccef658 "ysla", password=password@entry=0xaaaacccef670 "1qaz!QAZ") at crypt.c:118
118 {
(gdb) bt
#0 encrypt_password (target_type=PASSWORD_TYPE_MD5, role=0xaaaacccef658 "ysla", password=password@entry=0xaaaacccef670 "1qaz!QAZ") at crypt.c:118
#1 0x0000aaaabc48d160 in CreateRole (pstate=pstate@entry=0xaaaaccdd3038, stmt=stmt@entry=0xaaaacccef750) at user.c:446
#2 0x0000aaaabc68c420 in standard_ProcessUtility (pstmt=0xaaaacccef800, queryString=0xaaaaccceec28 "create user ysla with password '1qaz!QAZ';",
readOnlyTree=, context=PROCESS_UTILITY_TOPLEVEL, params=0x0, queryEnv=0x0, dest=0xaaaacccf0030, qc=0xffffc7625318) at utility.c:911
#3 0x0000ffffab246270 in pgss_ProcessUtility (pstmt=0xaaaacccef800, queryString=0xaaaaccceec28 "create user ysla with password '1qaz!QAZ';",
readOnlyTree=false, context=PROCESS_UTILITY_TOPLEVEL, params=0x0, queryEnv=0x0, dest=0xaaaacccf0030, qc=0xffffc7625318) at pg_stat_statements.c:1145
#4 0x0000aaaabc68a6cc in PortalRunUtility (portal=portal@entry=0xaaaaccd71f18, pstmt=pstmt@entry=0xaaaacccef800, isTopLevel=isTopLevel@entry=true,
setHoldSnapshot=setHoldSnapshot@entry=false, dest=dest@entry=0xaaaacccf0030, qc=qc@entry=0xffffc7625318) at pquery.c:1158
#5 0x0000aaaabc68a874 in PortalRunMulti (portal=portal@entry=0xaaaaccd71f18, isTopLevel=isTopLevel@entry=true,
setHoldSnapshot=setHoldSnapshot@entry=false, dest=dest@entry=0xaaaacccf0030, altdest=altdest@entry=0xaaaacccf0030, qc=qc@entry=0xffffc7625318)
at pquery.c:1315
#6 0x0000aaaabc68ae00 in PortalRun (portal=portal@entry=0xaaaaccd71f18, count=count@entry=9223372036854775807, isTopLevel=isTopLevel@entry=true,
run_once=run_once@entry=true, dest=dest@entry=0xaaaacccf0030, altdest=altdest@entry=0xaaaacccf0030, qc=qc@entry=0xffffc7625318) at pquery.c:791
#7 0x0000aaaabc686768 in exec_simple_query (query_string=query_string@entry=0xaaaaccceec28 "create user ysla with password '1qaz!QAZ';")
at postgres.c:1274
#8 0x0000aaaabc687648 in PostgresMain (dbname=, username=) at postgres.c:4637
#9 0x0000aaaabc5e0514 in BackendRun (port=0xaaaaccd23cd0, port=0xaaaaccd23cd0) at postmaster.c:4464
#10 BackendStartup (port=0xaaaaccd23cd0) at postmaster.c:4192
#11 ServerLoop () at postmaster.c:1782
#12 0x0000aaaabc5e165c in PostmasterMain (argc=argc@entry=1, argv=argv@entry=0xaaaaccc56d80) at postmaster.c:1466
#13 0x0000aaaabc298464 in main (argc=1, argv=0xaaaaccc56d80) at main.c:198

可以再加一个断点

(gdb) b pg_md5_encrypt
Breakpoint 2 at 0xaaaabc835a90: file md5_common.c, line 147.
(gdb) c
Continuing.

Breakpoint 2, pg_md5_encrypt (passwd=passwd@entry=0xaaaacccef670 "1qaz!QAZ", salt=salt@entry=0xaaaacccef658 "ysla", salt_len=4, buf=buf@entry=0xaaaaccdd32a8 "", errstr=errstr@entry=0xffffc7624ad0) at md5_common.c:147
147 size_t passwd_len = strlen(passwd);
(gdb) bt
#0 pg_md5_encrypt (passwd=passwd@entry=0xaaaacccef670 "1qaz!QAZ", salt=salt@entry=0xaaaacccef658 "ysla", salt_len=4, buf=buf@entry=0xaaaaccdd32a8 "",
errstr=errstr@entry=0xffffc7624ad0) at md5_common.c:147
#1 0x0000aaaabc4f7abc in encrypt_password (target_type=, role=0xaaaacccef658 "ysla", password=password@entry=0xaaaacccef670 "1qaz!QAZ")
at crypt.c:137
#2 0x0000aaaabc48d160 in CreateRole (pstate=pstate@entry=0xaaaaccdd3038, stmt=stmt@entry=0xaaaacccef750) at user.c:446
#3 0x0000aaaabc68c420 in standard_ProcessUtility (pstmt=0xaaaacccef800, queryString=0xaaaaccceec28 "create user ysla with password '1qaz!QAZ';",
readOnlyTree=, context=PROCESS_UTILITY_TOPLEVEL, params=0x0, queryEnv=0x0, dest=0xaaaacccf0030, qc=0xffffc7625318) at utility.c:911
#4 0x0000ffffab246270 in pgss_ProcessUtility (pstmt=0xaaaacccef800, queryString=0xaaaaccceec28 "create user ysla with password '1qaz!QAZ';",
readOnlyTree=false, context=PROCESS_UTILITY_TOPLEVEL, params=0x0, queryEnv=0x0, dest=0xaaaacccf0030, qc=0xffffc7625318) at pg_stat_statements.c:1145
#5 0x0000aaaabc68a6cc in PortalRunUtility (portal=portal@entry=0xaaaaccd71f18, pstmt=pstmt@entry=0xaaaacccef800, isTopLevel=isTopLevel@entry=true,
setHoldSnapshot=setHoldSnapshot@entry=false, dest=dest@entry=0xaaaacccf0030, qc=qc@entry=0xffffc7625318) at pquery.c:1158
#6 0x0000aaaabc68a874 in PortalRunMulti (portal=portal@entry=0xaaaaccd71f18, isTopLevel=isTopLevel@entry=true,
setHoldSnapshot=setHoldSnapshot@entry=false, dest=dest@entry=0xaaaacccf0030, altdest=altdest@entry=0xaaaacccf0030, qc=qc@entry=0xffffc7625318)
at pquery.c:1315
#7 0x0000aaaabc68ae00 in PortalRun (portal=portal@entry=0xaaaaccd71f18, count=count@entry=9223372036854775807, isTopLevel=isTopLevel@entry=true,
run_once=run_once@entry=true, dest=dest@entry=0xaaaacccf0030, altdest=altdest@entry=0xaaaacccf0030, qc=qc@entry=0xffffc7625318) at pquery.c:791
#8 0x0000aaaabc686768 in exec_simple_query (query_string=query_string@entry=0xaaaaccceec28 "create user ysla with password '1qaz!QAZ';")
at postgres.c:1274
#9 0x0000aaaabc687648 in PostgresMain (dbname=, username=) at postgres.c:4637
#10 0x0000aaaabc5e0514 in BackendRun (port=0xaaaaccd23cd0, port=0xaaaaccd23cd0) at postmaster.c:4464
#11 BackendStartup (port=0xaaaaccd23cd0) at postmaster.c:4192
#12 ServerLoop () at postmaster.c:1782
#13 0x0000aaaabc5e165c in PostmasterMain (argc=argc@entry=1, argv=argv@entry=0xaaaaccc56d80) at postmaster.c:1466
#14 0x0000aaaabc298464 in main (argc=1, argv=0xaaaaccc56d80) at main.c:198

可以往下走,可以看到这里把用户名当作salt和实际密码拼接在一起,然后根据这个做MD5的加密。

(gdb) n
150 char *crypt_buf = malloc(passwd_len + salt_len + 1);
(gdb) n
153 if (!crypt_buf)
(gdb) n
163 memcpy(crypt_buf, passwd, passwd_len);
(gdb) n
164 memcpy(crypt_buf + passwd_len, salt, salt_len);
(gdb) p crypt_buf
$2 = 0xaaaaccdf21b0 "1qaz!QAZ"
(gdb) p passwd_len
$3 = 8
(gdb) p salt
$4 = 0xaaaacccef658 "ysla"
(gdb) p salt_len
$5 = 4
(gdb) n
166 strcpy(buf, "md5");
(gdb) p crypt_buf
$6 = 0xaaaaccdf21b0 "1qaz!QAZysla"
(gdb) n
167 ret = (crypt_buf, passwd_len + salt_len, buf + 3, errstr);
(gdb) p crypt_buf
$7 = 0xaaaaccdf21b0 "1qaz!QAZysla"

(gdb) s pg_md5_hash
pg_md5_encrypt (passwd=passwd@entry=0xaaaad20f35e0 "1qaz!QAZ", salt=salt@entry=0xaaaad20f35c8 "ysla", salt_len=4,
buf=buf@entry=0xaaaad21f9448 "md5f2689c3cb387c12f0183882c6c080a05", errstr=errstr@entry=0xffffd33a3b80) at md5_common.c:169
169 free(crypt_buf);

(gdb) s pg_md5_hash
pg_md5_encrypt (passwd=passwd@entry=0xaaaad20f35e0 "1qaz!QAZ", salt=salt@entry=0xaaaad20f35c8 "ysla", salt_len=4,
buf=buf@entry=0xaaaad21f9448 "md5f2689c3cb387c12f0183882c6c080a05", errstr=errstr@entry=0xffffd33a3b80) at md5_common.c:169
169 free(crypt_buf);

...

(gdb) n
CreateRole (pstate=pstate@entry=0xaaaad21f91d8, stmt=stmt@entry=0xaaaad20f36c0) at user.c:448
448 new_record[Anum_pg_authid_rolpassword - 1] =
(gdb) s
cstring_to_text (s=0xaaaad21f9448 "md5f2689c3cb387c12f0183882c6c080a05") at varlena.c:184


从“buf”输出缓冲区里查看的md5值。然后把这个值和pg_authid系统表里的记录做对比。两个值是一致的。这个pg_authid里的rolpassword字段记录的md5值就是把密码和用户名加在一起,把用户名作为salt。然后把这个字符串做md5,然后前边加上md5开头并存储在pg_authid里的rolpassword字段。

Expanded display is on.
postgres=# select * from pg_authid where rolname='ysla';
-[ RECORD 1 ]--+------------------------------------
oid | 57735
rolname | ysla
rolsuper | f
rolinherit | t
rolcreaterole | f
rolcreatedb | f
rolcanlogin | t
rolreplication | f
rolbypassrls | f
rolconnlimit | -1
rolpassword | md5f2689c3cb387c12f0183882c6c080a05
rolvaliduntil |

如果使用md5函数处理下密码+用户的字符串,并加上md5前缀,可以发现和rolpassword字段的值是一样的。

postgres=# select 'md5'||md5('1qaz!QAZysla');
?column?
-------------------------------------
md5f2689c3cb387c12f0183882c6c080a05
(1 row)

postgres=# select rolpassword from pg_authid where rolname='ysla';
rolpassword
-------------------------------------
md5f2689c3cb387c12f0183882c6c080a05
(1 row)

postgres=# select rolpassword = 'md5'||md5('1qaz!QAZysla') from pg_authid where rolname='ysla';
?column?
----------
t
(1 row)


三、总结

通过上述测试可以发现,pg_authid系统表里的rolpassword字段的MD5码 ="md5"字符串+ md5(pwd+username)),MD5在理论上是几乎无法破解的,虽然不能反向解析,但是如果获取到了这个MD5处理后的字符串,可以通过撞库方式获取MD5算法处理前的字符,用预先计算好的MD5散列值与已知的散列值进行比较,以查找匹配的明文,从而获取到用户名和密码。但是这种方法需要预先有一定的可能的明文和计算MD5散列值的能力。MD5的认证不是很安全,所以,MD5现在已经被弃用了,发生碰撞的概率是1/(2^128)。


MD5的salt是用户名字符串,密码一致根据算法生成的字符也是一致的。而SCRAM-SHA-256有随机salt的加入,同样的密码, 修改后存储内容也会变化。所以SCRAM-SHA-256更加安全,几乎很难破解,因此比较建议使用SCRAM-SHA-256而不是MD5加密。


SCRAM-SHA-256

$

4096

:

fWK6w0/oDX42HJeYaPkIWA==

$

P7K5YHTAjRW4JHm/6aY4vPA549WepjUCdQzudRm/QtE=

:

GG/TgXZAiSuJqLBa1e7E7q2CFJJU106I9w8iHVB78Kk=



postgres=# select rolname,rolpassword from pg_authid where rolname='repl';

 rolname |                               rolpassword

---------+---------------------------------------------------------------------------------------------------------------------------------------

 repl  | SCRAM-SHA-256$4096:fWK6w0/oDX42HJeYaPkIWA==$P7K5YHTAjRW4JHm/6aY4vPA549WepjUCdQzudRm/QtE=:GG/TgXZAiSuJqLBa1e7E7q2CFJJU106I9w8iHVB78Kk=

(1 row)



本文分享自微信公众号 - 开源软件联盟PostgreSQL分会(kaiyuanlianmeng)。
如有侵权,请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”,欢迎正在阅读的你也加入,一起分享。

展开阅读全文
加载中
点击引领话题📣 发布并加入讨论🔥
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部