Hi, On Wed, May 6, 2020 at 12:57 AM <heiko.onnebr...@metronom.com> wrote:
> Hi > I am exited to see that with the latest patch we have LDAP support in > pgAdmin > 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. > > 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" "(userPrincipalName= > heiko.onnebr...@metronom.com)" > Enter LDAP Password: somepwd > > # extended LDIF > # > # LDAPv3 > # base <dc=madm,dc=net> with scope subtree > # filter: (userPrincipalName=heiko.onnebr...@metronom.com) > # requesting: ALL > # > > # Onnebrink Heiko, HQ01-DUS, Users, DE, MSYS, r2.madm.net > dn: CN=Onnebrink > Heiko,OU=HQ01-DUS,OU=Users,OU=DE,OU=MSYS,DC=r2,DC=madm,DC=net > .. > > If I do the same query without providing a bind DN gives an sasl error > > ldapsearch -H ldap://ldap.mgi.de:389 -b "dc=R2, dc=madm,dc=net" > "(userPrincipalName=heiko.onnebr...@metronom.com)" > > > SASL/GSSAPI authentication started > ldap_sasl_interactive_bind_s: Local error (-2) > 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)) > > > If I disable SASL (-x) it works but returns no data: > > ldapsearch -H ldap://ldap.mgi.de:389 -x -b "dc=R2, dc=madm,dc=net" > "(userPrincipalName=heiko.onnebr...@metronom.com)" > # extended LDIF > # > # LDAPv3 > # base <dc=R2, dc=madm,dc=net> with scope subtree > # filter: (userPrincipalName=heiko.onnebr...@metronom.com) > # requesting: ALL > # > > # search result > search: 2 > result: 0 Success > > # numResponses: 1 > > I transferred now the above settings to the pgAdmin config (docker is used > here) > > 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 > '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 > PGADMIN_DEFAULT_PASSWORD=admin -e > PGADMIN_DEFAULT_EMAIL=ad...@metronom.com > registry.metroscales.io/rdb-dev/pgadmin:latest > > As per your ldapsearch (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" "(userPrincipalName=heiko.onnebr...@metronom.com)"), the pgAdmin LDAP parameters should be configured as below. PGADMIN_CONFIG_AUTHENTICATION_SOURCES=["ldap", "internal"] PGADMIN_CONFIG_LDAP_SERVER_URI="ldap://ldap.mgi.de:389"; PGADMIN_CONFIG_LDAP_BASE_DN="CN=Users, DC=ASF, DC=madm, DC=net" PGADMIN_CONFIG_LDAP_USERNAME_ATTRIBUTE="CN" PGADMIN_CONFIG_SEARCH_SCOPE="SUBTREE" PGADMIN_CONFIG_LDAP_SEARCH_BASE_DN="dc=R2, dc=madm,dc=net" PGADMIN_CONFIG_LDAP_SEARCH_FILTER="(userPrincipalName= heiko.onnebr...@metronom.com)" The LDAP configuration details can be found at https://www.pgadmin.org/docs/pgadmin4/4.21/enabling_ldap_authentication.html When you try to login to the pgAdmin application, SVCLDAP should be given in the username input box. Ref: https://www.pgadmin.org/docs/pgadmin4/4.21/login.html > 2020-05-05 10:27:46,936: ERROR > flask.app: Error binding to the LDAP server. > Traceback (most recent call last): > File "/pgadmin4/pgadmin/authenticate/ldap.py", line 115, in connect > auto_bind=True > File "/usr/local/lib/python3.7/site-packages/ldap3/core/connection.py", > line 355, in __init__ > self.do_auto_bind() > File "/usr/local/lib/python3.7/site-packages/ldap3/core/connection.py", > line 384, in do_auto_bind > raise LDAPBindError(self.last_error) > ldap3.core.exceptions.LDAPBindError: None > > 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. > > pgAdmin will first bind the LDAP server with the given configurations, then filter out user based on the LDAP_SEARCH_BASE_DN and LDAP_SEARCH_FILTER configurations. Thanks, Khushboo > Thanks for sharing how it works internally and what mistake I have here in > my config.. > > cheers > Heiko > > Geschäftsanschrift/Business address: METRO-NOM GmbH, Metro-Straße 12, > 40235 Duesseldorf, Germany > 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 > > Betreffend Mails von *@metronom.com <http://metrosystems.net/> > 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. > > Regarding mails from *@metronom.com <http://metrosystems.net/> > 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 >
