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

Hi,

On Wed, May 6, 2020 at 2:53 PM <heiko(dot)onnebrink(at)metronom(dot)com> wrote:

> Created feature request for this issue
> https://redmine.postgresql.org/issues/5484 with details proposal how to
> enhance it
>
Thanks.

> Hope someone will grab the request as LDAP support is a really long
> awaited feature and the request would complete the LDAP implementation.
>
We will look into it.

Thanks,
Khushboo

> Unfortunately I have no Python skill .. otherwise I would have pushed a PR
> __
> cheers
> Heiko
>
> From: Khushboo Vashi <khushboo(dot)vashi(at)enterprisedb(dot)com>
> Date: Wednesday, 6. May 2020 at 10:50
> To: "Onnebrink, Heiko" <heiko(dot)onnebrink(at)metronom(dot)com>
> Cc: pgAdmin Support <pgadmin-support(at)postgresql(dot)org>
> Subject: Re: [EXT] Re: Problems to use LDAP again AD directory with
> disabled anonymous logon
>
> Hi,
>
> [Adding pgAdmin Support...]
>
> On Wed, May 6, 2020 at 11:43 AM <mailto:heiko(dot)onnebrink(at)metronom(dot)com>
> wrote:
> Hi
> hope it is ok to contact you via PM.
> First all thanks for the feedback on the pgAdmin mailing list ..
>
> I still do not understand the LDAP config properly.
> Maybe I should have also described more precise our LDAP scenario,
> therefore some more input
>
> We have users in different locations that want to work with pgAdmin
> Here 2 sample users that want to logon to pgAdmin:
>
> User A DN: CN=Onnebrink
> Heiko,OU=HQ01-DUS,OU=Users,OU=DE,OU=MSYS,DC=r2,DC=madm,DC=net
> will logon with his UPN mailto:heiko(dot)onnebrink(at)metronom(dot)com
>
> User B DN:
> CN=Other User,OU=BRANCH-BUK,OU=Users,OU=RO,OU=MSYS,DC=r3,DC=madm,DC=net
> will logon with his UPN mailto:other(dot)user(at)metrosystems(dot)ro
>
> They will enter there UPN heiko(dot)onnebrink(at)metronom(dot)ccom or mailto:
> other(dot)user(at)metrosystems(dot)ro in logon screen as username
>
> As our LDAP does not allow anonymous access we have a technical user
> SVCLDAP that can be used to connect to global catalog
> DN: (cn=SVCLDAP, cn=Users, dc=asf, dc=madm, dc=net) with some fixed
> password "secret" that we could configure somewhere if required.
>
> Can this be configured for pgAdmin ldap integration?
>
>
> If not I have a proposal that I implemented several times for different
> apps.
>
> define some parameter like LDAP_BIND_USER and LDAP_BIND_PWD that takes the
> credentials of a technical user that can be used to connect to the catalog
> If LDAP_BIND_USER and LDAP_BIND_PWD are set do the following:
> bind to the catalog using LDAP_BIND_USER / LDAP_BIND_PWD, in our case
> dn=(cn=SVCLDAP, cn=Users, dc=asf, dc=madm, dc=net) and password "secret"
> start a search for the entered username from login screen, e.g. mailto:
> heiko(dot)onnebrink(at)metronom(dot)com using the existing base and filter vars ..
> in our case a search would start from (dc=madm, dc=net) and search for
> userPrincipalName=mailto:heiko(dot)onnebrink(at)metronom(dot)com
> this search should return us the DN of the user
> If the DN of the user could be retrieved make a bind using the found DN
> and the entered password from logon screen
> If success user auth is done.
>
> Currently pgAdmin does not support this kind of configuration but looks
> like a valid proposal, so I would suggest to log the feature request @
> https://redmine.postgresql.org/projects/pgadmin4
>
> Right now, users can login with the DN itself. For example,
> cn=user1,ou=users,dc=example,dc=com
> cn=user2,ou=users,dc=example,dc=com
> So, here BASE_DN would be ou=users,dc=example,dc=com and
> USERNAME_ATTRIBUTE would be cn. So, the user can login with user1 (input
> for the username field) and password.
>
> Thanks,
> Khushboo
> Thanks for your feedback
> Cheers
> Heiko
>
>
> From: Khushboo Vashi <mailto:khushboo(dot)vashi(at)enterprisedb(dot)com>
> Date: Wednesday, 6. May 2020 at 06:42
> To: "Onnebrink, Heiko" <mailto:heiko(dot)onnebrink(at)metronom(dot)com>
> Cc: "pgadmin-support http://lists.postgresql.org" <mailto:
> pgadmin-support(at)lists(dot)postgresql(dot)org>
> Subject: [EXT] Re: Problems to use LDAP again AD directory with disabled
> anonymous logon
>
> Hi,
>
> On Wed, May 6, 2020 at 12:57 AM <mailto:mailto:
> 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://http://ldap.mgi.de:389 -D "CN=SVCLDAP, CN=Users,
> DC=ASF, DC=madm, DC=net" -W -b "dc=R2, dc=madm,dc=net"
> "(userPrincipalName=mailto:mailto:heiko(dot)onnebrink(at)metronom(dot)com)"
> Enter LDAP Password: somepwd
>
> # extended LDIF
> #
> # LDAPv3
> # base <dc=madm,dc=net> with scope subtree
> # filter: (userPrincipalName=mailto:mailto:heiko(dot)onnebrink(at)metronom(dot)com)
> # requesting: ALL
> #
>
> # Onnebrink Heiko, HQ01-DUS, Users, DE, MSYS, http://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://http://ldap.mgi.de:389 -b "dc=R2, dc=madm,dc=net"
> "(userPrincipalName=mailto:mailto: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://http://ldap.mgi.de:389 -x -b "dc=R2,
> dc=madm,dc=net" "(userPrincipalName=mailto:mailto:
> heiko(dot)onnebrink(at)metronom(dot)com)"
> # extended LDIF
> #
> # LDAPv3
> # base <dc=R2, dc=madm,dc=net> with scope subtree
> # filter: (userPrincipalName=mailto:mailto: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://http://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=mailto:mailto:admin(at)metronom(dot)com
> http://registry.metroscales.io/rdb-dev/pgadmin:latest
>
> As per your ldapsearch (ldapsearch -H ldap://http://ldap.mgi.de:389 -D
> "CN=SVCLDAP, CN=Users, DC=ASF, DC=madm, DC=net" -W -b "dc=R2,
> dc=madm,dc=net" "(userPrincipalName=mailto:mailto:
> 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://http://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=mailto:mailto:
> 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)http://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 *(at)http://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
>
> 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)http://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 *(at)http://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
>
> 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
>