Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Create a new role option NOSQLLOGIN to allow DB Console access and restrict SQL shell access #74482

Closed
thtruo opened this issue Jan 5, 2022 · 3 comments · Fixed by #74706
Closed

Create a new role option NOSQLLOGIN to allow DB Console access and restrict SQL shell access #74482

thtruo opened this issue Jan 5, 2022 · 3 comments · Fixed by #74706
Assignees
@dhartunian
Labels
A-webui-security C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception)

Comments

@thtruo
Copy link
Contributor

thtruo commented Jan 5, 2022

Describe the solution you'd like
We want to create a new role option called NOSQLLOGIN so that any DB user with this role option can access the DB Console but not be able to get SQL shell access. This will give customers more granular control over how to manage what access their users have within a cluster

CREATE ROLE app_developers WITH NOSQLLOGIN;

Epic: CRDB-12102
@thtruo thtruo added C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) A-webui-security T-observability-inf labels Jan 5, 2022
@dhartunian dhartunian mentioned this issue Jan 11, 2022
Merged
dhartunian added a commit to dhartunian/cockroach that referenced this issue Jan 12, 2022
@dhartunian
sql: add NOSQLLOGIN role which restricts SQL CLI only
434af3b 
Previously, in order to restrict user login ability, the `LOGIN` and
`NOLOGIN` role options were available which would restrict both SQL and
DB Console login ability.

This change adds the `NOSQLLOGIN` (and its inverse: `SQLLOGIN`) role
option in order to provide the ability to disable SQL CLI logins from
users while retaining DB Console login ability.

TODO(davidh): the API in authentication.go is clunky at the moment. I
should consider other design approaches to separate the login flags.

Resolves cockroachdb#74482

Release note (sql change): A new role option is now available,
`NOSQLLOGIN` (and its inverse `SQLLOGIN`), which restricts SQL CLI login
ability for a user while retaining their ability to login to the DB
Console (as opposed to `NOLOGIN` which restricts both SQL and DB
Console). Without any role options all login behavior remains permitted
as it does today. OIDC logins to the DB Console continue to be permitted
with `NOSQLLOGIN` set.
dhartunian added a commit to dhartunian/cockroach that referenced this issue Jan 14, 2022
@dhartunian
sql: add NOSQLLOGIN role which restricts SQL CLI only
f8997c5 
Previously, in order to restrict user login ability, the `LOGIN` and
`NOLOGIN` role options were available which would restrict both SQL and
DB Console login ability.

This change adds the `NOSQLLOGIN` (and its inverse: `SQLLOGIN`) role
option in order to provide the ability to disable SQL CLI logins from
users while retaining DB Console login ability.

Resolves cockroachdb#74482

Release note (sql change): A new role option is now available,
`NOSQLLOGIN` (and its inverse `SQLLOGIN`), which restricts SQL CLI login
ability for a user while retaining their ability to login to the DB
Console (as opposed to `NOLOGIN` which restricts both SQL and DB
Console). Without any role options all login behavior remains permitted
as it does today. OIDC logins to the DB Console continue to be permitted
with `NOSQLLOGIN` set.
craig bot pushed a commit that referenced this issue Jan 19, 2022
@dhartunian @ecwall @rytaft
Merge #74706 #75088 #75145
40daa53 
74706: sql: add NOSQLLOGIN role which restricts SQL CLI only r=rafiss,kylepatron-cockroachlabs a=dhartunian

Previously, in order to restrict user login ability, the `LOGIN` and
`NOLOGIN` role options were available which would restrict both SQL and
DB Console login ability.

This change adds the `NOSQLLOGIN` (and its inverse: `SQLLOGIN`) role
option in order to provide the ability to disable SQL CLI logins from
users while retaining DB Console login ability.

Resolves #74482

Release note (sql change): A new role option is now available,
`NOSQLLOGIN` (and its inverse `SQLLOGIN`), which restricts SQL CLI login
ability for a user while retaining their ability to login to the DB
Console (as opposed to `NOLOGIN` which restricts both SQL and DB
Console). Without any role options all login behavior remains permitted
as it does today. OIDC logins to the DB Console continue to be permitted
with `NOSQLLOGIN` set.

75088: sql: migrate has_sequence_privilege from evalPrivilegeCheck to ctx.Pl… r=otan a=ecwall

…anner.HasPrivilege

refs #66173

Migrate has_sequence_privilege from evalPrivilegeCheck to ctx.Planner.HasPrivilege.

Release note: None

75145: sql: deflake TestTelemetry r=rytaft a=rytaft

This commit deflakes `TestTelemetry` by adding a more precise
`feature-allowlist`.

Fixes #75138

Release note: None

Co-authored-by: David Hartunian <davidh@cockroachlabs.com>
Co-authored-by: Evan Wall <wall@cockroachlabs.com>
Co-authored-by: Rebecca Taft <becca@cockroachlabs.com>
craig bot pushed a commit that referenced this issue Jan 19, 2022
@dhartunian @shralex @RaduBerinde
Merge #74706 #74835 #74922
eb26eb3 
74706: sql: add NOSQLLOGIN role which restricts SQL CLI only r=rafiss,kylepatron-cockroachlabs a=dhartunian

Previously, in order to restrict user login ability, the `LOGIN` and
`NOLOGIN` role options were available which would restrict both SQL and
DB Console login ability.

This change adds the `NOSQLLOGIN` (and its inverse: `SQLLOGIN`) role
option in order to provide the ability to disable SQL CLI logins from
users while retaining DB Console login ability.

Resolves #74482

Release note (sql change): A new role option is now available,
`NOSQLLOGIN` (and its inverse `SQLLOGIN`), which restricts SQL CLI login
ability for a user while retaining their ability to login to the DB
Console (as opposed to `NOLOGIN` which restricts both SQL and DB
Console). Without any role options all login behavior remains permitted
as it does today. OIDC logins to the DB Console continue to be permitted
with `NOSQLLOGIN` set.

74835: kvserver: add AdminSplit and AdminScatter to secondary tenants API r=shralex a=shralex

This adds support for AdminSplit and AdminScatter for secondary tenants. This API allows indicating to KV that more data will be ingested, and so the range should be split and re-distributed across the cluster. This API will not be exposed through SQL, and in the future we might change it to give KV more control over whether and how to deal with expected ingest load. More discussion can be found in the github issue: #74389 and Epic: https://cockroachlabs.atlassian.net/browse/CRDB-10720

Release Note: None

74922: sql: clean up mutable not-null columns hack r=RaduBerinde a=RaduBerinde

Mutation columns in some cases need to be scanned even if they haven't
been backfilled yet, which means that we may retrieve NULL values even
if they are marked as not-nullable.

We currently have a hack in the table descriptor which changes the
nullable flags in the column descriptors when `ReadableColumns()` is
used. It is very surprising that we can get different descriptors for
a given ColumnID depending if we look for it in `ReadableColumns()` or
in `AllColumns()` (e.g. via FindColumnWithID).

This commit cleans this up, changing the scanning code to check for
`Public()` instead.

Release note: None

Co-authored-by: David Hartunian <davidh@cockroachlabs.com>
Co-authored-by: shralex <shralex@gmail.com>
Co-authored-by: Radu Berinde <radu@cockroachlabs.com>
@craig craig bot closed this as completed in 1376c8d Jan 19, 2022
dhartunian added a commit to dhartunian/cockroach that referenced this issue Jan 20, 2022
@dhartunian
sql: add NOSQLLOGIN role which restricts SQL CLI only
cdc973c 
Previously, in order to restrict user login ability, the `LOGIN` and
`NOLOGIN` role options were available which would restrict both SQL and
DB Console login ability.

This change adds the `NOSQLLOGIN` (and its inverse: `SQLLOGIN`) role
option in order to provide the ability to disable SQL CLI logins from
users while retaining DB Console login ability.

Resolves cockroachdb#74482

Release note (sql change): A new role option is now available,
`NOSQLLOGIN` (and its inverse `SQLLOGIN`), which restricts SQL CLI login
ability for a user while retaining their ability to login to the DB
Console (as opposed to `NOLOGIN` which restricts both SQL and DB
Console). Without any role options all login behavior remains permitted
as it does today. OIDC logins to the DB Console continue to be permitted
with `NOSQLLOGIN` set.
@dhartunian dhartunian mentioned this issue Jan 20, 2022
Merged
@dhartunian
Copy link
Collaborator

@thtruo backport is merged into 21.2. Should be out with 21.2.5

@thtruo
Copy link
Contributor Author

thtruo commented Jan 20, 2022

FYI @abhinavg6 and @kevin-v-ngo

@abhinavg6
Copy link
Contributor

Awesome, thanks for the quick work on this.

@thtruo thtruo mentioned this issue Jan 20, 2022
Closed
gtr pushed a commit to gtr/cockroach that referenced this issue Jan 24, 2022
@dhartunian
sql: add NOSQLLOGIN role which restricts SQL CLI only
88c4f87 
Previously, in order to restrict user login ability, the `LOGIN` and
`NOLOGIN` role options were available which would restrict both SQL and
DB Console login ability.

This change adds the `NOSQLLOGIN` (and its inverse: `SQLLOGIN`) role
option in order to provide the ability to disable SQL CLI logins from
users while retaining DB Console login ability.

Resolves cockroachdb#74482

Release note (sql change): A new role option is now available,
`NOSQLLOGIN` (and its inverse `SQLLOGIN`), which restricts SQL CLI login
ability for a user while retaining their ability to login to the DB
Console (as opposed to `NOLOGIN` which restricts both SQL and DB
Console). Without any role options all login behavior remains permitted
as it does today. OIDC logins to the DB Console continue to be permitted
with `NOSQLLOGIN` set.
@thtruo thtruo mentioned this issue Feb 18, 2022
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@dhartunian dhartunian

Labels
A-webui-security C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception)
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

sql: add NOSQLLOGIN role which restricts SQL CLI only dhartunian/cockroach
3 participants
@dhartunian @thtruo @abhinavg6