PostGIS connection: QGIS tries to connect with system username even if DB username/password are used

[Virvis]

I set up remote postgis database. I also made fail2ban firewall for it to protect from malicious connections.
Fail2ban search for /var/log/postgresql/postgresql-12-main.log for failed connections. When too many failed connections, connection is banned for 5min.

From local machine I use QGIS 3.16.3-Hannover
When fail2ban is unactive I can connect to database with my username+password, I can do everything I want.
If i activate fail2ban, it disconnects me from database.

I went to see postgresq logs, and it shows me that QGIS has let me in, but also simultaneusly tried to connect with another username without password. It uses qgis global variable "user_account_name", same as my computers username (erkki).

Postgresql log looks like:
176.x.x.x (56547) 2021-03-24 12:56:19 EET [1892948] erkki@mapsystem FATAL: password authentication failed for user "erkki"
176.x.x.x (56547) 2021-03-24 12:56:19 EET [1892948] erkki@mapsystem DETAIL: Role "erkki" does not exist.
Connection matched pg_hba.conf line 96: "hostssl all all 0.0.0.0/0 scram-sha-256"

[gioman]

Seems weird to me that QGIS by itself tries a connection with a credential that is not the one stored in the connection. Do you store username and password when creating a PostGIS connection? what if you try to convert to (authentication) configuration instead?

[Virvis]

When connection first time it asks for the username and password, then filling those but not saving to anywhere. Then it makes this problem.

I have been able to pass when using authentication.
BUT
If I open my friends project (maybe to copy layers styles), I of course will not have proper authentication key. Then QGIS asks for the name and password when opening the project and creating the connection for db. I can again connect properly with my own username+password, but quite soon I get banned because "Role "erkki" does not exist."
I don't get why it even tries to connect with this "user_account_name" or what happens.
Same thing banned gets if I do not fill the authentication form and just click "Ignore for this session"

[gioman]

When connection first time it asks for the username and password, then filling those but not saving to anywhere. Then it makes this problem.

I have been able to pass when using authentication.
BUT
If I open my friends project (maybe to copy layers styles), I of course will not have proper authentication key. Then QGIS asks for the name and password when opening the project and creating the connection for db. I can again connect properly with my own username+password, but quite soon I get banned because "Role "erkki" does not exist."
I don't get why it even tries to connect with this "user_account_name" or what happens.
Same thing banned gets if I do not fill the authentication form and just click "Ignore for this session"

@Virvis so gthe problem you describe is when you don't save username and password, correct?

and is ok when using the (authentication) configuration, correct? do you know that you can share the (authentication) configuration with other computers (and this way the users will use it will never know the real credentials)?

[Virvis]

Yes. Problem when I do not save credentials. I have not tested if I save them "as plain text" as one option is when creating new connection.

I know that I can share authentication between computers, but I want that everyone has own credentials, not the same ones. I could of course set like everyone has auth with same tag (ID) in the authentication settings, but it feels too complicated when we have quite many people in different location.

It should not be such hard to connnect on database :)

[gioman]

It should not be such hard to connnect on database :)

just ask everyone to create a connection with their credential, store it, then make it an auth config. Where is the problem with it?

[gioman]

@Virvis even from the postgresql CLI interface (psql) if you omit the -U parameter (the user) it tries to connect with the system username, this has nothing to do with QGIS.

[andreasneumann]

I could of course set like everyone has auth with same tag (ID) in the authentication settings, but it feels too complicated when we have quite many people in different location.

We do it exactly like this: all users have their own user role and password, but by using the same authid the users are able to open the project with their own credentials.

You have to "educate" your users in the beginning, but once they get it, it works fine.

Do you have a better idea how to do it?

The other thing that @gioman mentioned is a libpq thing. QGIS uses libpq (the standard PostgreSQL library) and inherits its behavior. libpq automatically uses the local user account, if the username is omitted. If you want to change the behavior of libpq, you have to open an issue with the PostgreSQL project.

[Virvis]

I will do the auth-trick from now. Everyone should have same auth-id and own credentials saved there. It works.

My "problem" is still same: without authing, user will get blocked from db.
When connecting to database, user will be asked for username and password. Even if these are given correctly, and connection is created, and layers are fetched, the log will tell "password authentication failed for user "erkki"".

Username is not omitted then?
(or does PostGIS/QGIS use username+password only when creating the connection and not afterwards. But this doesn't sound proper for me)

Thing I didn't mention earlier, I have set that connection requir to SSL.

[gioman]

Username is not omitted then?

@Virvis if you omit the username the system username is used, but this is not QGIS behavior is libpq's one, which QGIS uses in its PostgreSQL provider.

[Virvis]

But when correct username + password is offered and used, QGIS still sends system username to database.
I am able to connect db (which requires credentials) and fetch objects.
From the postgres logs I can clearly see that system username is used only when I move/zoom the mapcanvas. If I do nothing with the map, there is no happenings on the log. Instantly when I zoom, somehow there is connection error with system username.
This happens when username and password is offered, but not authentication used.

[gioman]

offered

@Virvis offered but NOT saved, correct?

[Virvis]

Correct.
I thought that then QGIS will save credentials in cache/memory in this kind of connection, not correct?

[gioman]

I thought that then QGIS will save credentials in cache/memory in this kind of connection, not correct?

@Virvis I guess you can say that. I edited the title, make sense?

[Virvis]

Yes, I think it is correctly said :)

[Virvis]

Comment one mone thing.
If someone opens project with wrong authid, person is asked to fill credentials (username + password) without possibility to save these credentials.
If person fills these, he will be able to connect db and get the layers.
Still he got banned due to also connected with "user_account_name".

[github-actions]

The QGIS project highly values your report and would love to see it addressed. However, this issue has been left in feedback mode for the last 14 days and is being automatically marked as "stale".
If you would like to continue with this issue, please provide any missing information or answer any open questions. If you could resolve the issue yourself meanwhile, please leave a note for future readers with the same problem and close the issue.
In case you should have any uncertainty, please leave a comment and we will be happy to help you proceed with this issue.
If there is no further activity on this issue, it will be closed in a week.

[tomtor]

This is a real issue.

When I load a QGis map on Windows (where my user name does NOT match the Postgres user name) which I have saved on Linux (where my username matches the Postgres name) then the first layer of each database complains about the non existing role (the Windows username) with a username/password dialog.

When I enter the correct username and leave the password empty then the other layers load fine.

Setting environment variable PGUSER to the correct Postgres username fixes this issue.

Somewhere the authcfg username is not used, but the password IS?

EDIT: The root cause is that the authcfg ID is different on the Windows machine, so that explains why we have to enter the username. It is unclear why the password does not have to be entered.

EDIT2: When I enter a wrong password the layer is still loading correctly. So it is reusing an existing connection, but passing no username, and libpq adds the system account name.

[sherzoddehqon]

the problem may be in IP of localhost, which changed and QGIS is trying to connect to old one