PostgreSQL pg_hba.conf "search bind" configure Use LDAP auth

上一篇BLOG讲了一下在PostgreSQL的pg_hba.conf中配置ldap simple bind的认证方式.

PostgreSQL还支持另一种bind方式.

search+bind

相当于PostgreSQL server需要两次和ldap server交互. 交互过程如下.

In the second mode, which we will call the search+bind mode, the server first binds to the LDAP directory with a fixed user name and password, specified with ldapbinddn and ldapbindpasswd, and performs a search for the user trying to log in to the database. If no user and password is configured, an anonymous bind will be attempted to the directory. The search will be performed over the subtree at ldapbasedn, and will try to do an exact match of the attribute specified in ldapsearchattribute. Once the user has been found in this search, the server disconnects and re-binds to the directory as this user, using the password specified by the client, to verify that the login is correct. This mode is the same as that used by LDAP authentication schemes in other software, such as Apache mod_authnz_ldap and pam_ldap. This method allows for significantly more flexibility in where the user objects are located in the directory, but will cause two separate connections to the LDAP server to be made.

使用tcpdump在PostgreSQL server抓包过滤LDAP server的IP, 

[root@db-172-16-3-39 ~]# tcpdump -i eth0 -n |grep 172.16.3.150
tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
listening on eth0, link-type EN10MB (Ethernet), capture size 96 bytes
第一次交互开始
14:30:17.084862 IP 172.16.3.39.61806 > 172.16.3.150.ldap: S 1377838322:1377838322(0) win 5840 <mss 1460,nop,nop,sackOK,nop,wscale 7>
14:30:17.085228 IP 172.16.3.150.ldap > 172.16.3.39.61806: S 3204831424:3204831424(0) ack 1377838323 win 14600 <mss 1460,nop,nop,sackOK,nop,wscale 7>
14:30:17.085256 IP 172.16.3.39.61806 > 172.16.3.150.ldap: . ack 1 win 46
14:30:17.085329 IP 172.16.3.39.61806 > 172.16.3.150.ldap: P 1:15(14) ack 1 win 46
14:30:17.085597 IP 172.16.3.150.ldap > 172.16.3.39.61806: . ack 15 win 115
14:30:17.086100 IP 172.16.3.150.ldap > 172.16.3.39.61806: P 1:15(14) ack 15 win 115
14:30:17.086111 IP 172.16.3.39.61806 > 172.16.3.150.ldap: . ack 15 win 46
14:30:17.086340 IP 172.16.3.39.61806 > 172.16.3.150.ldap: P 15:87(72) ack 15 win 46
14:30:17.087060 IP 172.16.3.150.ldap > 172.16.3.39.61806: P 15:77(62) ack 87 win 115
14:30:17.087070 IP 172.16.3.150.ldap > 172.16.3.39.61806: P 77:91(14) ack 87 win 115
14:30:17.087155 IP 172.16.3.39.61806 > 172.16.3.150.ldap: . ack 91 win 46
14:30:17.087227 IP 172.16.3.39.61806 > 172.16.3.150.ldap: P 87:94(7) ack 91 win 46
第一次交互结束fin
14:30:17.087247 IP 172.16.3.39.61806 > 172.16.3.150.ldap: F 94:94(0) ack 91 win 46
第二次交互开始
14:30:17.087310 IP 172.16.3.39.61807 > 172.16.3.150.ldap: S 1292716247:1292716247(0) win 5840 <mss 1460,nop,nop,sackOK,nop,wscale 7>
第一次交互结束fin
14:30:17.087537 IP 172.16.3.150.ldap > 172.16.3.39.61806: F 91:91(0) ack 95 win 115
14:30:17.087549 IP 172.16.3.39.61806 > 172.16.3.150.ldap: . ack 92 win 46
第二次交互继续
14:30:17.087613 IP 172.16.3.150.ldap > 172.16.3.39.61807: S 3434126489:3434126489(0) ack 1292716248 win 14600 <mss 1460,nop,nop,sackOK,nop,wscale 7>
14:30:17.087636 IP 172.16.3.39.61807 > 172.16.3.150.ldap: . ack 1 win 46
14:30:17.087677 IP 172.16.3.39.61807 > 172.16.3.150.ldap: P 1:58(57) ack 1 win 46
14:30:17.087935 IP 172.16.3.150.ldap > 172.16.3.39.61807: . ack 58 win 115
14:30:17.088268 IP 172.16.3.150.ldap > 172.16.3.39.61807: P 1:15(14) ack 58 win 115
14:30:17.088282 IP 172.16.3.39.61807 > 172.16.3.150.ldap: . ack 15 win 46
14:30:17.088357 IP 172.16.3.39.61807 > 172.16.3.150.ldap: P 58:65(7) ack 15 win 46
第二次交互结束fin
14:30:17.088368 IP 172.16.3.39.61807 > 172.16.3.150.ldap: F 65:65(0) ack 15 win 46
14:30:17.088728 IP 172.16.3.150.ldap > 172.16.3.39.61807: F 15:15(0) ack 66 win 115
14:30:17.088743 IP 172.16.3.39.61807 > 172.16.3.150.ldap: . ack 16 win 46

而simple bind则只需要交互一次.

LDAP authentication can operate in two modes. In the first mode, which we will call the simple bind mode, the server will bind to the distinguished name constructed as prefix username suffix. Typically, the prefix parameter is used to specify cn=, or DOMAIN\ in an Active Directory environment. suffix is used to specify the remaining part of the DN in a non-Active Directory environment.

使用tcpdump在PostgreSQL server抓包过滤LDAP server的IP, 

[root@db-172-16-3-39 ~]# tcpdump -i eth0 -n |grep 172.16.3.150
tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
listening on eth0, link-type EN10MB (Ethernet), capture size 96 bytes
交互开始
14:31:59.340873 IP 172.16.3.39.61808 > 172.16.3.150.ldap: S 608404204:608404204(0) win 5840 <mss 1460,nop,nop,sackOK,nop,wscale 7>
14:31:59.341230 IP 172.16.3.150.ldap > 172.16.3.39.61808: S 1781266100:1781266100(0) ack 608404205 win 14600 <mss 1460,nop,nop,sackOK,nop,wscale 7>
14:31:59.341257 IP 172.16.3.39.61808 > 172.16.3.150.ldap: . ack 1 win 46
14:31:59.341372 IP 172.16.3.39.61808 > 172.16.3.150.ldap: P 1:58(57) ack 1 win 46
14:31:59.341638 IP 172.16.3.150.ldap > 172.16.3.39.61808: . ack 58 win 115
14:31:59.342345 IP 172.16.3.150.ldap > 172.16.3.39.61808: P 1:15(14) ack 58 win 115
14:31:59.342356 IP 172.16.3.39.61808 > 172.16.3.150.ldap: . ack 15 win 46
14:31:59.342431 IP 172.16.3.39.61808 > 172.16.3.150.ldap: P 58:65(7) ack 15 win 46
结束fin
14:31:59.342451 IP 172.16.3.39.61808 > 172.16.3.150.ldap: F 65:65(0) ack 15 win 46
14:31:59.342776 IP 172.16.3.150.ldap > 172.16.3.39.61808: F 15:15(0) ack 66 win 115
14:31:59.342787 IP 172.16.3.39.61808 > 172.16.3.150.ldap: . ack 16 win 46

不管哪种模式, client都不需要和ldap server进行交互. 

为了安全考虑, 建议client 和 PostgreSQL server之间使用ssl连接. ldap server和PostgreSQL server之间也使用ssl连接.

search bind模式首先要根据pg_hba.conf中配置的ldap server, ldapbasedn,查找ldapattribute, 如果匹配到客户端提供的用户的话, 然后根据ldapattribute得到的值(作为下一次LDAP认证的用户名)以及客户端提供的密码进行第二次ldap认证.

[root@db-172-16-3-150 ldap]# ldapsearch -w 123321 -D "cn=Manager,dc=my-domain,dc=com" -b "ou=People,dc=my-domain,dc=com"
# new, People, my-domain.com
dn: uid=new,ou=People,dc=my-domain,dc=com
uid: new
cn: new
objectClass: account
objectClass: posixAccount
objectClass: top
objectClass: shadowAccount
loginShell: /bin/bash
uidNumber: 503
gidNumber: 500
homeDirectory: /home/new
userPassword:: e1NTSEF9WXJtTE8vNm1TWk9rUitmQ2J0SWJXSE9BTW9qdTljQ3o=

# digoal, People, my-domain.com
dn: uid=digoal,ou=People,dc=my-domain,dc=com
uid: digoal
cn: digoal
objectClass: account
objectClass: posixAccount
objectClass: top
objectClass: shadowAccount
userPassword:: e2NyeXB0fSQ2JFpqcTdKVTY4JGJVOWc1TjZBdi43MmxGWS9zeGp5QTdnenNqMzZ
 CUmVzS0F4T3IvaGUxaGYvLy9oZ05HV2xzSTEvVVQ0a0FsQmROU040eEl3ZzJLWldNTXdadElCdG4u
shadowLastChange: 16203
shadowMin: 0
shadowMax: 99999
shadowWarning: 7
loginShell: /bin/bash
uidNumber: 507
gidNumber: 510
homeDirectory: /home/digoal

search+bind的配置和simple bind差不多.

simple bind : 
host all new 0.0.0.0/0 ldap ldapserver=172.16.3.150 ldapport=389 ldapprefix="uid=" ldapsuffix=",ou=People,dc=my-domain,dc=com"
search bind : 
host all new 0.0.0.0/0 ldap ldapserver=172.16.3.150 ldapport=389 ldapsearchattribute="uid" ldapbasedn="ou=People,dc=my-domain,dc=com"

postgres@db5-> psql -h 172.16.3.39 -p 1999 -U digoal postgres
Password for user digoal: digoal
psql (9.1.3, server 9.3.1)
WARNING: psql version 9.1, server version 9.3.
         Some psql features might not work.
Type "help" for help.

postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 digoal    | Superuser                                      | {}
 new       |                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 srcheck   |                                                | {}

postgres@db5-> psql -h 172.16.3.39 -p 1999 -U new postgres
Password for user new: 111111
psql (9.1.3, server 9.3.1)
WARNING: psql version 9.1, server version 9.3.
         Some psql features might not work.
Type "help" for help.

postgres=> 

search + bind还可以满足一个比较特殊的认证需求，例如用户在数据库中需要使用cn以外的属性值（比如displayName）作为角色名。

例子：

首先我们需要在域中创建一个用户，有权限查询域中的条目。

例如这个用户叫ad_for_pg，密码是pwd_ad_for_pg。

它的DN为" cn=ad_for_pg,ou=质xxxx心,ou=skymobi,dc=sky-mobi,dc=com"

配置对应pg_hba.conf：

ldapbinddn="cn=ad_for_pg,ou=质xxxx心,ou=skymobi,dc=sky-mobi,dc=com" 
ldapbindpasswd="pwd_ad_for_pg"

这个用户将用于搜索域服务器中的  ldapbasedn 和  ldapsearchattribute。

接下来要指定pg_hba.conf中的  ldapbasedn和 ldapsearchattribute。

例如我是德哥，在域中的DN为 "cn=德哥,ou=质xxxx心,ou=skymobi,dc=sky-mobi,dc=com"，但是我不想在数据库中使用“德哥”作为数据库中的用户名，我想使用域中的displayName来作为数据库中的用户名，比如我的displayName是"Digoal.Zhou"。

配置对应pg_hba.conf：

ldapsearchattribute="displayName" 
ldapbasedn="cn=德哥,ou=质xxxx心,ou=skymobi,dc=sky-mobi,dc=com"

ldapbinddn和 ldapbindpasswd在域服务器中搜索到指定的 ldapbasedn和 ldapsearchattribute后，断开pg和ldapserver的连接，重新以   ldapbasedn和 ldapsearchattribute  以及 客户端提供的密码到域服务器进行认证。认证成功，则登录成功。

配置如下：

$ vi pg_hba.conf
host all "Digoal.Zhou" 0.0.0.0/0 ldap ldapserver=192.168.xxx.xxx ldapport=389 ldapsearchattribute="displayName" ldapbasedn="cn=德哥,ou=质xxxx心,ou=skymobi,dc=sky-mobi,dc=com" ldapbinddn="cn=ad_for_pg,ou=质xxxx心,ou=skymobi,dc=sky-mobi,dc=com" ldapbindpasswd="pwd_ad_for_pg"

$ pg_ctl reload

创建displayName对应的角色：

postgres=# create role "Digoal.Zhou" login;

认证测试：

psql -h 172.16.3.221 -U "Digoal.Zhou"
Password for user Digoal.Zhou:  输入ldapbasedn + ldapsearchattribute对应的域密码
psql (9.4.1)
Type "help" for help.
postgres=> 

注意，ldapbinddn和ldapbindpassword一定要配置正确，并且要有查询域条目的权限，否则可能报错如下：

2015-05-12 17:07:40.169 CST,"Digoal.Zhou","postgres",23459,"172.16.3.221:23481",5551c2dc.5ba3,2,"authentication",2015-05-12 17:07:40 CST,39/77777,0,LOG,00000,"could not search LDAP for filter ""(displayName=Digoal.Zhou)"" on server ""192.168.xxx.xxx"": Operations error",,,,,,,,"CheckLDAPAuth, auth.c:2028",""
2015-05-12 17:07:40.169 CST,"Digoal.Zhou","postgres",23459,"172.16.3.221:23481",5551c2dc.5ba3,3,"authentication",2015-05-12 17:07:40 CST,39/77777,0,FATAL,28000,"LDAP authentication failed for user ""Digoal.Zhou""","Connection matched pg_hba.conf line 94: ""host all ""Digoal.Zhou"" 0.0.0.0/0 ldap ldapserver=192.168.xxx.xxx ldapport=389 ldapsearchattribute=""displayName"" ldapbasedn=""cn=德哥,ou=质xxxx心,ou=skymobi,dc=sky-mobi,dc=com"" ldapbinddn=""cn=ad_for_pg,ou=质xxxx心,ou=skymobi,dc=sky-mobi,dc=com"" ldapbindpasswd="""pwd_ad_for_pg"""",,,,,,,"auth_failed, auth.c:285",""

[参考]

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

2.  http://www.postgresql.org/docs/9.4/static/auth-methods.html#AUTH-LDAP