User name maps and kerberos not interacting as expected

[andy-dulson]

Describe the problem

We were hoping to use user name maps to create foo_super user accounts that can be authed to with user foo's krb credentials. This works in postgres, but doesn't seem to in cockroach.

To Reproduce

What did you do? Describe in your own words.

These steps were on a v21.2.17 cluster:

(1) Create db users that match the system username, and the system username with a _super suffix:

root@crdb:26257/defaultdb> \du
        username       | options | member_of
-----------------------+---------+------------
...
  adulson              |         | {}
  adulson_super        |         | {admin}

(2) The cluster is already set-up (as per these instructions) so that kerberos authentication works for the user which matches the system username:

$ psql --no-psqlrc -h crdb -d defaultdb -p 26257 -c "select current_user"
 current_user 
--------------
 adulson
(1 row)

In particular

root@crdb:26257/defaultdb> show cluster setting server.host_based_authentication.configuration;
  server.host_based_authentication.configuration
--------------------------------------------------
  host all all all gss include_realm=0
(1 row)

(3) Set server.identity_map.configuration to the same config we have in postgres:

defaultdb> show cluster setting server.identity_map.configuration;
  server.identity_map.configuration
-------------------------------------
  user /^(.*)@DOMAIN\.COM$ \1
  user /^(.*)@DOMAIN\.COM$ \1_super
(1 row)

(4) At that point, I expect the -U argument to psql to allow me to login as adulson_super using adulson's krb credentials. That's not what happens though:

$ psql --no-psqlrc -h crdb -d defaultdb -p 26257 -c "select current_user" -U adulson_super
 current_user 
--------------
 adulson
(1 row)

Expected behavior
In step four, I expect the cluster to recognise me as user adulson_super.

Additional data / screenshots

This works on postgres; with the same config in pg_ident.conf as we set in etting server.identity_map.configuration we see:

$ psql -h postgres -d postgres -U adulson -c "select current_user"
 current_user
--------------
 adulson
(1 row)

$ psql -h postgres -d postgres -U adulson_super -c "select current_user"
 current_user  
---------------
 adulson_super

Environment:

• CockroachDB version: v21.2.17
• Server OS:

  $ cat /etc/redhat-release 
  Rocky Linux release 8.6 (Green Obsidian)
  

• Client app: psql (since cockroach sql doesn't support kerberos)

Additional context
What was the impact?

Add any other context about the problem here.

Jira issue: CRDB-22446

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

Hoot - a bug! Though bugs are the bane of my existence, rest assured the wretched thing will get the best of care here.

I have CC'd a few people who may be able to assist you:

• @cockroachdb/sql-experience (found keywords: pg_)
• @abhinavg6 (commented on release-21.2: pgwire: Add dynamic user identity mapping #74459)
• @bobvawter (author of release-21.2: pgwire: Add dynamic user identity mapping #74459)

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.}

[knz]

@andy-dulson many thanks for filing this.
Can I ask about point (3):

defaultdb> show cluster setting server.identity_map.configuration;
  server.identity_map.configuration
-------------------------------------
  user /^(.*)@DOMAIN\.COM$ \1
  user /^(.*)@DOMAIN\.COM$ \1_super

Do you get any better behavior if you remove the leading / from the regular expression?

[andy-dulson]

Removing the leading / causes this error:

root@crdb:26257/defaultdb> set cluster setting server.identity_map.configuration = 'user ^(.*)@DOMAIN\.COM$ \1
user ^(.*)@DOMAIN\.COM$ \1_super';
ERROR: saw \1 substitution on line 1, but pattern contains no subexpressions

[bobvawter]

@knz, I suspect this is because the auth API assumes there's a 1:1 mapping of external system identity to database role. If you look at this code at the top level, we assume that the external auth API is going to provide a single replacement identity, and we don't check that against the user/role name that the client requests. Maybe behaviors.ReplacementIdentity() should return a slice and we treat the incoming username as a request for one of the possibilites?

cockroach/pkg/sql/pgwire/auth.go

Lines 120 to 141 in 53b1dea

	// Choose the system identity that we'll use below for mapping
	// externally-provisioned principals to database users.
	var systemIdentity username.SQLUsername
	if found, ok := behaviors.ReplacementIdentity(); ok {
	systemIdentity = found
	ac.SetSystemIdentity(systemIdentity)
	} else {
	systemIdentity = c.sessionArgs.User
	}
	c.sessionArgs.SystemIdentity = systemIdentity
	// Delegate to the AuthMethod's MapRole to choose the actual
	// database user that a successful authentication will result in.
	if err := c.chooseDbRole(ctx, ac, behaviors.MapRole, systemIdentity); err != nil {
	log.Warningf(ctx, "unable to map incoming identity %q to any database user: %+v", systemIdentity, err)
	ac.LogAuthFailed(ctx, eventpb.AuthFailReason_USER_NOT_FOUND, err)
	return connClose, c.sendError(ctx, execCfg, pgerror.WithCandidateCode(err, pgcode.InvalidAuthorizationSpecification))
	}
	// Once chooseDbRole() returns, we know that the actual DB username
	// will be present in c.sessionArgs.User.
	dbUser := c.sessionArgs.User

[knz]

Thanks for breaking it down for me bob. Your explanation seems reasonable.