Hi, On Wed, May 6, 2020 at 2:53 PM <heiko.onnebr...@metronom.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.va...@enterprisedb.com> > Date: Wednesday, 6. May 2020 at 10:50 > To: "Onnebrink, Heiko" <heiko.onnebr...@metronom.com> > Cc: pgAdmin Support <pgadmin-supp...@postgresql.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.onnebr...@metronom.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.onnebr...@metronom.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.u...@metrosystems.ro > > They will enter there UPN heiko.onnebr...@metronom.ccom or mailto: > other.u...@metrosystems.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.onnebr...@metronom.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.onnebr...@metronom.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.va...@enterprisedb.com> > Date: Wednesday, 6. May 2020 at 06:42 > To: "Onnebrink, Heiko" <mailto:heiko.onnebr...@metronom.com> > Cc: "pgadmin-support http://lists.postgresql.org"; <mailto: > pgadmin-support@lists.postgresql.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.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://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.onnebr...@metronom.com)" > Enter LDAP Password: somepwd > > # extended LDIF > # > # LDAPv3 > # base <dc=madm,dc=net> with scope subtree > # filter: (userPrincipalName=mailto:mailto:heiko.onnebr...@metronom.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.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://http://ldap.mgi.de:389 -x -b "dc=R2, > dc=madm,dc=net" "(userPrincipalName=mailto:mailto: > heiko.onnebr...@metronom.com)" > # extended LDIF > # > # LDAPv3 > # base <dc=R2, dc=madm,dc=net> with scope subtree > # filter: (userPrincipalName=mailto:mailto: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://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:ad...@metronom.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.onnebr...@metronom.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.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 *@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 *@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 *@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 *@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 *@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 >
