Using your PostgreSQL database

PostgreSQL extensions

We install these extensions in all databases of our customers:

• PostGIS 3 (or 2.5 on our old servers)

• postgis_raster

• pg_trgm

• unaccent, fuzzystrmatch, tablefunc, hstore, uuid-ossp

Other extensions are possible, depending of the subscription, in particularly for the “dedicated server” offers. For example, we can install pgrouting, ogr-fdw, pg_cron… Contact us to install some of them.

Rights and roles

After you subscribe to an offer, we gave us a serveur name, a database name, a login and a password.

The database name follows this pattern: lizmap_<instancesgroup>_<instancename> (exemple lizmap_mygroup_myinstance).

This login is into a group named __group__@<instancesgroup>_<instancename> (exemple __group__@mygroup_myinstance), who has some rights allowing to manage your schemas, tables and other PostgreSQL objects.

This login and all logins that are into the __group__@.. group, allow you to:

• access to the database

• create tables, schemas, vues etc..

• read and modify the content of the tables.

• set rights on schemas, tables etc.

However, it does not allow you to:

• manage PostgreSQL roles

• install PostgreSQL extensions

• do any other administrative operation on the server.

To have other logins, contact us.

An other group is predefined: __group__restricted__@<instancesgroup>_<instancename> (exemple __group__restricted__@mygroup_myinstance). It has a read only access to the “public” schema and its tables. Only SELECT can be used on these tables. You can ask to set some login into this group.

There is also an other predefined group: __group__internal__@<instancesgroup>_<instancename>, which is used by Lizmap instances, and allow to access to schema dedicated to these instances (they have a name starting with lizmap_). Lizmap stores its own tables into this schema.

Modifying rights

When you create a schema or a table, your login is the owner of this schema, and other users don’t have access to it.

You have to give access to them with the GRANT sql query. Example:

GRANT USAGE ON SCHEMA myschema TO "group_name_or_login";
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA myschema TO "group_name_or_login";
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA myschema TO "group_name_or_login";
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA myschema TO "group_name_or_login";

GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO "restricted_group_name_or_login";
GRANT EXECUTE ON ALL SEQUENCES IN SCHEMA myschema TO "restricted_group_name_or_login";
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA myschema TO "restricted_group_name_or_login";

GRANT SELECT ON TABLE myschema.lizmap_search TO "restricted_group_name_or_login";

To know more on the GRANT command, see the PostgreSQL documentation about GRANT.

You can also use the REVOKE command to forbid the access to a user. So you can for example authorised all users of a group except few users of this group, by doing a GRANT on this group and a REVOKE on the user.

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA myschema TO "group_name";
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA myschema FROM "login_name";

To set rights automatically on each new table of a schema, you can use the command ALTER DEFAULT PRIVILEGES. Examples:

ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT ALL PRIVILEGES ON TABLES TO "__group__@..";
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT ALL PRIVILEGES ON SEQUENCES TO "__group__@..";
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT ALL PRIVILEGES ON FUNCTIONS TO "__group__@..";

ALTER DEFAULT PRIVILEGES IN SCHEMA myschema REVOKE ALL PRIVILEGES ON TABLES FROM "a_login";

ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO "__group__restricted__@..";
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON SEQUENCES TO "__group__restricted__@..";
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT EXECUTE ON FUNCTIONS TO "__group__restricted__@..";

Be careful: the command ALTER DEFAULT PRIVILEGES don’t change rights on existing tables/sequences/functions etc.

See see documentation of this command.

About lizmap_search

If you create a table or a view lizmap_search for Lizmap into one of your schema, you should give access on this table/view to the group __group__internal__@...

Limits on connections

Each login have a limited connection number (50). So you should not use the same login for all of your QGIS maps.

Access restriction from the internet

By default, your database is accessible from any computer on the internet. However, you can can limit this access from only few IP adresses.

These IP usually are the address of a proxy server. Contact your IT service to know these IP.

You can give us these IP, and we will configure the PostgreSQL server to allow an access to your database, only from these IP.

Configuring the access into QGIS

In QGIS, you must create a new connection to PostgreSQL with these informations:

• keep empty the “Service” field

• Check “Save” checkboxes beside the username and the password fields (after you clicked on “test the connection”)

• Check the last checkbox “Use the metadata table” at the bottom

• Validate and accept the warning message (for the save of the password)

In order to improve access performance to PostgreSQL, it is also interesting to modify an option in QGIS: tab “Rendering”, checkbox “Simplify on provider side if possible”. It allows to download some lighter data at little scales.

For PostGIS layers that have been added before activating this option, you can manually change it in your projects, into the “Rendering” tab of the properties dialog of each layers.

Restoring data

If you destroy some data, it is possible to retrieve them.

We can restore whole data of a database, freely, from a daily backup made in the last six days.

However, if you want to recover only a set of data (the content of a table), it can be complicated, depending of the complexity of the database. In this case, you have two choice:

• either you ask us for an estimate for the restoration: we study the structure of your database and we will propose you a paid service to restore your data.

• either you restore yourselves the data, from a backup we can give you.

View v_lizmap_users and log tables

Lizmap stores its user accounts and groups in a database, as well as detailed logs and associated counters.

In the default schema corresponding to your instance lizmap_<nominstance> the v_lizmap_users view show you existing lizmap users and the groups to which they belong.

In this same schema, the two tables log_counter and log_detail allow you to view lizmap’s detailed logs and counters.

Using pgAgent

PgAgent allow to launch SQL queries at specific times. For example, every days at such hour, or such day of the week etc. In theory, it can also execute bash scripts, but this function has been disabled on our infrastructure for security reasons.

PgAgent is a fee-based service installed only on dedicated servers.

Once you have subscribed to this service, a database is created, dedicated to the pgagent service. Your postgresql login is given rights to access it.

To use the service, you need to install the pgAdmin software. This provides an interface for managing jobs. It only appears if you connect directly to the PgAgent database.

In pgAdmin, you need to create a new connection to your database server, indicating the pgAgent database whose name was given to you when you subscribed.

Once logged in, you will see a “pgAgent jobs” item in the list of database objects.

Right-click on this item to create a new “job”.

In the box that appears, you enter a name, then in the “steps” tab, you add steps for the job. For each step, you must indicate :

1. a name

2. then click on the little pencil to the left of the name to indicate - either the name of the database in which your SQL script will be executed, and to which your login gives access, - or a connection string to access an external database

3. For the script type, only “SQL” is allowed. If you choose “Batch”, you won’t be able to save, as we’ve revoke rights to prohibit this for security reasons.

4. By clicking on the “code” tab, you can type in the SQL code to be executed. This can be anything from simple queries to more complex scripts with stored procedures and so on.

Finally, in the “Schedules” tab, you specify the dates and/or times when the the task will be launched. Make sure you launch your tasks at times that won’t inconvenience your users, especially if yours tasks involve heavy processing.

You should also make sure that your tasks don’t take hours to run, as this could affect the overall performance of your lizmap applications.

To find out whether the tasks have been completed successfully, click on a task in the right-hand panel, then display the “statistics” tab. If you don’t see it, click on the button having three vertical dots at the far right of the tab bar, and choose “Open > Statistics”. You’ll then see a table listing all task launches, with a “Status” column indicating the result of execution:

• “r” : running

• “s” : success

• “f” : fail

• “i” : internal fail

• “d” : aborted

For more details about the use of PgAgent, see the official documentation.