Using your PostgreSQL database
We install these extensions in all databases of our customers:
PostGIS 3 (or 2.5 on our old servers)
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:
This login is into a group named
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__@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:
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.
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.
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
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.
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
show you existing lizmap users and the groups to which they belong.
In this same schema, the two tables
log_detail allow you to view
lizmap’s detailed logs and counters.