Publication date: May 11, 2023
Description
In CockroachDB versions v22.1.19 and v22.2.8, some customers may experience spurious privilege errors when trying to run queries due to a bug in the query cache. This can happen if two or more databases exist on the same cluster with tables that have the same name and at least one foreign key reference. If identical queries are used to query the tables in the two different databases by users with different permissions, they may experience errors due to insufficient privileges.
Statement
This is resolved in CockroachDB by PR #102405 which ensures that privilege checks happen after staleness checks when attempting to use the query cache.
The fix has been applied to the maintenance release of CockroachDB v22.2.9.
This fix will be applied to the maintenance release of CockroachDB v22.1.20.
This public issue is tracked by #102375.
Mitigation
Users of CockroachDB v22.1.19 and v22.2.8 who experience spurious privilege errors with the query cache enabled are encouraged to upgrade to v22.1.20, v22.2.9, or a later version.
If an upgrade is not possible, the issue can be avoided by updating the SQL queries to qualify table names with the database name so there is no collision in the query cache. For example, SELECT * FROM table_name can be rewritten using partially qualified or fully qualified names as follows:
SELECT * FROM database_name.table_nameSELECT * FROM database_name.schema_name.table_name
Another option, if an upgrade is not possible, is to disable the query cache with the following command:
SET CLUSTER SETTING sql.query_cache.enabled = false;
Disabling the query cache may degrade the performance of the cluster, however.
Impact
Some customers running identical queries with different roles to access tables with the same name in different databases could experience spurious privilege errors on CockroachDB v22.1.19 and v22.2.8 with the query cache enabled.
Please reach out to the support team if more information or assistance is needed.
Was this helpful?
