Subject: Re: LDAP with TLS is taking more time in Postgresql 11.5
To: Mani Sankar <manisankar01695@gmail.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
References: 
 <CAEtVwW3Bygo1iYxOPYbFSjBPqA1_RU8iJMTdML609h0Gy4JN7Q@mail.gmail.com>
 <319e70c3-cd99-8bf9-260d-49064ba402c5@aklaver.com>
 <CAEtVwW2m_BiQ5WM0jwRBuvJkVDmdSyeA9ks2-HO7qmZqme+jUA@mail.gmail.com>
 <1e9458a5-6f83-7715-3b48-a1bfae35ca72@aklaver.com>
 <CAEtVwW1f=ZAk1F8+yjvujVxwzAZKOpBuH_8_vEt=vVVddCV_eg@mail.gmail.com>
From: Adrian Klaver <adrian.klaver@aklaver.com>
Message-ID: <ae8dc6a3-c3e9-c606-9ffb-d5ed31fa6126@aklaver.com>
Date: Tue, 25 Feb 2020 10:18:13 -0800
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101
 Thunderbird/68.5.0
MIME-Version: 1.0
In-Reply-To: 
 <CAEtVwW1f=ZAk1F8+yjvujVxwzAZKOpBuH_8_vEt=vVVddCV_eg@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 8bit
Precedence: bulk

On 2/25/20 10:08 AM, Mani Sankar wrote:
> Hi Adrian,
> 
> Should I want to try this configuration?

I thought you where already using this configuration?

Are the 9.4 and 11.5 instances are on the same machine and/or network?

In other words is ldapserver=XXXXXXXXXXXXXXX pointing at the same thing?


> 
> Regards,
> Mani.
> 
> On Tue, 25 Feb, 2020, 9:24 pm Adrian Klaver, <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>> wrote:
> 
>     On 2/24/20 9:07 PM, Mani Sankar wrote:
>     Please reply to list also.
>     Ccing list.
>      > Hi Adrian,
>      >
>      > Thanks for replying. Below are the requested details.
>      >
>      > ################ Configuration in 9.4 PG Version
>      >
>      > local all all ldap ldapserver=XXXXXXXXXXXXXX ldapport=3268
>      > ldapprefix="ADS\" ldapsuffix="" ldaptls=1
>      >
>      > host all someuser xx.xx.xx.xx/32 ldap ldapserver=XXXXXXXXXXXXXXX
>      > ldapport=3268 ldapprefix="ADS\" ldapsuffix="" ldaptls=1
>      >
>      > host all someuser ::1/128 ldap ldapserver=XXXXXXXXXXXXXXX
>     ldapport=3268
>      > ldapprefix="ADS\" ldapsuffix="" ldaptls=1
>      >
>      > host all all 0.0.0.0/0 <http://0.0.0.0/0> <http://0.0.0.0/0> ldap
>      > ldapserver=XXXXXXXXXXXXXXX ldapport=3268 ldapprefix="ADS\"
>     ldapsuffix=""
>      > ldaptls=1
>      >
>      > host all all ::1/128 ldap ldapserver=XXXXXXXXXXXXXXX ldapport=3268
>      > ldapprefix="ADS\" ldapsuffix="" ldaptls=1
>      >
>      > host replication someuser 0.0.0.0/0 <http://0.0.0.0/0>
>     <http://0.0.0.0/0> ldap
>      > ldapserver=XXXXXXXXXXXXXXX ldapport=3268 ldapprefix="ADS\"
>     ldapsuffix=""
>      > ldaptls=1
>      >
>      > host replication someuser 0.0.0.0/0 <http://0.0.0.0/0>
>     <http://0.0.0.0/0> ldap
>      > ldapserver=XXXXXXXXXXXXXXX ldapport=3268 ldapprefix="ADS\"
>     ldapsuffix=""
>      > ldaptls=1
>      >
>      > ############ Configuration in 11.5 Version.
>      >
>      > local all all ldap ldapserver=XXXXXXXXXXXXXXX ldapport=3268
>      > ldapprefix="ADS\" ldapsuffix="" ldaptls=1
>      >
>      > host all someuser xx.xx.xx.xx/32 ldap ldapserver=XXXXXXXXXXXXXXX
>      > ldapport=3268 ldapprefix="ADS\" ldapsuffix="" ldaptls=1
>      >
>      > host all someuser ::1/128 ldap ldapserver=XXXXXXXXXXXXXXX
>     ldapport=3268
>      > ldapprefix="ADS\" ldapsuffix="" ldaptls=1
>      >
>      > host all all 0.0.0.0/0 <http://0.0.0.0/0> <http://0.0.0.0/0> ldap
>      > ldapserver=XXXXXXXXXXXXXXX ldapport=3268 ldapprefix="ADS\"
>     ldapsuffix=""
>      > ldaptls=1
>      >
>      > host all all ::1/128 ldap ldapserver=XXXXXXXXXXXXXXX ldapport=3268
>      > ldapprefix="ADS\" ldapsuffix="" ldaptls=1
>      >
>      > host replication someuser 0.0.0.0/0 <http://0.0.0.0/0>
>     <http://0.0.0.0/0> ldap
>      > ldapserver=XXXXXXXXXXXXXXX ldapport=3268 ldapprefix="ADS\"
>     ldapsuffix=""
>      > ldaptls=1
>      >
>      > host replication someuser 0.0.0.0/0 <http://0.0.0.0/0>
>     <http://0.0.0.0/0> ldap
>      > ldapserver=XXXXXXXXXXXXXXX ldapport=3268 ldapprefix="ADS\"
>     ldapsuffix=""
>      > ldaptls=1
>      >
>      > host replication someuser 0.0.0.0/0 <http://0.0.0.0/0>
>     <http://0.0.0.0/0> ldap
>      > ldapserver=XXXXXXXXXXXXXXX ldapport=3268 ldapprefix="ADS\"
>     ldapsuffix=""
>      > ldaptls=1
>      >
>      > host    replication     replicator  XXXXXXXXXXXXX/22        md5
>      >
>      > host    replication     replicator  1XXXXXXXXXXXX/22        md5
>      >
>      > Linux Version: Red Hat Enterprise Linux Server release 6.10
>     (Santiago)
>      >
>      > Server Installation is Source code installation. Custom build for
>     our
>      > environment.
>      >
>      > Authentication logs from PG 11.5:
>      >
>      > 2020-02-24 00:00:15 MST [25089]:
>      >
>     application=[unknown],host=xx.xx.xxx.xx(55742),user=[unknown],db=[unknown],state=00000
> 
>      > LOG:  connection received: host=xx.xx.xxx.xx port=55742
>      >
>      > 2020-02-24 00:00:16 MST [25090]:
>      >
>     application=[unknown],host=xx.xx.xxx.xx(55748),user=[unknown],db=[unknown],state=00000
> 
>      > LOG:  connection received: host=xx.xx.xxx.xx port=55748
>      >
>      > 2020-02-24 00:00:16 MST [25092]:
>      >
>     application=[unknown],host=xx.xx.xxx.xx(55765),user=[unknown],db=[unknown],state=00000
> 
>      > LOG:  connection received: host=xx.xx.xxx.xx port=55765
>      >
>      > 2020-02-24 00:00:16 MST [25093]:
>      >
>     application=[unknown],host=xx.xx.xxx.xx(55770),user=[unknown],db=[unknown],state=00000
> 
>      > LOG:  connection received: host=xx.xx.xxx.xx port=55770
>      >
>      > 2020-02-24 00:00:17 MST [25090]:
>      >
>     application=[unknown],host=xx.xx.xxx.xx(55748),user=Someuser,db=test_db,state=00000
> 
>      > LOG:  connection authorized: user=Someuser database=test_db
>      >
>      > 2020-02-24 00:00:17 MST [25089]:
>      >
>     application=[unknown],host=xx.xx.xxx.xx(55742),user=Someuser,db=test_db,state=00000
> 
>      > LOG:  connection authorized: user=Someuser database=test_db
>      >
>      > 2020-02-24 00:00:17 MST [25092]:
>      >
>     application=[unknown],host=xx.xx.xxx.xx(55765),user=Someuser,db=test_db,state=00000
> 
>      > LOG:  connection authorized: user=Someuser database=test_db
>      >
>      > 2020-02-24 00:00:17 MST [25093]:
>      >
>     application=[unknown],host=xx.xx.xxx.xx(55770),user=Someuser,db=test_db,state=00000
> 
>      > LOG:  connection authorized: user=Someuser database=test_db
>      >
>      > Authentication logs from PG 9.4:
>      >
>      > 2020-02-17 22:40:01 MST [127575]:
>      >
>     application=[unknown],host=xx.xx.xx.xx(39451),user=[unknown],db=[unknown]
>     LOG:
>      > connection received: host=xx.xx.xx.xx port=39451
>      >
>      > 2020-02-17 22:40:01 MST [127575]:
>      >
>     application=[unknown],host=xx.xx.xx.xx(39451),user=Someuser,db=test_db
>      > LOG:  connection authorized: user=Someuser database=test_db
>      >
>      > 2020-02-24 21:57:44 MST [117472]:
>      >
>     application=[unknown],host=xx.xx.xx.xx(58500),user=[unknown],db=[unknown]
>     LOG:
>      > connection received: host=xx.xx.xx.xx port=58500
>      >
>      > 2020-02-24 21:57:44 MST [117472]:
>      >
>     application=[unknown],host=xx.xx.xx.xx(58500),user=Someuser,db=test_db
>      > LOG:  connection authorized: user=Someuser database=test_db
>      >
>      > 2020-02-24 21:58:27 MST [117620]:
>      >
>     application=[unknown],host=xx.xx.xx.xx(58520),user=[unknown],db=[unknown]
>     LOG:
>      > connection received: host=xx.xx.xx.xx port=58520
>      >
>      > 2020-02-24 21:58:27 MST [117620]:
>      >
>     application=[unknown],host=xx.xx.xx.xx(58520),user=Someuser,db=test_db
>      > LOG:  connection authorized: user=Someuser database=test_db
>      >
>      > 2020-02-24 21:58:31 MST [117632]:
>      >
>     application=[unknown],host=xx.xx.xx.xx(58524),user=[unknown],db=[unknown]
>     LOG:
>      > connection received: host=xx.xx.xx.xx port=58524
>      >
>      > 2020-02-24 21:58:31 MST [117632]:
>      >
>     application=[unknown],host=xx.xx.xx.xx(58524),user=Someuser,db=test_db
>      > LOG:  connection authorized: user=Someuser database=test_db
>      >
>      > We also have a local .ldaprc file with below entry
>      >
>      > TLS_REQCERT allow
>      >
>      >
>      > On Tue, Feb 25, 2020 at 2:28 AM Adrian Klaver
>     <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>      > <mailto:adrian.klaver@aklaver.com
>     <mailto:adrian.klaver@aklaver.com>>> wrote:
>      >
>      >     On 2/24/20 11:50 AM, Mani Sankar wrote:
>      >      > Hi All,
>      >      >
>      >      > We have recently upgraded our postgres servers from 9.4
>     version
>      >     to 11.5
>      >      > version. Post upgrade we are see delay in authentication.
>      >      >
>      >      > Issue is when we are using ldaptls=1 the authentication
>     takes 1
>      >     second
>      >      > or greater than that. But if I disable ldaptls it's getting
>      >      > authenticated within milliseconds.
>      >      >
>      >      > But in 9.4 even if I enable ldaptls it's getting authenticated
>      >     within
>      >      > milliseconds any idea why we are facing the issue?
>      >
>      >     This is going to need a good deal more information:
>      >
>      >     1) OS the server is running on and did the OS or OS version
>     change with
>      >     the upgrade?
>      >
>      >     2) How was the server installed from packages(if so from
>     where?) or
>      >     from
>      >     source?
>      >
>      >     3) The configuration for LDAP in pg_hba.conf.
>      >
>      >     4) Pertinent information from the Postgres log.
>      >
>      >     5) Pertinent information from the system log.
>      >
>      >      >
>      >      > Regards,
>      >      > Mani.
>      >      >
>      >
>      >
>      >     --
>      >     Adrian Klaver
>      > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>     <mailto:adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>
>      >
> 
> 
>     -- 
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com