security: Add cluster setting option for password timeout

[piyush-singh]

Although we have the option to set a password expiry per user account using VALID UNTIL, it would be useful to some of our customers to be able to enforce a global password timeout for all users of a cluster via a cluster setting.

gz#7876

Jira issue: CRDB-2681

[fabiog1901]

A workaround has been identified as below:

If all DB users are set with the NOCREATELOGIN privilege, you would be able to create a single role with the CREATELOGIN privilege. This role could be attached to a single service which can run the alter user commands to set a password. You can then have all passwords set through this service which would enforce the VALID UNTIL parameter.

However, customer doesn't want to setup a service that resets passwords.

Fear is that a privileged user creates a new user in the database with privilege to access the UI. The password would never expire and then they can get into the UI as that users, untracked. This can be prevented by enforcing a cluster wide password expiration time.

[knz]

@nancy-vargas the code that creates SQL users is in pkg/sql/create_role.go.

Generally the creation of a user goes as follows:

• the user entry is created in the system.users table (userTableName)
• their role options (including VALID UNTIL) is written into system.role_options after that. see n.roleOptions.GetSQLStmts().

The general idea for this project is thus:

1. create two new cluster settings A and B (you'd choose the name judiciously, based on the pattern of existing cluster settings):
   • A is for the default value
   • B is for the maximum value
2. when creating a user, inspect the specified role options (n.roleOptions).
   • if there is no password, don't do anything special
   • if there's a password and VALID UNTIL is specified explicitly, use that.
   • if there's a password and VALID UNTIL is not specified, and the cluster setting A is non-zero, synthetize a virtual VALID UNTIL clause with the delay from the current time to the configured setting value.
3. look at the VALID UNTIL clause after step 2 (regardless of whether it was specified by the user or synthetized from A). If the cluster setting B is non-zero, and the VALID UNTIL clause is further in the future than the value specified by B, then clamp the value in the VALID UNTIL clause as per the cluster setting.
4. complete the user creation as usual.

[fabiog1901]

All - here's my understanding after discussing with customer:

The cluster-wide timeout setting should supersede any VALID UNTIL options set on the SQL prompt via any among ALTER USER, CREATE USER, ..

In other words, if there's a password and VALID UNTIL is specified explicitly, use MIN(VALID UNTIL value passed, the valid until cluster setting).

[knz]

@fabiog1901 I don't understand the use case. Can you explain how this would be used in practice.

What you suggest right now has immense and obvious UX shortcomings, so I must be misunderstanding what you mean.

[knz]

So I have done some investigation of my own and I think I understand what is going on.

There are two different requirements (both are useful)

• we want a default password expiration delay when none is specified.
• we also want a maximum password expiration delay to cap any specified value.

The first value is there as a security hygiene measure. The second value is there as a security compliance measure. Both are useful in different contexts. I will amend the explanation above accordingly.

Meanwhile, separately, @fabiog1901 delivered some words:

the customers wants a cluster setting for VALID UNTIL to avoid rough users being able to access the cluster after they have been deactivated using kerberos. so even if you set VALID UNTIL = somedate, if somedate is more than the cluster setting, they will use the cluster setting. They want password to expire after 1 hour max

For the sake of ensuring technical explanations become better over time, I'd like to point out this explanation uses very confused wording:

• if the deployment using kerberos, then by definition there are no SQL passwords inside cockroachdb and therefore the VALID UNTIL clause is not applicable.
• the explanation above is heavily confused with the difference between timestamps (in the VALID UNTIL clause) and durations (in the cluster settings). We don't place timestamps in cluster settings, nor durations in the VALID UNTIL clause. They have different mathematical units, so we can't use a MIN function.

[knz]

For the maximum delay, I just realized we may want that to apply when users log in, not when the role option is set (i.e. when user are configured).

[knz]

cc @jtsiros for triage