Tutorial¶
This tutorial will demonstrate how to prepare a project to be used with the Guided Offline Editing QGIS plugin. It will focus on the prior steps, before the plugin can be used, as using the plugin is really easy. It will guide you through the preparation process, from modifying a PostgreSQL database so that it can be used with the plugin, to creating a to-be-offline-edited project.
Example context: collecting data about wetlands¶
Imagine that you, Alice (or Alexander if you’re a man, but we will use Alice from now on), work for a biodiversity NGO named We Love Biodiv.
Inside We Love Biodiv, you are the Database Administrator and also the one who knows QGIS best. Your colleague, Bob, knows nothing about databases, SQL, and only uses QGIS to make beautiful maps and for data analysis. He doesn’t understand the DB Manager dialog, he can’t figure out where to look for PostgreSQL layers, and he thinks that creating relationships between layers in QGIS is too complicated, same thing for creating forms (sounds familiar to you?).
Now, We Love Biodiv is involved in a project whose goal is to collect data about wetlands on a specific territory. For this project, you and bob will survey wetlands on the field, then upload collected data into PostreSQL. You are in charge of designing and creating tables and views in database, then you have to set up forms in QGIS so that Bob can use them to enter field data.
PostgreSQL prerequisites¶
Let’s assume you have a PostgreSQL service on host db.priv.welovbiodiv.org
that listens on TCP port 5432
. On that cluster, you have a database named
wlbdb
. You will use that database to store and protect collected wetland
data.
Schemas¶
Inside the wlbdb
database you have two PostgreSQL schemas named ref
and
wetland
. The ref
schema contains tables with reference data. For
example, there is a taxon
table containing names of all species known to be
present in the territory, and a city
table that hold all cities information
with their perimeters.
The wetland
schema will hold collected data about wetlands. Inside that
schema you will create tables and design a data model as normal as possible.
For the sake of simplicity, we will assume there are only two tables: the main
wetland
table with a geometry
column, and a wetland_type
table
containing the set of all wetland types. Of course, the wetland
table will
have a type_id
column which is foreign key referencing the wetlandt_ype
table.
Note
Each table that will be edited in QGIS must have an integer primary key, even if a natural key already exists (for that column use a unique constraint and index): this is what the QGIS Offline Editing plugin expects so that it can match offline and remote features (when a feature is updated or deleted for example).
We also recommend to create a qgis
schema and to save QGIS projects
inside that schema. So as the wlbdb
owner, type the following in a
PostgreSQL console (or use a software like PgAdmin to create the schema).
create schema qgis;
Note
You do not need to create any table inside that schema. The first time
you save a QGIS project to PostgreSQL and select the qgis
schema as
destination, QGIS will automatically create a qgis_projects
table
and use it to save and load projects.
Permissions¶
Now we discuss PostgreSQL roles and permissions.
Note
Since you’ll be modifying data directly inside PostgreSQL, we recommend to have one PostgreSQL user for each real person that will edit data.
Say you have two PostgreSQL users named alice
and bob
. These users
needs to query data in the ref
schema (but they must not modify it since
this is reference data) and edit data in the wetland
schema.
So you need to grant, to both alice
and bob
:
USAGE
permission on both schemasref
andwetland
,SELECT
permission on theref.taxon
andref.city
tables,USAGE
permission on all sequences in schemawetland
,SELECT, INSERT, UPDATE, DELETE
permissions onwetland.wetland
andwetland.wetland_type
tables.
But instead of granting these permissions directly to alice
and bob
, we
recommend to create a dedicated role, named gis
for example, grand the
permissions to gis
, and assign alice
and bob
to the gis
role.
See https://www.postgresql.org/docs/current/role-membership.html for more
information about role membership. In this manner, if a new user must have the
same permissions, you just need to assign the new user to the gis
role.
In a PostgreSQL console, this would looks like the following.
create role gis nologin noinherit;
grant usage on schema ref to gis;
grant usage on schema wetland to gis;
grant usage on all sequences in schema wetland to gis;
grant select on all tables in schema ref;
grant select, insert, update, delete to gis on all tables in schema wetland;
grant alice to gis;
grant bob to gis;
Note
One final word about users. Even if you, Alice, are the Database
Administrator, we recommend not to grant all permissions to alice
user. Instead, you should log in as a dedicated user for administration
tasks, for example named dbadmin
. alice
is the user you should
impersonate when you wear your QGIS User hat. When you wear your
Database Administrator hat, you should use dbadmin
.
QGIS prerequisites¶
Now that your wlbdb
PostgreSQL database is all set and ready to receive
field data about wetlands, let’s talk about QGIS environment.
Common configuration ID in authentication database¶
How both Alice and Bob will open the same QGIS project and connect to PostgreSQL using their own distinct credentials? The trick is to use a common configuration ID in QGIS authentication database.
The configuration ID is a string of exactly seven characters that refers to some credentials used by QGIS. Whenever you save new credentials (username/password, private key, …) within QGIS (for example to connect to a WFS server or to a PostgreSQL database), QGIS creates a new configuration in its authentication database. And each configuration is identified programmatically by this 7-chars ID. Usually, this ID is randomly generated by QGIS. It is saved inside each project using those credentials, so that no password is stored in clear text in the project. See https://docs.qgis.org/3.4/en/docs/user_manual/auth_system/auth_overview.html#authentication-database for more information.
A QGIS authentication database (authdb for short) is private to a user. So for a QGIS project to work for both Alice and Bob, the project must refers to the same configuration ID in both Alice authdb and Bob authdb. Thus, the configuration ID must not be randomly generated, but must be set to a value shared across all users.
Back to our example, let’s assume that the shared configuration ID for all We
Love Biodiv users is the string wlbauth
(length: 7). Consequently, Alice
must create a new authentication configuration in QGIS (menu
and click on the
+ button on the right side), with the following information:
name: a human readable name for this configuration. For example Alice wlbdb credentials,
Id: click on the lock button to prevent the Id to be randomly generated. Then, enter the shared ID
wlbauth
.Username and :quilabel:`Password`: Alice’s credentials to connect to
wlbdb
PostgreSQL database.

Alice’s authentication configuration in QGIS using wlbauth
ID¶
Of course Bob must do the same on his side. He opens QGIS on his computer then
creates a new authentication configuration using the same wlbauth
ID.
Everything else is specific to Bob:
name: Bob wlbdb credentials,
Id:
wlbauth
,Username and Password: Bob’s credentials to connect to
wlbdb
PostgreSQL database.