Re: LDAP with TLS is taking more time in Postgresql 11.5

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(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
> > <mailto:adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
> <mailto:adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>>
> >
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com