.. Copyright (C) 2018, 2019, 2021 The Meme Factory, Inc.
   http://www.karlpinc.com/


   This file is part of PGWUI_Server.
  
   This program is free software: you can redistribute it and/or
   modify it under the terms of the GNU Affero General Public License
   as published by the Free Software Foundation, either version 3 of
   the License, or (at your option) any later version.

   This program is distributed in the hope that it will be useful, but
   WITHOUT ANY WARRANTY; without even the implied warranty of
   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
   Affero General Public License for more details.

   You should have received a copy of the GNU Affero General Public
   License along with this program.  If not, see
   <http://www.gnu.org/licenses/>.

   Karl O. Pinc <kop@karlpinc.com>

.. #End Of Copyright Marker#

PGWUI_Server
============

Short Documentation
-------------------

PGWUI_Server contains what's needed to put PGWUI components on the
web.  It should be installed by most PGWUI users.  The alternative to
installing PGWUI_Server is writing your own `Pyramid`_ application.

PGWUI_Server stands for `PostgreSQL`_ Web User Interface, Server support.

PGWUI web pages can be safely accessed by a browser running on the
local machine by installing ``waitress``, a simple, `pip`_
installable, `WSGI`_ webserver, alongside PGWUI_Server.  PGWUI_Server
provides the `WSGI`_ interface which connects PGWUI's Python packages
to any `WSGI`_ capable webserver.

Waitress is for those who want something simple and stand-alone.  More
advanced users may take advantage of a more full-featured webserver or
reverse proxy like `Apache`_, `Nginx`_, `Hitch`_ or `HAProxy`_.
Connections to PGWUI_Server can be direct, via WSGI, or via reverse
proxy to waitress or similar.

PGWUI_Server does not support HTTPS out-of-the-box.  HTTPS is often
essential for security.  PGWUI_Server is designed to stand behind a
reverse web proxy or an advanced WSGI web server which does support
HTTPS.

PGWUI_Server supports configuration using a traditional ``.ini`` file.


About PGWUI
-----------

The PGWUI package suite is a Python framework for creating
`PostgreSQL`_ web user interfaces, and a collection of user interfaces
built using the framework..  The focus is on interfaces supporting
bulk data upload or download, on execution of batches of arbitrary
SQL, and on transactions.  A distinguishing feature is an emphasis on
discovering and reporting as many errors as is possible per bulk
interaction with the database.


Installation
------------

Install into a Python 3 virtual environment with commands like::

  # Create virtual environment
  virtualenv -p python3 pgwui_venv
  # Install PGWUI web server support
  pgwui_venv/bin/pip install pgwui_server
  # Install waitress web server (see below))
  pgwui_venv/bin/pip install waitress
  # Install desired PGWUI components
  pgwui_venv/bin/pip install pgwui_logout
  pgwui_venv/bin/pip install pgwui_upload


Startup and Usage
-----------------

PGWUI_Server comes with an example configuration which connects it to
the ``waitress`` `WSGI`_ webserver and configures waitress.  The
simplest approach is to install and use waitress as the application's
WSGI server.

Waitress is not a requirement and is not automatically installed.  To
use another WSGI capable webserver modify the appropriate sections of
the supplied configuration file using the comments written into the
file as a guide.

Use ``pserve`` to start PGWUI and the stand-alone WSGI webserver you
installed::

  pgwui_venv/bin/pserve /etc/pgwui.ini

The installation can then be accessed via URLs similar to::

  http://localhost:6543/logout

Automating startup at boot time is OS dependent.  An example systemd
configuration is provided with PGWUI_Server.


Troubleshooting
^^^^^^^^^^^^^^^

Minimal error reporting is done on stderr to help diagnose startup
failure.  Full error reporting is done via logging.  When there are
problems it is important to check the log and the logging
configuration.


Putting PGWUI on the Internet
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

The default configuration does not expose the website to the network.
Of course this can be reconfigured in the .ini file.  Although it will
work, using unsecured communications and exposing pserve/waitress to
the Internet, or even the local network, is not recommended.  Use a
reverse web proxy to talk with PGWUI_Server instead, or configure your
webserver of choice to talk with PGWUI_Server via WSGI.

PGWUI_Server does not secure network communications in *any* way.  A
connection between the PGWUI_Server application and a full-featured
webserver is required to layer HTTPS with TLS encryption over
PGWUI_Server's network traffic.  This layering is necessary to provide
privacy, identity validation, and data integrity.

A full-featured webserver or web proxy is also necessary for SNI/HTTP2
support.

Some popular projects providing security and other advanced webserving
features:

`Nginx`_
  A webserver/reverse proxy with modern configuration
`Apache`_
  A traditional webserver/reverse proxy
`Hitch`_
  A lightweight reverse proxy
`HAProxy`_
  A full-featured reverse proxy

This document gives minimal guidance in connecting PGWUI_Server to a
full-featured webserver.  There is no difference between connecting
PGWUI_Server to a webserver and connecting any other WSGI
application. And there is no difference between reverse-proxying to
PGWUI_Server and reverse-proxying to any other web-based application.
Details will vary based on the full-featured webserver selected.  Any
feature-full webserver will work.  Additional guidance in obtaining
suitable SSL certificates, using them on a webserver, WSGI
connectivity, reverse web proxying, webserver configuration, securing
web traffic with HTTPS, and many other web related system
administration basics and network security essentials, are beyond the
scope of this document.

`Nginx`_ is often the simplest, and best, choice for a secure
Internet-facing, reverse proxy, web server.  A simple but runnable
nginx configuration, without HTTPS support, is provided with
PGWUI_Server.

A useful `Nginx`_ reverse-proxy configuration which connects
PGWUI_Server's default WSGI server to the Internet might be::

        location / {
            proxy_pass       http://localhost:6543;
            # Using $http_host relies on the client, but is useful
            # because it preserves the original URL's port when
            # ssh tunneling.  If the client does not send the HOST
            # header than it may be necessary to use $host instead.
            proxy_set_header Host      $http_host;
            proxy_set_header X-Real-IP $remote_addr;
            proxy_redirect default;
	}

Replace the ``location / { ... }`` section appearing in the default
`Nginx`_ configuration with the above.  For more information see the
`Nginx`_ documentation.

It is beyond the scope of this document to describe how to use
PGWUI_Server's WSGI support or the the nuances of reverse-proxy
configuration.  Any WSGI web server can be used with PGWUI_Server, as
can any reverse web-proxy.

Again, ``pserve`` with ``waitress`` does not support HTTPS.  Consult your
webserver's HTTPS documentation.


Configuration
-------------

Configuration is done in the ``[app:main]`` section of an ``.ini``
configuration file.

Those using HTTPS can improve security by changing the session.secure
configuration setting to True.

Advanced Configuration
^^^^^^^^^^^^^^^^^^^^^^

If you do not want to autodetect the installed PGWUI components
you may manually specify them:

A sample ``.ini`` file::
  
    [app:main]
    # Turn off component autodetection
    pgwui.autoconfigure = False
    # The PGWUI components to use
    pyramid.include = pgwui_common
                      pgwui_logout
                      pgwui_upload

Manual specification, with or without autoconfiguration, can be
convenient when writing your own components.  (It also eliminates the
trivial overhead involved in autoconfiguration.)

Configuring Navbar Links
^^^^^^^^^^^^^^^^^^^^^^^^

Navbar link configuration is optional.  PGWUI comes with sensible
defaults.

 pgwui.home_page::  How to link to the site's home page.
   type:  URL (default), asset, file
       
   URL
     source:

       The default is ``/``, when there is no home_page setting.
       Which produces an URL with no "path".
     
       * A URI path beginning with ``/``.  E.g.: '/home'

       * An URL without a protocol, so an URL beginning with ``//`` and
         followed by a domain.  E.g.: //www.example.com  The URL
         delivered to the browser contains the protocol used in the request.

       * An URL with a protocol.  E.g.: https://www.example.com

   file:
     source:
       A fully-qualified file system path, so a path beginning with
       a ``/``.  E.g.  /var/www/html/index.html
       Served with a content encoding of ``text/html``.

   asset:
     source:
       * A `Pyramid`_ `asset specification`_.  It must reference a
         `static asset`_, a file included in a `Pyramid`_ application.
         Typically file containing a page of HTML.

         This is only useful to users who write their own `Pyramid`_
         applications that are either PGWUI modules or incorporate
         PGWUI.
       
   route:
     source:
       * A `Pyramid`_ `route name`_.  Used to reach a page generated
         by a `Pyramid`_ application which uses `URL dispatch`_.

         This is only useful to users who write their own `Pyramid`_
         applications that incorporate PGWUI.

 pgwui.menu_page::  How to link to a menu of PGWUI components.
 All of the "type"s of ``pgwui.home_page`` are available.


Configuration Settings Common to All PGWUI Components
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

PGWUI modules all have the following configuration settings:

 menu_label
   The label for PGWUI_Menu to display, when different from the default


Configuring Routing
^^^^^^^^^^^^^^^^^^^

Configuring routing means changing the URL used to access a page.
Usually this is the part of the URL which comes after the domain, the
part after the ``https://www.example.com`` part.  Alternatively, a
full URL may be supplied as a route.  This can be used to direct the
user to an external website.

PGWUI_Server comes with sensible defaults.  Configuring routing
is optional.

A prefix can be put in front of every route, so that instead of the
default route of ``logout`` in the URL
``https://www.example.com/logout`` the URL can be set to
``https://www.example.com/pgwui/programs/logout``::

    # Set a global route prefix.  URLs will look like:
    # https://www.example.com/pgwui/programs/logout
    pgwui.route_prefix = /pgwui/programs


The routes of individual PGWUI components are configured via each
component's name and can be changed::

    pgwui.routes =
        # The syntax is: routename = new/route/value
        # Make PGWUI Logout available at https://www.example.com/logmeout
        pgwui_logout = /logmeout
        # Make PGWUI upload available at https://www.example.com/gimmie
        pgwui_upload = /gimmie

Route names must match component names.

Both ``pgwui.route_prefix`` and ``pgwui.routes`` may be used in one
configuration.

Customizing Pages
^^^^^^^^^^^^^^^^^

The PGWUI files which control what is displayed on pages and how pages
are displayed can be *overridden*, replaced with files which you have
customized.  The files which can be overridden are identified by their
Pyramid asset specifications.

There are 2 categories of assets, CSS files and `Mako`_ HTML
templates.  CSS files control colors, sizes, placement, and the like.
HTML templates are used to generate the page's HTML.  Templates
control what is displayed.

Here is a list of the current asset specifications with brief
descriptions:

  pgwui_common:static/pgwui.css
    The CSS file for PGWUI.

  pgwui_common:templates/base.mak
    Common "background" items on all pages.

  pgwui_common:templates/auth_base.mak
    Common "background" items on all
    pages requesting database connection and login information.

  pgwui_logout:templates/logout.mak
    The logout page.

  pgwui_upload:templates/upload.mak
    The upload page.

Assets can be overridden in the configuration file with
``pgwui.override_asset``::

    pgwui.override_asset =
        # Syntax is: asset_to_override = override_with
        # Paths must be fully qualified.
        pgwui_common:static/pgwui.css = /var/www/custom/static/my.css
        pgwui_logout:templates/logout.mak = /var/www/custom/templates/mylogout.mak

When overriding assets it is probably best to start with a copy of the
original file taken from the source code and make changes from there.

For more information on the override feature and possible values of
``asset_to_override`` and ``override_with`` see: `Overriding Assets
<https://docs.pylonsproject.org/projects/pyramid/en/1.9-branch/narr/assets.html#overriding-assets>`_


Configuring Your Own Application
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

See the PGWUI_Common documentation for information how to configure
PGWUI if you are writing a complete Pyramid application, or simply
want full control.


Development Status
------------------

Although in use in production systems, and suitable for production
use, PGWUI_Server must be considered Beta code.  There is a reasonable
likelihood that it may undergo some change before final Production
release.  Install a specific version of PGWUI_Server to ensure a
backwards incompatible change does not break your code.


Developing PGWUI
----------------

In addition to the above components, the pyramid_debugtoolbar must be
installed in the virtual environment::

  pgwui_venv/bin/pip install pyramid_debugtoolbar

Rudimentary validation of the ``pyramid_beaker`` ``session.secret``
setting's value may be turned off.  To do this change the
``pgwui.validate_hmac`` setting to ``False``.  Having validation off
in production is not recommended.

Writing Plugable Components
---------------------------

In order to be a PGWUI component that is auto-configurable your
setup.py must include a ``pgwui.components`` entry point.\ [#f1]_ More
detail is provided below.

You module can (should) check the settings it receives for validity.
Expected checks are for missing required settings and unknown
settings.  It is also desirable to check that setting values conform
to expectations; boolean settings contain only boolean values, and
other settings contain only the expected values.

To check settings your component must contain a function of one argument,
as follows:


   def check_settings(component_config):

       component_config is a dict containing the configuration of the
       component.

       The components configuation settings should be checked,
       particularly for required configuration keys and unknown
       configuration keys.

       Return a list of the errors found. Preferably, an error is a child
       of UnknownSettingKeyError but it may be anything with a string
       representation.

PGWUI_Common contains helpers to use when checking settings.  An
example setting checker from PGWUI_Upload is::

   from pgwui_common import checkset
   from . import exceptions as upload_ex


   PGWUI_COMPONENT = 'pgwui_upload'
   UPLOAD_SETTINGS = ['menu_label',
                      'literal_column_headings',
                      ]
   REQUIRED_SETTINGS = []
   BOOLEAN_SETTINGS = []


   def validate_literal_column_headings(errors, settings):
       '''Make sure the values are those allowed
       '''
       value = settings.get('literal_column_headings')
       if value is None:
           return
       if value not in ('on', 'off', 'ask'):
           errors.append(upload_ex.BadLiteralColumnHeadingsError(value))


   def check_settings(component_config):
       '''Check that all pgwui_upload specific settings are good.
       This includes:
         checking for unknown settings
         checking for missing required settings
         checking the boolean settings
         checking that the values of other settings are valid
       '''
       errors = []
       errors.extend(checkset.unknown_settings(
           PGWUI_COMPONENT, UPLOAD_SETTINGS, component_config))
       errors.extend(checkset.require_settings(
           PGWUI_COMPONENT, REQUIRED_SETTINGS, component_config))
       errors.extend(checkset.boolean_settings(
           PGWUI_COMPONENT, BOOLEAN_SETTINGS, component_config))
       validate_literal_column_headings(errors, component_config)

       return errors


When establishing the entrypoint for your PGWUI componenent the name
of the entrypoint is the name of the pgwui component.  The value
assigned to the given module must be the name of the PGWUI component
which the module impliments.  So the syntax is:

   entry_points={'pgwui.components': '.COMPONENTNAME = MODULEPATH', ...}


Where MODULEPATH is a period separated list of module names.
(MODULENAME{.MODULENAME}...)

There must also be a ``pgwui.check_settings`` entrypoint to specify
the setting checker.  The syntax is:

   entry_points={'pgwui.check_settings':
                 '.COMPONENTNAME = MODULEPATH:FUNCTIONNAME', ...}

In the case of the ``pgwui_upload`` module, both the module name and
the component name are "pgwui_upload".  The check_settings module name
is ``check_settings`` and the function which does the check has the
same name.  So the entry point assignment in setup.py looks like::

    # Setup an entry point to support PGWUI autoconfigure discovery.
    entry_points={
        'pgwui.components': '.pgwui_upload = pgwui_upload',
        'pgwui.check_settings':
        '.pgwui_upload = pgwui_upload.check_settings:check_settings'}


Your package's ``__init__.py`` must setup the component's default
configuration.  It should contain a ``includeme(config)``
function. This is used by Pyramid to initialize the component at
module load time, and should establish setting defaults and perform
any necessary pre-processing on settings as part of it's
initialization::

   '''Provide a way to configure PGWUI.
   '''
   PGWUI_COMPONENT = 'pgwui_componentname'
   DEFAULT_COMPONENTNAME_ROUTE = '/componentname'
   DEFAULT_COMPONENTNAME_MENU_LABEL = \
       'componentname --  Example PGWUI Component Label'


   def establish_settings(config):
       '''Add defaults into settings when they are not present
       and pre-process setting values as needed
       '''
       settings = config.get_settings()
       pgwui = settings.setdefault('pgwui', dict())
       pgwui.setdefault(PGWUI_COMPONENT, dict())
       pgwui[PGWUI_COMPONENT].setdefault(
           'menu_label', DEFAULT_COMPONENTNAME_MENU_LABEL)


   def includeme(config):
       '''Pyramid configuration for PGWUI_Componentname
       '''
       establish_settings(config)
       config.add_route(PGWUI_COMPONENT, DEFAULT_COMPONENTNAME_ROUTE)
       config.scan()


The PGWUI_Testing component contains fixtures to test that your
component's entry points are correctly setup::

   from pgwui_testing import testing

   # Activiate our pytest plugin
   pytest_plugins = ("pgwui",)


   # Module packaging test

   def test_pgwui_upload_is_pgwui_component(pgwui_component_entry_point):
       '''Ensure that pgwui_upload is a pgwui.component entry point
       '''
       assert pgwui_component_entry_point('pgwui_upload') is True

   def test_check_setting_is_pgwui_check_settings(
           pgwui_check_settings_entry_point):
       '''Ensure that pgwui_upload has a pgwui.check_settings entry point
       '''
       assert (pgwui_check_settings_entry_point('pgwui_upload.check_settings')
               is True)


Complete Documentation
----------------------

The complete documentation set can be found on the PGWUI_Server home page at
http://pgwui_server.readthedocs.io/.


License
-------

Except for files otherwise marked, distributed WITHOUT ANY WARRANTY
under the terms of the GNU Affero General Public License, version 3 or
a later version at your option.  See the copyright notices at the top
of each file and the LICENSE.txt file for details.


Acknowledgments
---------------

The PGWUI_Server code is based on the GMI_Pyramid sub-system created for
the `Gombe Mother Infant Database Project
<https://gombemi.ccas.gwu.edu>`_.  Support for extracting PGWUI_Core
from GMI_Pyramid, its Python packaging, and further enhancement was
provided by `The Dian Fossey Gorilla Fund
<https://www.gorillafund.org>`_.


.. _Mako: https://www.makotemplates.org/
.. _Nginx: https://www.nginx.org/
.. _Apache: https://http.apache.org/
.. _Hitch: https://hitch-tls.org/
.. _HAProxy: https://www.haproxy.org
.. _PostgreSQL: https://www.postgresql.org/
.. _Pyramid: https://trypyramid.com/
.. _WSGI: https://en.wikipedia.org/wiki/Web_Server_Gateway_Interface
.. _pip: https://pip.pypa.io/en/stable/

.. _asset specification: https://docs.pylonsproject.org/projects/pyramid/en/latest/narr/assets.html#understanding-asset-specifications
.. _static asset: https://docs.pylonsproject.org/projects/pyramid/en/latest/narr/assets.html#serving-static-assets
.. _route name: https://docs.pylonsproject.org/projects/pyramid/en/1.10-branch/narr/urldispatch.html#route-configuration
.. _URL dispatch: https://docs.pylonsproject.org/projects/pyramid/en/1.10-branch/narr/urldispatch.html


.. rubric:: Footnotes

.. [#f1] https://setuptools.readthedocs.io/en/latest/setuptools.html#dynamic-discovery-of-services-and-plugins