请不要在pg_hba.conf中配置客户端认证方法为password, 这样将会在网络中传输密码明文. 非常危险.
除非你用的是hostssl数据传输模式. 否则请至少要使用md5认证方法.
以下截取自pg_hba.conf
# METHOD can be "trust", "reject", "md5", "password", "gss", "sspi",
# "krb5", "ident", "peer", "pam", "ldap", "radius" or "cert". Note that
# "password" sends passwords in clear text; "md5" is preferred since
# it sends encrypted passwords.
如果使用了password 配置, 那么认证过程中, 密码将以明文形式在网络中传输.
下面来测试一下 :
1. hostnossl, password方法
抓包 :
连接 :
vi pg_hba.conf
hostnossl all all 0.0.0.0/0 password
pg_ctl reload抓包 :
[root@db-172-16-3-33 libpq]# tcpdump -i eth0 host 172.16.3.39 -s 0 -w plain.dmp连接 :
pg92@db-172-16-3-39-> psql -h 172.16.3.33 -p 1999 -U postgres digoal
psql (9.2beta1, server 9.3devel)
WARNING: psql version 9.2, server version 9.3.
Some psql features might not work.
Type "help" for help.
digoal=# \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | test | table | postgres
(1 row)
#查询
digoal=# select * from test limit 10;
id | info | crt_time
----+----------------------------------+----------------------------
1 | 8c6488c425f041c8ed28514ef2985afd | 2013-05-22 20:55:42.940045
2 | f92ecbe588516e2f59dc23b69305afc9 | 2013-05-22 20:55:42.940422
3 | b98827408bdd1865757f8db7a7001111 | 2013-05-22 20:55:42.940435
4 | 85911d5a2060917c7d98a1ed22ac3247 | 2013-05-22 20:55:42.940443
5 | db863ff0911485f6fc58559b58b56042 | 2013-05-22 20:55:42.940451
6 | 95636eb443f4925f310a2472edd2b064 | 2013-05-22 20:55:42.940458
7 | ed7ca0280469fb1e3e497c33fc338978 | 2013-05-22 20:55:42.940466
8 | 48cea37b756d00e4309db46152df3918 | 2013-05-22 20:55:42.940473
9 | 04cd192c0500a0b76e9bbb3e3a31f416 | 2013-05-22 20:55:42.940493
10 | a6a83937ffc053baa82cfbbed26b86ce | 2013-05-22 20:55:42.940502
(10 rows)
使用wireshark分析包 :
密码postgres, 明文 :
SQL, 明文 :
结果, 明文 :
2. hostnossl, md5方法
改成md5认证后, 抓包 :
抓包 :
连接查询 :
抓包结束 :
vi pg_hba.conf
hostnossl all all 0.0.0.0/0 md5
pg_ctl reload抓包 :
[root@db-172-16-3-33 libpq]# tcpdump -i eth0 host 172.16.3.39 -s 0 -w md5.dmp连接查询 :
pg92@db-172-16-3-39-> psql -h 172.16.3.33 -p 1999 -U postgres digoal
psql (9.2beta1, server 9.3devel)
WARNING: psql version 9.2, server version 9.3.
Some psql features might not work.
Type "help" for help.
digoal=# select * from test limit 10;
id | info | crt_time
----+----------------------------------+----------------------------
1 | 8c6488c425f041c8ed28514ef2985afd | 2013-05-22 20:55:42.940045
2 | f92ecbe588516e2f59dc23b69305afc9 | 2013-05-22 20:55:42.940422
3 | b98827408bdd1865757f8db7a7001111 | 2013-05-22 20:55:42.940435
4 | 85911d5a2060917c7d98a1ed22ac3247 | 2013-05-22 20:55:42.940443
5 | db863ff0911485f6fc58559b58b56042 | 2013-05-22 20:55:42.940451
6 | 95636eb443f4925f310a2472edd2b064 | 2013-05-22 20:55:42.940458
7 | ed7ca0280469fb1e3e497c33fc338978 | 2013-05-22 20:55:42.940466
8 | 48cea37b756d00e4309db46152df3918 | 2013-05-22 20:55:42.940473
9 | 04cd192c0500a0b76e9bbb3e3a31f416 | 2013-05-22 20:55:42.940493
10 | a6a83937ffc053baa82cfbbed26b86ce | 2013-05-22 20:55:42.940502
(10 rows)抓包结束 :
tcpdump: listening on eth0, link-type EN10MB (Ethernet), capture size 65535 bytes
31 packets captured
31 packets received by filter
0 packets dropped by kernel
使用wireshark分析包 :
密码为md5加salt的二次加密后的md5值,
salt :
加密后的md5明文 :
不是pg_shadow中存储的md5值 :
digoal=# select * from pg_shadow where usename='postgres';
usename | usesysid | usecreatedb | usesuper | usecatupd | userepl | passwd | valuntil | useconfig
----------+----------+-------------+----------+-----------+---------+-------------------------------------+----------+-----------
postgres | 10 | t | t | t | t | md53175bce1d3201d16594cebf9d7eb3f9d | |
(1 row)
SQL明文 :
结果明文 :
3.
hostssl, password方法
改成hostssl, 但是依旧使用password方法.
vi pg_hba.conf
hostssl all all 0.0.0.0/0 password
#hostnossl all all 0.0.0.0/0 password
pg_ctl reload
ssl的配置参考 :
抓包 :
连接查询 :
抓包结束 :
使用wireshark 分析包 :
[root@db-172-16-3-33 libpq]# tcpdump -i eth0 host 172.16.3.39 -s 0 -w ssl_plain.dmp连接查询 :
pg92@db-172-16-3-39-> psql -h 172.16.3.33 -p 1999 -U postgres digoal
psql (9.2beta1, server 9.3devel)
WARNING: psql version 9.2, server version 9.3.
Some psql features might not work.
SSL connection (cipher: RC4-SHA, bits: 128)
Type "help" for help.
digoal=# select * from test limit 10;
id | info | crt_time
----+----------------------------------+----------------------------
1 | 8c6488c425f041c8ed28514ef2985afd | 2013-05-22 20:55:42.940045
2 | f92ecbe588516e2f59dc23b69305afc9 | 2013-05-22 20:55:42.940422
3 | b98827408bdd1865757f8db7a7001111 | 2013-05-22 20:55:42.940435
4 | 85911d5a2060917c7d98a1ed22ac3247 | 2013-05-22 20:55:42.940443
5 | db863ff0911485f6fc58559b58b56042 | 2013-05-22 20:55:42.940451
6 | 95636eb443f4925f310a2472edd2b064 | 2013-05-22 20:55:42.940458
7 | ed7ca0280469fb1e3e497c33fc338978 | 2013-05-22 20:55:42.940466
8 | 48cea37b756d00e4309db46152df3918 | 2013-05-22 20:55:42.940473
9 | 04cd192c0500a0b76e9bbb3e3a31f416 | 2013-05-22 20:55:42.940493
10 | a6a83937ffc053baa82cfbbed26b86ce | 2013-05-22 20:55:42.940502
(10 rows)抓包结束 :
tcpdump: listening on eth0, link-type EN10MB (Ethernet), capture size 65535 bytes
21 packets captured
21 packets received by filter
0 packets dropped by kernel使用wireshark 分析包 :
密码, SQL, 查询结果全部被加密.
图略.
[参考]
