You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
It is possible to create objects as a user after that user is dropped by a different connection. Two possible ways:
Breaking catalog change is not propagated: DropRole is a breaking catalog change, but catalog changes may not be communicated quickly enough such that one connection doing DROP USER and another connection quickly doing CREATE TABLE as that user can succeed.
Transaction block takes catalog snapshot and doesn't check breaking catalog change unless DML is done: see below.
yugabyte: CREATE USER u;
u: BEGIN;
yugabyte: DROP USER u;
u: CREATE TABLE t (i int);
u: CREATE VIEW v AS SELECT * FROM t WHERE 1 > 5;
u: ALTER TABLE t ADD PRIMARY KEY (i);
u: CREATE FUNCTION f() RETURNS int LANGUAGE sql AS $$select 1;$$;
u: COMMIT;
I also managed to insert data into the table whose user was dropped, but I do not remember how and have a hard time reproducing.
The first way may possibly be fixed by catalog consistency project (#13369) part 1, depending on the implementation. The second way could be fixed by catalog consistency project part 2, also depending on part 1, if DDLs in txn block would be rolled back on txn block abort. That is not the case today. Then another way to catch it is to send catalog version in read/write requests to master sys catalog and validate it there (currently not done).
Part 1 has been discussed whether it can be relaxed to be eventually consistent, but that appears to be a problem with the specific examples here leaving the metadata inconsistent.
Example of what it looks like when user is dropped but object's owner is that user:
yugabyte=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+---------------------
public | t | table | unknown (OID=16488)
(1 row)
The text was updated successfully, but these errors were encountered:
It appears creating view on nonexisting table is also a problem:
A: CREATE TABLE t (i int);
B: BEGIN;
A: DROP TABLE t;
B: CREATE VIEW v AS SELECT * FROM t;
B: COMMIT;
B: SELECT * FROM v;
ERROR: cache lookup failed for relation 16384
I tried also
create foreign data wrapper useless;
create server useless_server foreign data wrapper useless; -- depending on fdw
create user mapping for yugabyte server useless_server; -- depending on server
create table rtest_t1 (a int4, b int4);
create view rtest_v1 as select * from rtest_t1;
create rule rtest_v1_del as on delete to rtest_v1 do instead delete from rtest_t1 where a = old.a; -- depending on view
COMMENT ON RULE rtest_v1_del ON rtest_v1 IS 'delete rule'; -- depending on rule
CREATE TEXT SEARCH CONFIGURATION alt_ts_conf1 (copy=english);
ALTER TEXT SEARCH CONFIGURATION alt_ts_conf1 RENAME TO alt_ts_conf3; -- depending on ts config
but those don't have issue. For some of them, I even tried doing ops to cache the metadata beforehand, but it didn't make a differenc. Have to take a close look to figure out why.
jasonyb
changed the title
[YSQL] Prevent objects created after user dropped
[YSQL] Prevent objects created after dependent object dropped
Dec 3, 2022
Jira Link: DB-4296
Description
It is possible to create objects as a user after that user is dropped by a different connection. Two possible ways:
yugabyte:
CREATE USER u;
u:
BEGIN;
yugabyte:
DROP USER u;
u:
CREATE TABLE t (i int);
u:
CREATE VIEW v AS SELECT * FROM t WHERE 1 > 5;
u:
ALTER TABLE t ADD PRIMARY KEY (i);
u:
CREATE FUNCTION f() RETURNS int LANGUAGE sql AS $$select 1;$$;
u:
COMMIT;
I also managed to insert data into the table whose user was dropped, but I do not remember how and have a hard time reproducing.
The first way may possibly be fixed by catalog consistency project (#13369) part 1, depending on the implementation. The second way could be fixed by catalog consistency project part 2, also depending on part 1, if DDLs in txn block would be rolled back on txn block abort. That is not the case today. Then another way to catch it is to send catalog version in read/write requests to master sys catalog and validate it there (currently not done).
Part 1 has been discussed whether it can be relaxed to be eventually consistent, but that appears to be a problem with the specific examples here leaving the metadata inconsistent.
Example of what it looks like when user is dropped but object's owner is that user:
The text was updated successfully, but these errors were encountered: