Thread: Problems to use LDAP again AD directory with disabled anonymous logon
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 ldapsearchtests 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.onnebrink@metronom.com)" Enter LDAP Password: somepwd # extended LDIF # # LDAPv3 # base <dc=madm,dc=net> with scope subtree # filter: (userPrincipalName=heiko.onnebrink@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.onnebrink@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.onnebrink@metronom.com)" # extended LDIF # # LDAPv3 # base <dc=R2, dc=madm,dc=net> with scope subtree # filter: (userPrincipalName=heiko.onnebrink@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=admin@metronom.com registry.metroscales.io/rdb-dev/pgadmin:latest 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) sothat an LDAP query can be executed that finds the logon user via his UPN. Once record is found we have the DN that canbe used to bind the user with his entered password to verify that password is valid. 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 LocalCourt, 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. Siekönnen rechtlich geschützte, vertrauliche Informationen enthalten. Falls Sie nicht der bezeichnete Empfänger oder zumEmpfang dieser E-Mail nicht berechtigt sind, ist die Verwendung, Vervielfältigung oder Weitergabe der Nachrichten undAnhänge untersagt. Falls Sie diese E-Mail irrtümlich erhalten haben, informieren Sie bitte unverzüglich den Absender undvernichten 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 informationwhich may also be protected by professional secrecy. Unless you are the named addressee (or authorised to receivefor 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
Hi,
On Wed, May 6, 2020 at 12:57 AM <heiko.onnebrink@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.onnebrink@metronom.com)"
Enter LDAP Password: somepwd
# extended LDIF
#
# LDAPv3
# base <dc=madm,dc=net> with scope subtree
# filter: (userPrincipalName=heiko.onnebrink@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.onnebrink@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.onnebrink@metronom.com)"
# extended LDIF
#
# LDAPv3
# base <dc=R2, dc=madm,dc=net> with scope subtree
# filter: (userPrincipalName=heiko.onnebrink@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=admin@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.onnebrink@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.onnebrink@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.
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
Hi I did as advised and get now this as response in UI {"success":0,"errormsg":"attribute type not present","info":"","result":null,"data":null} and here the error stack 2020-05-06 04:57:59,908: ERROR flask.app: attribute type not present Traceback (most recent call last): File "/usr/local/lib/python3.7/site-packages/flask/app.py", line 1813, in full_dispatch_request rv = self.dispatch_request() File "/usr/local/lib/python3.7/site-packages/flask/app.py", line 1799, in dispatch_request return self.view_functions[rule.endpoint](**req.view_args) File "/pgadmin4/pgadmin/authenticate/__init__.py", line 54, in login status, msg = auth_obj.authenticate() File "/pgadmin4/pgadmin/authenticate/__init__.py", line 117, in authenticate status, msg = source.authenticate(self.form) File "/pgadmin4/pgadmin/authenticate/ldap.py", line 49, in authenticate status, user_email = self.search_ldap_user() File "/pgadmin4/pgadmin/authenticate/ldap.py", line 170, in search_ldap_user attributes=ALL_ATTRIBUTES File "/usr/local/lib/python3.7/site-packages/ldap3/core/connection.py", line 765, in search search_base = safe_dn(search_base) File "/usr/local/lib/python3.7/site-packages/ldap3/utils/dn.py", line 353, in safe_dn for component in parse_dn(dn, escape=True): File "/usr/local/lib/python3.7/site-packages/ldap3/utils/dn.py", line 315, in parse_dn if not _validate_attribute_type(attribute_type): File "/usr/local/lib/python3.7/site-packages/ldap3/utils/dn.py", line 172, in _validate_attribute_type raise LDAPInvalidDnError('attribute type not present') ldap3.core.exceptions.LDAPInvalidDnError: attribute type not present Also one question: The user that will logon in our should be heiko.onnebrink@metronom.com, the user SVCLDAP is not used to work with pgAdmin.. so should the config not be swapped between these users ? cheers Heiko From: Khushboo Vashi <khushboo.vashi@enterprisedb.com> Date: Wednesday, 6. May 2020 at 06:42 To: "Onnebrink, Heiko" <heiko.onnebrink@metronom.com> Cc: "pgadmin-support lists.postgresql.org" <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:heiko.onnebrink@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 ldapsearchtests 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:heiko.onnebrink@metronom.com)" Enter LDAP Password: somepwd # extended LDIF # # LDAPv3 # base <dc=madm,dc=net> with scope subtree # filter: (userPrincipalName=mailto:heiko.onnebrink@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:heiko.onnebrink@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:heiko.onnebrink@metronom.com)" # extended LDIF # # LDAPv3 # base <dc=R2, dc=madm,dc=net> with scope subtree # filter: (userPrincipalName=mailto:heiko.onnebrink@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:admin@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:heiko.onnebrink@metronom.com)"), the pgAdmin LDAP parameters shouldbe 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:heiko.onnebrink@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) sothat an LDAP query can be executed that finds the logon user via his UPN. Once record is found we have the DN that canbe 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_DNand 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 LocalCourt, 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. Siekönnen rechtlich geschützte, vertrauliche Informationen enthalten. Falls Sie nicht der bezeichnete Empfänger oder zumEmpfang dieser E-Mail nicht berechtigt sind, ist die Verwendung, Vervielfältigung oder Weitergabe der Nachrichten undAnhänge untersagt. Falls Sie diese E-Mail irrtümlich erhalten haben, informieren Sie bitte unverzüglich den Absender undvernichten 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 informationwhich may also be protected by professional secrecy. Unless you are the named addressee (or authorised to receivefor 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 LocalCourt, 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. Siekönnen rechtlich geschützte, vertrauliche Informationen enthalten. Falls Sie nicht der bezeichnete Empfänger oder zumEmpfang dieser E-Mail nicht berechtigt sind, ist die Verwendung, Vervielfältigung oder Weitergabe der Nachrichten undAnhänge untersagt. Falls Sie diese E-Mail irrtümlich erhalten haben, informieren Sie bitte unverzüglich den Absender undvernichten 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 informationwhich may also be protected by professional secrecy. Unless you are the named addressee (or authorised to receivefor 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
Re: [EXT] Re: Problems to use LDAP again AD directory with disabledanonymous logon
From
Khushboo Vashi
Date:
Hi,
[Adding pgAdmin Support...]
On Wed, May 6, 2020 at 11:43 AM <heiko.onnebrink@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 heiko.onnebrink@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 other.user@metrosystems.ro
They will enter there UPN heiko.onnebrink@metronom.ccom or other.user@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. heiko.onnebrink@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=heiko.onnebrink@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 <khushboo.vashi@enterprisedb.com>
Date: Wednesday, 6. May 2020 at 06:42
To: "Onnebrink, Heiko" <heiko.onnebrink@metronom.com>
Cc: "pgadmin-support lists.postgresql.org" <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:heiko.onnebrink@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:heiko.onnebrink@metronom.com)"
Enter LDAP Password: somepwd
# extended LDIF
#
# LDAPv3
# base <dc=madm,dc=net> with scope subtree
# filter: (userPrincipalName=mailto:heiko.onnebrink@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:heiko.onnebrink@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:heiko.onnebrink@metronom.com)"
# extended LDIF
#
# LDAPv3
# base <dc=R2, dc=madm,dc=net> with scope subtree
# filter: (userPrincipalName=mailto:heiko.onnebrink@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:admin@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:heiko.onnebrink@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:heiko.onnebrink@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 *@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
Created feature request for this issue https://redmine.postgresql.org/issues/5484 with details proposal how to enhance it Hope someone will grab the request as LDAP support is a really long awaited feature and the request would complete the LDAPimplementation. Unfortunately I have no Python skill .. otherwise I would have pushed a PR __ cheers Heiko From: Khushboo Vashi <khushboo.vashi@enterprisedb.com> Date: Wednesday, 6. May 2020 at 10:50 To: "Onnebrink, Heiko" <heiko.onnebrink@metronom.com> Cc: pgAdmin Support <pgadmin-support@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.onnebrink@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.onnebrink@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.user@metrosystems.ro They will enter there UPN heiko.onnebrink@metronom.ccom or mailto:other.user@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 ifrequired. 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 usedto 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.onnebrink@metronom.com using the existing baseand filter vars .. in our case a search would start from (dc=madm, dc=net) and search for userPrincipalName=mailto:heiko.onnebrink@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 logthe 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 (inputfor the username field) and password. Thanks, Khushboo Thanks for your feedback Cheers Heiko From: Khushboo Vashi <mailto:khushboo.vashi@enterprisedb.com> Date: Wednesday, 6. May 2020 at 06:42 To: "Onnebrink, Heiko" <mailto:heiko.onnebrink@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.onnebrink@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 ldapsearchtests 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.onnebrink@metronom.com)" Enter LDAP Password: somepwd # extended LDIF # # LDAPv3 # base <dc=madm,dc=net> with scope subtree # filter: (userPrincipalName=mailto:mailto:heiko.onnebrink@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.onnebrink@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.onnebrink@metronom.com)" # extended LDIF # # LDAPv3 # base <dc=R2, dc=madm,dc=net> with scope subtree # filter: (userPrincipalName=mailto:mailto:heiko.onnebrink@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:admin@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.onnebrink@metronom.com)"), the pgAdmin LDAP parametersshould 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.onnebrink@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) sothat an LDAP query can be executed that finds the logon user via his UPN. Once record is found we have the DN that canbe 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_DNand 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 LocalCourt, 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. Siekönnen rechtlich geschützte, vertrauliche Informationen enthalten. Falls Sie nicht der bezeichnete Empfänger oder zumEmpfang dieser E-Mail nicht berechtigt sind, ist die Verwendung, Vervielfältigung oder Weitergabe der Nachrichten undAnhänge untersagt. Falls Sie diese E-Mail irrtümlich erhalten haben, informieren Sie bitte unverzüglich den Absender undvernichten 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 informationwhich may also be protected by professional secrecy. Unless you are the named addressee (or authorised to receivefor 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 LocalCourt, 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. Siekönnen rechtlich geschützte, vertrauliche Informationen enthalten. Falls Sie nicht der bezeichnete Empfänger oder zumEmpfang dieser E-Mail nicht berechtigt sind, ist die Verwendung, Vervielfältigung oder Weitergabe der Nachrichten undAnhänge untersagt. Falls Sie diese E-Mail irrtümlich erhalten haben, informieren Sie bitte unverzüglich den Absender undvernichten 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 informationwhich may also be protected by professional secrecy. Unless you are the named addressee (or authorised to receivefor 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 LocalCourt, 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. Siekönnen rechtlich geschützte, vertrauliche Informationen enthalten. Falls Sie nicht der bezeichnete Empfänger oder zumEmpfang dieser E-Mail nicht berechtigt sind, ist die Verwendung, Vervielfältigung oder Weitergabe der Nachrichten undAnhänge untersagt. Falls Sie diese E-Mail irrtümlich erhalten haben, informieren Sie bitte unverzüglich den Absender undvernichten 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 informationwhich may also be protected by professional secrecy. Unless you are the named addressee (or authorised to receivefor 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
Re: [EXT] Re: Problems to use LDAP again AD directory with disabledanonymous logon
From
Khushboo Vashi
Date:
Hi,
On Wed, May 6, 2020 at 2:53 PM <heiko.onnebrink@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.vashi@enterprisedb.com>
Date: Wednesday, 6. May 2020 at 10:50
To: "Onnebrink, Heiko" <heiko.onnebrink@metronom.com>
Cc: pgAdmin Support <pgadmin-support@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.onnebrink@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.onnebrink@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.user@metrosystems.ro
They will enter there UPN heiko.onnebrink@metronom.ccom or mailto:other.user@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.onnebrink@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.onnebrink@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.vashi@enterprisedb.com>
Date: Wednesday, 6. May 2020 at 06:42
To: "Onnebrink, Heiko" <mailto:heiko.onnebrink@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.onnebrink@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.onnebrink@metronom.com)"
Enter LDAP Password: somepwd
# extended LDIF
#
# LDAPv3
# base <dc=madm,dc=net> with scope subtree
# filter: (userPrincipalName=mailto:mailto:heiko.onnebrink@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.onnebrink@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.onnebrink@metronom.com)"
# extended LDIF
#
# LDAPv3
# base <dc=R2, dc=madm,dc=net> with scope subtree
# filter: (userPrincipalName=mailto:mailto:heiko.onnebrink@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:admin@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.onnebrink@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.onnebrink@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