Re: Hosting pgAdmin4 with Apache/WSGI

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

> Hi
>
> On Sat, Sep 16, 2017 at 1:31 AM, Gabriel Sánchez <
> gabrielesanchez(at)gmail(dot)com> wrote:
>
>>
>> On Thu, Sep 14, 2017 at 2:33 PM, Dave Page <dpage(at)pgadmin(dot)org> wrote:
>>
>>>
>>>
>>> On Thu, Sep 14, 2017 at 5:59 PM, Mike Surcouf <mikes(at)surcouf(dot)co(dot)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/li
>> b/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/pgad
>> min4/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/pgadmin
> 4/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)?

>
>>
>>
>>>
>>>>
>>>> *From:* Gabriel Sánchez [mailto:gabrielesanchez(at)gmail(dot)com]
>>>> *Sent:* 14 September 2017 17:32
>>>> *To:* pgadmin-support(at)postgresql(dot)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/p
>>>> ython2.7/site-packages/pgadmin4/pgAdmin4.wsgi
>>>>
>>>>
>>>>
>>>> <Directory /opt/pgadmin4/virtualenv/lib/p
>>>> ython2.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
>>>
>>
>>
>