How do I manage user privileges when working with a QGIS project stored in a PostGIS database?

Question

I have set up a PostGIS database (PostgreSQL 15.5, PostGIS 3.4, QGIS 3.34 LTR) which 2 different user groups can access: editor and reader. For simplicity, let's say there are 3 schemas in the database. The schema all_layers contains all tables of the database that both user groups have access to. Additionally, each user group has its own schema, which only they have access to. This schema only contains a QGIS project each:

My issue is with the way QGIS handles PostGIS credentials, especially when using QGIS projects saved to the database. As mentioned in this related question, any credentials saved in a QGIS project override the locally saved credentials, which can lead to giving editor privileges to readers. For this reason, I implemented two separate schemas with separate QGIS projects (I then manually updated the project in the project_reader project while making sure no credentials were saved in the project file). I also have to take into account that the editors need UPDATE privileges to the QGIS project itself, but I want to prohibit them from accidentally saving their credentials into the project file.

Is there a way to have a single QGIS project saved to a schema (like in the following image) in my PostGIS database where the following is given:

• Both user groups (editor and reader) have access to the same schema & project
• The editor can make changes to the project file
• The editor can't accidentally save his/her credentials to the project file, which then provides the readers with more privileges
• And crucially: users don't have to log in 2-3 times each time they open the project file (currently the case)

I am aware of the option of using service config files as described in this post. However, the users accessing the database work at a seperate company and it is not feasible for me to install a service config file on each computer that gets access to the database.

Answer 1

After a lot of research and help from a consulting firm, I found the issue and the answer.

Issue: The issue turned out to be how QGIS projects are saved to the database. Depending on the PostGIS connection the user had set up when creating the project, those credentials were used and saved to the project. This happens on a layer level. So if multiple people edit the project before uploading and add layers, the project can have various connection settings saved per layer. This leads to issues after being uploaded to the database as whoever wants to open the project needs to login once per layer that does not match one's own PostGIS connection settings.

Solution: The best way to manage user privileges when working with QGIS projects saved in a PostGIS database is via the QGIS authentication database.

The solution is firstly, for all users to work with an authentication configuration and not with a basic authentication:

Second, all users (independant of privilege level) need to enter their personal user credentials and then manually enter the same id ('test2db' in this example). Normally, the id is generated automatically. However, one does have the option to enter a user-defined id (has to be 7 characters and alphanumeric):

Explanation: When using an authentication configuration, QGIS only saves the configuration id in the project file. If all users have the same configuration id, QGIS queries it and then uses the individual credentials of each user, granting them access on their privilege level without the need to ever enter their login credentials manually. It is important to set this up from the start before loading a QGIS project into the database. Otherwise, the previous settings might be saved on individual layers and need to be correcter after the fact. After implementing this solution:

• only one project (in one schema) is needed
• all users have their respective privileges
• users don't ever have to enter login credentials manually

Fixing existing QGIS projects in a PostGIS database: I already had large projects in my database and needed to fix the authentication issue. The best way to do this is to download the project and save as a QGS file. Then, open the project in a text editor and find and replace all instances of credentials saved in clear text with the text snippet 'authcfg=7charid' and then re-upload the project to the database. I have written python scripts to automate this process and would be willing to help if anybody has the same issues.