Re: [EXT] Re: pgadmin4 container deployment with ldap-authentication

So after installing pgadmin4 from .deb-file and trying out several
configurations, i found out a working one, but with a
file-permission-issue. Because i didn't try out which specific file made
the problems, i chown-ed all pgadmin4/web/*-files to www:data:www-data,
since pgadmin4 from the .deb-file is hosted by apache2. So finally i got
it working.

After that i adapted my docker-parameters to

> docker run -p 8280:80
    -e "PGADMIN_DEFAULT_EMAIL=<emaiAddress>"
    -e "PGADMIN_DEFAULT_PASSWORD=<password>"
    -e "PGADMIN_CONFIG_AUTHENTICATION_SOURCES=['ldap']"
    -e "PGADMIN_CONFIG_LDAP_AUTO_CREATE_USER=True"
    -e "PGADMIN_CONFIG_LDAP_SERVER_URI='ldaps://dc.mydomain.local:636'"
    -e "PGADMIN_CONFIG_LDAP_BASE_DN='cn=Users,dc=mydomain,dc=local'"
    -e "PGADMIN_CONFIG_LDAP_USE_STARTTLS=True"
    -e
"PGADMIN_CONFIG_LDAP_BIND_USER='cn=user,cn=Users,dc=mydomain,dc=local'"
    -e "PGADMIN_CONFIG_LDAP_BIND_PASSWORD='<bind-password>'"
    -e "PGADMIN_CONFIG_LDAP_CA_CERT_FILE='/certs/ca.crt'"
    -e "PGADMIN_CONFIG_LDAP_CERT_FILE='/certs/client.crt'"
    -e "PGADMIN_CONFIG_LDAP_KEY_FILE='/private/client.key'"
    -e "PGADMIN_CONFIG_LDAP_USERNAME_ATTRIBUTE='sAMAccountName'"
    -e
"PGADMIN_CONFIG_LDAP_SEARCH_BASE_DN='cn=Users,dc=mydomain,dc=local'"
    -v '/local/path/to/ca.crt:/certs/ca.crt'
    -v '/local/path/to/client.crt:/certs/client.crt'
    -v '/local/path/to/client.key:/private/client.key'
    -d <my docker-image>

As expected, i got the same file-permission-issue. So i interactively
entered the docker-session with

> docker exec -it -u 0 <container id> /bin/sh

and did

> chown -R pgadmin:pgadmin /pgadmin4/*

since the process is running under user pgadmin and

> chmod 644 /private/*

> chmod 644 /certs/*

as a quickfix. So i finally got it working, so that i can login with a
valid sAMAccountName and password. It would be great if you would fix
that file-permission-issues in the image as well as in the .deb-file.

Due to the issue, i got

LDAPSocketOpenError socket ssl wrapping error: [Errno 13] Permission denied

before.

Best regards,

Hendrik Hansmeier

Hendrik Hansmeier IT-Consulting ::: Bunsenstraße 5 ::: 51647 Gummersbach
FON +49 (0) 2261 814 174 ::: MOB +49 (0) 151 235 866 02 ::: E-MAIL
hendrik(dot)hansmeier(at)hh-it(dot)co
USt-IdNr.: DE311717013 ::: Finanzamt Gummersbach

Am 11.08.20 um 15:09 schrieb Khushboo Vashi:
>
>
> On Tue, Aug 11, 2020 at 6:26 PM <heiko(dot)onnebrink(at)metronom(dot)com
> <mailto:heiko(dot)onnebrink(at)metronom(dot)com>> wrote:
>
> Can you confirm that the parameter that I pass to docker are
> (syntactical) correct to properly filter for the requested user
> record.
>
> They are correct except PGADMIN_CONFIG_LDAP_USERNAME_ATTRIBUTE should
> be "*cn"*
>
> As we should not timeout once we properly filter by
> userPrincipalName I want to be sure that filtering is properly
> passed to the LDAP quey.
>
> If you want to filter by  userPrincipalName then use
> LDAP_SEARCH_FILTER option.
> PGADMIN_CONFIG_LDAP_SEARCH_FILTER="xxxxx"
>
> *From: *Khushboo Vashi <khushboo(dot)vashi(at)enterprisedb(dot)com
> <mailto:khushboo(dot)vashi(at)enterprisedb(dot)com>>
> *Date: *Tuesday, 11. August 2020 at 14:36
> *To: *"Onnebrink, Heiko" <heiko(dot)onnebrink(at)metronom(dot)com
> <mailto:heiko(dot)onnebrink(at)metronom(dot)com>>
> *Cc: *"pgadmin-support lists.postgresql.org
> <http://lists.postgresql.org>"
> <pgadmin-support(at)lists(dot)postgresql(dot)org
> <mailto:pgadmin-support(at)lists(dot)postgresql(dot)org>>, Hendrik Hansmeier
> <hendrik(dot)hansmeier(at)hh-it(dot)co <mailto:hendrik(dot)hansmeier(at)hh-it(dot)co>>
> *Subject: *Re: [EXT] Re: pgadmin4 container deployment with
> ldap-authentication
>
> Hi,
>
> On Tue, Aug 11, 2020 at 4:29 PM <heiko(dot)onnebrink(at)metronom(dot)com
> <mailto:heiko(dot)onnebrink(at)metronom(dot)com>> wrote:
>
> Hi,
> I am just back from holiday and wanted to test the same (as I
> authored this LDAP change request I think its overdue to test
> it __ ))
>
> To ensure the env is fine I executed ldapsearch on the docker
> host to have some check first:
>
> ldapsearch -LLL -x -h ldap.mgi.de:389 <http://ldap.mgi.de:389>
> -D "cn=SVCLDAP,cn=Users,dc=asf,dc=madm,dc=net" -w xxxxxx
> -b"dc=madm,dc=net" 
> userPrincipalName=Heiko(dot)Onnebrink(at)metronom(dot)com
> <mailto:Heiko(dot)Onnebrink(at)metronom(dot)com>
>
> I got some fine output back within some ms:
>
> dn: CN=Onnebrink
> Heiko,OU=HQ01-DUS,OU=Users,OU=DE,OU=MSYS,DC=r2,DC=madm,DC=netobjectClass:
> topobjectClass: person
> objectClass: organizationalPerson
> objectClass: user
> cn: Onnebrink Heiko
> sn: Onnebrink
> c: DE
> l: Duesseldorf
> title: Mr
> description: XPC User (migriert) - managed by identityDirectory
> postalCode: 40235
> physicalDeliveryOfficeName: 09.02.207
> etc (truncated)
>
> Next I transferred the args from test and passed them to
> pgBadger docker container
>
> docker run -p 443:443
> -e PGADMIN_DEFAULT_EMAIL=admin(at)metronom(dot)com
> <mailto:admin(at)metronom(dot)com>
> -e PGADMIN_DEFAULT_PASSWORD=admin
> -e 'PGADMIN_CONFIG_AUTHENTICATION_SOURCES=["ldap"]'
> -e 'PGADMIN_CONFIG_LDAP_SERVER_URI="ldap://ldap.mgi.de:389
> <http://ldap.mgi.de:389>"'
> -e 'PGADMIN_CONFIG_LDAP_USERNAME_ATTRIBUTE="userPrincipalName"'
> -e
> 'PGADMIN_CONFIG_LDAP_BIND_USER="cn=SVCLDAP,cn=Users,dc=asf,dc=madm,dc=net"'
>
> -e 'PGADMIN_CONFIG_LDAP_BIND_PASSWORD="xxxxxx"'
> -e 'PGADMIN_CONFIG_LDAP_SEARCH_BASE_DN="dc=madm,dc=net"'
> -e PGADMIN_CONFIG_LDAP_AUTO_CREATE_USER=True
> -e PGADMIN_ENABLE_TLS=TRUE
> -v '/dockerdata/pgadmin/servers.json:/servers.json'
> -v '/dockerdata/pgadmin/server.cert:/certs/server.cert'
> -v '/dockerdata/pgadmin/server.key:/certs/server.key'
> --name pgadminssl
> registry.metroscales.io/rdb-dev/pgadmin:latest
> <http://registry.metroscales.io/rdb-dev/pgadmin:latest>
> NOTE: Configuring authentication for SERVER mode.
>
> sudo: setrlimit(RLIMIT_CORE): Operation not permitted
> [2020-08-11 10:45:49 +0000] [1] [INFO] Starting gunicorn 19.9.0
> [2020-08-11 10:45:49 +0000] [1] [INFO] Listening at:
> http://[::]:443 (1)
> [2020-08-11 10:45:49 +0000] [1] [INFO] Using worker: threads
> /usr/local/lib/python3.8/os.py:1023: RuntimeWarning: line
> buffering (buffering=1) isn't supported in binary mode, the
> default buffer size will be used
>   return io.open(fd, *args, **kwargs)
> [2020-08-11 10:45:49 +0000] [97] [INFO] Booting worker with
> pid: 97
>
> I started up pgAdmin web and entered
> heiko(dot)onnebrink(at)metronom(dot)com
> <mailto:heiko(dot)onnebrink(at)metronom(dot)com> with pwd as credentials
>
> After logon a new window pops up with this Json result
> {
>  success:0,
>  result:null,
>  info:"",
>  data:null,
>  errormsg:"error receiving data: timed out"
>  }
>
> Here the error stack from pgAdmin container:
>
> ::ffff:10.97.177.148 - - [11/Aug/2020:10:49:02 +0000] "GET /
> HTTP/1.1" 302 237 "-" "Mozilla/5.0 (Macintosh; Intel Mac OS X
> 10_15_5) AppleWebKit/605.1.15 (KHTML, like Gecko)
> Version/13.1.1 Safari/605.1.15"
> ::ffff:10.97.177.148 - - [11/Aug/2020:10:49:02 +0000] "GET
> /login?next=%2F HTTP/1.1" 200 1698 "-" "Mozilla/5.0
> (Macintosh; Intel Mac OS X 10_15_5) AppleWebKit/605.1.15
> (KHTML, like Gecko) Version/13.1.1 Safari/605.1.15"
> 2020-08-11 10:49:27,835: ERROR  flask.app: error receiving
> data: timed out
> Traceback (most recent call last):
>   File
> "/usr/local/lib/python3.8/site-packages/ldap3/strategy/sync.py",
> line 82, in receiving
>     data = self.connection.socket.recv(self.socket_size)
> socket.timeout: timed out
>
> During handling of the above exception, another exception
> occurred:
>
> Traceback (most recent call last):
>   File "/usr/local/lib/python3.8/site-packages/flask/app.py",
> line 1813, in full_dispatch_request
>     rv = self.dispatch_request()
>   File "/usr/local/lib/python3.8/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 55,
> in login
>     status, msg = auth_obj.authenticate()
>   File "/pgadmin4/pgadmin/authenticate/__init__.py", line 118,
> in authenticate
>     status, msg = source.authenticate(self.form)
>   File "/pgadmin4/pgadmin/authenticate/ldap.py", line 73, in
> authenticate
>     status, ldap_user = self.search_ldap_user()
>   File "/pgadmin4/pgadmin/authenticate/ldap.py", line 228, in
> search_ldap_user
>     self.conn.search(search_base=search_base_dn,
>   File
> "/usr/local/lib/python3.8/site-packages/ldap3/core/connection.py",
> line 819, in search
>     response =
> self.post_send_search(self.send('searchRequest', request,
> controls))
>   File
> "/usr/local/lib/python3.8/site-packages/ldap3/strategy/sync.py",
> line 139, in post_send_search
>     responses, result = self.get_response(message_id)
>   File
> "/usr/local/lib/python3.8/site-packages/ldap3/strategy/base.py",
> line 353, in get_response
>     responses = self._get_response(message_id, timeout)
>   File
> "/usr/local/lib/python3.8/site-packages/ldap3/strategy/sync.py",
> line 157, in _get_response
>     responses = self.receiving()
>   File
> "/usr/local/lib/python3.8/site-packages/ldap3/strategy/sync.py",
> line 92, in receiving
>     raise
> communication_exception_factory(LDAPSocketReceiveError,
> type(e)(str(e)))(self.connection.last_error)
> ldap3.core.exceptions.LDAPSocketReceiveError: error receiving
> data: timed out
> ::ffff:10.97.177.148 - - [11/Aug/2020:10:49:27 +0000] "POST
> /authenticate/login HTTP/1.1" 500 94
> "https://10.96.48.68/login?next=%2F" "Mozilla/5.0 (Macintosh;
> Intel Mac OS X 10_15_5) AppleWebKit/605.1.15 (KHTML, like
> Gecko) Version/13.1.1 Safari/605.1.15"
>
> Looking at the error (receiving data timed out), I think we need
> to provide the configuration option to set the /"Receive Timeout"
> / parameter.
>
> Can you please log this issue @
> https://redmine.postgresql.org/projects/pgadmin4 , so we can fix
> and track it ?
>
> Thanks for the testing.
>
> Thanks,
>
> Khushboo
>
> Thanks for any advice
> cheers
> Heiko
>
> From: Khushboo Vashi <khushboo(dot)vashi(at)enterprisedb(dot)com
> <mailto:khushboo(dot)vashi(at)enterprisedb(dot)com>>
> Date: Tuesday, 11. August 2020 at 06:09
> To: Hendrik Hansmeier <hendrik(dot)hansmeier(at)hh-it(dot)co
> <mailto:hendrik(dot)hansmeier(at)hh-it(dot)co>>
> Cc: "pgadmin-support lists.postgresql.org
> <http://lists.postgresql.org>"
> <pgadmin-support(at)lists(dot)postgresql(dot)org
> <mailto:pgadmin-support(at)lists(dot)postgresql(dot)org>>
> Subject: [EXT] Re: pgadmin4 container deployment with
> ldap-authentication
>
> Hi,
>
>
> On Tue, Aug 11, 2020 at 4:35 AM Hendrik Hansmeier
> <mailto:hendrik(dot)hansmeier(at)hh-it(dot)co
> <mailto:hendrik(dot)hansmeier(at)hh-it(dot)co>> wrote:
> Hi,
> i am trying to get pgadmin4 running in server-mode as a
> docker-container. So i pulled the image and after i tried out
> the image a little bit, i tried to use ldap-authentication.
> Unfortunately, i didn't get it running as expected. I am not
> able to authenticate against my samba 4-domain. This is how i
> tried to launch the container:
>
> docker run -p 8280:80
>     -e "PGADMIN_DEFAULT_EMAIL=<emailAddress>"
>     -e "PGADMIN_DEFAULT_PASSWORD=<password>"
>     -e "AUTHENTICATION_SOURCES=['ldap']"
>     -e "LDAP_AUTO_CREATE_USER=True"
>     -e "LDAP_SERVER_URI='ldaps://<domaincontroller>:636'"
>     -e "LDAP_BASE_DN='cn=Users,dc=mydomain,dc=local'"
>     -e "LDAP_BIND_USER='cn=User1,cn=Users,dc=mydomain,dc=local'"
>     -e "LDAP_BIND_PASSWORD=<BindDNPassword>"
>     -e "LDAP_CA_CERT_FILE='/etc/ssl/certs/myca.pem'"
>     -e "LDAP_CERT_FILE='/etc/ssl/certs/my.cert.pem'"
>     -e "LDAP_KEY_FILE='/etc/ssl/private/my.key.pem'"
>     -d dpage/pgadmin4
>
> I am using the container behind a reverse-proxy on nginx
> (debian buster), for the first try via http. The
> authentication with the given user PGADMIN_DEFAULT_EMAIL works
> as expected but ldap-authentication results in an
> error-message "Specified user does not exist".
> Am i using the environment-parameters for ldap-authentication
> correctly? May a reverse-proxy over https help to get ldaps
> working?
> The variable prefix "PGADMIN_CONFIG_"  should be used to
> override any of the configuration options in
> pgAdmin’s config.py file. So add this prefix to all the config
> params you have used.
> (Ex, AUTHENTICATION_SOURCES, LDAP_SERVER_URI etc...)
>
> Ex:  AUTHENTICATION_SOURCES should
> be PGADMIN_CONFIG_AUTHENTICATION_SOURCES
> Please refer
> https://www.pgadmin.org/docs/pgadmin4/4.24/container_deployment.html#environment-variables for
> more information.
>
> Also, set LDAP_SEARCH_BASE_DN param which is required to
> configure LDAP Authentication in Dedicated User mode (which
> you have configured).
> Please refer
> https://www.pgadmin.org/docs/pgadmin4/4.24/enabling_ldap_authentication.html
>
> Thanks,
> Khushboo
>
> --
> Best regards,
>
> Hendrik Hansmeier
>
>
> Hendrik Hansmeier IT-Consulting ::: Bunsenstraße 5 ::: 51647
> Gummersbach
> FON +49 (0) 2261 814 174 ::: MOB +49 (0) 151 235 866 02 :::
> E-MAIL mailto:hendrik(dot)hansmeier(at)hh-it(dot)co
> <mailto:hendrik(dot)hansmeier(at)hh-it(dot)co>
> USt-IdNr.: DE311717013 ::: Finanzamt Gummersbach
>
> 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://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)metronom(dot)com <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
>