2

Problem:
We have several layers stored in a PostgreSQL database with PostGIS extension (Stored on AWS RDS Instance).
The connection has been working perfectly to date.
I get asked for login credentials every time I open the QGIS 3.16.5 project which is stored locally.

What I'd like to happen:
I'd like to open the QGIS project and have the layers loaded from PostgreSQL+ PostGIS database without needing to authenticate every time.

Why it's important:

  1. Saves time every time we open a project
  2. Multiple database connections means entering credentials for every database connection
  3. I'd like to know how to improve the UX for our users

What I have tried:

  1. Stored credentials as plain text in the project file Edit PostGIS Connection > Authentication > Basic > Check Store checkbox
  2. Converted to configuration. Edit PostGIS Connection > Authentication > Configurations > Create Authentication Configuration > Fill in Authentication Dialog inputs

Resources I've looked at already:

  1. https://issues.qgis.org/issues/21582

  2. From QGIS 3.4.5 and 3.6 asks password repeadly to connect to PostgreSQL 10

  3. https://docs.qgis.org/3.16/en/docs/user_manual/auth_system/index.html?highlight=authentication

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

  5. QGIS asks for PostgreSQL credentials multiple times

Questions:

  1. Is this a known bug with the 3.16-LTR, if so where can I find out more?
  2. Am I managing the connections incorrectly or overlooking something?

Environment:
OS - Windows 10 Professional
QGIS Version - 3.16.5-Hannover

2
  • Are you using pgpass? gis.stackexchange.com/questions/331053/…
    – Mapperz
    Apr 10, 2021 at 3:42
  • No, but it sounds like a good idea to. I read the article. Thank you for the link! There is a note there on security "NB : it's not safe to use it this way but it's ok if it's a local database." Do you know a safer way to store credentials?
    – Robbo
    Apr 12, 2021 at 4:23

1 Answer 1

7

A very good solution is to use a connection service file.

Using this, you can have your credentials in one file, completely independent of QGIS.

You can share the project. Other users can use the same project as well, using their own credentials.

The same project can be used by QGIS Server, using other credentials as well.

And you can start saving your projects in the database.

On Windows

Since you are using Windows, you need three steps to get pg service files working.

Set PGSYSCONFDIR

On your local computer, you must set the environment variable PGSYSCONFDIR pointing to the folder where you want to store your pg_service.conf file.

Usually, this can go to your home folder, like in the following print screen.

enter image description here

Create your own pg_service.conf

After setting PGSYSCONFDIR, create a text file called pg_service.conf with your authentication details. The file will look like this:

[neverland]
host=postgresql.neverland.com
port=5432
dbname=neverland
user=peter
password=wendy

This creates a new service called neverland. You can create as many as you like.

Use pg services in QGIS

After setting PGSYSCONFDIR and create your own pg_service.conf file, you can start using this authentication method in QGIS.

You just need to enter the service name in the New PostGIS Connection dialogue.

enter image description here

1
  • thanks for the detailed explanation! Appreciate it. This answer is the more maintainable solution I was looking for.
    – Robbo
    Aug 26, 2021 at 1:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.