Quick Links

Problems to use LDAP again AD directory with disabled anonymous logon

From:	<heiko(dot)onnebrink(at)metronom(dot)com>
To:	<pgadmin-support(at)lists(dot)postgresql(dot)org>
Subject:	Problems to use LDAP again AD directory with disabled anonymous logon
Date:	2020-05-05 19:13:20
Message-ID:	1EAE5003-7A56-42EB-8061-6255E10B2DC9@metronom.com
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Thread:
Lists:	pgadmin-support

<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<style></style>
</head>
<body lang="en-DE" link="#0563C1" vlink="#954F72">
<div class="WordSection1">
Hi 
I am exited to see that with the latest patch we have LDAP support in pgAdmin<o:p></o:p>
I tried to make it work but did not succeed.
 
 
We use Microsoft AD. We have a global catalog that allows LDAP access but anonymous access is disabled.<o:p></o:p>
 
I have a technical user SVCLDAP that I can use to auth against LDAP and search for a user via UPN and did some ldapsearch tests before I changed the config of pgAdmin: 
 
ldapsearch -H ldap://ldap.mgi.de:389 -D "CN=SVCLDAP, CN=Users, DC=ASF, DC=madm, DC=net" -W  -b "dc=R2, dc=madm,dc=net" "(<a href="mailto:userPrincipalName=heiko(dot)onnebrink(at)metronom(dot)com" title="mailto:userPrincipalName=heiko(dot)onnebrink(at)metronom(dot)com">userPrincipalName=heiko(dot)onnebrink(at)metronom(dot)com</a>)"<o:p></o:p>
<p style="margin:0cm;margin-bottom:.0001pt;caret-color: rgb(0, 0, 0);font-variant-caps: normal;orphans: auto;text-align:start;widows: auto;-webkit-text-size-adjust: auto;-webkit-text-stroke-width: 0px;font-stretch: normal;word-spacing:0px">
Enter LDAP Password:
somepwd<o:p></o:p>
<p style="margin:0cm;margin-bottom:.0001pt;caret-color: rgb(0, 0, 0);font-variant-caps: normal;orphans: auto;text-align:start;widows: auto;-webkit-text-size-adjust: auto;-webkit-text-stroke-width: 0px;font-stretch: normal;word-spacing:0px">
<o:p> </o:p>
<p style="margin:0cm;margin-bottom:.0001pt;caret-color: rgb(0, 0, 0);font-variant-caps: normal;orphans: auto;text-align:start;widows: auto;-webkit-text-size-adjust: auto;-webkit-text-stroke-width: 0px;font-stretch: normal;word-spacing:0px">
# extended LDIF<o:p></o:p>
<p style="margin:0cm;margin-bottom:.0001pt;caret-color: rgb(0, 0, 0);font-variant-caps: normal;orphans: auto;text-align:start;widows: auto;-webkit-text-size-adjust: auto;-webkit-text-stroke-width: 0px;font-stretch: normal;word-spacing:0px">
#<o:p></o:p>
<p style="margin:0cm;margin-bottom:.0001pt;caret-color: rgb(0, 0, 0);font-variant-caps: normal;orphans: auto;text-align:start;widows: auto;-webkit-text-size-adjust: auto;-webkit-text-stroke-width: 0px;font-stretch: normal;word-spacing:0px">
# LDAPv3<o:p></o:p>
<p style="margin:0cm;margin-bottom:.0001pt;caret-color: rgb(0, 0, 0);font-variant-caps: normal;orphans: auto;text-align:start;widows: auto;-webkit-text-size-adjust: auto;-webkit-text-stroke-width: 0px;font-stretch: normal;word-spacing:0px">
# base <dc=madm,dc=net> with scope subtree<o:p></o:p>
<p style="margin:0cm;margin-bottom:.0001pt;caret-color: rgb(0, 0, 0);font-variant-caps: normal;orphans: auto;text-align:start;widows: auto;-webkit-text-size-adjust: auto;-webkit-text-stroke-width: 0px;font-stretch: normal;word-spacing:0px">
# filter: (<a href="mailto:userPrincipalName=heiko(dot)onnebrink(at)metronom(dot)com">userPrincipalName=heiko(dot)onnebrink(at)metronom(dot)com</a>)<o:p></o:p>
<p style="margin:0cm;margin-bottom:.0001pt;caret-color: rgb(0, 0, 0);font-variant-caps: normal;orphans: auto;text-align:start;widows: auto;-webkit-text-size-adjust: auto;-webkit-text-stroke-width: 0px;font-stretch: normal;word-spacing:0px">
# requesting: ALL<o:p></o:p>
<p style="margin:0cm;margin-bottom:.0001pt;caret-color: rgb(0, 0, 0);font-variant-caps: normal;orphans: auto;text-align:start;widows: auto;-webkit-text-size-adjust: auto;-webkit-text-stroke-width: 0px;font-stretch: normal;word-spacing:0px">
#<o:p></o:p>
<p style="margin:0cm;margin-bottom:.0001pt;caret-color: rgb(0, 0, 0);font-variant-caps: normal;orphans: auto;text-align:start;widows: auto;-webkit-text-size-adjust: auto;-webkit-text-stroke-width: 0px;font-stretch: normal;min-height: 24px;word-spacing:0px">
<o:p> </o:p>
<p style="margin:0cm;margin-bottom:.0001pt;caret-color: rgb(0, 0, 0);font-variant-caps: normal;orphans: auto;text-align:start;widows: auto;-webkit-text-size-adjust: auto;-webkit-text-stroke-width: 0px;font-stretch: normal;word-spacing:0px">
# Onnebrink Heiko, HQ01-DUS, Users, DE, MSYS, r2.madm.net<o:p></o:p>
<p style="margin:0cm;margin-bottom:.0001pt;caret-color: rgb(0, 0, 0);font-variant-caps: normal;orphans: auto;text-align:start;widows: auto;-webkit-text-size-adjust: auto;-webkit-text-stroke-width: 0px;font-stretch: normal;word-spacing:0px">
dn: CN=Onnebrink Heiko,OU=HQ01-DUS,OU=Users,OU=DE,OU=MSYS,DC=r2,DC=madm,DC=net<o:p></o:p>
..<o:p></o:p>
<o:p> </o:p>
If I do the same query without providing a bind DN gives an sasl error<o:p></o:p>
<o:p> </o:p>
<p style="margin:0cm;margin-bottom:.0001pt;caret-color: rgb(0, 0, 0);font-variant-caps: normal;orphans: auto;text-align:start;widows: auto;-webkit-text-size-adjust: auto;-webkit-text-stroke-width: 0px;font-stretch: normal;word-spacing:0px">
ldapsearch -H ldap://ldap.mgi.de:389 -b "dc=R2, dc=madm,dc=net" "(<a href="mailto:userPrincipalName=heiko(dot)onnebrink(at)metronom(dot)com" title="mailto:userPrincipalName=heiko(dot)onnebrink(at)metronom(dot)com">userPrincipalName=heiko(dot)onnebrink(at)metronom(dot)com</a>)"<o:p></o:p>
<p style="margin:0cm;margin-bottom:.0001pt;caret-color: rgb(0, 0, 0);font-variant-caps: normal;orphans: auto;text-align:start;widows: auto;-webkit-text-size-adjust: auto;-webkit-text-stroke-width: 0px;font-stretch: normal;word-spacing:0px">
 
 
<o:p></o:p>
<p style="margin:0cm;margin-bottom:.0001pt;caret-color: rgb(0, 0, 0);font-variant-caps: normal;orphans: auto;text-align:start;widows: auto;-webkit-text-size-adjust: auto;-webkit-text-stroke-width: 0px;font-stretch: normal;word-spacing:0px">
SASL/GSSAPI authentication started<o:p></o:p>
<p style="margin:0cm;margin-bottom:.0001pt;caret-color: rgb(0, 0, 0);font-variant-caps: normal;orphans: auto;text-align:start;widows: auto;-webkit-text-size-adjust: auto;-webkit-text-stroke-width: 0px;font-stretch: normal;word-spacing:0px">
ldap_sasl_interactive_bind_s: Local error (-2)<o:p></o:p>
<p style="margin:0cm;margin-bottom:.0001pt;caret-color: rgb(0, 0, 0);font-variant-caps: normal;orphans: auto;text-align:start;widows: auto;-webkit-text-size-adjust: auto;-webkit-text-stroke-width: 0px;font-stretch: normal;word-spacing:0px">
additional info: SASL(-1): generic failure: GSSAPI Error: Unspecified GSS failure.  Minor code may provide more information (No Kerberos credentials available (default cache: FILE:/tmp/krb5cc_500))<o:p></o:p>
 
 
<o:p></o:p>
If I disable SASL (-x) it works but returns no data:<o:p></o:p>
<o:p> </o:p>
ldapsearch -H ldap://ldap.mgi.de:389 -x  -b "dc=R2, dc=madm,dc=net" "(<a href="mailto:userPrincipalName=heiko(dot)onnebrink(at)metronom(dot)com" title="mailto:userPrincipalName=heiko(dot)onnebrink(at)metronom(dot)com">userPrincipalName=heiko(dot)onnebrink(at)metronom(dot)com</a>)"<o:p></o:p>
# extended LDIF<o:p></o:p>
#<o:p></o:p>
# LDAPv3<o:p></o:p>
# base <dc=R2, dc=madm,dc=net> with scope subtree<o:p></o:p>
# filter: (<a href="mailto:userPrincipalName=heiko(dot)onnebrink(at)metronom(dot)com" title="mailto:userPrincipalName=heiko(dot)onnebrink(at)metronom(dot)com">userPrincipalName=heiko(dot)onnebrink(at)metronom(dot)com</a>)<o:p></o:p>
# requesting: ALL<o:p></o:p>
#<o:p></o:p>

<o:p> </o:p>
# search result<o:p></o:p>
search: 2<o:p></o:p>
result: 0 Success<o:p></o:p>

<o:p> </o:p>
# numResponses: 1<o:p></o:p>
<o:p> </o:p>
I transferred now the above settings to the pgAdmin config (docker is used here)<o:p></o:p>
 
docker run -p 443:443 --name pgadminssl -e 'PGADMIN_CONFIG_LDAP_SERVER_URI="ldap://ldap.mgi.de:389"'  -e 'PGADMIN_CONFIG_LDAP_USERNAME_ATTRIBUTE="userPrincipalName"' -e 'PGADMIN_CONFIG_LDAP_BASE_DN="(dc=madm,dc=net)"'
-e 'PGADMIN_CONFIG_SEARCH_SCOPE="SUBTREE"' -e<o:p></o:p>
'PGADMIN_CONFIG_AUTHENTICATION_SOURCES="ldap","internal"' -v '/dockerdata/pgadmin/servers.json:/servers.json' -v '/dockerdata/pgadmin/server.cert:/certs/server.cert'
-v '/dockerdata/pgadmin/server.key:/certs/server.key' -e PGADMIN_ENABLE_TLS=TRUE -e
<o:p></o:p>
PGADMIN_DEFAULT_PASSWORD=admin
-e<o:p></o:p>
<a href="mailto:PGADMIN_DEFAULT_EMAIL=admin(at)metronom(dot)comr">PGADMIN_DEFAULT_EMAIL=admin(at)metronom(dot)com</a>
registry.metroscales.io/rdb-dev/pgadmin:latest<o:p></o:p>
<o:p> </o:p>
2020-05-05 10:27:46,936: ERROR<o:p></o:p>
flask.app: Error binding to the LDAP server.<o:p></o:p>
Traceback (most recent call last):<o:p></o:p>
  File "/pgadmin4/pgadmin/authenticate/ldap.py", line 115, in connect<o:p></o:p>
    auto_bind=True<o:p></o:p>
  File "/usr/local/lib/python3.7/site-packages/ldap3/core/connection.py", line 355, in __init__<o:p></o:p>
    self.do_auto_bind()<o:p></o:p>
  File "/usr/local/lib/python3.7/site-packages/ldap3/core/connection.py", line 384, in do_auto_bind<o:p></o:p>
    raise LDAPBindError(self.last_error)<o:p></o:p>
ldap3.core.exceptions.LDAPBindError: None<o:p></o:p>
<o:p> </o:p>
From config description I do not see how I pass a bind user that would required (as we do not allow anonymous access) so that an LDAP query can be executed that finds
the logon user via his UPN. Once record is found we have the DN that can be used to bind the user with his entered password to verify that password is valid. 
 
<o:p></o:p>
Thanks for sharing how it works internally and what mistake I have here in my config..<o:p></o:p>
 
cheers 
Heiko 
 
<o:p></o:p>
<o:p> </o:p>
</div>
Geschäftsanschrift/Business
address: METRO-NOM GmbH, Metro-Straße 12, 40235 Duesseldorf, Germany <span lang="DE" style="font-size: 9pt; font-family: Verdana, sans-serif; color: #222222; background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">Aufsichtsrat/Supervisory Board: Olaf Koch
(Vorsitzender/Chairman) 
Geschäftsführung/Management Board: Timo Salzsieder (Vorsitzender/CEO), Felix
Lindemann (COO), Frank Hammerle (CFO) 
Sitz Düsseldorf, Amtsgericht Düsseldorf, HRB 18232/Registered Office
Düsseldorf, Commercial Register of the Düsseldorf Local Court, HRB 18232<o:p></o:p>

Betreffend
Mails von *@<a href="http://metrosystems.net/" target="_blank">metronom.com</a> 
Die in dieser E-Mail enthaltenen Nachrichten und Anhänge sind ausschließlich
für den bezeichneten Adressaten bestimmt. Sie können rechtlich geschützte,
vertrauliche Informationen enthalten. Falls Sie nicht der bezeichnete Empfänger
oder zum Empfang dieser E-Mail nicht berechtigt sind, ist die Verwendung,
Vervielfältigung oder Weitergabe der Nachrichten und Anhänge untersagt. Falls
Sie diese E-Mail irrtümlich erhalten haben, informieren Sie bitte unverzüglich
den Absender und vernichten Sie die E-Mail.<o:p></o:p>

Regarding mails from *@<a href="http://metrosystems.net/" target="_blank">metronom.com</a> 
This e-mail message and any attachment are intended exclusively for the named
addressee. They may contain confidential information which may also be protected
by professional secrecy. Unless you are the named addressee (or authorised to
receive for the addressee) you may not copy or use this message or any
attachment or disclose the contents to anyone else. If this e-mail was</body>
</html>

Attachment	Content-Type	Size
unknown_filename	text/html	20.0 KB

Browse pgadmin-support by date

	From	Date	Subject
Next Message	heiko.onnebrink	2020-05-05 19:27:36	Problems to use LDAP again AD directory with disabled anonymous logon
Previous Message	Doug Reed	2020-05-05 15:10:08	Re: What does this icon means?