PostgreSQL security - don't use password method in pg_hba.conf

请不要在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的配置参考 : 

http://blog.163.com/digoal@126/blog/static/163877040201342233131835/

抓包 :

[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, 查询结果全部被加密. 

图略.

[参考]

1. http://blog.163.com/digoal@126/blog/static/163877040201342233131835/