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

passwordpostgisqgisqgis-3

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?

Best Answer

If saving the QGIS project as zipped .qgz file, then 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, then 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.

Related Solutions

[GIS] How to access user credentials in database connection

QGIS caches the connection credentials inside QgsCredentials based on the connection info. So for a layer lyr do:

connInfo = QgsDataSourceURI( lyr.dataProvider().dataSourceUri() ).connectionInfo()
(success, user, passwd ) = QgsCredentials.instance().get( connInfo, None, None )
# Put the credentials back (for yourself and the provider), as QGIS removes it when you "get" it
if success:
    QgsCredentials.instance().put( connInfo, user, passwd )

[GIS] How to get User and Password of a PostGIS layer from PyQGIS

Yes, you can do that, but only if you chose to save user and password while establishing the PostGIS connection.

enter image description here

If you did so and your active layer is a PostGIS layer, you can access the connection information this way:

layer = iface.activeLayer()
source = layer.source()
print source

You'll get a key-value string like this:

dbname='mydb' host=localhost port=5432 user='myuser' password='mypassword' sslmode=disable key='gid' srid=4326 type=MULTIPOLYGON table="public"."mytable" (geographicextent) sql=

From which you can obtain the password this way:

kvp = source.split(" ")
for kv in kvp:
    if kv.startswith("password"):
        thePassword = kv.split("=")[1][1:-1]
        print thePassword

Include a similar if for the user into the for loop and you are done.

Best Answer

Related Solutions

[GIS] How to access user credentials in database connection

[GIS] How to get User and Password of a PostGIS layer from PyQGIS

Related Question