QGIS failure in PostGIS connection: "ident authentication failed for user"

Question

I am trying to use the 'ident' authentication of for local PostGIS (2.5) server, so that each user can connect to his/her account without having to provide password.

The local connection (to 127.0.0.1) worked if I use trust in /etc/postgresql/11/main/pg_hba.conf:

host    all             all             127.0.0.1/32            trust
host    all             all             ::1/128                 trust

, but fails when trust is replaced with ident and if connection is made from QGIS (3.8.1).

ident authentication failed for user

The strange thing is that I can connect successfully (with ident authentication) without giving user names or password, both from the command line client psql and from pyscopg2 using python. But QGIS3 connection fails.

From documentation:

ident

Obtain the operating system user name of the client by contacting the ident server on the client and check if it matches the requested database user name. Ident authentication can only be used on TCP/IP connections. When specified for local connections, peer authentication will be used instead. See Section 19.3.5 for details.

peer

Obtain the client's operating system user name from the operating system and check if it matches the requested database user name. This is only available for local connections. See Section 19.3.6 for details.

Is this a bug in QGIS? Or am I missing something in the QGIS or PostGIS configuration?

How can I get ident working for QGIS?

Note: I googled the issue, some suggest using other authentication method like md5. But I specifically need to ident behavior for multiple users. md5 requires password, and trust is not secure. I need each user to access his/her own database without password, without allowing access to any other databases.

Answer 1

This ticket explains the issue, you need to use trust for QGis to work as it is explicitly setting the username in the connection.

By default PostgreSQL uses IDENT-based authentication and this will never allow you to login via -U and -W options. Allow username and password based authentication from your application by applying 'trust' as the authentication method for the JIRA database user. You can do this by modifying the pg_hba.conf file.

So you will either need to use trust or set up passwords (MD5) or other authentication system.