How to audit Login attempts

Hello,

Is there any way I can check login/logout attempts to a node?
I’m looking for something like:

user X logged in at time T1
Failed login attempt for user Y
user X logged out at time T2

I tried using EXPERIMENTAL_AUDIT, on table system.users, but no success so far:

root@localhost:26257/system> ALTER TABLE users EXPERIMENTAL_AUDIT SET READ WRITE;
pq: user root does not have CREATE privilege on relation users
And I can’t grant CREATE privilege to any user on that table =(

Any ideas?

Thanks in advance!

Hi Max,
thanks for your interest.
After a quick check it seems like we do not have a feature yet inside CockroachDB that would do what you want automatically. Even audit logging, if you had a way to overcome the permission hurdle, would not provide you “user X logged out” events, and would not distinguish between “logged in” and “failed login attempt”.

There are two things I can think of:

  • you could use a postgres proxy server (like haproxy) and inspects its logs
  • you could use your own SSL proxy which would inspect the TLS certificates presented by clients (this contains the username) and the reply given by the server to login attempts.

That said your request is legimiate and this ought to be available in CockroachDB. I will file an issue on your behalf.