Re: Hosting pgAdmin4 with Apache/WSGI

Hi Dave,

Regarding User management related issue RM2641
<https://redmine.postgresql.org/issues/2641> is already logged and also I
have sent patch for the same.

--
*Harshal Dhumal*
*Sr. Software Engineer*

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

On Mon, Sep 18, 2017 at 1:34 PM, Dave Page <dpage(at)pgadmin(dot)org> wrote:

>
>
> 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/py
>>> 27/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/python2.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
>