Postgis Database Setup
Warning
Be sure you have successfully completed all the steps in the GeoNode System 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.
# 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
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 geonode42
and geonode42_data
with owner geonode
sudo -u postgres createdb -O geonode geonode42
sudo -u postgres createdb -O geonode geonode42_data
Next, let’s create the PostGIS extensions
sudo -u postgres psql -d geonode42 -c 'CREATE EXTENSION postgis;'
sudo -u postgres psql -d geonode42 -c 'GRANT ALL ON geometry_columns TO PUBLIC;'
sudo -u postgres psql -d geonode42 -c 'GRANT ALL ON spatial_ref_sys TO PUBLIC;'
sudo -u postgres psql -d geonode42 -c 'GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO geonode;'
sudo -u postgres psql -d geonode42 -c 'GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO geonode;'
sudo -u postgres psql -d geonode42_data -c 'CREATE EXTENSION postgis;'
sudo -u postgres psql -d geonode42_data -c 'GRANT ALL ON geometry_columns TO PUBLIC;'
sudo -u postgres psql -d geonode42_data -c 'GRANT ALL ON spatial_ref_sys TO PUBLIC;'
sudo -u postgres psql -d geonode42_data -c 'GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO geonode;'
sudo -u postgres psql -d geonode42_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
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.
...
# 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.
sudo service postgresql restart
PostgreSQL is now ready. To test the configuration, try to connect to the geonode
database as the geonode
role.
psql -U postgres geonode42
# This should not ask for any password
psql -U geonode geonode42
# This should ask for the password geonode
# Repeat the test with geonode_data DB
psql -U postgres geonode42_data
psql -U geonode geonode42_data