sql: renaming tables into a different parent database allows creating prohibited cross-database references

[jayshrivastava]

Describe the problem

Even with sql.cross_db_fks.enabled=false, one can move a table to another database such that there is a cross-database foreign key relation.

To Reproduce

root@:26257/otherdb> show cluster setting sql.cross_db_fks.enabled;
  sql.cross_db_fks.enabled
----------------------------
           false
(1 row)

Time: 0ms total (execution 0ms / network 0ms)

root@:26257/defaultdb> create database otherdb;
CREATE DATABASE

Time: 81ms total (execution 81ms / network 0ms)

root@:26257/defaultdb> create table t1(i int PRIMARY KEY); create table t2(j int, constraint t1_reference foreign key(j) references t1(i));
CREATE TABLE

Note: timings for multiple statements on a single line are not supported. See https://go.crdb.dev/issue-v/48180/v20.2.

root@:26257/defaultdb> alter table t2 rename to otherdb.public.t2;
NOTICE: renaming tables with a qualification is deprecated
HINT: use ALTER TABLE t2 RENAME TO t2 instead
RENAME TABLE

Time: 296ms total (execution 93ms / network 203ms)

root@:26257/defaultdb> use otherdb;
SET

Time: 0ms total (execution 0ms / network 0ms)

root@:26257/otherdb> select * from information_schema.tables where table_name = 't2';
  table_catalog | table_schema | table_name | table_type | is_insertable_into | version
----------------+--------------+------------+------------+--------------------+----------
  otherdb       | public       | t2         | BASE TABLE | YES                |       4
(1 row)

Time: 5ms total (execution 4ms / network 0ms)

Expected behavior
This should only work if sql.cross_db_fks.enabled is set to true.

Environment:

• CockroachDB version [e.g. 20.2]

Epic: CRDB-1519

[thoszhang]

This is also broken for type references and sequence owner references (gated by sql.cross_db_sequence_owners.enabled in the latter case):

[email protected]:52326/defaultdb> create type typ as enum('hello');
CREATE TYPE

Time: 4ms total (execution 4ms / network 0ms)

[email protected]:52326/defaultdb> create table tbl(a typ);
CREATE TABLE

Time: 40ms total (execution 13ms / network 27ms)

[email protected]:52326/defaultdb> insert into tbl values ('hello');
INSERT 1

Time: 5ms total (execution 5ms / network 0ms)

[email protected]:52326/defaultdb> create database otherdb;
CREATE DATABASE

Time: 3ms total (execution 3ms / network 0ms)

[email protected]:52326/defaultdb> alter table tbl rename to otherdb.tbl;
NOTICE: renaming tables with a qualification is deprecated
HINT: use ALTER TABLE tbl RENAME TO tbl instead
RENAME TABLE

Time: 45ms total (execution 6ms / network 38ms)

[email protected]:52536/defaultdb> create table t(a int);
CREATE TABLE

Time: 3ms total (execution 3ms / network 0ms)

[email protected]:52536/defaultdb> create sequence s owned by t.a;
CREATE SEQUENCE

Time: 28ms total (execution 7ms / network 22ms)

[email protected]:52536/defaultdb> create database otherdb;
CREATE DATABASE

Time: 3ms total (execution 3ms / network 0ms)

[email protected]:52536/defaultdb> alter table t rename to otherdb.t;
NOTICE: renaming tables with a qualification is deprecated
HINT: use ALTER TABLE t RENAME TO t instead
RENAME TABLE

Time: 30ms total (execution 6ms / network 24ms)

Fortunately, for now we don't allow renaming tables that are referred to by views.

I'm worried about cross-db type references the most, since they're supposed to be disallowed from the start, and now we'll have to account for them during whatever migration we have coming up.

[thoszhang]

Linking to #54126 as the original issue.

[jayshrivastava]

Linking to #55709

[ajwerner]

@fqazi this would be a good one for you to take a look at. It pairs nicely with #58867.