github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/role (about) 1 # LogicTest: local 2 3 statement error a role/user named admin already exists 4 CREATE ROLE admin 5 6 statement error a role/user named root already exists 7 CREATE ROLE root 8 9 statement ok 10 CREATE ROLE IF NOT EXISTS root 11 12 statement ok 13 CREATE ROLE IF NOT EXISTS admin 14 15 statement error pq: cannot drop role/user admin: grants still exist on .* 16 DROP ROLE admin 17 18 statement error pq: cannot drop role/user root: grants still exist on .* 19 DROP ROLE root 20 21 statement error pq: cannot drop roles/users admin, root: grants still exist on .* 22 DROP ROLE admin, root 23 24 statement ok 25 CREATE ROLE myrole 26 27 query TTT colnames 28 SHOW ROLES 29 ---- 30 username options member_of 31 admin CREATEROLE {} 32 myrole NOLOGIN {} 33 root CREATEROLE {admin} 34 testuser · {} 35 36 statement error a role/user named myrole already exists 37 CREATE ROLE myrole 38 39 statement ok 40 CREATE ROLE IF NOT EXISTS myrole 41 42 statement error a role/user named myrole already exists 43 CREATE USER myrole 44 45 statement ok 46 DROP USER myrole 47 48 statement ok 49 CREATE ROLE myrole 50 51 statement ok 52 CREATE USER IF NOT EXISTS myrole 53 54 statement error pq: cannot drop roles/users admin, myrole: grants still exist on .* 55 DROP ROLE admin, myrole 56 57 query TTT colnames 58 SHOW ROLES 59 ---- 60 username options member_of 61 admin CREATEROLE {} 62 myrole NOLOGIN {} 63 root CREATEROLE {admin} 64 testuser · {} 65 66 statement ok 67 DROP ROLE myrole 68 69 query TTT colnames 70 SHOW ROLES 71 ---- 72 username options member_of 73 admin CREATEROLE {} 74 root CREATEROLE {admin} 75 testuser · {} 76 77 statement error pq: role/user myrole does not exist 78 DROP ROLE myrole 79 80 statement ok 81 DROP ROLE IF EXISTS myrole 82 83 statement ok 84 CREATE ROLE rolea 85 86 statement ok 87 CREATE ROLE roleb 88 89 statement ok 90 CREATE ROLE rolec 91 92 statement ok 93 CREATE ROLE roled 94 95 statement error pq: role/user rolee does not exist 96 DROP ROLE rolea, roleb, rolec, roled, rolee 97 98 statement ok 99 DROP ROLE IF EXISTS rolec, roled, rolee 100 101 statement ok 102 DROP ROLE rolea, roleb 103 104 query TTT colnames 105 SHOW ROLES 106 ---- 107 username options member_of 108 admin CREATEROLE {} 109 root CREATEROLE {admin} 110 testuser · {} 111 112 statement ok 113 CREATE USER testuser2 114 115 statement ok 116 CREATE ROLE testrole 117 118 query TTB colnames 119 SHOW GRANTS ON ROLE 120 ---- 121 role_name member is_admin 122 admin root true 123 124 query TTT colnames,rowsort 125 SELECT * FROM information_schema.administrable_role_authorizations 126 ---- 127 grantee role_name is_grantable 128 root admin YES 129 130 query TTT colnames,rowsort 131 SELECT * FROM information_schema.applicable_roles 132 ---- 133 grantee role_name is_grantable 134 root admin YES 135 136 query T colnames,rowsort 137 SELECT * FROM information_schema.enabled_roles 138 ---- 139 role_name 140 admin 141 root 142 143 statement error pq: role/user unknownuser does not exist 144 GRANT testrole TO unknownuser 145 146 statement error pq: role/user unknownrole does not exist 147 GRANT unknownrole TO testuser 148 149 # Test role "grant" and WITH ADMIN option. 150 user testuser 151 152 statement error pq: testuser is not a superuser or role admin for role testrole 153 GRANT testrole TO testuser2 154 155 user root 156 157 statement ok 158 GRANT testrole TO testuser 159 160 query TTB colnames 161 SELECT * FROM system.role_members 162 ---- 163 role member isAdmin 164 admin root true 165 testrole testuser false 166 167 query TTB colnames 168 SHOW GRANTS ON ROLE 169 ---- 170 role_name member is_admin 171 admin root true 172 testrole testuser false 173 174 user testuser 175 176 statement error pq: testuser is not a superuser or role admin for role testrole 177 GRANT testrole TO testuser2 178 179 user root 180 181 statement ok 182 GRANT testrole TO testuser WITH ADMIN OPTION 183 184 query TTB colnames 185 SELECT * FROM system.role_members 186 ---- 187 role member isAdmin 188 admin root true 189 testrole testuser true 190 191 user testuser 192 193 statement ok 194 GRANT testrole TO testuser2 WITH ADMIN OPTION 195 196 query TTT colnames,rowsort 197 SELECT * FROM information_schema.administrable_role_authorizations 198 ---- 199 grantee role_name is_grantable 200 testuser testrole YES 201 202 query TTT colnames,rowsort 203 SELECT * FROM information_schema.applicable_roles 204 ---- 205 grantee role_name is_grantable 206 testuser testrole YES 207 208 query T colnames,rowsort 209 SELECT * FROM information_schema.enabled_roles 210 ---- 211 role_name 212 testrole 213 testuser 214 215 user root 216 217 statement ok 218 GRANT admin TO testuser 219 220 # Verify that is_admin reports the right value. 221 query B 222 SELECT crdb_internal.is_admin() 223 ---- 224 true 225 226 # Dropping users/roles deletes all their memberships. 227 query TTB colnames 228 SELECT * FROM system.role_members 229 ---- 230 role member isAdmin 231 admin root true 232 admin testuser false 233 testrole testuser true 234 testrole testuser2 true 235 236 query TTB colnames 237 SHOW GRANTS ON ROLE 238 ---- 239 role_name member is_admin 240 admin root true 241 admin testuser false 242 testrole testuser true 243 testrole testuser2 true 244 245 query TTB colnames 246 SHOW GRANTS ON ROLE admin 247 ---- 248 role_name member is_admin 249 admin root true 250 admin testuser false 251 252 query TTB colnames 253 SHOW GRANTS ON ROLE FOR testuser 254 ---- 255 role_name member is_admin 256 admin testuser false 257 testrole testuser true 258 259 query TTB colnames 260 SHOW GRANTS ON ROLE testrole FOR testuser2 261 ---- 262 role_name member is_admin 263 testrole testuser2 true 264 265 query TTB colnames 266 SHOW GRANTS ON ROLE foo,testrole 267 ---- 268 role_name member is_admin 269 testrole testuser true 270 testrole testuser2 true 271 272 query TTB colnames 273 SHOW GRANTS ON ROLE FOR testuser, testuser2 274 ---- 275 role_name member is_admin 276 admin testuser false 277 testrole testuser true 278 testrole testuser2 true 279 280 query TTB colnames 281 SHOW GRANTS ON ROLE admin, testrole FOR root, testuser2 282 ---- 283 role_name member is_admin 284 admin root true 285 testrole testuser2 true 286 287 statement ok 288 DROP USER testuser 289 290 statement ok 291 CREATE USER testuser 292 293 query TTB colnames 294 SELECT * FROM system.role_members 295 ---- 296 role member isAdmin 297 admin root true 298 testrole testuser2 true 299 300 statement ok 301 DROP ROLE testrole 302 303 query TTB colnames 304 SELECT * FROM system.role_members 305 ---- 306 role member isAdmin 307 admin root true 308 309 # Test cycle detection. 310 statement error pq: admin cannot be a member of itself 311 GRANT admin TO admin 312 313 statement ok 314 CREATE ROLE rolea 315 316 statement ok 317 CREATE ROLE roleb 318 319 statement ok 320 CREATE ROLE rolec 321 322 statement ok 323 CREATE ROLE roled 324 325 statement ok 326 GRANT rolea TO roleb 327 328 statement error pq: making rolea a member of roleb would create a cycle 329 GRANT roleb TO rolea 330 331 statement ok 332 GRANT roleb TO rolec 333 334 statement ok 335 GRANT rolec TO roled 336 337 statement error pq: rolea cannot be a member of itself 338 GRANT rolea TO rolea 339 340 statement error pq: making rolea a member of roleb would create a cycle 341 GRANT roleb TO rolea 342 343 statement error pq: making rolea a member of rolec would create a cycle 344 GRANT rolec TO rolea 345 346 statement error pq: making rolea a member of roled would create a cycle 347 GRANT roled TO rolea 348 349 statement ok 350 CREATE ROLE rolee 351 352 # Test inherited ADMIN OPTION. 353 statement ok 354 GRANT roled TO testuser 355 356 statement ok 357 GRANT rolea TO roleb WITH ADMIN OPTION 358 359 user testuser 360 361 query TTT colnames,rowsort 362 SELECT * FROM information_schema.administrable_role_authorizations 363 ---- 364 grantee role_name is_grantable 365 testuser rolea YES 366 367 query TTT colnames,rowsort 368 SELECT * FROM information_schema.applicable_roles 369 ---- 370 grantee role_name is_grantable 371 testuser roled NO 372 testuser rolec NO 373 testuser roleb NO 374 testuser rolea YES 375 376 query T colnames,rowsort 377 SELECT * FROM information_schema.enabled_roles 378 ---- 379 role_name 380 rolea 381 roleb 382 rolec 383 roled 384 testuser 385 386 statement error pq: testuser is not a superuser or role admin for role roled 387 GRANT roled TO rolee 388 389 statement error pq: testuser is not a superuser or role admin for role rolec 390 GRANT rolec TO rolee 391 392 statement error pq: testuser is not a superuser or role admin for role roleb 393 GRANT roleb TO rolee 394 395 statement ok 396 GRANT rolea TO rolee 397 398 query TTT colnames,rowsort 399 SELECT * FROM information_schema.administrable_role_authorizations 400 ---- 401 grantee role_name is_grantable 402 testuser rolea YES 403 404 query TTT colnames,rowsort 405 SELECT * FROM information_schema.applicable_roles 406 ---- 407 grantee role_name is_grantable 408 testuser rolec NO 409 testuser roleb NO 410 testuser rolea YES 411 testuser roled NO 412 413 query T colnames,rowsort 414 SELECT * FROM information_schema.enabled_roles 415 ---- 416 role_name 417 rolea 418 roleb 419 rolec 420 roled 421 testuser 422 423 user root 424 425 query TTB colnames 426 SELECT * FROM system.role_members 427 ---- 428 role member isAdmin 429 admin root true 430 rolea roleb true 431 rolea rolee false 432 roleb rolec false 433 rolec roled false 434 roled testuser false 435 436 statement ok 437 DROP ROLE rolea 438 439 statement ok 440 DROP ROLE rolec 441 442 query TTB colnames 443 SELECT * FROM system.role_members 444 ---- 445 role member isAdmin 446 admin root true 447 roled testuser false 448 449 query TTT 450 SHOW ROLES 451 ---- 452 admin CREATEROLE {} 453 roleb NOLOGIN {} 454 roled NOLOGIN {} 455 rolee NOLOGIN {} 456 root CREATEROLE {admin} 457 testuser · {roled} 458 testuser2 · {} 459 460 statement ok 461 DROP ROLE roleb 462 463 statement ok 464 DROP ROLE roled 465 466 statement ok 467 DROP ROLE rolee 468 469 statement error pq: role/user root cannot be removed from role admin or lose the ADMIN OPTION 470 REVOKE admin FROM root 471 472 statement error pq: role/user root cannot be removed from role admin or lose the ADMIN OPTION 473 REVOKE ADMIN OPTION FOR admin FROM root 474 475 statement error pq: role/user unknownuser does not exist 476 REVOKE ADMIN OPTION FOR admin FROM unknownuser 477 478 statement error pq: role/user unknownrole does not exist 479 REVOKE ADMIN OPTION FOR unknownrole FROM root 480 481 statement ok 482 CREATE ROLE rolea 483 484 statement ok 485 CREATE ROLE roleb 486 487 statement ok 488 GRANT rolea,roleb TO testuser WITH ADMIN OPTION 489 490 query TTB colnames 491 SELECT * FROM system.role_members 492 ---- 493 role member isAdmin 494 admin root true 495 rolea testuser true 496 roleb testuser true 497 498 user testuser 499 500 statement ok 501 GRANT rolea,roleb TO root WITH ADMIN OPTION 502 503 user root 504 505 query TTB colnames 506 SELECT * FROM system.role_members 507 ---- 508 role member isAdmin 509 admin root true 510 rolea root true 511 rolea testuser true 512 roleb root true 513 roleb testuser true 514 515 query TTT colnames,rowsort 516 SELECT * FROM information_schema.administrable_role_authorizations 517 ---- 518 grantee role_name is_grantable 519 root admin YES 520 root rolea YES 521 root roleb YES 522 523 query TTT colnames,rowsort 524 SELECT * FROM information_schema.applicable_roles 525 ---- 526 grantee role_name is_grantable 527 root admin YES 528 root rolea YES 529 root roleb YES 530 531 query T colnames,rowsort 532 SELECT * FROM information_schema.enabled_roles 533 ---- 534 role_name 535 admin 536 rolea 537 roleb 538 root 539 540 user testuser 541 542 query TTT colnames,rowsort 543 SELECT * FROM information_schema.administrable_role_authorizations 544 ---- 545 grantee role_name is_grantable 546 testuser rolea YES 547 testuser roleb YES 548 549 query TTT colnames,rowsort 550 SELECT * FROM information_schema.applicable_roles 551 ---- 552 grantee role_name is_grantable 553 testuser rolea YES 554 testuser roleb YES 555 556 query T colnames,rowsort 557 SELECT * FROM information_schema.enabled_roles 558 ---- 559 role_name 560 rolea 561 roleb 562 testuser 563 564 statement ok 565 REVOKE ADMIN OPTION FOR rolea FROM testuser 566 567 statement error pq: testuser is not a superuser or role admin for role rolea 568 REVOKE ADMIN OPTION FOR rolea FROM root 569 570 statement ok 571 REVOKE roleb FROM root 572 573 user root 574 575 query TTB colnames 576 SELECT * FROM system.role_members 577 ---- 578 role member isAdmin 579 admin root true 580 rolea root true 581 rolea testuser false 582 roleb testuser true 583 584 statement ok 585 REVOKE rolea, roleb FROM testuser, root 586 587 query TTB colnames 588 SELECT * FROM system.role_members 589 ---- 590 role member isAdmin 591 admin root true 592 593 # Test privilege checks. 594 595 statement ok 596 CREATE DATABASE db1 597 598 user testuser 599 600 statement error only users with the admin role are allowed to CREATE DATABASE 601 CREATE DATABASE db2 602 603 statement error user testuser does not have DROP privilege on database db1 604 DROP DATABASE db1 605 606 statement error testuser is not a role admin for role admin 607 GRANT admin TO testuser 608 609 user root 610 611 statement ok 612 CREATE ROLE newgroup 613 614 statement ok 615 GRANT newgroup TO testuser 616 617 statement ok 618 GRANT admin TO newgroup 619 620 user testuser 621 622 query TTB colnames 623 SELECT * FROM system.role_members 624 ---- 625 role member isAdmin 626 admin newgroup false 627 admin root true 628 newgroup testuser false 629 630 statement ok 631 CREATE DATABASE db2 632 633 statement ok 634 DROP DATABASE db1 635 636 # Revoke admin privileges. 'newgroup' does not have any privileges. 637 user root 638 639 statement ok 640 REVOKE admin FROM newgroup 641 642 user testuser 643 644 statement error user testuser does not have SELECT privilege on relation role_members 645 SELECT * FROM system.role_members 646 647 statement error user testuser does not have CREATE privilege on database db2 648 CREATE TABLE db2.foo (k int); 649 650 user root 651 652 query TTB colnames 653 SELECT * FROM system.role_members 654 ---- 655 role member isAdmin 656 admin root true 657 newgroup testuser false 658 659 statement ok 660 GRANT ALL ON DATABASE db2 TO newgroup 661 662 user testuser 663 664 query TTTT colnames 665 SHOW GRANTS ON DATABASE db2 666 ---- 667 database_name schema_name grantee privilege_type 668 db2 crdb_internal admin ALL 669 db2 crdb_internal newgroup ALL 670 db2 crdb_internal root ALL 671 db2 information_schema admin ALL 672 db2 information_schema newgroup ALL 673 db2 information_schema root ALL 674 db2 pg_catalog admin ALL 675 db2 pg_catalog newgroup ALL 676 db2 pg_catalog root ALL 677 db2 pg_extension admin ALL 678 db2 pg_extension newgroup ALL 679 db2 pg_extension root ALL 680 db2 public admin ALL 681 db2 public newgroup ALL 682 db2 public root ALL 683 684 statement ok 685 CREATE TABLE db2.foo (k int); 686 687 statement ok 688 INSERT INTO db2.foo VALUES (1),(2),(3); 689 690 statement ok 691 SELECT * FROM db2.foo 692 693 # We may be in the 'newgroup', but we don't have the admin option. 694 statement error testuser is not a superuser or role admin for role newgroup 695 GRANT newgroup TO testuser2 696 697 statement error testuser is not a superuser or role admin for role newgroup 698 REVOKE newgroup FROM testuser 699 700 statement error testuser is not a superuser or role admin for role newgroup 701 GRANT newgroup TO testuser WITH ADMIN OPTION 702 703 # Regression for #31784 704 user root 705 706 # grant admin to testuser without ADMIN OPTION 707 statement ok 708 CREATE USER user1; 709 GRANT admin TO testuser 710 711 user testuser 712 713 statement error pq: testuser is not a role admin for role admin 714 GRANT admin TO user1 715 716 statement error pq: testuser is not a role admin for role admin 717 REVOKE admin FROM user1 718 719 user root 720 721 # WITH ADMIN OPTION means that testuser now has permission to add to the admin role 722 statement ok 723 GRANT admin TO testuser WITH ADMIN OPTION 724 725 user testuser 726 727 statement ok 728 GRANT admin TO user1 729 730 statement ok 731 REVOKE admin FROM user1 732 733 user root 734 735 statement ok 736 DROP USER user1 737 738 user root 739 740 # The user does not have direct privileges on anything, so we can drop it. 741 statement ok 742 DROP USER testuser 743 744 query TTB colnames 745 SELECT * FROM system.role_members 746 ---- 747 role member isAdmin 748 admin root true 749 750 statement error cannot drop role/user newgroup: grants still exist on db2, db2.public.foo 751 DROP ROLE newgroup 752 753 statement ok 754 REVOKE ALL ON db2.* FROM newgroup 755 756 statement ok 757 REVOKE ALL ON DATABASE db2 FROM newgroup 758 759 statement ok 760 DROP ROLE newgroup 761 762 # Test the "public" pseudo-role. 763 764 statement error role name "public" is reserved 765 CREATE USER public 766 767 statement error role name "public" is reserved 768 CREATE ROLE public 769 770 statement error cannot drop role/user public: grants still exist on system.public.comments 771 DROP USER public 772 773 statement error cannot drop role/user public: grants still exist on system.public.comments 774 DROP ROLE public 775 776 statement error role/user public does not exist 777 GRANT public TO testuser 778 779 statement error role/user public does not exist 780 GRANT admin TO public 781 782 statement error role/user public does not exist 783 REVOKE public FROM testuser 784 785 statement error role/user public does not exist 786 REVOKE admin FROM public 787 788 # Test "WITH CREATEROLE" option 789 790 statement ok 791 CREATE USER testuser 792 793 query TTB colnames 794 SELECT * FROM system.role_members 795 ---- 796 role member isAdmin 797 admin root true 798 799 800 user testuser 801 802 statement error pq: user testuser does not have CREATEROLE privilege 803 CREATE ROLE rolef 804 805 user root 806 807 statement ok 808 ALTER ROLE testuser CREATEROLE 809 810 user testuser 811 812 statement ok 813 CREATE ROLE rolef 814 815 statement ok 816 ALTER ROLE rolef LOGIN 817 818 statement ok 819 DROP ROLE rolef 820 821 # Testing invalid CREATEROLE combinations 822 user root 823 824 statement ok 825 ALTER ROLE testuser NOCREATEROLE 826 827 statement error pq: conflicting role options 828 CREATE ROLE rolewithcreate WITH NOCREATEROLE CREATEROLE 829 830 statement error pq: conflicting role options 831 CREATE ROLE rolewithcreate NOCREATEROLE CREATEROLE 832 833 statement error pq: conflicting role options 834 ALTER ROLE testrole WITH CREATEROLE NOCREATEROLE 835 836 statement error pq: conflicting role options 837 ALTER ROLE testrole CREATEROLE NOCREATEROLE 838 839 statement error pq: redundant role options 840 CREATE ROLE rolewithcreate WITH CREATEROLE CREATEROLE 841 842 statement error pq: redundant role options 843 CREATE ROLE rolewithcreate WITH NOCREATEROLE NOCREATEROLE 844 845 statement error pq: redundant role options 846 ALTER ROLE testrole WITH CREATEROLE CREATEROLE 847 848 statement error pq: redundant role options 849 ALTER ROLE testrole WITH NOCREATEROLE NOCREATEROLE 850 851 statement ok 852 CREATE ROLE rolewithcreate WITH CREATEROLE 853 854 statement ok 855 CREATE ROLE anotherrolewithcreate CREATEROLE 856 857 statement ok 858 CREATE ROLE rolewithoutcreate WITH NOCREATEROLE 859 860 statement ok 861 CREATE ROLE IF NOT EXISTS rolewithcreate2 WITH CREATEROLE 862 863 statement ok 864 CREATE ROLE IF NOT EXISTS anotherrolewithcreate2 CREATEROLE 865 866 statement ok 867 CREATE ROLE IF NOT EXISTS rolewithoutcreate2 WITH NOCREATEROLE 868 869 query TTB colnames 870 SELECT * FROM system.role_members 871 ---- 872 role member isAdmin 873 admin root true 874 875 user testuser 876 877 # User should not have permissions to CREATE / ALTER role without CREATEROLE privilege 878 statement error pq: user testuser does not have CREATEROLE privilege 879 CREATE ROLE rolewithcreate3 WITH CREATEROLE 880 881 statement error pq: user testuser does not have CREATEROLE privilege 882 ALTER ROLE rolewithcreate WITH NOCREATEROLE 883 884 user root 885 886 statement ok 887 GRANT rolewithcreate TO testuser 888 889 user testuser 890 891 statement ok 892 CREATE ROLE roleg WITH CREATEROLE 893 894 statement ok 895 ALTER ROLE roleg WITH NOCREATEROLE 896 897 statement ok 898 DROP ROLE roleg 899 900 statement ok 901 CREATE ROLE IF NOT EXISTS roleg 902 903 statement ok 904 CREATE ROLE IF NOT EXISTS roleg 905 906 # Need Admin option to GRANT role, CREATEROLE should not give GRANT role privilege for other roles 907 statement ok 908 CREATE USER testuser3 909 910 statement error pq: testuser is not a role admin for role admin 911 GRANT admin to testuser3 912 913 statement error pq: testuser is not a superuser or role admin for role roleg 914 GRANT roleg to testuser3 915 916 user root 917 918 statement ok 919 ALTER ROLE rolewithcreate WITH NOCREATEROLE 920 921 statement ok 922 ALTER ROLE rolewithcreate NOCREATEROLE 923 924 statement error pq: cannot edit admin role 925 ALTER ROLE admin with NOCREATEROLE 926 927 query TTB colnames 928 SELECT * FROM system.role_members 929 ---- 930 role member isAdmin 931 admin root true 932 rolewithcreate testuser false 933 934 # testuser should no longer have CREATEROLE privileges 935 936 user testuser 937 938 statement error pq: user testuser does not have CREATEROLE privilege 939 CREATE ROLE roleh WITH CREATEROLE 940 941 statement error pq: user testuser does not have CREATEROLE privilege 942 ALTER ROLE roleg with NOCREATEROLE 943 944 statement error pq: user testuser does not have CREATEROLE privilege 945 DROP ROLE roleg 946 947 statement error pq: user testuser does not have CREATEROLE privilege 948 CREATE ROLE IF NOT EXISTS rolewithcreate WITH CREATEROLE 949 950 statement error pq: user testuser does not have CREATEROLE privilege 951 CREATE USER testuser4 952 953 statement error pq: user testuser does not have CREATEROLE privilege 954 ALTER USER testuser3 WITH PASSWORD 'ilov3beefjerky' 955 956 user root 957 958 statement error pq: role/user rolek does not exist 959 ALTER ROLE rolek CREATEROLE 960 961 statement ok 962 ALTER ROLE IF EXISTS rolek CREATEROLE 963 964 statement ok 965 ALTER USER IF EXISTS rolek NOCREATEROLE 966 967 statement ok 968 ALTER USER rolewithcreate WITH NOCREATEROLE 969 970 statement ok 971 ALTER ROLE rolewithcreate CREATEROLE 972 973 user testuser 974 975 statement ok 976 CREATE ROLE IF NOT EXISTS rolei WITH NOCREATEROLE 977 978 statement ok 979 DROP ROLE rolewithcreate 980 981 statement error pq: user testuser does not have CREATEROLE privilege 982 CREATE ROLE rolewithcreate 983 984 statement error pq: user testuser does not have CREATEROLE privilege 985 CREATE ROLE IF NOT EXISTS roleh WITH CREATEROLE 986 987 # Testing nested role privilege 988 user root 989 990 statement ok 991 CREATE USER childrole WITH NOCREATEROLE 992 993 statement ok 994 CREATE ROLE parentrole WITH CREATEROLE 995 996 statement ok 997 GRANT parentrole TO childrole 998 999 statement ok 1000 GRANT childrole to testuser 1001 1002 user testuser 1003 1004 statement ok 1005 CREATE ROLE rolej 1006 1007 # Testing LOGIN and VALID UNTIL role privilege 1008 user root 1009 1010 statement ok 1011 DELETE FROM system.role_options WHERE NOT username in ('root', 'admin') 1012 1013 statement ok 1014 CREATE ROLE rolewithlogin LOGIN 1015 1016 query TTT 1017 SELECT * FROM system.role_options 1018 ---- 1019 admin CREATEROLE NULL 1020 root CREATEROLE NULL 1021 1022 statement ok 1023 CREATE ROLE rolewithnologin NOLOGIN 1024 1025 query TTT 1026 SELECT * FROM system.role_options 1027 ---- 1028 admin CREATEROLE NULL 1029 rolewithnologin NOLOGIN NULL 1030 root CREATEROLE NULL 1031 1032 statement ok 1033 ALTER ROLE rolewithlogin VALID UNTIL '2020-01-01' 1034 1035 query TTT 1036 SELECT * FROM system.role_options 1037 ---- 1038 admin CREATEROLE NULL 1039 rolewithlogin VALID UNTIL 2020-01-01 00:00:00+00:00 1040 rolewithnologin NOLOGIN NULL 1041 root CREATEROLE NULL 1042 1043 statement ok 1044 ALTER ROLE rolewithlogin VALID UNTIL NULL 1045 1046 query TTT 1047 SELECT * FROM system.role_options 1048 ---- 1049 admin CREATEROLE NULL 1050 rolewithlogin VALID UNTIL NULL 1051 rolewithnologin NOLOGIN NULL 1052 root CREATEROLE NULL 1053 1054 statement ok 1055 DROP ROLE rolewithlogin 1056 1057 query TTT 1058 SELECT * FROM system.role_options 1059 ---- 1060 admin CREATEROLE NULL 1061 rolewithnologin NOLOGIN NULL 1062 root CREATEROLE NULL 1063 1064 statement error pq: conflicting role options 1065 CREATE ROLE thisshouldntwork LOGIN NOLOGIN 1066 1067 statement error pq: redundant role options 1068 CREATE ROLE thisshouldntwork LOGIN LOGIN 1069 1070 statement ok 1071 DROP ROLE childrole 1072 1073 statement ok 1074 DROP ROLE parentrole 1075 1076 query TTB colnames 1077 SHOW GRANTS ON ROLE 1078 ---- 1079 role_name member is_admin 1080 admin root true 1081 1082 query TTB colnames 1083 SHOW GRANTS ON ROLE admin 1084 ---- 1085 role_name member is_admin 1086 admin root true 1087 1088 query TTB colnames 1089 SHOW GRANTS ON ROLE FOR root 1090 ---- 1091 role_name member is_admin 1092 admin root true 1093 1094 query TTB colnames 1095 SHOW GRANTS ON ROLE admin FOR root 1096 ---- 1097 role_name member is_admin 1098 admin root true 1099 1100 query TTB colnames 1101 SHOW GRANTS ON ROLE FOR testuser 1102 ---- 1103 role_name member is_admin 1104 1105 query TTB colnames 1106 SHOW GRANTS ON ROLE testuser,admin FOR testuser,admin 1107 ---- 1108 role_name member is_admin 1109 1110 # Test the "public" pseudo-role. 1111 1112 statement error role name "public" is reserved 1113 CREATE USER public 1114 1115 statement error cannot drop role/user public: grants still exist on system.public.comments 1116 DROP USER public 1117 1118 statement ok 1119 CREATE DATABASE publicdb; 1120 1121 statement ok 1122 CREATE DATABASE privatedb; 1123 1124 statement ok 1125 CREATE TABLE publicdb.publictable (k int) 1126 1127 statement ok 1128 CREATE TABLE publicdb.privatetable (k int) 1129 1130 statement ok 1131 CREATE TABLE privatedb.publictable (k int) 1132 1133 statement ok 1134 CREATE TABLE privatedb.privatetable (k int) 1135 1136 statement ok 1137 GRANT GRANT,SELECT ON DATABASE publicdb TO public 1138 1139 statement ok 1140 GRANT GRANT,SELECT ON publicdb.publictable TO public 1141 1142 statement ok 1143 GRANT GRANT,SELECT ON privatedb.publictable TO public 1144 1145 user testuser 1146 1147 query T 1148 SHOW DATABASES 1149 ---- 1150 publicdb 1151 1152 query TTT 1153 SHOW TABLES FROM publicdb 1154 ---- 1155 public publictable table 1156 1157 query TTT 1158 SHOW TABLES FROM privatedb 1159 ---- 1160 1161 statement ok 1162 SELECT * FROM publicdb.publictable 1163 1164 statement error user testuser does not have SELECT privilege on relation privatetable 1165 SELECT * FROM publicdb.privatetable 1166 1167 statement ok 1168 SELECT * FROM privatedb.publictable 1169 1170 statement error user testuser does not have SELECT privilege on relation privatetable 1171 SELECT * FROM privatedb.privatetable 1172 1173 statement error user testuser does not have INSERT privilege on relation publictable 1174 INSERT INTO publicdb.publictable VALUES (1) 1175 1176 user root 1177 1178 statement ok 1179 GRANT INSERT ON publicdb.publictable TO public 1180 1181 user testuser 1182 1183 statement ok 1184 INSERT INTO publicdb.publictable VALUES (1) 1185 1186 user root 1187 1188 # Revoke public access. 1189 statement ok 1190 REVOKE ALL ON publicdb.publictable FROM public 1191 1192 user testuser 1193 1194 statement error user testuser does not have SELECT privilege on relation publictable 1195 SELECT * FROM publicdb.publictable 1196 1197 statement error user testuser does not have INSERT privilege on relation publictable 1198 INSERT INTO publicdb.publictable VALUES (1) 1199 1200 query TTT 1201 SHOW TABLES FROM publicdb 1202 ----