github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/grant_table (about) 1 # LogicTest: local 2 3 statement ok 4 CREATE DATABASE a 5 6 statement ok 7 CREATE USER readwrite 8 9 statement ok 10 GRANT ALL ON DATABASE a TO readwrite 11 12 query TTTT colnames 13 SHOW GRANTS ON DATABASE a 14 ---- 15 database_name schema_name grantee privilege_type 16 a crdb_internal admin ALL 17 a crdb_internal readwrite ALL 18 a crdb_internal root ALL 19 a information_schema admin ALL 20 a information_schema readwrite ALL 21 a information_schema root ALL 22 a pg_catalog admin ALL 23 a pg_catalog readwrite ALL 24 a pg_catalog root ALL 25 a pg_extension admin ALL 26 a pg_extension readwrite ALL 27 a pg_extension root ALL 28 a public admin ALL 29 a public readwrite ALL 30 a public root ALL 31 32 # Show that by default GRANT is restricted to the current database 33 query TTTTT colnames 34 SHOW GRANTS 35 ---- 36 database_name schema_name table_name grantee privilege_type 37 test crdb_internal NULL admin ALL 38 test crdb_internal NULL root ALL 39 test crdb_internal backward_dependencies public SELECT 40 test crdb_internal builtin_functions public SELECT 41 test crdb_internal cluster_queries public SELECT 42 test crdb_internal cluster_sessions public SELECT 43 test crdb_internal cluster_settings public SELECT 44 test crdb_internal cluster_transactions public SELECT 45 test crdb_internal create_statements public SELECT 46 test crdb_internal create_type_statements public SELECT 47 test crdb_internal feature_usage public SELECT 48 test crdb_internal forward_dependencies public SELECT 49 test crdb_internal gossip_alerts public SELECT 50 test crdb_internal gossip_liveness public SELECT 51 test crdb_internal gossip_network public SELECT 52 test crdb_internal gossip_nodes public SELECT 53 test crdb_internal index_columns public SELECT 54 test crdb_internal jobs public SELECT 55 test crdb_internal kv_node_status public SELECT 56 test crdb_internal kv_store_status public SELECT 57 test crdb_internal leases public SELECT 58 test crdb_internal node_build_info public SELECT 59 test crdb_internal node_metrics public SELECT 60 test crdb_internal node_queries public SELECT 61 test crdb_internal node_runtime_info public SELECT 62 test crdb_internal node_sessions public SELECT 63 test crdb_internal node_statement_statistics public SELECT 64 test crdb_internal node_transactions public SELECT 65 test crdb_internal node_txn_stats public SELECT 66 test crdb_internal partitions public SELECT 67 test crdb_internal predefined_comments public SELECT 68 test crdb_internal ranges public SELECT 69 test crdb_internal ranges_no_leases public SELECT 70 test crdb_internal schema_changes public SELECT 71 test crdb_internal session_trace public SELECT 72 test crdb_internal session_variables public SELECT 73 test crdb_internal table_columns public SELECT 74 test crdb_internal table_indexes public SELECT 75 test crdb_internal tables public SELECT 76 test crdb_internal zones public SELECT 77 test information_schema NULL admin ALL 78 test information_schema NULL root ALL 79 test information_schema administrable_role_authorizations public SELECT 80 test information_schema applicable_roles public SELECT 81 test information_schema check_constraints public SELECT 82 test information_schema column_privileges public SELECT 83 test information_schema columns public SELECT 84 test information_schema constraint_column_usage public SELECT 85 test information_schema enabled_roles public SELECT 86 test information_schema key_column_usage public SELECT 87 test information_schema parameters public SELECT 88 test information_schema referential_constraints public SELECT 89 test information_schema role_table_grants public SELECT 90 test information_schema routines public SELECT 91 test information_schema schema_privileges public SELECT 92 test information_schema schemata public SELECT 93 test information_schema sequences public SELECT 94 test information_schema statistics public SELECT 95 test information_schema table_constraints public SELECT 96 test information_schema table_privileges public SELECT 97 test information_schema tables public SELECT 98 test information_schema user_privileges public SELECT 99 test information_schema views public SELECT 100 test pg_catalog NULL admin ALL 101 test pg_catalog NULL root ALL 102 test pg_catalog pg_aggregate public SELECT 103 test pg_catalog pg_am public SELECT 104 test pg_catalog pg_attrdef public SELECT 105 test pg_catalog pg_attribute public SELECT 106 test pg_catalog pg_auth_members public SELECT 107 test pg_catalog pg_authid public SELECT 108 test pg_catalog pg_available_extensions public SELECT 109 test pg_catalog pg_cast public SELECT 110 test pg_catalog pg_class public SELECT 111 test pg_catalog pg_collation public SELECT 112 test pg_catalog pg_constraint public SELECT 113 test pg_catalog pg_conversion public SELECT 114 test pg_catalog pg_database public SELECT 115 test pg_catalog pg_default_acl public SELECT 116 test pg_catalog pg_depend public SELECT 117 test pg_catalog pg_description public SELECT 118 test pg_catalog pg_enum public SELECT 119 test pg_catalog pg_event_trigger public SELECT 120 test pg_catalog pg_extension public SELECT 121 test pg_catalog pg_foreign_data_wrapper public SELECT 122 test pg_catalog pg_foreign_server public SELECT 123 test pg_catalog pg_foreign_table public SELECT 124 test pg_catalog pg_index public SELECT 125 test pg_catalog pg_indexes public SELECT 126 test pg_catalog pg_inherits public SELECT 127 test pg_catalog pg_language public SELECT 128 test pg_catalog pg_locks public SELECT 129 test pg_catalog pg_matviews public SELECT 130 test pg_catalog pg_namespace public SELECT 131 test pg_catalog pg_operator public SELECT 132 test pg_catalog pg_prepared_statements public SELECT 133 test pg_catalog pg_prepared_xacts public SELECT 134 test pg_catalog pg_proc public SELECT 135 test pg_catalog pg_range public SELECT 136 test pg_catalog pg_rewrite public SELECT 137 test pg_catalog pg_roles public SELECT 138 test pg_catalog pg_seclabel public SELECT 139 test pg_catalog pg_seclabels public SELECT 140 test pg_catalog pg_sequence public SELECT 141 test pg_catalog pg_settings public SELECT 142 test pg_catalog pg_shdepend public SELECT 143 test pg_catalog pg_shdescription public SELECT 144 test pg_catalog pg_shseclabel public SELECT 145 test pg_catalog pg_stat_activity public SELECT 146 test pg_catalog pg_tables public SELECT 147 test pg_catalog pg_tablespace public SELECT 148 test pg_catalog pg_trigger public SELECT 149 test pg_catalog pg_type public SELECT 150 test pg_catalog pg_user public SELECT 151 test pg_catalog pg_user_mapping public SELECT 152 test pg_catalog pg_views public SELECT 153 test pg_extension NULL admin ALL 154 test pg_extension NULL root ALL 155 test pg_extension geography_columns public SELECT 156 test pg_extension geometry_columns public SELECT 157 test pg_extension spatial_ref_sys public SELECT 158 test public NULL admin ALL 159 test public NULL root ALL 160 161 query TTTTT colnames 162 SHOW GRANTS FOR root 163 ---- 164 database_name schema_name table_name grantee privilege_type 165 test crdb_internal NULL root ALL 166 test information_schema NULL root ALL 167 test pg_catalog NULL root ALL 168 test pg_extension NULL root ALL 169 test public NULL root ALL 170 171 # With no database set, we show the grants everywhere 172 statement ok 173 SET DATABASE = '' 174 175 query TTTTT colnames 176 SELECT * FROM [SHOW GRANTS] 177 WHERE schema_name NOT IN ('crdb_internal', 'pg_catalog', 'information_schema') 178 ORDER BY 1,2,3 179 ---- 180 database_name schema_name table_name grantee privilege_type 181 a pg_extension NULL admin ALL 182 a pg_extension NULL readwrite ALL 183 a pg_extension NULL root ALL 184 a pg_extension geography_columns public SELECT 185 a pg_extension geometry_columns public SELECT 186 a pg_extension spatial_ref_sys public SELECT 187 a public NULL root ALL 188 a public NULL admin ALL 189 a public NULL readwrite ALL 190 defaultdb pg_extension NULL admin ALL 191 defaultdb pg_extension NULL root ALL 192 defaultdb pg_extension geography_columns public SELECT 193 defaultdb pg_extension geometry_columns public SELECT 194 defaultdb pg_extension spatial_ref_sys public SELECT 195 defaultdb public NULL admin ALL 196 defaultdb public NULL root ALL 197 postgres pg_extension NULL admin ALL 198 postgres pg_extension NULL root ALL 199 postgres pg_extension geography_columns public SELECT 200 postgres pg_extension geometry_columns public SELECT 201 postgres pg_extension spatial_ref_sys public SELECT 202 postgres public NULL admin ALL 203 postgres public NULL root ALL 204 system pg_extension NULL admin SELECT 205 system pg_extension NULL admin GRANT 206 system pg_extension NULL root GRANT 207 system pg_extension NULL root SELECT 208 system pg_extension geography_columns public SELECT 209 system pg_extension geometry_columns public SELECT 210 system pg_extension spatial_ref_sys public SELECT 211 system public NULL root SELECT 212 system public NULL root GRANT 213 system public NULL admin SELECT 214 system public NULL admin GRANT 215 system public comments admin DELETE 216 system public comments admin GRANT 217 system public comments public SELECT 218 system public comments root UPDATE 219 system public comments root INSERT 220 system public comments root SELECT 221 system public comments root GRANT 222 system public comments admin SELECT 223 system public comments admin INSERT 224 system public comments admin UPDATE 225 system public comments root DELETE 226 system public descriptor root GRANT 227 system public descriptor admin SELECT 228 system public descriptor admin GRANT 229 system public descriptor root SELECT 230 system public eventlog root UPDATE 231 system public eventlog admin INSERT 232 system public eventlog admin DELETE 233 system public eventlog admin SELECT 234 system public eventlog admin UPDATE 235 system public eventlog root DELETE 236 system public eventlog root GRANT 237 system public eventlog root INSERT 238 system public eventlog root SELECT 239 system public eventlog admin GRANT 240 system public jobs root UPDATE 241 system public jobs root SELECT 242 system public jobs admin DELETE 243 system public jobs root INSERT 244 system public jobs root GRANT 245 system public jobs root DELETE 246 system public jobs admin SELECT 247 system public jobs admin INSERT 248 system public jobs admin UPDATE 249 system public jobs admin GRANT 250 system public lease admin GRANT 251 system public lease root SELECT 252 system public lease root INSERT 253 system public lease root GRANT 254 system public lease root DELETE 255 system public lease admin UPDATE 256 system public lease admin SELECT 257 system public lease admin INSERT 258 system public lease admin DELETE 259 system public lease root UPDATE 260 system public locations root DELETE 261 system public locations admin UPDATE 262 system public locations admin DELETE 263 system public locations admin INSERT 264 system public locations admin GRANT 265 system public locations root GRANT 266 system public locations root SELECT 267 system public locations root UPDATE 268 system public locations admin SELECT 269 system public locations root INSERT 270 system public namespace admin SELECT 271 system public namespace admin GRANT 272 system public namespace root GRANT 273 system public namespace root SELECT 274 system public namespace2 root SELECT 275 system public namespace2 root GRANT 276 system public namespace2 admin SELECT 277 system public namespace2 admin GRANT 278 system public protected_ts_meta admin SELECT 279 system public protected_ts_meta admin GRANT 280 system public protected_ts_meta root SELECT 281 system public protected_ts_meta root GRANT 282 system public protected_ts_records root SELECT 283 system public protected_ts_records admin GRANT 284 system public protected_ts_records root GRANT 285 system public protected_ts_records admin SELECT 286 system public rangelog admin GRANT 287 system public rangelog admin UPDATE 288 system public rangelog root UPDATE 289 system public rangelog root SELECT 290 system public rangelog root INSERT 291 system public rangelog root GRANT 292 system public rangelog root DELETE 293 system public rangelog admin DELETE 294 system public rangelog admin SELECT 295 system public rangelog admin INSERT 296 system public replication_constraint_stats admin DELETE 297 system public replication_constraint_stats admin INSERT 298 system public replication_constraint_stats admin UPDATE 299 system public replication_constraint_stats root GRANT 300 system public replication_constraint_stats root INSERT 301 system public replication_constraint_stats root DELETE 302 system public replication_constraint_stats root UPDATE 303 system public replication_constraint_stats admin SELECT 304 system public replication_constraint_stats admin GRANT 305 system public replication_constraint_stats root SELECT 306 system public replication_critical_localities root SELECT 307 system public replication_critical_localities root INSERT 308 system public replication_critical_localities admin SELECT 309 system public replication_critical_localities root GRANT 310 system public replication_critical_localities root DELETE 311 system public replication_critical_localities admin DELETE 312 system public replication_critical_localities admin GRANT 313 system public replication_critical_localities admin INSERT 314 system public replication_critical_localities root UPDATE 315 system public replication_critical_localities admin UPDATE 316 system public replication_stats admin GRANT 317 system public replication_stats admin SELECT 318 system public replication_stats root UPDATE 319 system public replication_stats admin INSERT 320 system public replication_stats admin UPDATE 321 system public replication_stats root GRANT 322 system public replication_stats root DELETE 323 system public replication_stats admin DELETE 324 system public replication_stats root INSERT 325 system public replication_stats root SELECT 326 system public reports_meta root INSERT 327 system public reports_meta admin DELETE 328 system public reports_meta admin GRANT 329 system public reports_meta admin INSERT 330 system public reports_meta admin SELECT 331 system public reports_meta admin UPDATE 332 system public reports_meta root UPDATE 333 system public reports_meta root SELECT 334 system public reports_meta root DELETE 335 system public reports_meta root GRANT 336 system public role_members admin DELETE 337 system public role_members admin INSERT 338 system public role_members admin SELECT 339 system public role_members root DELETE 340 system public role_members root GRANT 341 system public role_members root UPDATE 342 system public role_members admin GRANT 343 system public role_members root SELECT 344 system public role_members root INSERT 345 system public role_members admin UPDATE 346 system public role_options admin INSERT 347 system public role_options root SELECT 348 system public role_options root INSERT 349 system public role_options root GRANT 350 system public role_options admin DELETE 351 system public role_options admin GRANT 352 system public role_options admin SELECT 353 system public role_options root DELETE 354 system public role_options root UPDATE 355 system public role_options admin UPDATE 356 system public settings admin GRANT 357 system public settings root GRANT 358 system public settings admin UPDATE 359 system public settings root UPDATE 360 system public settings root SELECT 361 system public settings root INSERT 362 system public settings admin DELETE 363 system public settings admin SELECT 364 system public settings admin INSERT 365 system public settings root DELETE 366 system public statement_bundle_chunks admin DELETE 367 system public statement_bundle_chunks admin GRANT 368 system public statement_bundle_chunks admin INSERT 369 system public statement_bundle_chunks admin UPDATE 370 system public statement_bundle_chunks root DELETE 371 system public statement_bundle_chunks root GRANT 372 system public statement_bundle_chunks root INSERT 373 system public statement_bundle_chunks root SELECT 374 system public statement_bundle_chunks root UPDATE 375 system public statement_bundle_chunks admin SELECT 376 system public statement_diagnostics admin INSERT 377 system public statement_diagnostics admin SELECT 378 system public statement_diagnostics admin UPDATE 379 system public statement_diagnostics root DELETE 380 system public statement_diagnostics root GRANT 381 system public statement_diagnostics root INSERT 382 system public statement_diagnostics root SELECT 383 system public statement_diagnostics root UPDATE 384 system public statement_diagnostics admin DELETE 385 system public statement_diagnostics admin GRANT 386 system public statement_diagnostics_requests root SELECT 387 system public statement_diagnostics_requests root GRANT 388 system public statement_diagnostics_requests root DELETE 389 system public statement_diagnostics_requests admin UPDATE 390 system public statement_diagnostics_requests admin SELECT 391 system public statement_diagnostics_requests admin INSERT 392 system public statement_diagnostics_requests admin GRANT 393 system public statement_diagnostics_requests admin DELETE 394 system public statement_diagnostics_requests root UPDATE 395 system public statement_diagnostics_requests root INSERT 396 system public table_statistics admin SELECT 397 system public table_statistics root UPDATE 398 system public table_statistics root SELECT 399 system public table_statistics root GRANT 400 system public table_statistics admin DELETE 401 system public table_statistics admin GRANT 402 system public table_statistics admin INSERT 403 system public table_statistics root DELETE 404 system public table_statistics admin UPDATE 405 system public table_statistics root INSERT 406 system public tenants admin GRANT 407 system public tenants admin SELECT 408 system public tenants root GRANT 409 system public tenants root SELECT 410 system public ui admin SELECT 411 system public ui admin GRANT 412 system public ui admin DELETE 413 system public ui root GRANT 414 system public ui root INSERT 415 system public ui root SELECT 416 system public ui root DELETE 417 system public ui root UPDATE 418 system public ui admin UPDATE 419 system public ui admin INSERT 420 system public users root UPDATE 421 system public users root SELECT 422 system public users admin DELETE 423 system public users root INSERT 424 system public users root GRANT 425 system public users root DELETE 426 system public users admin UPDATE 427 system public users admin SELECT 428 system public users admin INSERT 429 system public users admin GRANT 430 system public web_sessions admin DELETE 431 system public web_sessions admin GRANT 432 system public web_sessions admin INSERT 433 system public web_sessions admin SELECT 434 system public web_sessions admin UPDATE 435 system public web_sessions root DELETE 436 system public web_sessions root GRANT 437 system public web_sessions root INSERT 438 system public web_sessions root UPDATE 439 system public web_sessions root SELECT 440 system public zones admin DELETE 441 system public zones admin SELECT 442 system public zones admin UPDATE 443 system public zones root DELETE 444 system public zones root GRANT 445 system public zones root INSERT 446 system public zones root SELECT 447 system public zones root UPDATE 448 system public zones admin GRANT 449 system public zones admin INSERT 450 test pg_extension NULL admin ALL 451 test pg_extension NULL root ALL 452 test pg_extension geography_columns public SELECT 453 test pg_extension geometry_columns public SELECT 454 test pg_extension spatial_ref_sys public SELECT 455 test public NULL admin ALL 456 test public NULL root ALL 457 458 query TTTTT colnames 459 SHOW GRANTS FOR root 460 ---- 461 database_name schema_name table_name grantee privilege_type 462 a crdb_internal NULL root ALL 463 a information_schema NULL root ALL 464 a pg_catalog NULL root ALL 465 a pg_extension NULL root ALL 466 a public NULL root ALL 467 defaultdb crdb_internal NULL root ALL 468 defaultdb information_schema NULL root ALL 469 defaultdb pg_catalog NULL root ALL 470 defaultdb pg_extension NULL root ALL 471 defaultdb public NULL root ALL 472 postgres crdb_internal NULL root ALL 473 postgres information_schema NULL root ALL 474 postgres pg_catalog NULL root ALL 475 postgres pg_extension NULL root ALL 476 postgres public NULL root ALL 477 system crdb_internal NULL root GRANT 478 system crdb_internal NULL root SELECT 479 system information_schema NULL root GRANT 480 system information_schema NULL root SELECT 481 system pg_catalog NULL root GRANT 482 system pg_catalog NULL root SELECT 483 system pg_extension NULL root GRANT 484 system pg_extension NULL root SELECT 485 system public NULL root GRANT 486 system public NULL root SELECT 487 system public comments root DELETE 488 system public comments root GRANT 489 system public comments root INSERT 490 system public comments root SELECT 491 system public comments root UPDATE 492 system public descriptor root GRANT 493 system public descriptor root SELECT 494 system public eventlog root DELETE 495 system public eventlog root GRANT 496 system public eventlog root INSERT 497 system public eventlog root SELECT 498 system public eventlog root UPDATE 499 system public jobs root DELETE 500 system public jobs root GRANT 501 system public jobs root INSERT 502 system public jobs root SELECT 503 system public jobs root UPDATE 504 system public lease root DELETE 505 system public lease root GRANT 506 system public lease root INSERT 507 system public lease root SELECT 508 system public lease root UPDATE 509 system public locations root DELETE 510 system public locations root GRANT 511 system public locations root INSERT 512 system public locations root SELECT 513 system public locations root UPDATE 514 system public namespace root GRANT 515 system public namespace root SELECT 516 system public namespace2 root GRANT 517 system public namespace2 root SELECT 518 system public protected_ts_meta root GRANT 519 system public protected_ts_meta root SELECT 520 system public protected_ts_records root GRANT 521 system public protected_ts_records root SELECT 522 system public rangelog root DELETE 523 system public rangelog root GRANT 524 system public rangelog root INSERT 525 system public rangelog root SELECT 526 system public rangelog root UPDATE 527 system public replication_constraint_stats root DELETE 528 system public replication_constraint_stats root GRANT 529 system public replication_constraint_stats root INSERT 530 system public replication_constraint_stats root SELECT 531 system public replication_constraint_stats root UPDATE 532 system public replication_critical_localities root DELETE 533 system public replication_critical_localities root GRANT 534 system public replication_critical_localities root INSERT 535 system public replication_critical_localities root SELECT 536 system public replication_critical_localities root UPDATE 537 system public replication_stats root DELETE 538 system public replication_stats root GRANT 539 system public replication_stats root INSERT 540 system public replication_stats root SELECT 541 system public replication_stats root UPDATE 542 system public reports_meta root DELETE 543 system public reports_meta root GRANT 544 system public reports_meta root INSERT 545 system public reports_meta root SELECT 546 system public reports_meta root UPDATE 547 system public role_members root DELETE 548 system public role_members root GRANT 549 system public role_members root INSERT 550 system public role_members root SELECT 551 system public role_members root UPDATE 552 system public role_options root DELETE 553 system public role_options root GRANT 554 system public role_options root INSERT 555 system public role_options root SELECT 556 system public role_options root UPDATE 557 system public settings root DELETE 558 system public settings root GRANT 559 system public settings root INSERT 560 system public settings root SELECT 561 system public settings root UPDATE 562 system public statement_bundle_chunks root DELETE 563 system public statement_bundle_chunks root GRANT 564 system public statement_bundle_chunks root INSERT 565 system public statement_bundle_chunks root SELECT 566 system public statement_bundle_chunks root UPDATE 567 system public statement_diagnostics root DELETE 568 system public statement_diagnostics root GRANT 569 system public statement_diagnostics root INSERT 570 system public statement_diagnostics root SELECT 571 system public statement_diagnostics root UPDATE 572 system public statement_diagnostics_requests root DELETE 573 system public statement_diagnostics_requests root GRANT 574 system public statement_diagnostics_requests root INSERT 575 system public statement_diagnostics_requests root SELECT 576 system public statement_diagnostics_requests root UPDATE 577 system public table_statistics root DELETE 578 system public table_statistics root GRANT 579 system public table_statistics root INSERT 580 system public table_statistics root SELECT 581 system public table_statistics root UPDATE 582 system public tenants root GRANT 583 system public tenants root SELECT 584 system public ui root DELETE 585 system public ui root GRANT 586 system public ui root INSERT 587 system public ui root SELECT 588 system public ui root UPDATE 589 system public users root DELETE 590 system public users root GRANT 591 system public users root INSERT 592 system public users root SELECT 593 system public users root UPDATE 594 system public web_sessions root DELETE 595 system public web_sessions root GRANT 596 system public web_sessions root INSERT 597 system public web_sessions root SELECT 598 system public web_sessions root UPDATE 599 system public zones root DELETE 600 system public zones root GRANT 601 system public zones root INSERT 602 system public zones root SELECT 603 system public zones root UPDATE 604 test crdb_internal NULL root ALL 605 test information_schema NULL root ALL 606 test pg_catalog NULL root ALL 607 test pg_extension NULL root ALL 608 test public NULL root ALL 609 610 statement error pgcode 42P01 relation "a.t" does not exist 611 SHOW GRANTS ON a.t 612 613 statement error pgcode 42P01 relation "t" does not exist 614 SHOW GRANTS ON t 615 616 statement ok 617 SET DATABASE = a 618 619 statement error pgcode 42P01 relation "t" does not exist 620 SHOW GRANTS ON t 621 622 statement error pgcode 42P01 relation "a.t" does not exist 623 GRANT ALL ON a.t TO readwrite 624 625 statement ok 626 CREATE TABLE t (id INT PRIMARY KEY) 627 628 query TTTTT colnames 629 SHOW GRANTS ON t 630 ---- 631 database_name schema_name table_name grantee privilege_type 632 a public t admin ALL 633 a public t readwrite ALL 634 a public t root ALL 635 636 query TTTTT colnames 637 SHOW GRANTS ON a.t 638 ---- 639 database_name schema_name table_name grantee privilege_type 640 a public t admin ALL 641 a public t readwrite ALL 642 a public t root ALL 643 644 statement ok 645 INSERT INTO system.users VALUES('test-user',''); 646 647 statement ok 648 GRANT ALL ON t TO readwrite, "test-user" 649 650 query TTTTT 651 SHOW GRANTS ON t 652 ---- 653 a public t admin ALL 654 a public t readwrite ALL 655 a public t root ALL 656 a public t test-user ALL 657 658 query TTTTT 659 SHOW GRANTS ON t FOR readwrite, "test-user" 660 ---- 661 a public t readwrite ALL 662 a public t test-user ALL 663 664 statement ok 665 REVOKE INSERT,DELETE ON t FROM "test-user",readwrite 666 667 query TTTTT 668 SHOW GRANTS ON t 669 ---- 670 a public t admin ALL 671 a public t readwrite CREATE 672 a public t readwrite DROP 673 a public t readwrite GRANT 674 a public t readwrite SELECT 675 a public t readwrite UPDATE 676 a public t readwrite ZONECONFIG 677 a public t root ALL 678 a public t test-user CREATE 679 a public t test-user DROP 680 a public t test-user GRANT 681 a public t test-user SELECT 682 a public t test-user UPDATE 683 a public t test-user ZONECONFIG 684 685 query TTTTT 686 SHOW GRANTS ON t FOR readwrite, "test-user" 687 ---- 688 a public t readwrite CREATE 689 a public t readwrite DROP 690 a public t readwrite GRANT 691 a public t readwrite SELECT 692 a public t readwrite UPDATE 693 a public t readwrite ZONECONFIG 694 a public t test-user CREATE 695 a public t test-user DROP 696 a public t test-user GRANT 697 a public t test-user SELECT 698 a public t test-user UPDATE 699 a public t test-user ZONECONFIG 700 701 statement ok 702 REVOKE SELECT ON t FROM "test-user" 703 704 query TTTTT 705 SHOW GRANTS ON t 706 ---- 707 a public t admin ALL 708 a public t readwrite CREATE 709 a public t readwrite DROP 710 a public t readwrite GRANT 711 a public t readwrite SELECT 712 a public t readwrite UPDATE 713 a public t readwrite ZONECONFIG 714 a public t root ALL 715 a public t test-user CREATE 716 a public t test-user DROP 717 a public t test-user GRANT 718 a public t test-user UPDATE 719 a public t test-user ZONECONFIG 720 721 query TTTTT 722 SHOW GRANTS ON t FOR readwrite, "test-user" 723 ---- 724 a public t readwrite CREATE 725 a public t readwrite DROP 726 a public t readwrite GRANT 727 a public t readwrite SELECT 728 a public t readwrite UPDATE 729 a public t readwrite ZONECONFIG 730 a public t test-user CREATE 731 a public t test-user DROP 732 a public t test-user GRANT 733 a public t test-user UPDATE 734 a public t test-user ZONECONFIG 735 736 statement ok 737 REVOKE ALL ON t FROM readwrite,"test-user" 738 739 query TTTTT 740 SHOW GRANTS ON t 741 ---- 742 a public t admin ALL 743 a public t root ALL 744 745 query TTTTT 746 SHOW GRANTS ON t FOR readwrite, "test-user" 747 ---- 748 749 # The same as above, but on a view 750 751 statement ok 752 CREATE VIEW v as SELECT id FROM t 753 754 query TTTTT colnames 755 SHOW GRANTS ON v 756 ---- 757 database_name schema_name table_name grantee privilege_type 758 a public v admin ALL 759 a public v readwrite ALL 760 a public v root ALL 761 762 query TTTTT colnames 763 SHOW GRANTS ON a.v 764 ---- 765 database_name schema_name table_name grantee privilege_type 766 a public v admin ALL 767 a public v readwrite ALL 768 a public v root ALL 769 770 statement ok 771 GRANT ALL ON v TO readwrite, "test-user" 772 773 query TTTTT 774 SHOW GRANTS ON v 775 ---- 776 a public v admin ALL 777 a public v readwrite ALL 778 a public v root ALL 779 a public v test-user ALL 780 781 query TTTTT 782 SHOW GRANTS ON v FOR readwrite, "test-user" 783 ---- 784 a public v readwrite ALL 785 a public v test-user ALL 786 787 statement ok 788 REVOKE INSERT,DELETE ON v FROM "test-user",readwrite 789 790 query TTTTT 791 SHOW GRANTS ON v 792 ---- 793 a public v admin ALL 794 a public v readwrite CREATE 795 a public v readwrite DROP 796 a public v readwrite GRANT 797 a public v readwrite SELECT 798 a public v readwrite UPDATE 799 a public v readwrite ZONECONFIG 800 a public v root ALL 801 a public v test-user CREATE 802 a public v test-user DROP 803 a public v test-user GRANT 804 a public v test-user SELECT 805 a public v test-user UPDATE 806 a public v test-user ZONECONFIG 807 808 query TTTTT 809 SHOW GRANTS ON v FOR readwrite, "test-user" 810 ---- 811 a public v readwrite CREATE 812 a public v readwrite DROP 813 a public v readwrite GRANT 814 a public v readwrite SELECT 815 a public v readwrite UPDATE 816 a public v readwrite ZONECONFIG 817 a public v test-user CREATE 818 a public v test-user DROP 819 a public v test-user GRANT 820 a public v test-user SELECT 821 a public v test-user UPDATE 822 a public v test-user ZONECONFIG 823 824 statement ok 825 REVOKE SELECT ON v FROM "test-user" 826 827 query TTTTT 828 SHOW GRANTS ON v 829 ---- 830 a public v admin ALL 831 a public v readwrite CREATE 832 a public v readwrite DROP 833 a public v readwrite GRANT 834 a public v readwrite SELECT 835 a public v readwrite UPDATE 836 a public v readwrite ZONECONFIG 837 a public v root ALL 838 a public v test-user CREATE 839 a public v test-user DROP 840 a public v test-user GRANT 841 a public v test-user UPDATE 842 a public v test-user ZONECONFIG 843 844 query TTTTT 845 SHOW GRANTS ON v FOR readwrite, "test-user" 846 ---- 847 a public v readwrite CREATE 848 a public v readwrite DROP 849 a public v readwrite GRANT 850 a public v readwrite SELECT 851 a public v readwrite UPDATE 852 a public v readwrite ZONECONFIG 853 a public v test-user CREATE 854 a public v test-user DROP 855 a public v test-user GRANT 856 a public v test-user UPDATE 857 a public v test-user ZONECONFIG 858 859 query TTTTT 860 SHOW GRANTS FOR readwrite, "test-user" 861 ---- 862 a crdb_internal NULL readwrite ALL 863 a information_schema NULL readwrite ALL 864 a pg_catalog NULL readwrite ALL 865 a pg_extension NULL readwrite ALL 866 a public NULL readwrite ALL 867 a public v readwrite CREATE 868 a public v readwrite DROP 869 a public v readwrite GRANT 870 a public v readwrite SELECT 871 a public v readwrite UPDATE 872 a public v readwrite ZONECONFIG 873 a public v test-user CREATE 874 a public v test-user DROP 875 a public v test-user GRANT 876 a public v test-user UPDATE 877 a public v test-user ZONECONFIG 878 879 statement ok 880 REVOKE ALL ON v FROM readwrite,"test-user" 881 882 query TTTTT 883 SHOW GRANTS ON v 884 ---- 885 a public v admin ALL 886 a public v root ALL 887 888 query TTTTT 889 SHOW GRANTS ON v FOR readwrite, "test-user" 890 ---- 891 892 query TTTTT 893 SHOW GRANTS FOR readwrite, "test-user" 894 ---- 895 a crdb_internal NULL readwrite ALL 896 a information_schema NULL readwrite ALL 897 a pg_catalog NULL readwrite ALL 898 a pg_extension NULL readwrite ALL 899 a public NULL readwrite ALL 900 901 # Verify that the DB privileges have not changed. 902 query TTTT colnames 903 SHOW GRANTS ON DATABASE a 904 ---- 905 database_name schema_name grantee privilege_type 906 a crdb_internal admin ALL 907 a crdb_internal readwrite ALL 908 a crdb_internal root ALL 909 a information_schema admin ALL 910 a information_schema readwrite ALL 911 a information_schema root ALL 912 a pg_catalog admin ALL 913 a pg_catalog readwrite ALL 914 a pg_catalog root ALL 915 a pg_extension admin ALL 916 a pg_extension readwrite ALL 917 a pg_extension root ALL 918 a public admin ALL 919 a public readwrite ALL 920 a public root ALL 921 922 923 # Errors due to invalid targets. 924 statement ok 925 SET DATABASE = "" 926 927 statement error at or near "@": syntax error 928 GRANT ALL ON a.t@xyz TO readwrite 929 930 statement error no database specified 931 GRANT ALL ON * TO readwrite 932 933 statement error pgcode 42P01 relation "a.tt" does not exist 934 GRANT ALL ON a.t, a.tt TO readwrite 935 936 # '*' doesn't work for databases. 937 statement error at or near "\*": syntax error 938 GRANT ALL ON DATABASE * TO readwrite 939 940 statement ok 941 CREATE DATABASE b 942 943 statement ok 944 CREATE TABLE b.t (id INT PRIMARY KEY) 945 946 statement ok 947 CREATE TABLE b.t2 (id INT PRIMARY KEY) 948 949 statement ok 950 CREATE DATABASE c 951 952 statement ok 953 CREATE TABLE c.t (id INT PRIMARY KEY) 954 955 # `*` works after you've set a database 956 statement ok 957 SET DATABASE = "b" 958 959 statement error pq: user or role vanilli does not exist 960 GRANT ALL ON * TO Vanilli 961 962 statement ok 963 CREATE USER Vanilli 964 965 statement ok 966 GRANT ALL ON * TO Vanilli 967 968 query TTTTT colnames 969 SHOW GRANTS ON * 970 ---- 971 database_name schema_name table_name grantee privilege_type 972 b public t admin ALL 973 b public t root ALL 974 b public t vanilli ALL 975 b public t2 admin ALL 976 b public t2 root ALL 977 b public t2 vanilli ALL 978 979 980 # Multiple targets. 981 statement ok 982 CREATE USER Millie 983 984 statement ok 985 GRANT ALL ON c.*, b.t TO Millie 986 987 query TTTTT colnames 988 SHOW GRANTS ON b.* 989 ---- 990 database_name schema_name table_name grantee privilege_type 991 b public t admin ALL 992 b public t millie ALL 993 b public t root ALL 994 b public t vanilli ALL 995 b public t2 admin ALL 996 b public t2 root ALL 997 b public t2 vanilli ALL 998 999 query TTTTT colnames 1000 SHOW GRANTS ON a.*, b.* 1001 ---- 1002 database_name schema_name table_name grantee privilege_type 1003 a public t admin ALL 1004 a public t root ALL 1005 a public v admin ALL 1006 a public v root ALL 1007 b public t admin ALL 1008 b public t millie ALL 1009 b public t root ALL 1010 b public t vanilli ALL 1011 b public t2 admin ALL 1012 b public t2 root ALL 1013 b public t2 vanilli ALL 1014 1015 query TTTTT colnames 1016 SHOW GRANTS ON c.t 1017 ---- 1018 database_name schema_name table_name grantee privilege_type 1019 c public t admin ALL 1020 c public t millie ALL 1021 c public t root ALL 1022 1023 statement ok 1024 REVOKE ALL ON *, c.* FROM Vanilli 1025 1026 query TTTTT colnames 1027 SHOW GRANTS ON b.* 1028 ---- 1029 database_name schema_name table_name grantee privilege_type 1030 b public t admin ALL 1031 b public t millie ALL 1032 b public t root ALL 1033 b public t2 admin ALL 1034 b public t2 root ALL 1035 1036 statement ok 1037 CREATE DATABASE empty 1038 1039 query TTTTT colnames 1040 SHOW GRANTS ON empty.* 1041 ---- 1042 database_name schema_name table_name grantee privilege_type 1043 1044 query TTTTT colnames 1045 SHOW GRANTS ON empty.*, b.* 1046 ---- 1047 database_name schema_name table_name grantee privilege_type 1048 b public t admin ALL 1049 b public t millie ALL 1050 b public t root ALL 1051 b public t2 admin ALL 1052 b public t2 root ALL