ActiveDirectory Authentication Support

[vovchykbratyk]

Feature description

Need QGIS to be able to authenticate to resources that support AD authentication (e.g., PostGIS, MSSQL, Oracle, etc).

Additional context

Most large professional enterprises use AD for domain resource access/authentication management. While QGIS does not support AD, it inhibits its potential for adoption in enterprises.

[komima]

For PostGIS at least (I'd assume other drivers could work similarly?) just omitting username & password from the datasource uri works quite well.

Without explicit connection details given, postgresql provider (libpq) will fall back to use whatever is configured by standard env vars and also if those all are missing, at least domain-joined Windows machines (that fetch a kerberos TGT on login) will fetch a kerberos ticket for the db host and use gssapi automatically without user ever needing to give any login details. This requires the db itself is configured to support gssapi.

[jonnyforestGIS]

This kind of authentication AD can be done using LDAPS protocol and set as a backend authentication service in the databases.

Nowadays I have PostgreSQL with ldaps configuration that binds with users of AD using LDAPS protocol. So in this scenario, if you add a user in AD, you can map this user in PostgreSQL and login in QGIS. The only thing that you need to have is synchronization between AD (via LDAPS protocol) users and PostgreSQL users to keep all updated (users and rules).

The Active Directory is a proprietary service from Microsoft and uses LDAP or LDAPS as core protocol to communicate. Quick search I found this to explain the difference between AD vs LDAP (https://www.n-able.com/blog/difference-between-ldap-ad).

Some extra LDAP configuration info:
Database configuration:
https://www.postgresql.org/docs/current/auth-ldap.html
https://docs.oracle.com/cd/B14099_19/idmanage.1012/b14082/intro.htm

Some tools or hacks for LDAP sync configurations with AD:
https://ldap2pg.readthedocs.io/en/latest/
https://wiki.postgresql.org/wiki/LDAP_Authentication_against_AD
https://github.com/larskanis/pg-ldap-sync

[dafvid]

For PostGIS at least (I'd assume other drivers could work similarly?) just omitting username & password from the datasource uri works quite well.

Without explicit connection details given, postgresql provider (libpq) will fall back to use whatever is configured by standard env vars and also if those all are missing, at least domain-joined Windows machines (that fetch a kerberos TGT on login) will fetch a kerberos ticket for the db host and use gssapi automatically without user ever needing to give any login details. This requires the db itself is configured to support gssapi.

I'm having trouble getting this to work. I'm getting Unable to connect to server:\n\nconnection failed: connection to server at "XX.XX.XX.XX", port 5432 failed: fe_sendauth: no password supplied. Can you give an example on how the datasource uri should look like?

I'm guessing the "omit user and password" is not working and libpq is not picking up that it should use GSSAPI. Is the libpq that's shipped with QGIS built with GSS support? How can I test this? It would be nice to have a checkbox "Use Kerberos" that ensures that the underlying libpq installation tries to use a kerberos ticket to sign in.

[komima]

I'm having trouble getting this to work. I'm getting Unable to connect to server:\n\nconnection failed: connection to server at "XX.XX.XX.XX", port 5432 failed: fe_sendauth: no password supplied. Can you give an example on how the datasource uri should look like?

I'm guessing the "omit user and password" is not working and libpq is not picking up that it should use GSSAPI. Is the libpq that's shipped with QGIS built with GSS support? How can I test this? It would be nice to have a checkbox "Use Kerberos" that ensures that the underlying libpq installation tries to use a kerberos ticket to sign in.

For us standard network installer QGIS works on Windows.

In this case it seems the pg_hba is not configured correctly? Authentication method is decided on the database server side after the initial connection, and only a single method is offered to the client based on which row of pg_hba configuration matches. For this to work the user/db/source etc. of the connection must first match the GSS row in pg_hba. For example if there is a higher priority scram-sha/md5 row on the pg_hba which the connection attempt happens to match also, that method is offered instead to the user and GSS is never tried.

If the GSS is matched correctly, the error would probably be a bit different, since for that to work AFAIK you need to connect via full hostname, not an IP.

There is also a new require_auth/PGREQUIREAUTH option with PG 16+, I have not tested if setting that to gss allows pg_hba to match lower priority rows, and documentation does not explicitly mention that as supported. Based on that I'd assume the option only allows better error handling if the server does not match the required method, not as a filter for pg_hba rows.

[dafvid]

Thank you! After changing the order of rows in pg_has it used GSS but QGIS is only passing my username as user and not my full principal (with @). If I set the user manually it works but I need to set up layers without username for shared projects (for QGIS-server).

edit:
so I changed include_realm to 0 in pg_hba.conf and now it works! But now I can't distinquish between AD-users and database users. =/

[komima]

Thank you! After changing the order of rows in pg_has it used GSS but QGIS is only passing my username as user and not my full principal (with @). If I set the user manually it works but I need to set up layers without username for shared projects (for QGIS-server).

Oh I see now, "skip the username and it works" relies on these two facts: libpq default username is the operating system username (ie. principal without the realm), and we have been using username maps (since theres only one realm) that removes the realm part from the auth user (same behaviour as the include_realm=0 option).

This way QGIS will connect as "USER", ticket is for "USER@DOMAIN" and with the username mapping to "USER" that ticket is allowed to connect as "USER", I previously thought the ticket name is mapped and its used as the login name, but those are actually two separate concepts.

For our use case local db users are always lowercase and AD users are uppercase, but also we have been using a pg_hba gss config with user column match like +ad_user. When we sync the users from AD as members of that role, we can both check which users are AD users, and allow both GSS and password auth from same host with the +group match higher in priority. Maybe this works for you as well?

There could be improvements to QGIS as well to support this kind of use better, for example to allow for user-environment based configurations for project layers parameters, so there could a project file with a layer uri like user={some-env-var} templating or something like that.

[dafvid]

Thanks alot for the quick replies!
It seems that passing the user without realm is the default behaviour for kerberos. I think I'll use your idea but make a pw_user group instead. For connections that for one reason or other can't handle kerberos.

I went with include_realm=0 och skipped the map. In the database I'm using just the AD username without REALM and it's working great.

This funcionality should really be more clearly documented somewhere. I couldn't find anything in the documentation. Just leaving out username and password to use GSS is quite a hidden feature.

[dafvid]

I guess adding "Trusted connection" checkbox as with the SQL Server connector would go a long way. Just clear the username and password. At first I was looking for that checkbox for PostGIS but couldn't find it. I assumed that it was not supported until I found this thread.