sql: name resolution handles unpermissioned schemas differently from postgres

[solongordon]

While testing out #54538 I noticed that we handle name resolution differently from Postgres when it comes to schemas the user doesn't have privileges on.

For instance, say my username is "solon" and the search path is the default $user,public. If the solon schema exists and I don't have any permissions on it, this is the result:

solon@:26257/defaultdb> create table t ();
ERROR: user solon does not have CREATE privilege on schema solon
SQLSTATE: 42501

Similarly if there is a table named t in both the solon and public schemas, this is the result:

solon@:26257/defaultdb> select * from t;
ERROR: user solon does not have USAGE privilege on schema solon
SQLSTATE: 42501

This is not the case with Postgres, which would fallback to the public schema in both cases.

More investigation is needed to understand exactly what the Postgres logic is here.

Jira issue: CRDB-3737

[github-actions]

We have marked this issue as stale because it has been inactive for
18 months. If this issue is still relevant, removing the stale label
or adding a comment will keep it active. Otherwise, we'll close it in
10 days to keep the issue queue tidy. Thank you for your contribution
to CockroachDB!