sql: use follower reads for internal authentication-related queries

[rafiss]

Is your feature request related to a problem? Please describe.
When authenticating a non-root user, CockroachDB must query the system.users and system.role_options tables. See

cockroach/pkg/sql/user.go

Line 93 in ea4c2fa

func retrieveUserAndPassword(

In a multi-region cluster, the leaseholder for these system ranges may live in a different region than the node that is being connected to. This causes multiple cross-region network hops, thereby increasing the time it takes to establish the connection. Some applications configure aggressive connection timeouts, so in the worst case, this could prevent an application from connecting.

Describe the solution you'd like
Use follower reads for the above queries, and any other queries that are performed during authentication.

Describe alternatives you've considered
A cache could be added to avoid the cross-region hop. This could become hard to manage, since we'd need to make sure to invalidate stale data correctly.

Additional context
Customers have reported concerns about connection latency being too high for non-root users in multi-region clusters. See https://github.com/cockroachlabs/support/issues/742 and https://github.com/cockroachlabs/support/issues/736

[ajwerner]

cc @aaron-crl regarding the implications of the semantics of only lazily realizing password changes.

[rafiss]

Yeah that would be my main concern. At best, it seems like a pretty confusing user experience, and at worst a big security hole.

[ajwerner]

Another option that I think I prefer is to using a leasing protocol similar to how we do with system.role_membership that allows nodes to cache the password hash.

[ajwerner]

This has come up before.

[rafiss]

Note that #58671 #58674 and #58677 eliminate much of the latency of the internal auth-queries, so this issue is lower in priority.

[aaron-crl]

cc @aaron-crl regarding the implications of the semantics of only lazily realizing password changes.

Checking auth against anything other than the current state of the database introduces the potential for authentication race conditions in certain circumstances. We should avoid performing any auth checks against data or permissions that are or may be out of date.

[ajwerner]

In principle we could get authn down to 0 RTTs without too much lift. I'm inclined to close this issue to instead open an enhancement about caching hashes and using leasing rather than anything to do with follower reads.

[ajwerner]

Closing in favor of #58869.