Unable to grant schema level permissions to the user

[giorgimode]

Describe the problem
GRANT ALL ON SCHEMA public TO max; fails with the following error

[42601] ERROR: at or near "public": syntax error Detail: source SQL: GRANT ALL ON SCHEMA public TO max
 ^ Hint: try \h GRANT

Please describe the issue you observed, and any steps we can take to reproduce it:
As described in the documentation, schema level grant can be applied via:
GRANT ALL ON SCHEMA cockroach_labs TO max;
In fact all schema related operations fail, e.g. SHOW GRANTS ON SCHEMA public;
I am using Postgresql driver version 42.2.18. CockroachDb cluster server version 19.1.11

To Reproduce

What did you do? Describe in your own words.
Connect to the CockroachDb cluster via Postgresql driver and run the commands above

Expected behavior
SHOW GRANTS ON SCHEMA public; -> you can view the list of grants
GRANT ALL ON SCHEMA public TO max; -> you can grant permissions to existing user max

Environment:

• CockroachDB version 19.1.11
• Server OS: MacOS Catalina 10.15.7
• Client app: postgresql-42.2.18

Additional context
If I cannot grant schema level permissions, application fails to read data when a new table is created unless I give explicit permissions on the table. I have to run grant select,insert,delete,update on public.* to max; every time a new table is created

[blathers-crl]

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

Hoot - a bug! Though bugs are the bane of my existence, rest assured the wretched thing will get the best of care here.

I have CC'd a few people who may be able to assist you:

• @tim-o (member of the technical support engineering team)

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.}

[ajwerner]

This is a real problem. The public schema is really just an alias for the database. Before 20.2, we didn't have user defined schemas but we just said that all tables were in the public schema. That means we have nothing to hang the privileges off of on the public schema.

We want to do the migration soon (21.1). In the meantime, you can either change the privileges on the database or convert the database to a schema or something like that.

[ajwerner]

#55793 is the issue for the migration.

[giorgimode]

Thanks for the update. When is 21.1 expected to be rolled out?

[ajwerner]

Thanks for the update. When is 21.1 expected to be rolled out?

In the spring, probably April. Hope the workarounds are sufficient in the meantime.

[rafiss]

Unfortunately, we didn't get to this in 21.1, but we are now nearing completion on it and it will be in 22.1.

I'll close this in favor of #55793 and #67376