Login with LDAP authentication takes 5 seconds
Hi, I configured my PostgreSQL 10 DB on Debian 9.2 with LDAP authentication (simple bind mode). While this basically works, it has the strange effect that the first login with psql takes around 5 seconds. When I reconnect within 60 seconds, the login completes immediately. The LDAP server is behind a firewall. So for a test, in pg_hba.conf I put the LDAP servers IP address instead of its DNS name (for parameter ldapserver). Like that, all logins complete immediately. But in general I prefer specifying the DNS name rather than the IP. When I checked on the DB machine with the following commands host my.ldap.server.org dig my.ldap.server.org both always returned the host name and IP address of the LDAP server immediately. Does anyone of you have an explanation for this, or a hint, where I could do some further investigation? Thanks, Andy
Re: Login with LDAP authentication takes 5 seconds
Thanks a lot to all of you for your valuable hints. So I tried some more and found that traceroute and ping show the same symptoms, i.e. first call takes 5 seconds. However, traceroute -4 and ping -4 always respond immediately. So, searching for "linux dns lookup takes long ipv4" brought me to https://askubuntu.com/a/32312 on AskUbuntu that suggested adding options single-request to /etc/resolv.conf. And wow, this did the trick. So, according to the page linked there, I'm maybe having to do with a DNS Server or Firewall that doesn't handle the parallel IPv4 and IPv6 requests properly... I'll check with my IT. Thank you again, folks. Andy On 31 May 2018 at 16:54, Achilleas Mantzios wrote: > On 28/05/2018 17:26, Andreas Schmid wrote: > > Hi, > > I configured my PostgreSQL 10 DB on Debian 9.2 with LDAP authentication > (simple bind mode). While this basically works, it has the strange effect > that the first login with psql takes around 5 seconds. When I reconnect > within 60 seconds, the login completes immediately. > > The LDAP server is behind a firewall. So for a test, in pg_hba.conf I put > the LDAP servers IP address instead of its DNS name (for parameter > ldapserver). Like that, all logins complete immediately. But in general I > prefer specifying the DNS name rather than the IP. > > When I checked on the DB machine with the following commands > host my.ldap.server.org > dig my.ldap.server.org > both always returned the host name and IP address of the LDAP server > immediately. > > Does anyone of you have an explanation for this, or a hint, where I could > do some further investigation? > > IPv4 vs IPv6 ? any strange timeouts? look in the postgresql logs for any > messages. > Also definitely ran wireshark, it'll tell you a lot on what's happening > between postgresql and your LDAP . > > > Thanks, > Andy > > > -- > Achilleas Mantzios > IT DEV Lead > IT DEPT > Dynacom Tankers Mgmt > >
UNION ALL: Apparently based on column order rather than on column name or alias
Hi list I realized the following behaviour of UNION ALL: SELECT 'a' AS col1, 'b' AS col2 UNION ALL SELECT 'c' AS col1, 'd' AS col2; returns: col1 | col2 --+-- a| b c| d Now I switch the column aliases in the second SELECT-Statement: SELECT 'a' AS col1, 'b' AS col2 UNION ALL SELECT 'c' AS col2, 'd' AS col1; This returns the same result: col1 | col2 --+-- a| b c| d Same behaviour when working just with column names, no aliases. So my conclusion is that the result of UNION ALL depends on the column order, not on the column names or aliases. Is this the intended behaviour? And is it documented somewhere? What I found is the last sentence on https://www.postgresql.org/docs/current/queries-union.html which says "[...] they return the same number of columns and the corresponding columns have compatible data types [...]" It says nothing about column order, column names or aliases. Does this obviously imply it's the column order? Thank you for some clarification. Andy
