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: Support for ALTER DEFAULT PRIVILEGES #65604

Closed
ghost opened this issue May 24, 2021 · 2 comments · Fixed by #66785
Closed

sql: Support for ALTER DEFAULT PRIVILEGES #65604

ghost opened this issue May 24, 2021 · 2 comments · Fixed by #66785
Assignees
@RichardJCai
Labels
C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) O-community Originated from the community T-sql-foundations SQL Foundations Team (formerly SQL Schema + SQL Sessions)

Comments

@ghost
Copy link

ghost commented May 24, 2021 •
edited by exalate-issue-sync bot
Loading

Currently Cockroach does not support a statement similar to ALTER DEFAULT PRIVILEGES to alter the default privileges given to a role for all new tables added to a specific schema.

Describe the solution you'd like
Support for a statement with similar functionality to ALTER DEFAULT PRIVILEGES.

Describe alternatives you've considered
The current approaches of giving permissions on tables to users have some drawbacks:

  1. Using GRANT <PRIVILEGES> ON TABLE <db>.<schema>.* TO <ROLE>; would only grant permissions on existing tables.
  2. Granting permissions on DB level which are then inherited to tables means that if we want to give SELECT to a role for all tables in a given schema then the SELECT would also be granted on a DB level.

Epic CRDB-2586
@ghost ghost added the C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) label May 24, 2021
@blathers-crl
Copy link

blathers-crl bot commented May 24, 2021

Hello, I am Blathers. I am here to help you get the issue triaged.

It looks like you have not filled out the issue in the format of any of our templates. To best assist you, we advise you to use one of these templates.

I was unable to automatically find someone to ping.

If we have not gotten back to your issue within a few business days, you can try the following:

  • Join our community slack channel and ask on #cockroachdb.
  • Try find someone from here if you know they worked closely on the area and CC them.

🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.

@blathers-crl blathers-crl bot added O-community Originated from the community X-blathers-untriaged blathers was unable to find an owner labels May 24, 2021
@rafiss
Copy link
Collaborator

rafiss commented May 24, 2021

Thanks for this request @cybros7! We'll use this issue to track any changes we make here.

cc @RichardJCai

@rafiss rafiss added T-sql-foundations SQL Foundations Team (formerly SQL Schema + SQL Sessions) and removed X-blathers-untriaged blathers was unable to find an owner labels May 24, 2021
@rafiss rafiss changed the title Support for ALTER DEFAULT PRIVILEGES sql: Support for ALTER DEFAULT PRIVILEGES May 24, 2021
@rafiss rafiss mentioned this issue Jul 19, 2021
Merged
@craig craig bot closed this as completed in 6cdb10c Jul 21, 2021
jeffswenson pushed a commit to jeffswenson/cockroach that referenced this issue Jul 26, 2021
@RichardJCai @jeffswenson
Merge cockroachdb#66785
b2ac9da 
66785: sql: support default privileges at the database level  r=RichardJCai a=RichardJCai

fixes cockroachdb#65604

sql: support default privileges at the database level

This only adds default privileges stored on the database, we can add support for storing default privileges on schemas afterwards (shouldn't be a huge add onto this). Hopefully we can wait for cockroachdb#55793 so we don't need special logic to handle the public schema.

This PR does not yet address handling having USAGE on types as a default privilege for the public role.

Migration is not addressed yet, this PR still maintains backwards compatibility with how we "inherited" privileges before.

Also sorry to the reviewers about the size of the PR, a lot of lines do come from tests however, specifically parse test. Hopefully it shouldn't be too bad.

Release note (sql change): Added support for ALTER DEFAULT PRIVILEGES
and default privileges stored on databases.

All objects created in a database will have the privilege set defined
by the default privileges for that type of object on the database.
The types of objects are TABLES, SEQUENCES, SCHEMAS, TYPES.

Example: ALTER DEFAULT PRIVILEGES GRANT SELECT ON TABLES TO foo
makes it such that all tables created by the user that executed the
ALTER DEFAULT PRIVILEGES command will have SELECT privilege on the table
for user foo.

Additionally, one can specify a role.
Example: ALTER DEFAULT PRIVILEGES FOR ROLE bar GRANT SELECT ON TABLES TO foo.
All tables created by bar will have SELECT privilege for foo.
If a role is not specified, it uses the current user.

See: https://www.postgresql.org/docs/current/sql-alterdefaultprivileges.html

Currently, default privileges are not supported on the schema.
Specifying a schema like ALTER DEFAULT PRIVILEGES IN SCHEMA s will error.

WITH GRANT OPTION is ignored.
GRANT OPTION FOR is also ignored.

Co-authored-by: richardjcai <[email protected]>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@RichardJCai RichardJCai

Labels
C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) O-community Originated from the community 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: support default privileges at the database level RichardJCai/cockroach
2 participants
@rafiss @RichardJCai