use Windows AD (Active Directory) manage PostgreSQL auth

之前写过关于使用LDAP来认证PostgreSQL用户的文章, 有兴趣的朋友可以参考 : 

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

但是以前没有测试过windows AD是否可用.

其实方法和LDAP一样, 

首先我们要测试一下在linux下, 使用ldapsearch是否可以正常访问WINDOWS AD 服务.

例如 : 

# ldapsearch -H ldap://xxx.xxx.1.133:389  -x
# extended LDIF
#
# LDAPv3
# base <> with scope subtree
# filter: (objectclass=*)
# requesting: ALL
#

# search result
search: 2
result: 1 Operations error
text: 000004DC: LdapErr: DSID-0C0906E8, comment: In order to perform this ope
 ration a successful bind must be completed on the connection., data 0, v1db1

# numResponses: 1

检查使用域账号bind 是否正常 : 

# ldapsearch -H ldap://xxx.xxx.1.133:389  -x  -D "cn=德哥,ou=digoal,ou=SKYMOBI,dc=sky-mobi,dc=com" -W
输入密码后可以正常返回.
Enter LDAP Password:
# extended LDIF
#
# LDAPv3
# base <> with scope subtree
# filter: (objectclass=*)
# requesting: ALL
#

# search result
search: 2
result: 10 Referral
text: 0000202B: RefErr: DSID-031007EF, data 0, 1 access points
        ref 1: 'examp
 le.com'

ref: ldap://example.com/dc=example,dc=com

# numResponses: 1

接下来就可以配置postgresql的域认证了.

首先要在域中创建账号, 并获取账号的Distinguished Name 即binddn.

例如 : 

cn=德哥,ou=digoal,ou=SKYMOBI,dc=sky-mobi,dc=com

在数据库中创建对应的用户.

$ psql postgres postgres
# create role "德哥" login;

配置pg_hba.conf, 使用如下条目 : 

host all all 0.0.0.0/0 ldap ldapserver=xxx.xxx.1.133 ldapport=389 ldapprefix="cn=" ldapsuffix=",ou=digoal,ou=SKYMOBI,dc=sky-mobi,dc=com"

测试 : 

psql -h 172.16.3.150 -p 1921 -U 德哥
Password for user 德哥: 如果密码错误则
psql: FATAL:  LDAP authentication failed for user "德哥"
密码正确可以登录
Password for user 德哥:
psql (9.3.5)
Type "help" for help.

postgres=> \q

[参考]
1. http://blog.163.com/digoal@126/blog/static/16387704020145914717111/