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

sql: GRANT gets confused when username argument is not normalized #65556

Closed
keith-mcclellan opened this issue May 21, 2021 · 1 comment · Fixed by #67625
Closed

sql: GRANT gets confused when username argument is not normalized #65556

keith-mcclellan opened this issue May 21, 2021 · 1 comment · Fixed by #67625
Assignees
@rafiss
Labels
A-security A-sql-privileges SQL privilege handling and permission checks. C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior. T-server-and-security DB Server & Security T-sql-foundations SQL Foundations Team (formerly SQL Schema + SQL Sessions)

Comments

@keith-mcclellan
Copy link
Contributor

keith-mcclellan commented May 21, 2021 •
edited by elinorgarcia
Loading

Describe the problem

usernames are supposed to be case insensitive. Grant seems to be case sensitive.

To Reproduce

What did you do? Describe in your own words.

CREATE USER "v-root-keycloak-ruKOXLtxBFOd6iO473d7-1621531073" WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}';
GRANT ALL ON DATABASE keycloak TO "v-root-keycloak-ruKOXLtxBFOd6iO473d7-1621531073";

* 1 error occurred:
* pq: user or role "v-root-keycloak-ruKOXLtxBFOd6iO473d7-1621531073" does not exist

show users
username options member_of
admin {}
dba {admin}
root {admin}
v-root-keycloak-rukoxltxbfod6io473d7-1621531073 VALID UNTIL=2022-01-25 10:10:10.555555+00:00 {}

Expected behavior
GRANT should succeed because usernames are case-insensitive.

Environment:

  • CockroachDB v21.1.0

Additional context
What was the impact?
A partner at Red Hat is working on Vault/Keycloak integration for customers to have a solution to manage users and permissions.

Epic CRDB-7217
@keith-mcclellan keith-mcclellan added the C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior. label May 21, 2021
@knz knz added the A-security label May 21, 2021
@knz knz added the A-sql-privileges SQL privilege handling and permission checks. label May 21, 2021
@knz knz changed the title Can't grant permissions to user with mixed-case in username sql: GRANT gets confused when username argument is not normalized May 21, 2021
@rafiss
Copy link
Collaborator

rafiss commented May 21, 2021

this relates to #54696 as well

@rafiss rafiss added the T-sql-foundations SQL Foundations Team (formerly SQL Schema + SQL Sessions) label May 21, 2021
@jlinder jlinder added the T-server-and-security DB Server & Security label Jun 16, 2021
@rafiss rafiss mentioned this issue Jul 14, 2021
Merged
@rafiss rafiss assigned rafiss and unassigned arulajmani Jul 20, 2021
craig bot pushed a commit that referenced this issue Jul 21, 2021
@rafiss
Merge #67625 #67821
4288afd 
67625: sql: GRANT/REVOKE treat names case insensitively r=RichardJCai a=rafiss

fixes #65556

Release note (bug fix): Previously the GRANT and REVOKE commands would
incorrectly handle role names. CockroachDB treats role names as case
insensitive, but these commands were incorrectly handling the names.
Now, GRANT and REVOKE normalize the names and are case-insensitive.

67821: sql: removed pg_stat exclusion for pg_catalog r=rafiss a=mnovelodou

Previously, we excluded pg_stat* tables from difftool
This was inadequate because we were unable to add missing empty tables
To address this, this patch removes the exclusion

Release note: None

Co-authored-by: Rafi Shamim <[email protected]>
Co-authored-by: MiguelNovelo <[email protected]>
@craig craig bot closed this as completed in d81b01c Jul 22, 2021
@blathers-crl blathers-crl bot mentioned this issue Jul 22, 2021
Merged
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@rafiss rafiss

Labels
A-security A-sql-privileges SQL privilege handling and permission checks. C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior. T-server-and-security DB Server & Security T-sql-foundations SQL Foundations Team (formerly SQL Schema + SQL Sessions)
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

sql: GRANT/REVOKE treat names case insensitively rafiss/cockroach
5 participants
@jlinder @knz @rafiss @keith-mcclellan @arulajmani