Describe the problem
Because user permissions are attached to table descriptors, whenever a user is granted or revoked a permission it triggers a schema change. Because of this, applying or removing permissions for a user in bulk can take an extremely long time.
To Reproduce
Create a multi-region cluster
create a database with a large number of tables (>10)
create a user
grant that user permissions on all tables in the database.
Expected behavior
This should be fast
Additional data / screenshots
Below example is a 9 node cluster (3 nodes per dc, 30ms latency between dcs, >1ms latency between nodes within a dc). Database has 127 tables. User grant and revoke each take over 5 minutes.
select current_timestamp;
+----------------------------------+
2019-10-25 15:23:38.129753+00:00
(1 row)
revoke all on database pbj from pbjapp;
REVOKE
select current_timestamp;
+----------------------------------+
2019-10-25 15:23:38.141579+00:00
(1 row)
revoke all on table pbj.* from pbjapp;
REVOKE
select current_timestamp;
+----------------------------------+
2019-10-25 15:28:42.911679+00:00
(1 row)
drop user pbjapp;
DROP USER 1
select current_timestamp;
+----------------------------------+
2019-10-25 15:28:42.933457+00:00
(1 row)
create user pbjapp with password ******;
CREATE USER 1
select current_timestamp;
+----------------------------------+
2019-10-25 15:28:43.042432+00:00
(1 row)
grant all on table pbj.* to pbjapp;
GRANT
select current_timestamp;
+----------------------------------+
2019-10-25 15:33:48.584587+00:00
(1 row)
Environment:
19.1.5
9 node cluster (3 nodes per dc, 30ms latency between dcs, >1ms latency between nodes within a dc)
Additional context
What was the impact?
This has a big impact on DBAs as it takes a long time to provision a new user. Also takes a long time to de-provision a user that may have been compromised.
Epic: CRDB-8577
Describe the problem
Because user permissions are attached to table descriptors, whenever a user is granted or revoked a permission it triggers a schema change. Because of this, applying or removing permissions for a user in bulk can take an extremely long time.
To Reproduce
Create a multi-region cluster
create a database with a large number of tables (>10)
create a user
grant that user permissions on all tables in the database.
Expected behavior
This should be fast
Additional data / screenshots
Below example is a 9 node cluster (3 nodes per dc, 30ms latency between dcs, >1ms latency between nodes within a dc). Database has 127 tables. User grant and revoke each take over 5 minutes.
+----------------------------------+
2019-10-25 15:23:38.129753+00:00
(1 row)
REVOKE
+----------------------------------+
2019-10-25 15:23:38.141579+00:00
(1 row)
REVOKE
+----------------------------------+
2019-10-25 15:28:42.911679+00:00
(1 row)
DROP USER 1
+----------------------------------+
2019-10-25 15:28:42.933457+00:00
(1 row)
CREATE USER 1
+----------------------------------+
2019-10-25 15:28:43.042432+00:00
(1 row)
GRANT
+----------------------------------+
2019-10-25 15:33:48.584587+00:00
(1 row)
Environment:
19.1.5
9 node cluster (3 nodes per dc, 30ms latency between dcs, >1ms latency between nodes within a dc)
Additional context
What was the impact?
This has a big impact on DBAs as it takes a long time to provision a new user. Also takes a long time to de-provision a user that may have been compromised.
Epic: CRDB-8577