PostgreSQL, SSSD, Kerberos, and local logins

I’m having a little difficulty troubleshooting something…I have SSSD setup to use either kerberos or local user password authentication. Local users are ignored in sssd.conf. A local user can log in via ssh using a local password, and kerberos users can log in via ssh using their kerberos password (assuming a local account is also setup). I’m trying to get PostgreSQL to behave the same way ssh does, however it only accepts kerberos logins and rejects all local user logins.

When attempting a PostgreSQL connection using a local user account, instead accepting a user credentials, all authentication fails.

Some relevant error messages (local user = “logwriter”; psql database = “Syslog”):

Syslog

unix_chkpwd[939]: check pass; user unknown
postmaster[938]:  logwriter Syslog x.x.x.41(37414) authentication: pam_unix(postgresql:auth): conversation failed
postmaster[938]:  logwriter Syslog x.x.x.41(37414) authentication: pam_unix(postgresql:auth): auth could not identify password for [logwriter]
lpostmaster[942]:  ogwriter Syslog x.x.x.41(37418) authentication: pam_unix(postgresql:auth): authentication failure; logname= uid=26 euid=26 tty= ruser= rhost=x.x.x.41  user=logwriter
postmaster[942]:  logwriter Syslog x.x.x.41(37418) authentication: pam_sss(postgresql:auth): authentication failure; logname= uid=26 euid=26 tty= ruser= rhost=x.x.x.41 user=logwriter
postmaster[942]:  logwriter Syslog x.x.x.41(37418) authentication: pam_sss(postgresql:auth): received for user logwriter: 10 (User not known to the underlying authentication module)

CLI output:

Password for user logwriter: 
psql: error: could not connect to server: could not initiate GSSAPI security context: Unspecified GSS failure.  Minor code may provide more information
could not initiate GSSAPI security context: Configuration file does not specify default realm
FATAL:  PAM authentication failed for user "logwriter"

sssd.conf:

[domain/default]
cache_credentials = True

[sssd]
config_file_version = 2
reconnection_retries = 3
sbus_timeout = 30
services = nss, pam
domains = ad.example.com
id_provider = proxy

[nss]
filter_groups = root
filter_users = root,logwriter
reconnection_retries = 3

[pam]
reconnection_retries = 3

[domain/ad.example.com]
id_provider = proxy
proxy_lib_name = files
enumerate = True
auth_provider = krb5
krb5_server = kerberos.example.com
krb5_realm = AD.EXAMPLE.COM
cache_credentials = True
krb5_store_password_if_offline = True
chpass_provider = krb5
krb5_kpasswd = kerberos.example.com
offline_credentials_expiration = 0
krb5_renewable_lifetime = 7d
krb5_renew_interval = 30m

Additionally, I’ve attempted to replace the default /etc/pam.d/postgresql configuration file with /etc/pam.d/password-auth, just for testing purposes, but that still fails. Does anyone have an idea where the problem might lie?

Thanks.

So, I cannot help with the Postgresql issue, but I am curious with this:

You say you have SSSD set up. This negates the need to have local users with the same name as domain users if you want the domain users to be able to log in. SSSD caches domain user information and NSS will read from that the same way it reads from ‘files’ (/etc/passwd & /etc/shadow).

Also, you should never really use enumerate = true within sssd.conf for production environments, as this option was put in place merely for debugging purposes.

IMO, your sssd.conf should look like this:

[sssd]
config_file_version = 2
reconnection_retries = 3
sbus_timeout = 30
services = nss, pam
domains = ad.example.com

[nss]
filter_groups = root
filter_users = root,logwriter
reconnection_retries = 3

[pam]
reconnection_retries = 3

[domain/ad.example.com]
id_provider = ad
cache_credentials = True
krb5_store_password_if_offline = True

SSSD will use the Host Principals from the Keytab which resemble MACHINENAME$@AD.EXAMPLE.COM to bind with Active Directory. The id_provider option does not belong within the [sssd] section. I also removed chpass_provider and auth_provider because they default to whatever id_provider is set to.

Additionally, no domain information is required to be added into the [domain/ad.example.com] section because SSSD will auto-fill with whatever is set within the [domain/...] specific line.

1 Like

I assumed the correct way to handle this was to have local users that map to domain users. If any domain users could log into a host, I wonder how one would prevent access from all but a small subset of users.

Also, I see I need to tweak my sssd.conf some, and I could probably stand to investigate this a bit more.

These systems simply use kerberos for authentication, but are not part of the present AD domain (and I’m not certain I really want them to be).

Ultimately, I am not entirely certain whether or not my problems lie in how I configured SSSD, or if it’s more of a problem with the PostgreSQL PAM configuration. I’ll do some more testing and see if I can narrow it down.

Thanks for the help Striker!

With SSSD, you do not need to do that. SSSD caches domain users locally.

Within SSSD, you can set access control options. Since you are connecting SSSD with Active Directory, you can configure:

access_provider = ad
ad_gpo_access_control = enforcing

After that, restart SSSD and configure GPOs within Active Directory to control user access to this machine from there. Or, you can just use the simple access provider, like so:

access_provider = simple
simple_allow_groups = user1, user2, bob, john, thanos, etc.

It’s not a requirement, but I would suggest considering it. Having the machine joined to AD for SSSD to be able to bind has many advantages such as user ID Mapping, GPO processing, SSO capabilities (using GSSAPI), etc.

A good way to narrow down the issue is to simply try logging into the system through SSH. If you are successful, SSSD is working how it is configured.

If you want to diagnose Postgresql, check out this link which will help with diagnosing login issues.

1 Like

To add another reply to this comment, if you’re looking to have some fun, you might consider deploying a FreeIPA domain to join your *nix clients to. This would allow you to have a single connection to Active Directory from FreeIPA (instead of multiple individual connections from directly-joined clients).

To simplify, FreeIPA would have a trust relationship with Active Directory and AD would see it as a single machine. All Fedora clients joined to IPA would still have the ability to be logged into using AD accounts (because there is a trust).