Re: Problems to use LDAP again AD directory with disabled anonymous logon

Hi,

On Wed, May 6, 2020 at 12:57 AM <heiko(dot)onnebrink(at)metronom(dot)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(dot)onnebrink(at)metronom(dot)com)"
> Enter LDAP Password: somepwd
>
> # extended LDIF
> #
> # LDAPv3
> # base <dc=madm,dc=net> with scope subtree
> # filter: (userPrincipalName=heiko(dot)onnebrink(at)metronom(dot)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(dot)onnebrink(at)metronom(dot)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(dot)onnebrink(at)metronom(dot)com)"
> # extended LDIF
> #
> # LDAPv3
> # base <dc=R2, dc=madm,dc=net> with scope subtree
> # filter: (userPrincipalName=heiko(dot)onnebrink(at)metronom(dot)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=admin(at)metronom(dot)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(dot)onnebrink(at)metronom(dot)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(dot)onnebrink(at)metronom(dot)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 *(at)metronom(dot)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 *(at)metronom(dot)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
>