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: migrate away from descriptorless public schemas #55793

Closed
thoszhang opened this issue Oct 21, 2020 · 0 comments
Closed

sql: migrate away from descriptorless public schemas #55793

thoszhang opened this issue Oct 21, 2020 · 0 comments
Assignees
@RichardJCai
Labels
C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) T-sql-foundations SQL Foundations Team (formerly SQL Schema + SQL Sessions)

Comments

@thoszhang
Copy link
Contributor

thoszhang commented Oct 21, 2020 •
edited by rafiss
Loading

With the introduction of user-defined schemas in 20.2, the existing public schemas for databases, which lack a schema descriptor and all share an ID of 29, are now outliers. They can't be renamed or deleted or have their own privileges or zone configs. They also require special treatment in many places where we handle schemas in the code, which introduces a lot of complexity and likely some subtle inconsistencies with other user-defined schemas.

The proposal is to migrate away from the special descriptorless schema implementation, with the goal of having public schemas being implemented like all other user-defined schemas, created by default for each new database. Here's a sketch of a plan, which gets us to the final state of no descriptorless public schemas in 21.2:

  • In 21.1, all new databases will get a real public schema with a descriptor. This means that clusters bootstrapped on 21.1 will never deal with descriptorless public schemas.
  • To migrate existing descriptorless schemas, we do one of the following:
    • We implement a long-running migration that, for each database, creates a public schema and corresponding namespace entry, reassigns the schema ID for all child objects of the public schema, and deletes the old namespace entry and (if needed?) marks the database as no longer having a descriptorless public schema.
    • We provide a way for users to manually trigger the migration on a per-database level, and we have a long-running migration that just enforces that users have done this for every database by the time they upgrade to 21.2.
      • What would the syntax be for this?

Regardless of whether we force users to migrate their own schemas or do it for them, this migration provides some UI challenges. It's not clear how we should indicate to the user that they have an upgraded public schema versus a non-upgraded one.

Epic CRDB-1521
@thoszhang thoszhang added the C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) label Oct 21, 2020
@thoszhang thoszhang mentioned this issue Oct 27, 2020
Open
@ajwerner ajwerner mentioned this issue Dec 17, 2020
Closed
@jlinder jlinder added the T-sql-schema-deprecated Use T-sql-foundations instead label Jun 16, 2021
@RichardJCai RichardJCai mentioned this issue Jun 24, 2021
Merged
@RichardJCai RichardJCai mentioned this issue Jul 8, 2021
Closed
@blathers-crl blathers-crl bot added the T-sql-foundations SQL Foundations Team (formerly SQL Schema + SQL Sessions) label Jul 19, 2021
craig bot pushed a commit that referenced this issue Jul 21, 2021
@RichardJCai
Merge #66785
6cdb10c 
66785: sql: support default privileges at the database level  r=RichardJCai a=RichardJCai

fixes #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 #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]>
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]>
@exalate-issue-sync exalate-issue-sync bot removed the T-sql-schema-deprecated Use T-sql-foundations instead label Aug 6, 2021
@RichardJCai RichardJCai mentioned this issue Oct 12, 2021
Closed
@RichardJCai RichardJCai mentioned this issue Nov 4, 2021
Closed
@rafiss rafiss closed this as completed Jan 27, 2022
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) T-sql-foundations SQL Foundations Team (formerly SQL Schema + SQL Sessions)
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
4 participants
@jlinder @rafiss @thoszhang @RichardJCai