# 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

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