Authentication to AD thru PAM (sssd, krb5, and samba) on Ubuntu

[borange]

Good afternoon fellows, it been a while.

I have been pondering on the idea of using Active Directory with PostgreSQL and xTuple.

I have reviewed the article found here:
Authenticating through LDAP

This describes loading users and groups into xtuple using psycopg and python-ldap and setting the pg_hba to send authentication requests to ldap. While this seems like a fine approach, I was thinking about sending my authentication request to PAM to be handled by sssd, krb5, and samba and thus AD.

I have had a lot of luck with the sssd, krb5, and samba stack as I plan to use this backend for some aspcore web applications elsewhere in the environment.

Does this sound like something that can work?

When does that ldap python script get used?

[jim.wirt]

Just now noticed this post so even if it is a bit old I thought I'd respond.

I've been using ldap to AD authentication for several years now. I've had no issues and didn't have to install a lot of Samba or Pam stuff. All I had to do add some lines to my pg_hba.conf file. This environment allows both local and ldap authentication as appropriate. Here is a snippet of the related config file:

host all admin 172.20.0.0/16 md5
host all admin 172.16.0.0/23 md5
host @memsb_dbs memsb-rw 172.16.1.27/32 md5
host @memsb_dbs memsb-rw 172.16.1.99/32 md5
host all domo_ro 172.16.0.0/23 md5
host all @fpp_local 172.16.0.0/23 md5
host all @fpp_local 172.20.0.0/16 md5
host all @fpp_local 172.16.3.0/24 md5
host all all 172.16.0.0/23 ldap ldapserver=fpp-dc01.ad.osufpp.org ldapprefix="" ldapsuffix="@ad.osufpp.org"
host all all 172.20.0.0/16 ldap ldapserver=fpp-dc01.ad.osufpp.org ldapprefix="" ldapsuffix="@ad.osu

[szuke]

This looks like a great post to bookmark for later. Thanks for sharing.

Scott

[borange]

Thanks Jim, this looks like a great way to hook in ldap. I haven't explored this completely but I will be happy to turn back with some results when I do.

[fredcyr]

The official documentation linked in the first post doesn't work as PG (9.5 in my case) can't parse th eLDAP options.

What Jim posted is working except for a tiny little problem, the postgres user needs to be configures with Superuser access right. If not, I'm receiving this error:

[jim.wirt]

Hi Fred,
I don't remember having to assign any special linux system rights to the "postgres" user. The user postgres/xtuple account of course has to be a member of the "xtrole" postgres group but I believe the xtuple client creates them when you add them as an enabled user.

I my French is VERY bad but it the error message seems to indicate that the authenticated user can access the view named "usr". When I look at that view I see that it grants all acces to the postgres group named "xtrole". I wonder if perhaps the user attempting to authenticate is NOT a member of the xtrole group.

Jim

[fredcyr]

Jim, your French is great! It basically says that a system error happened in login2.cpp, the rest is English.

The user is part of the xtrole group. If I reactivate MD5 authentication for the user can log in without problem. I'll look into the access rights of the usr view just in case.

Thanks for the pointers.

[fredcyr]

Jim you were right. The access rights were not restored correctly as I didn't used the right version of pg_restore.

This is working great when users get the password right the first time but if a wrong password is entered, Postgres will try to authenticate against the LDAP server and even if it gets the "Invalid Credentials" message, it will retry ten times.

This will lock the user's account each time. Anybody knows where in PG config files I can change the number of retry?

Even with MD5 it retry a lot of times before giving up.

[jim.wirt]

Fred,
I'm ignorant regarding postgres retry attempts. When I tail the postgres log of the systems I support I usually see one failed attempt and then one successful attempt. They happen very quickly so the user never is slowed down. My guess is that the client is using two first and encrypted method and then falls back to un-encrypted.

I'm not sure if the 10 attempts is coming from postgres or if it is coming from the xTuple client. Do you see the same problem when you attempt to login with ldap credentials from sort of database tool like pgadmin or some other simple way?

Jim

[gmoskowitz]

Fred and Jim,

The xTuple ERP 4.12.0 and later desktop client tries to log in 12 times and this is built in to the login window. Different versions of PostgreSQL and its connection libraries supported different login settings, and different versions of xTuple ERP imposed different requirements. Where does 12 come from?

• 4 possible values for the connection options

force TLS | name the client (license checking)
--------- | ----------------
     Y    |       Y
     Y    |       N
     N    |       Y
     N    |       N

• 3 versions of the password
  • xTuple enhanced authentication
  • plain password authentication
  • OpenMFG enhanced authentication

Between versions 4.1 and 4.12 I think the count was 6.

We'll talk in Development to try reducing this count.

Gil

[fredcyr]

Thanks Gil

Maybe have a switch to force only one type of authentication would be great.