.. _postgres:

======================
Postgis Database Setup
======================

.. warning::

  Be sure you have successfully completed all the steps in the :ref:`dependencies` section.

In this section, we are going to set up users and databases for GeoNode in PostgreSQL.

Install and Configure the PostgreSQL Database System
....................................................

In this section, we are going to install the ``PostgreSQL`` packages along with the ``PostGIS`` extension. Those steps must **only** be done if you don't already have the DB installed on your system.

.. code-block:: shell

  # Ubuntu 22.04.2 (Jammy Jellyfish)
  sudo apt install postgresql -y
  sudo passwd postgres
  # password: postgres
  sudo apt install postgis -y

We must now create two databases, ``geonode`` and ``geonode_data``, with the role ``geonode``.

.. warning:: This is our default configuration.
  You can use any database or role you need.
  The connection parameters must be correctly configured in ``settings``, as we will see later on in this section.

Databases and Permissions
.........................

First, create the geonode user. GeoNode is going to use this user to access the database

.. code-block:: shell

  sudo service postgresql start
  sudo -u postgres createuser -P geonode

  # Use the password: geonode

You will be prompted to set a password for the user. **Enter geonode as the password**.

.. warning:: This is a sample password used for the sake of simplicity. This password is very **weak** and should be changed in a production environment.

Create the databases ``geonode4`` and ``geonode4_data`` with owner ``geonode``

.. code-block:: shell

  sudo -u postgres createdb -O geonode geonode4
  sudo -u postgres createdb -O geonode geonode4_data

Next, let's create the PostGIS extensions

.. code-block:: shell

  sudo -u postgres psql -d geonode4 -c 'CREATE EXTENSION postgis;'
  sudo -u postgres psql -d geonode4 -c 'GRANT ALL ON geometry_columns TO PUBLIC;'
  sudo -u postgres psql -d geonode4 -c 'GRANT ALL ON spatial_ref_sys TO PUBLIC;'
  sudo -u postgres psql -d geonode4 -c 'GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO geonode;'
  sudo -u postgres psql -d geonode4 -c 'GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO geonode;'

  sudo -u postgres psql -d geonode4_data -c 'CREATE EXTENSION postgis;'
  sudo -u postgres psql -d geonode4_data -c 'GRANT ALL ON geometry_columns TO PUBLIC;'
  sudo -u postgres psql -d geonode4_data -c 'GRANT ALL ON spatial_ref_sys TO PUBLIC;'
  sudo -u postgres psql -d geonode4_data -c 'GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO geonode;'
  sudo -u postgres psql -d geonode4_data -c 'GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO geonode;'

The final step is to change the user access policies for local connections in the file ``pg_hba.conf``

.. code-block:: shell

  sudo vim /etc/postgresql/14/main/pg_hba.conf

Scroll down to the bottom of the document. We want to make the local connection ``trusted`` for the default user.

Make sure your configuration looks like the one below.

.. code-block:: shell

    ...
    # DO NOT DISABLE!
    # If you change this first entry you will need to make sure that the
    # database superuser can access the database using some other method.
    # Noninteractive access to all databases is required during automatic
    # maintenance (custom daily cronjobs, replication, and similar tasks).
    #
    # Database administrative login by Unix domain socket
    local   all             postgres                                trust

    # TYPE  DATABASE        USER            ADDRESS                 METHOD

    # "local" is for Unix domain socket connections only
    local   all             all                                     scram-sha-256
    # IPv4 local connections:
    host    all             all             127.0.0.1/32            scram-sha-256
    # IPv6 local connections:
    host    all             all             ::1/128                 scram-sha-256
    # Allow replication connections from localhost, by a user with the
    # replication privilege.
    local   replication     all                                     peer
    host    replication     all             127.0.0.1/32            scram-sha-256
    host    replication     all             ::1/128                 scram-sha-256

.. warning:: If your ``PostgreSQL`` database resides on a **separate/remote machine**, you'll have to **allow** the ``geonode`` user remote access to the databases in the ``/etc/postgresql/14/main/pg_hba.conf`` file and tell PostgreSQL to **accept** non-local connections in your ``/etc/postgresql/14/main/postgresql.conf`` file

Restart PostgreSQL to effect the change.

.. code-block:: shell

  sudo service postgresql restart

PostgreSQL is now ready. To test the configuration, try to connect to the ``geonode`` database as the ``geonode`` role.

.. code-block:: shell

  psql -U postgres geonode4
  # This should not ask for any password

  psql -U geonode geonode4
  # This should ask for the password geonode

  # Repeat the test with geonode_data DB
  psql -U postgres geonode4_data
  psql -U geonode geonode4_data