Change in PostGIS database password prompts for user credentials on QGIS start

Question

I am using QGIS 3.4.6 and I have a project file which was set up with a PostGIS database connection with basic authentication prompting for a username and password. This was stored as plain text in the project file.

Recently the person who manages the PostGIS database changed the password but kept the username the same. This has prompted QGIS to ask for the database credentials whenever the project file is now opened or any past projects that I have worked on.

If I enter the credentials that a connection is made so I know that this is all correct, however if I edit the database connection with the new password and re-save the project file I am still asked for the credentials on start-up.

I have saved the new credentials and stored them as plain text in the project file as before. Interestingly, when I open the project and check the credentials the new ones have been saved.

Does anyone know how I can overcome this and get my project file to remember my user credentials?

Sorin RUSU · Answer

I know this is an old post, but I thought I would share the solution, for future references.
If saving the QGIS project as zipped .qgz file, open with 7-Zip the .qgs file, extract the .qgs file, and open it in a text editor, preferably something like Notepad++.
If saving the QGIS project as a plain .qgs file, just open the file in the text editor.
In the text editor look for the following attribute: providerKey="postgres", and the text editor should find it for each layer you loaded in a project. Now you need to find the "source" tag and change the stored password, or if like me, you added a layer without saving the user/passw credentials as plain text, when connecting to PostgreSQL, then add the password='' tag.
So if you want to add a password to a .qgs layers change this type of source:
source="dbname='gis_prod_orcl' host=localhost port=5433 user='postgres' sslmode=disable key='oid' srid=31700 type=Point...

into
source="dbname='gis_prod_orcl' host=localhost port=5433 user='postgres' password='some_password' sslmode=disable key='oid' srid=31700 type=Point...

After changing/adding the password for all layers, save the .qgs file and if using the .qgz format, repackage the .qgs -> .qgz using 7-zip.
Open the .QGZ file with QGIS and the layers should connect automatically if the supplied password is correct.

Change in PostGIS database password prompts for user credentials on QGIS start

One Answer

Add your own answers!

Ask a Question