I'm trying to setup kerberos auth between my lab RHEL PSQL 13 and my AD with some success unless I try to configure user maps in pg_ident.conf as documented.
My psql user is user and my AD user is [email protected] . The later is used to connect by the users and the former is how users are defined in the DB. I need to strip the domain to match correctly with psql users.
The AD user has the machine SPN configured and the keytab has been set in psql configuration.
pg_hba.conf file:
# TYPE DATABASE USER ADDRESS METHOD
host all all 0.0.0.0/0 gss include_realm=1 krb_realm=DOMAIN.COM map=ad
pg_ident.conf file:
# MAPNAME SYSTEM-USERNAME PG-USERNAME
ad /^(.*)@DOMAIN\.COM$ 1円
ad /^(.*)@domain\.com$ 1円
Now when I try to connect using kerberos auth with [email protected] , in pgAdmin4 for example, it fails with the following in the psql logs:
LOG: no match in usermap "ad" for user "[email protected]" authenticated as "[email protected]"
FATAL: GSSAPI authentication failed for user "[email protected]"
DETAIL: Connection matched pg_hba.conf line 99: "host all all 0.0.0.0/0 gss include_realm=1 krb_realm=DOMAIN.COM map=ad"
If I set configure the pg_hba.conf as below instead and connect using user without the domain, I can connect just fine.
pg_hba.conf file:
# TYPE DATABASE USER ADDRESS METHOD
host all all 0.0.0.0/0 gss include_realm=0 krb_realm=DOMAIN.COM
What am I doing wrong here? Why aren't users mapping? I'm on RHEL8 running PSQL 13.
-
1What did you put in pgAdmin4 usernme field? "[email protected]"? If so, don't do that. If you want to log in as "user", put "user".jjanes– jjanes2021年10月27日 02:31:20 +00:00Commented Oct 27, 2021 at 2:31
-
seems that would match sAMAccountName passed on by ticket when using SSO (ie PSQL client on windows machine). Odd pgAdmin allows to set the username when it's not really necessary with kerberos! ThanksJulioQc– JulioQc2021年10月27日 14:37:55 +00:00Commented Oct 27, 2021 at 14:37
2 Answers 2
This question is the same problem.
The third column in pg_ident.conf must match the username that you are logging into the database as. In your case, login to the database/PgAdmin4 as just user, not the fully qualified [email protected] .
-
that is correct and pgAdmin shouldn't even ask for the username when kerberos is enabled imho.JulioQc– JulioQc2021年10月27日 14:39:11 +00:00Commented Oct 27, 2021 at 14:39
-
@JulioQc So then if I have more than one PostgreSQL accounts, I need to keep logging out of windows and back in as a different user? Also, pgAdmin doesn't know if Kerberos will be used for any given connection until half way through establishing it.jjanes– jjanes2021年10月27日 15:02:48 +00:00Commented Oct 27, 2021 at 15:02
-
@jjanes, changing the username wont change your ticket; you'll need to reauthenticate for the new user! you could logout/login or as most would do, 'run as' the user for a given application.JulioQc– JulioQc2021年10月28日 09:33:23 +00:00Commented Oct 28, 2021 at 9:33
-
@JulioQc the ident mapping file can authorize one ticket to log in as a number of different database usernames, so that changing the ticket is not needed.jjanes– jjanes2021年10月28日 11:56:10 +00:00Commented Oct 28, 2021 at 11:56
-
@jjanes that doesn't work; pgsql complains in the log with "LOG: provided user name (<user1>) and authenticated user name (<user2>) do not match" and rejects the connection.JulioQc– JulioQc2021年10月28日 12:33:30 +00:00Commented Oct 28, 2021 at 12:33
Ok turns out the setup works as intended in other clients when not specifying the username, as it takes the one in the kerberos ticket, aka the sAMAccountName. For example psql client on Windows will work without specifying a username.
If I put the sAMAccountName in the pgAdmin username field, it works. In my case the UPN does not match the sAMAccountName+REALM but in most cases they would match so using the left side of the UPN is safe to assume equals the sAMAccountName property.
However, I still don't know why the pg_ident.conf doesn't strip the domain name if I provide one...