github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/system (about) 1 # LogicTest: !3node-tenant 2 query T 3 SHOW DATABASES 4 ---- 5 defaultdb 6 postgres 7 system 8 test 9 10 query TTT 11 SHOW TABLES FROM system 12 ---- 13 public comments table 14 public descriptor table 15 public eventlog table 16 public jobs table 17 public lease table 18 public locations table 19 public namespace table 20 public namespace2 table 21 public protected_ts_meta table 22 public protected_ts_records table 23 public rangelog table 24 public replication_constraint_stats table 25 public replication_critical_localities table 26 public replication_stats table 27 public reports_meta table 28 public role_members table 29 public role_options table 30 public settings table 31 public statement_bundle_chunks table 32 public statement_diagnostics table 33 public statement_diagnostics_requests table 34 public table_statistics table 35 public tenants table 36 public ui table 37 public users table 38 public web_sessions table 39 public zones table 40 41 query I rowsort 42 SELECT id FROM system.descriptor 43 ---- 44 1 45 2 46 3 47 4 48 5 49 6 50 8 51 11 52 12 53 13 54 14 55 15 56 19 57 20 58 21 59 23 60 24 61 25 62 26 63 27 64 28 65 30 66 31 67 32 68 33 69 34 70 35 71 36 72 50 73 51 74 52 75 76 # Verify we can read "protobuf" columns. 77 query I 78 SELECT length(descriptor) * (id - 1) FROM system.descriptor WHERE id = 1 79 ---- 80 0 81 82 83 query TTBTTTB 84 SHOW COLUMNS FROM system.descriptor 85 ---- 86 id INT8 false NULL · {primary} false 87 descriptor BYTES true NULL · {} false 88 89 query TTBTTTB 90 SHOW COLUMNS FROM system.users 91 ---- 92 username STRING false NULL · {primary} false 93 hashedPassword BYTES true NULL · {} false 94 isRole BOOL false false · {} false 95 96 query TTBTTTB 97 SHOW COLUMNS FROM system.zones 98 ---- 99 id INT8 false NULL · {primary} false 100 config BYTES true NULL · {} false 101 102 query TTBTTTB 103 SHOW COLUMNS FROM system.lease 104 ---- 105 descID INT8 false NULL · {primary} false 106 version INT8 false NULL · {primary} false 107 nodeID INT8 false NULL · {primary} false 108 expiration TIMESTAMP false NULL · {primary} false 109 110 query TTBTTTB 111 SHOW COLUMNS FROM system.eventlog 112 ---- 113 timestamp TIMESTAMP false NULL · {primary} false 114 eventType STRING false NULL · {} false 115 targetID INT8 false NULL · {} false 116 reportingID INT8 false NULL · {} false 117 info STRING true NULL · {} false 118 uniqueID BYTES false uuid_v4() · {primary} false 119 120 query TTBTTTB 121 SHOW COLUMNS FROM system.rangelog 122 ---- 123 timestamp TIMESTAMP false NULL · {primary} false 124 rangeID INT8 false NULL · {} false 125 storeID INT8 false NULL · {} false 126 eventType STRING false NULL · {} false 127 otherRangeID INT8 true NULL · {} false 128 info STRING true NULL · {} false 129 uniqueID INT8 false unique_rowid() · {primary} false 130 131 query TTBTTTB 132 SHOW COLUMNS FROM system.ui 133 ---- 134 key STRING false NULL · {primary} false 135 value BYTES true NULL · {} false 136 lastUpdated TIMESTAMP false NULL · {} false 137 138 query TTBTTTB 139 SHOW COLUMNS FROM system.jobs 140 ---- 141 id INT8 false unique_rowid() · {primary,jobs_status_created_idx,jobs_created_by_type_created_by_id_idx} false 142 status STRING false NULL · {jobs_status_created_idx,jobs_created_by_type_created_by_id_idx} false 143 created TIMESTAMP false now():::TIMESTAMP · {jobs_status_created_idx} false 144 payload BYTES false NULL · {} false 145 progress BYTES true NULL · {} false 146 created_by_type STRING true NULL · {jobs_created_by_type_created_by_id_idx} false 147 created_by_id INT8 true NULL · {jobs_created_by_type_created_by_id_idx} false 148 149 query TTBTTTB 150 SHOW COLUMNS FROM system.settings 151 ---- 152 name STRING false NULL · {primary} false 153 value STRING false NULL · {} false 154 lastUpdated TIMESTAMP false now():::TIMESTAMP · {} false 155 valueType STRING true NULL · {} false 156 157 query TTBTTTB 158 SHOW COLUMNS FROM system.role_members 159 ---- 160 role STRING false NULL · {primary,role_members_role_idx,role_members_member_idx} false 161 member STRING false NULL · {primary,role_members_role_idx,role_members_member_idx} false 162 isAdmin BOOL false NULL · {} false 163 164 165 # Verify default privileges on system tables. 166 query TTTT 167 SHOW GRANTS ON DATABASE system 168 ---- 169 system crdb_internal admin GRANT 170 system crdb_internal admin SELECT 171 system crdb_internal root GRANT 172 system crdb_internal root SELECT 173 system information_schema admin GRANT 174 system information_schema admin SELECT 175 system information_schema root GRANT 176 system information_schema root SELECT 177 system pg_catalog admin GRANT 178 system pg_catalog admin SELECT 179 system pg_catalog root GRANT 180 system pg_catalog root SELECT 181 system pg_extension admin GRANT 182 system pg_extension admin SELECT 183 system pg_extension root GRANT 184 system pg_extension root SELECT 185 system public admin GRANT 186 system public admin SELECT 187 system public root GRANT 188 system public root SELECT 189 190 query TTTTT 191 SHOW GRANTS ON system.* 192 ---- 193 system public comments admin DELETE 194 system public comments admin GRANT 195 system public comments admin INSERT 196 system public comments admin SELECT 197 system public comments admin UPDATE 198 system public comments public SELECT 199 system public comments root DELETE 200 system public comments root GRANT 201 system public comments root INSERT 202 system public comments root SELECT 203 system public comments root UPDATE 204 system public descriptor admin GRANT 205 system public descriptor admin SELECT 206 system public descriptor root GRANT 207 system public descriptor root SELECT 208 system public eventlog admin DELETE 209 system public eventlog admin GRANT 210 system public eventlog admin INSERT 211 system public eventlog admin SELECT 212 system public eventlog admin UPDATE 213 system public eventlog root DELETE 214 system public eventlog root GRANT 215 system public eventlog root INSERT 216 system public eventlog root SELECT 217 system public eventlog root UPDATE 218 system public jobs admin DELETE 219 system public jobs admin GRANT 220 system public jobs admin INSERT 221 system public jobs admin SELECT 222 system public jobs admin UPDATE 223 system public jobs root DELETE 224 system public jobs root GRANT 225 system public jobs root INSERT 226 system public jobs root SELECT 227 system public jobs root UPDATE 228 system public lease admin DELETE 229 system public lease admin GRANT 230 system public lease admin INSERT 231 system public lease admin SELECT 232 system public lease admin UPDATE 233 system public lease root DELETE 234 system public lease root GRANT 235 system public lease root INSERT 236 system public lease root SELECT 237 system public lease root UPDATE 238 system public locations admin DELETE 239 system public locations admin GRANT 240 system public locations admin INSERT 241 system public locations admin SELECT 242 system public locations admin UPDATE 243 system public locations root DELETE 244 system public locations root GRANT 245 system public locations root INSERT 246 system public locations root SELECT 247 system public locations root UPDATE 248 system public namespace admin GRANT 249 system public namespace admin SELECT 250 system public namespace root GRANT 251 system public namespace root SELECT 252 system public namespace2 admin GRANT 253 system public namespace2 admin SELECT 254 system public namespace2 root GRANT 255 system public namespace2 root SELECT 256 system public protected_ts_meta admin GRANT 257 system public protected_ts_meta admin SELECT 258 system public protected_ts_meta root GRANT 259 system public protected_ts_meta root SELECT 260 system public protected_ts_records admin GRANT 261 system public protected_ts_records admin SELECT 262 system public protected_ts_records root GRANT 263 system public protected_ts_records root SELECT 264 system public rangelog admin DELETE 265 system public rangelog admin GRANT 266 system public rangelog admin INSERT 267 system public rangelog admin SELECT 268 system public rangelog admin UPDATE 269 system public rangelog root DELETE 270 system public rangelog root GRANT 271 system public rangelog root INSERT 272 system public rangelog root SELECT 273 system public rangelog root UPDATE 274 system public replication_constraint_stats admin DELETE 275 system public replication_constraint_stats admin GRANT 276 system public replication_constraint_stats admin INSERT 277 system public replication_constraint_stats admin SELECT 278 system public replication_constraint_stats admin UPDATE 279 system public replication_constraint_stats root DELETE 280 system public replication_constraint_stats root GRANT 281 system public replication_constraint_stats root INSERT 282 system public replication_constraint_stats root SELECT 283 system public replication_constraint_stats root UPDATE 284 system public replication_critical_localities admin DELETE 285 system public replication_critical_localities admin GRANT 286 system public replication_critical_localities admin INSERT 287 system public replication_critical_localities admin SELECT 288 system public replication_critical_localities admin UPDATE 289 system public replication_critical_localities root DELETE 290 system public replication_critical_localities root GRANT 291 system public replication_critical_localities root INSERT 292 system public replication_critical_localities root SELECT 293 system public replication_critical_localities root UPDATE 294 system public replication_stats admin DELETE 295 system public replication_stats admin GRANT 296 system public replication_stats admin INSERT 297 system public replication_stats admin SELECT 298 system public replication_stats admin UPDATE 299 system public replication_stats root DELETE 300 system public replication_stats root GRANT 301 system public replication_stats root INSERT 302 system public replication_stats root SELECT 303 system public replication_stats root UPDATE 304 system public reports_meta admin DELETE 305 system public reports_meta admin GRANT 306 system public reports_meta admin INSERT 307 system public reports_meta admin SELECT 308 system public reports_meta admin UPDATE 309 system public reports_meta root DELETE 310 system public reports_meta root GRANT 311 system public reports_meta root INSERT 312 system public reports_meta root SELECT 313 system public reports_meta root UPDATE 314 system public role_members admin DELETE 315 system public role_members admin GRANT 316 system public role_members admin INSERT 317 system public role_members admin SELECT 318 system public role_members admin UPDATE 319 system public role_members root DELETE 320 system public role_members root GRANT 321 system public role_members root INSERT 322 system public role_members root SELECT 323 system public role_members root UPDATE 324 system public role_options admin DELETE 325 system public role_options admin GRANT 326 system public role_options admin INSERT 327 system public role_options admin SELECT 328 system public role_options admin UPDATE 329 system public role_options root DELETE 330 system public role_options root GRANT 331 system public role_options root INSERT 332 system public role_options root SELECT 333 system public role_options root UPDATE 334 system public settings admin DELETE 335 system public settings admin GRANT 336 system public settings admin INSERT 337 system public settings admin SELECT 338 system public settings admin UPDATE 339 system public settings root DELETE 340 system public settings root GRANT 341 system public settings root INSERT 342 system public settings root SELECT 343 system public settings root UPDATE 344 system public statement_bundle_chunks admin DELETE 345 system public statement_bundle_chunks admin GRANT 346 system public statement_bundle_chunks admin INSERT 347 system public statement_bundle_chunks admin SELECT 348 system public statement_bundle_chunks admin UPDATE 349 system public statement_bundle_chunks root DELETE 350 system public statement_bundle_chunks root GRANT 351 system public statement_bundle_chunks root INSERT 352 system public statement_bundle_chunks root SELECT 353 system public statement_bundle_chunks root UPDATE 354 system public statement_diagnostics admin DELETE 355 system public statement_diagnostics admin GRANT 356 system public statement_diagnostics admin INSERT 357 system public statement_diagnostics admin SELECT 358 system public statement_diagnostics admin UPDATE 359 system public statement_diagnostics root DELETE 360 system public statement_diagnostics root GRANT 361 system public statement_diagnostics root INSERT 362 system public statement_diagnostics root SELECT 363 system public statement_diagnostics root UPDATE 364 system public statement_diagnostics_requests admin DELETE 365 system public statement_diagnostics_requests admin GRANT 366 system public statement_diagnostics_requests admin INSERT 367 system public statement_diagnostics_requests admin SELECT 368 system public statement_diagnostics_requests admin UPDATE 369 system public statement_diagnostics_requests root DELETE 370 system public statement_diagnostics_requests root GRANT 371 system public statement_diagnostics_requests root INSERT 372 system public statement_diagnostics_requests root SELECT 373 system public statement_diagnostics_requests root UPDATE 374 system public table_statistics admin DELETE 375 system public table_statistics admin GRANT 376 system public table_statistics admin INSERT 377 system public table_statistics admin SELECT 378 system public table_statistics admin UPDATE 379 system public table_statistics root DELETE 380 system public table_statistics root GRANT 381 system public table_statistics root INSERT 382 system public table_statistics root SELECT 383 system public table_statistics root UPDATE 384 system public tenants admin GRANT 385 system public tenants admin SELECT 386 system public tenants root GRANT 387 system public tenants root SELECT 388 system public ui admin DELETE 389 system public ui admin GRANT 390 system public ui admin INSERT 391 system public ui admin SELECT 392 system public ui admin UPDATE 393 system public ui root DELETE 394 system public ui root GRANT 395 system public ui root INSERT 396 system public ui root SELECT 397 system public ui root UPDATE 398 system public users admin DELETE 399 system public users admin GRANT 400 system public users admin INSERT 401 system public users admin SELECT 402 system public users admin UPDATE 403 system public users root DELETE 404 system public users root GRANT 405 system public users root INSERT 406 system public users root SELECT 407 system public users root UPDATE 408 system public web_sessions admin DELETE 409 system public web_sessions admin GRANT 410 system public web_sessions admin INSERT 411 system public web_sessions admin SELECT 412 system public web_sessions admin UPDATE 413 system public web_sessions root DELETE 414 system public web_sessions root GRANT 415 system public web_sessions root INSERT 416 system public web_sessions root SELECT 417 system public web_sessions root UPDATE 418 system public zones admin DELETE 419 system public zones admin GRANT 420 system public zones admin INSERT 421 system public zones admin SELECT 422 system public zones admin UPDATE 423 system public zones root DELETE 424 system public zones root GRANT 425 system public zones root INSERT 426 system public zones root SELECT 427 system public zones root UPDATE 428 429 statement error user root does not have DROP privilege on database system 430 ALTER DATABASE system RENAME TO not_system 431 432 statement error user root does not have DROP privilege on database system 433 DROP DATABASE system 434 435 statement error user root does not have ALL privilege on database system 436 GRANT ALL ON DATABASE system TO testuser 437 438 statement error user root does not have INSERT privilege on database system 439 GRANT GRANT, SELECT, INSERT ON DATABASE system TO testuser 440 441 statement ok 442 GRANT GRANT, SELECT ON DATABASE system TO testuser 443 444 statement error user root does not have ALL privilege on relation namespace 445 GRANT ALL ON system.namespace TO testuser 446 447 statement error user root does not have INSERT privilege on relation namespace 448 GRANT GRANT, SELECT, INSERT ON system.namespace TO testuser 449 450 statement ok 451 GRANT GRANT, SELECT ON system.namespace TO testuser 452 453 statement ok 454 GRANT SELECT ON system.descriptor TO testuser 455 456 # Superusers must have exactly the allowed privileges. 457 statement error user root does not have ALL privilege on database system 458 GRANT ALL ON DATABASE system TO root 459 460 statement error user root does not have DELETE privilege on database system 461 GRANT DELETE, INSERT ON DATABASE system TO root 462 463 statement error user root does not have ALL privilege on relation namespace 464 GRANT ALL ON system.namespace TO root 465 466 statement error user root does not have DELETE privilege on relation descriptor 467 GRANT DELETE, INSERT ON system.descriptor TO root 468 469 statement error user root does not have ALL privilege on relation descriptor 470 GRANT ALL ON system.descriptor TO root 471 472 statement error user root must have exactly GRANT, SELECT privileges on system object with ID=.* 473 REVOKE GRANT ON DATABASE system FROM root 474 475 statement error user root must have exactly GRANT, SELECT privileges on system object with ID=.* 476 REVOKE GRANT ON system.namespace FROM root 477 478 statement error user root does not have ALL privilege on relation namespace 479 REVOKE ALL ON system.namespace FROM root 480 481 statement error user root does not have privileges over system object with ID=.* 482 REVOKE GRANT,SELECT ON system.namespace FROM root 483 484 statement error user root does not have ALL privilege on database system 485 GRANT ALL ON DATABASE system TO admin 486 487 statement error user root does not have DELETE privilege on database system 488 GRANT DELETE, INSERT ON DATABASE system TO admin 489 490 statement error user admin must have exactly GRANT, SELECT privileges on system object with ID=.* 491 REVOKE GRANT ON DATABASE system FROM admin 492 493 statement error user root does not have ALL privilege on relation namespace 494 GRANT ALL ON system.namespace TO admin 495 496 statement error user root does not have DELETE privilege on relation descriptor 497 GRANT DELETE, INSERT ON system.descriptor TO admin 498 499 statement error user root does not have ALL privilege on relation descriptor 500 GRANT ALL ON system.descriptor TO admin 501 502 statement error user admin must have exactly GRANT, SELECT privileges on system object with ID=.* 503 REVOKE GRANT ON system.descriptor FROM admin 504 505 statement error user admin must have exactly GRANT, SELECT privileges on system object with ID=.* 506 REVOKE GRANT ON DATABASE system FROM admin 507 508 statement error user admin must have exactly GRANT, SELECT privileges on system object with ID=.* 509 REVOKE GRANT ON system.namespace FROM admin 510 511 statement error user root does not have ALL privilege on relation namespace 512 REVOKE ALL ON system.namespace FROM admin 513 514 statement error user admin does not have privileges over system object with ID=.* 515 REVOKE GRANT,SELECT ON system.namespace FROM admin 516 517 # Some tables (we test system.lease here) used to allow multiple privilege sets for 518 # backwards compatibility, and superusers were allowed very wide privileges. 519 # We make sure this is no longer the case. 520 statement error user root does not have ALL privilege on relation lease 521 GRANT ALL ON system.lease TO testuser 522 523 statement error user root does not have CREATE privilege on relation lease 524 GRANT CREATE on system.lease to root 525 526 statement error user root does not have CREATE privilege on relation lease 527 GRANT CREATE on system.lease to admin 528 529 statement error user root does not have CREATE privilege on relation lease 530 GRANT CREATE on system.lease to testuser 531 532 statement error user root does not have ALL privilege on relation lease 533 GRANT ALL ON system.lease TO root 534 535 statement error user root does not have ALL privilege on relation lease 536 GRANT ALL ON system.lease TO admin 537 538 statement error user root does not have ALL privilege on relation lease 539 GRANT ALL ON system.lease TO testuser 540 541 # NB: the "order by" is necessary or this test is flaky under DistSQL. 542 # This is somewhat surprising. 543 query T 544 SELECT name 545 FROM system.settings 546 WHERE name != 'sql.defaults.distsql' 547 AND name != 'sql.stats.automatic_collection.enabled' 548 AND name NOT LIKE '%sql.defaults.vectorize%' 549 AND name NOT LIKE '%sql.testing.vectorize.batch_size%' 550 ORDER BY name 551 ---- 552 cluster.secret 553 diagnostics.reporting.enabled 554 kv.range_merge.queue_enabled 555 sql.stats.automatic_collection.min_stale_rows 556 version 557 558 statement ok 559 INSERT INTO system.settings (name, value) VALUES ('somesetting', 'somevalue') 560 561 query TT 562 SELECT name, value 563 FROM system.settings 564 WHERE name NOT IN ('version', 'sql.defaults.distsql', 'cluster.secret', 565 'sql.stats.automatic_collection.enabled', 'sql.defaults.vectorize', 566 'sql.defaults.vectorize_row_count_threshold', 567 'sql.testing.vectorize.batch_size') 568 ORDER BY name 569 ---- 570 diagnostics.reporting.enabled true 571 kv.range_merge.queue_enabled false 572 somesetting somevalue 573 sql.stats.automatic_collection.min_stale_rows 5 574 575 user testuser 576 577 statement error user testuser does not have SELECT privilege on relation settings 578 select name from system.settings 579 580 statement error user testuser does not have INSERT privilege on relation settings 581 UPSERT INTO system.settings (name, value) VALUES ('somesetting', 'somevalueother') 582 583 user root 584 585 query TTB 586 SELECT * from system.role_members 587 ---- 588 admin root true