Re: Hosting pgAdmin4 with Apache/WSGI

On Sat, Sep 16, 2017 at 3:14 PM, Gabriel Sánchez <gabrielesanchez@gmail.com> wrote:

On Sat, Sep 16, 2017 at 6:47 AM, Surinder Kumar <surinder.kumar@enterprisedb.com> wrote:

Hi

On Sat, Sep 16, 2017 at 1:31 AM, Gabriel Sánchez <gabrielesanchez@gmail.com> wrote:

On Thu, Sep 14, 2017 at 2:33 PM, Dave Page <dpage@pgadmin.org> wrote:

On Thu, Sep 14, 2017 at 5:59 PM, Mike Surcouf <mikes@surcouf.co.uk> wrote:

I share your pain.

I do have it working but as a root app and it took me a while to work through the docs and fill in the blanks.

I think Dave was looking at making server install much easier but not sure if anything was implemented in the next version.

 

It has been, and the docs are updated on the website and in the test builds.

 

Regarding issue #2 running the app as a subdirectory this was broken in 1.6

As you have found the docs won’t give you a working solution.

You can work around this by running the app in the root of apache but that’s not a great solution.

I am waiting on 1.7 to fix this but there is no info on release as of yet.

https://developer.pgadmin.org/~dpage/2.0-rc1/

I'm probably not going to release that as there have been a few fixes since then, but that build includes the "server mode out of the box" config (though, if using the wheel or source, you will need to create /var/log/pgadmin and /var/lib/pgadmin and change the owner to whoever you run the server as), and webpacking of all the code which makes it *much* faster.

 

Thanks.  I installed RC1 in as before (in a virtual environment and with the modification on pgadmin.wsgi to activate the virtual environment (see Issue #1 in the original post)), and I'm trying to run it on the web root, but I'm getting the following in the apache error log:

[mod_wsgi (pid=12596): Target WSGI script '/opt/pgadmin4_2/virtualenv/lib/python2.7/site-packages/pgadmin4/pgAdmin4.wsgi' cannot be loaded as Python module.

mod_wsgi (pid=12596): Exception occurred processing WSGI script '/opt/pgadmin4_2/virtualenv/lib/python2.7/site-packages/pgadmin4/pgAdmin4.wsgi'.

 Traceback (most recent call last):

   File "/opt/pgadmin4_2/virtualenv/lib/python2.7/site-packages/pgadmin4/pgAdmin4.wsgi", line 20, in <module>

     import config

   File "/opt/pgadmin4_2/virtualenv/lib/python2.7/site-packages/pgadmin4/config.py", line 118, in <module>

     if builtins.SERVER_MODE is None:

 AttributeError: 'module' object has no attribute 'SERVER_MODE'

Any suggestions on what to try next?

That's an issue. To fix just add this snippet at line 117 in config.py

builtins.SERVER_MODE=builtins.SERVER_MODE if hasattr(builtins, 'SERVER_MODE') else None​

  

​

I will send a patch for this.

I also tried it without the modification to pgadmin.wsgi, but I was getting an error similar to before, with "ImportError: No module named flask_paranoid", and the activation of the virtual environment seems to have addressed that.  

Is the modification to pgadmin.wsgi to activate the virtual environment a good fix?  Or is there an option I could add in config_local.py with a path to the virtual environment?

​You don't need to modify pgAdmin4.wsgi, instead set the python-path to virtual env in your apache.conf file​

 

​like:

WSGIDaemonProcess pgadmin processes=1 threads=25 \

    python-path=/home/surinder/venv/py27/:/home/surinder/venv/py27/lib/python2.7/site-packages/

And, just to confirm, does v2 RC1 run from a subdirectory as well as on web root?

​Yes, It runs from subdirectory.

​For example: Sub-directory can be given like:

WSGIScriptAlias /pgadmin

​/sub-directory/​

/opt/pgadmin4/virtualenv/lib/python2.7/site-packages/pgadmin4/pgAdmin4.wsgi

Got it working with your help!  Thanks very much!  It looks great!

 

​

If you still having any issue, let us know.

Three things:

1.  I am unable to add another pgAdmin user.  The dialog opens, and I can type in the e-mail, password, and confirm-password, but the role drop-down is empty and disabled, and there is no way to save a new user.

2. Playing around with this, I set myself to user instead of administrator, and now I can't switch myself back to administrator.  Is there a recommended way to modify the user database with shell access?  I'd use this to switch myself back to administrator and/or get around not being able to add a user with the GUI.  (For now I'll clear the DB file and rerun setup.py, but I'd like to have a way that doesn't erase the server definitions or other users that are working well.)

3.  I'm still wondering about issue #3 in my original post.  The documentation says that on the dialog to add a server "On Unix based systems, the address field may be left blank to use the default PostgreSQL Unix Domain Socket on the local machine", but the dialog forces me to enter either a host name/address on the Connection tab or a host address on the Advanced tab.  These seem to trigger host rules for authentication in pg_hba.  Since I have peer authentication enabled for localhost, with psql I don't need to enter a password.  Is there a way to use the unix socket (local rules for authentication in pg_hba)?

Surinder, please look into these issues ASAP: on 1 & 2, obviously it should be possible to add users, and it should be impossible to change the role of the current user to a non-administrator.

Thanks.

 

 

 

From: Gabriel Sánchez [mailto:gabrielesanchez@gmail.com]
Sent: 14 September 2017 17:32
To: pgadmin-support@postgresql.org
Subject: Hosting pgAdmin4 with Apache/WSGI

 

Hi team,

 

Thanks for the hard work you've put in to develop pgAdmin4 and provide support to the community.  I'm writing to ask for your help to set up pgAdmin4 in server mode under Apache with WSGI.  I'm running Ubuntu 16.04 Server and installing pgAdmin4 version 1.6 with Python 2.7.12.  A couple of issues are getting in the way.

 

Issue #1  Installation

 

I struggled to install pgAdmin4 with the online documentation, being unfamiliar with Python and WSGI.  I followed the instructions on the pgAdmin website but I was not getting the pgAdmin page on my browser.  

 

I turned on debug logging in apache and was getting the following (details redacted):

 

####

mod_wsgi (pid): Target WSGI script '/opt/pgadmin4/virtualenv/lib/python2.7/site-packages/pgadmin4/pgAdmin4.wsgi' cannot be loaded as Python module.

mod_wsgi (pid): Exception occurred processing WSGI script '/opt/pgadmin4/virtualenv/lib/python2.7/site-packages/pgadmin4/pgAdmin4.wsgi'.

Traceback (most recent call last):

File "/opt/pgadmin4/virtualenv/lib/python2.7/site-packages/pgadmin4/pgAdmin4.wsgi", line 17, in <module>

     import config

   File "/opt/pgadmin4/virtualenv/lib/python2.7/site-packages/pgadmin4/config.py", line 24, in <module>

     from pgadmin.utils import env, IS_PY2, IS_WIN, fs_short_path

   File "/opt/pgadmin4/virtualenv/lib/python2.7/site-packages/pgadmin4/pgadmin/__init__.py", line 17, in <module>

     from flask import Flask, abort, request, current_app, session

 ImportError: No module named flask

####

 

But flask *was* installed in the virtual environment!  I also ran setup.py within the virtual environment.  After hours of reading forum and mailing list posts, scanning error logs, and trying things, I was able to get to the pgAdmin4 landing page.  As best I can tell, the issue was that I installed pgAdmin4 in a virtual environment, under /opt/pgadmin4/virtualenv, but apache2 wasn't using that virtual environment.  The solution was to add the following to pgAdmin4.wsgi right before "import config":

 

activate_this = '/opt/pgadmin4/virtualenv/bin/activate_this.py'

execfile(activate_this, dict(__file__=activate_this))

 

Is this the right solution, or is there a better fix to this?  Note that the download instructions of the website recommend installing pgAdmin inside a virtual environment (I had to learn what that was and how to set it up), and the Server Setup instructions in the documentation don't mention the need to add these lines. 

 

If this is the right solution, it would help to clarify in the documentation, and perhaps to add the hint to the error log.  I'm including this here hoping that others with the same problem will find this post and it will save them hours of troubleshooting.   A detailed walk-through in the documentation would've been super helpful.  If you'd like, I can share my setup as a walk-through for posting on the website once I get it all running well.  

 

 

Issue #2  Hosting within web subdirectory

 

I want to host pgAdmin within a web subdirectory, e.g. example.com/pgadmin, since the server has a website on root, e.g. example.com.  I have the following apache site configuration (details redacted):

 

<IfModule mod_ssl.c>

        <VirtualHost _default_:443>

                ServerName ***

                ServerAdmin ***

                DocumentRoot /var/www/html

                ErrorLog ${APACHE_LOG_DIR}/error.log

                CustomLog ${APACHE_LOG_DIR}/access.log combined

 

                SSLEngine on

                SSLCertificateFile      ***

                SSLCertificateKeyFile   ***

                SSLCACertificateFile ***

 

                WSGIDaemonProcess pgadmin processes=1 threads=25

                WSGIScriptAlias /pgadmin /opt/pgadmin4/virtualenv/lib/python2.7/site-packages/pgadmin4/pgAdmin4.wsgi

 

                <Directory /opt/pgadmin4/virtualenv/lib/python2.7/site-packages/pgadmin4>

                        WSGIProcessGroup pgadmin

                        WSGIApplicationGroup %{GLOBAL}

                        AllowOverride None

                        Require all granted

                </Directory>

                <Location "/pgadmin">

                        SSLVerifyClient require

                        SSLVerifyDepth 1

                </Location>

        </VirtualHost>

</IfModule>

 

This gets me the pgAdmin landing page, but nothing works.  Clicking on "Add New Server" does nothing.  Clicking on "Configure pgAdmin" opens up a dialog that says "Category is not selected."  Nothing shows up in the Browser.  

 

If I change the apache config to host under / instead of under /pgadmin, things do work.  I see the "Servers" node in the browser, and the Create Server dialog pops up when I click on "Add New Server".  I am able to connect to the database.  

 

What configuration changes are necessary to get pgAdmin to work within a subdirectory, e.g. /pgadmin?

 

 

Issue #3  Using peer authentication

 

Is there a way to use peer authentication without passwords?  I had to setup the connection without SSL and connecting to localhost, with a password.  I'm already protecting the landing page with client certificate authentication, and pgAdmin itself requires password authentication, so I figure another password isn't necessary.  (Right?)  The documentation says "On Unix based systems, the address field may be left blank to use the default PostgreSQL Unix Domain Socket on the local machine..." but pgAdmin is requiring me to enter a host name in the connection tab or a host address in the advanced tab.

 

Should I use SSL for traffic within the server (between the database and the client) if I'm not sharing the server with anyone?  

 

Any other configuration suggestions or tips are welcome.  

 

Thanks in advance for your help. 

 

 

 

Regards,

Gabriel

 

 

 

--

Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--

Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company