How to open QGIS project without being asked for PostGIS credentials every time

Question

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

Answer 1

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.

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.