Re: Hosting pgAdmin4 with Apache/WSGI

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

>
>
> 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)?
>

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(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
>>>>
>>>
>>>
>>
>

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

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