search

cockroachdb / cockroach

CockroachDB — the cloud native, distributed SQL database designed for high availability, effortless scale, and control over data placement.
https://www.cockroachlabs.com
Other
30.03k stars 3.8k forks source link

sql: extend admin logging to any role with the "SQLLOG" role option #61545

Open knz opened 3 years ago

knz commented 3 years ago

This is a followup to #56149, due to an extra push by @fabiog1901

A customer wants the ability to force statement logging for all human operators that access a cluster via SQL. These human operators are identified by a special role, which is different from the roles used by client apps.

They also are human meaning that the rate of their queries is low (entered manually), they are less performance sensitive and therefore would be OK with the overhead of logging all their activity.

So in other words, the customer wants the ability to mark a role such that any session with that role gets statement logging always enabled.

This is a natural extension to the "admin logging" feature - however instead of enabling logging just due to membership to the admin role, we'd enable logging due to a role option, which can be granted on a per-role or per-user basis.

They are not satisfied with the audit logging feature (because they want to log all statements, not just those that touch tables), and not satisfied with the admin logging feature (because these human operators are not granted the admin role, for security reasons). That motivates a separate feature altogether.

cc @solongordon @RichardJCai for routing.

gz#7794

Jira issue: CRDB-2998

knz commented 3 years ago

@piyush-singh that will need a new prioritization push, I'll let you investigate with Fabio and/or Bram. I'm not excited about the fact we're realizing this so far in the cycle, and I'm not sure we'll be able to backport, but probably we can't ignore it either. The ask is reasonable and matches other security scenarios I've heard about.

RichardJCai commented 3 years ago

They also are human meaning that the rate of their queries is low (entered manually), they are less performance sensitive and therefore would be OK with the overhead of logging all their activity.

Giving this some initial thought, I'm not sure the overhead would be isolated to those with the role option as we'd have to check the role option for every user regardless. Maybe we could gate this behind a feature flag if we don't want the overhead to apply to every user?

piyush-singh commented 3 years ago

Quick follow up here. Met with the customer that requested this and noted that we have the option to log all SQL statements executed by admins in the sensitive_access channel. This satisfies the immediate concern for 21.1. They are still interested in exploring the ability to log SQL for all users associated with a specific role as described above, but are okay with that change happening beyond 21.1.

rafiss commented 2 years ago

I feel like observability-infra is a better home for this now -- I'll move it there.