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 schemas ref and wetland,

  • SELECT permission on the ref.taxon and ref.city tables,

  • USAGE permission on all sequences in schema wetland,

  • SELECT, INSERT, UPDATE, DELETE permissions on wetland.wetland and wetland.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 Settings ‣ Options ‣ Authentication 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

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.

Shared plugin configuration

Preparing the project