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.