sql: GRANT ALL ON DATABASE does not honor individual tables

[dbist]

Describe the problem
The user with ALL grants does not necessarily have access to underlying tables.

Please describe the issue you observed, and any steps we can take to reproduce it:

To Reproduce

CREATE USER IF NOT EXISTS maxroach;
GRANT ALL ON DATABASE movr TO maxroach;

login as maxroach and try to show tables

maxroach@:26257/movr> show tables;
  table_name
+------------+
  rides
(1 row)

Time: 2.5587ms

I added rides explicitly with

GRANT ALL ON TABLE movr.rides TO maxroach;

What did you do? Describe in your own words.

If possible, provide steps to reproduce the behavior:

Expected behavior
A user with grant on the database without explicit DENY access on any underlying table should have access to the tables.

Additional data / screenshots
If the problem is SQL-related, include a copy of the SQL query and the schema
of the supporting tables.

If a node in your cluster encountered a fatal error, supply the contents of the
log directories (at minimum of the affected node(s), but preferably all nodes).

Note that log files can contain confidential information. Please continue
creating this issue, but contact [email protected] to submit the log
files in private.

If applicable, add screenshots to help explain your problem.

Environment:

• CockroachDB version v19.2.3.
• Server OS: Official Docker image.
• Client app: cockroach sql.

Additional context
What was the impact?

Add any other context about the problem here.

[ajwerner]

A user with grant on the database without explicit DENY access on any underlying table should have access to the tables.

That's not how our privilege model works. I'll admit our privilege model is hard to grok. The way it works is that you can grant things on a database that seem like they would only apply to tables. If you do that, then for subsequently created entities, they will inherit those privileges. Granting on the database itself doesn't change the privileges of its members. Privileges are not inherited.

In postgres this is all a slight bit more sane. There are two distinct concepts, privileges and default privileges. The privileges only affect that entity. The default privileges affect newly created children. Postgres then has syntax to grant to all existing children. We do to. Our syntax to do what you want is:

GRANT ALL ON movr.* TO maxroach;

See much more discussion here: #16790 (comment)