Digoal.zhou

Digoal.zhou

公益是一辈子的事, I'm digoal, Just do it.

PostgreSQL LDAP认证之 search bind配置

5 minute read

背景

在企业中，如果要使用统一的账户管理，可以考虑LDAP或者WINDOWS AD域进行管理。

PostgreSQL数据库也支持LDAP的认证手段，支持simple bind和search bind。

本文介绍一下PostgreSQL的search bind配置方法。

上一篇BLOG介绍了simple bind的配置

PostgreSQL LDAP simple bind认证配置

PostgreSQL LDAP search bind认证介绍

上一篇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.

search bind包分析

使用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.

simple bind包分析

使用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

search bind配置例子

不管哪种模式, 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的额外功能

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. PostgreSQL LDAP simple bind认证配置

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

Flag Counter

digoal’s 大量PostgreSQL文章入口

You May Also Enjoy

PostgreSQL(PPAS 兼容Oracle) 从零开始入门手册 - 珍藏版

17 minute read

背景 云数据库PPAS版，是阿里云与EnterpriseDB公司(简称EDB)合作基于PostgreSQL高度兼容Oracle语法的数据库服务，为用户提供易于操作的迁移工具，兼容范围涵盖：PL/SQL、数据类型、高级函数、表分区等。 用户可以直接在阿里云购买PPAS进行使用。 如果在购买PPAS前，想试用一下...

PostgreSQL pipelinedb 流计算插件 - IoT应用 - 实时轨迹聚合

1 minute read

背景 IoT场景，车联网场景，共享单车场景，人的行为位点等，终端实时上报的是孤立的位点，我们需要将其补齐成轨迹。 例如共享单车，下单，开锁，生成订单，骑行，关闭订单，关锁。这个过程有一个唯一的订单号，每次上报的位点会包含时间，订单号，位置。 根据订单号，将点聚合为轨迹。 使用pipelinedb插件，可以实...

PostgreSQL datediff 日期间隔（单位转换）兼容SQL用法

2 minute read

背景 使用datediff，对时间或日期相减，得到的间隔，转换为目标单位(日、月、季度、年、小时、秒。。。等)的数值。 DATEDIFF ( datepart, {date|timestamp}, {date|timestamp} ) 周 select datediff(week,'2009-01-0...