pgwire,sql: user account expiration

[knz]

Requested by large customer account:

• disable a user (but not delete) temporarily
• disable a user at a specific date
• disable a user "some time after" the last log-in

The first two points are equivalent, the last is separate.

[knz]

cc @BramGruneir

[knz]

cc @rolandcrosby @piyush-singh

[knz]

Discussed this with @bdarnell today: we can add a column on system.users to track the timestamp of last login. To avoid/reduce contention, we can round the timestamp to the closest second, and only write the new value if it hasn't changed (CPut).

[bdarnell]

#29633 also mentions disabling users.

disable a user "some time after" the last log-in

After the last login event, or after the last logged-in session ends? Should this imply that any existing sessions are terminated at that point?

only write the new value if it hasn't changed (CPut).

CPut still works like a write for contention purposes. To get any reduction in contention, we need to do our own reads before writing.

[knz]

After the last login event,

After the last login (regardless of how long the session was)

Should this imply that any existing sessions are terminated at that point?

The user understand they can use CANCEL SESSIONS (with a suitable filter on users) to preempt existing sessions

To get any reduction in contention, we need to do our own reads before writing.

ok thanks for clarifying

[mberhault]

The standard postgres way is a combination of:

• VALID UNTIL 'timestamp' for CREATE|ALTER ROLE
• NOLOGIN (as opposed to LOGIN), also on CREATE|ALTER ROLE

The former disables the password (not sure if that applies to other authentication methods) at the specified time. The latter prevents the user from logging in at all.

The only odd case in the ones mentioned in the original message is "disable some time after the last login". That's a very specific request.

[RoachietheSupportRoach]

Zendesk ticket #3849 has been linked to this issue.

[knz]

@ajwerner made me realize we already have a form of user expiration: the expiration field in the client TLS certificate. Customers can use that already.

[knz]

@RichardJCai when you have some idea of what's to be done here, can you explain it to me? I'm tasked with adding some audit logging on a separate area and the two things need to combine gracefully.

[RichardJCai]

@knz Sure thing, I'll message you on Slack once I have a plan.

[RichardJCai]

@solongordon
Some thoughts about VALID UNTIL

Here is postgres' documentation for valid until

VALID UNTIL 'timestamp'
The VALID UNTIL clause sets a date and time after which the role's password is no longer valid. If this clause is omitted the password will be valid for all time.

(From what I understand, if Postgres has password required - then all users must have a password to login. If no password is required, the password is ignored - this means even if the password is "expired" then the user can still login see here for more)

To copy postgres' behavior, users must have a "password" in order to be disabled.
Do we want this behavior or should valid until simply not allow a user to login after the timestamp?

[knz]

Good finding. I had not expected that pgs expiry was a property of the password and not of the account. I wonder if this is a misdesign or a good feature which we were not trained to fully appreciate. Both pg and crdb allow other authn methods than password checks. On the one hand, Pgs behavior suggests that it doesn't support user account expiration across all authn methods. This would suggest an unwanted assymetry and thus a misdesign. However OTOH maybe the idea is to delegate to the authn method the responsibility to decide account validity. This is meaningful : TLS has cert expiry, kerberos has its own expiry, AD too etc. I have a vague feeling we may want to copy pgs behavior because there is probably more history and motivation in their choices compared to our relative collective inexperience. That said I also appreciate that our customers want an answer. What about this : - make passwords work like pg; - make gss delegate to kerberos ( it already does) ; - make a new something for cert authn. The new something is something we're missing anyway and eventually needed to support : revocation lists for client certs. @aaron-crl what do you think?

…

-- Verstuurd vanaf mijn Android apparaat met K-9 Mail. Excuseer mijn beknoptheid.

[aaron-crl]

• make passwords work like pg;

Following the pg design pattern has the advantage of behaving as other users may be accustomed and it potentially backed by perspectives we have not captured here.

As a security admin, if I seek to functionally disabled user's access to a system, I'd like to have user level security controls and not have to know all the ways that a user might have been granted access to the system. I'm pretty sure setting "NOLOGIN" does this, though doesn't trigger based on last login.

So I can see reasons to do both standard pg behavior for expired passwords, but also have a user activity trigger to lock login.

• make a new something for cert authn. The new something is something we're missing anyway and eventually needed to support : revocation lists for client certs.

I think this feature should exist as a base access control regardless. This is a use case where many of the traditional security challenges related to revocation lists don't apply.

TL;DR: My gut feeling is that we:

1. Do password expiration the pg way for consistency.
2. Create a user account lock feature (which can terminate existing sessions) with a trigger that can be time since last login. This will lock the account regardless of whether the user has valid credentials.
3. Implement revocation lists for client certs so that we have a means of revoking credentials on demand.

[RichardJCai]

• make passwords work like pg;

Following the pg design pattern has the advantage of behaving as other users may be accustomed and it potentially backed by perspectives we have not captured here.

As a security admin, if I seek to functionally disabled user's access to a system, I'd like to have user level security controls and not have to know all the ways that a user might have been granted access to the system. I'm pretty sure setting "NOLOGIN" does this, though doesn't trigger based on last login.

So I can see reasons to do both standard pg behavior for expired passwords, but also have a user activity trigger to lock login.

• make a new something for cert authn. The new something is something we're missing anyway and eventually needed to support : revocation lists for client certs.

I think this feature should exist as a base access control regardless. This is a use case where many of the traditional security challenges related to revocation lists don't apply.

TL;DR: My gut feeling is that we:

1. Do password expiration the pg way for consistency.
2. Create a user account lock feature (which can terminate existing sessions) with a trigger that can be time since last login. This will lock the account regardless of whether the user has valid credentials.
3. Implement revocation lists for client certs so that we have a means of revoking credentials on demand.

For point 2, terminating existing sessions can be tricky (and expensive) since we would have to check before any action. I think for the first pass for this ticket, we should look to copy pg's password expiry unless the customer specifies otherwise.

[knz]

For point 2 we have a mechanism for this already. The operator who wants to kick user X out would:

1. set NOLOGIN for X
2. use CANCEL SESSIONS with a filter on username X

and then they get what they want.

[nstewart]

Hi team, one other point re: VALID UNTIL. This seems to be a pattern that comes up when creating short-lived, instance-specific credentials in microservices. Take a look at how valid until is used for Vault's postgres SQL engine: https://www.vaultproject.io/docs/secrets/databases/postgresql/

If we don't have the bandwidth to support VALID UNTIL for this release, I'll put it on the backlog for consideration in the 20.2 timeframe.