1# Copyright: (c) 2019, Andrew Klychkov (@Andersson007) <aaklychkov@mail.ru> 2# GNU General Public License v3.0+ (see COPYING or https://www.gnu.org/licenses/gpl-3.0.txt) 3 4#################### 5# Prepare for tests: 6 7# Create test roles: 8- name: postgresql_owner - create test roles 9 become_user: "{{ pg_user }}" 10 become: yes 11 postgresql_user: 12 login_user: "{{ pg_user }}" 13 db: postgres 14 name: "{{ item }}" 15 ignore_errors: yes 16 with_items: 17 - alice 18 - bob 19 20# Create test database: 21- name: postgresql_owner - create test database 22 become_user: "{{ pg_user }}" 23 become: yes 24 postgresql_db: 25 login_user: "{{ pg_user }}" 26 db: acme 27 28# Create test table: 29- name: postgresql_owner - create test table 30 become_user: "{{ pg_user }}" 31 become: yes 32 postgresql_query: 33 login_user: "{{ pg_user }}" 34 db: acme 35 query: "CREATE TABLE my_table (id int)" 36 37- name: postgresql_owner - set owner 38 become_user: "{{ pg_user }}" 39 become: yes 40 postgresql_owner: 41 login_user: "{{ pg_user }}" 42 db: acme 43 new_owner: bob 44 obj_name: my_table 45 obj_type: table 46 47# Create test sequence: 48- name: postgresql_owner - create test sequence 49 become_user: "{{ pg_user }}" 50 become: yes 51 postgresql_query: 52 login_user: "{{ pg_user }}" 53 db: acme 54 query: "CREATE SEQUENCE test_seq" 55 56# Create test function: 57- name: postgresql_owner - create test function 58 become_user: "{{ pg_user }}" 59 become: yes 60 postgresql_query: 61 login_user: "{{ pg_user }}" 62 db: acme 63 query: "CREATE FUNCTION increment(integer) RETURNS integer AS 'select $1 + 1;' LANGUAGE SQL IMMUTABLE RETURNS NULL ON NULL INPUT;" 64 65# Create test schema: 66- name: postgresql_owner - create test schema 67 become_user: "{{ pg_user }}" 68 become: yes 69 postgresql_query: 70 login_user: "{{ pg_user }}" 71 db: acme 72 query: "CREATE SCHEMA test_schema" 73 74# Create test view: 75- name: postgresql_owner - create test view 76 become_user: "{{ pg_user }}" 77 become: yes 78 postgresql_query: 79 login_user: "{{ pg_user }}" 80 db: acme 81 query: "CREATE VIEW test_view AS SELECT * FROM my_table" 82 83# Create test materialized view (available from PG ver 9.4): 84- name: postgresql_owner - create test materialized view 85 become_user: "{{ pg_user }}" 86 become: yes 87 postgresql_query: 88 login_user: "{{ pg_user }}" 89 db: acme 90 query: "CREATE MATERIALIZED VIEW test_mat_view AS SELECT * FROM my_table" 91 when: postgres_version_resp.stdout is version('9.4', '>=') 92 93# Create test tablespace 94- name: postgresql_owner - create a new tablespace called acme and set bob as an its owner 95 become_user: "{{ pg_user }}" 96 become: yes 97 postgresql_tablespace: 98 db: acme 99 login_user: "{{ pg_user }}" 100 name: acme 101 owner: alice 102 location: /ssd 103 104################ 105# Do main tests: 106 107# 108# check reassign_owned_by param 109# 110# try to reassign ownership to non existent user: 111- name: postgresql_owner - reassign_owned_by to non existent user 112 become_user: "{{ pg_user }}" 113 become: yes 114 postgresql_owner: 115 login_user: "{{ pg_user }}" 116 db: acme 117 new_owner: non_existent 118 reassign_owned_by: bob 119 register: result 120 ignore_errors: yes 121 122- assert: 123 that: 124 - result.failed == true 125 126# try to reassign ownership from existent user with fail_on_role: 127- name: postgresql_owner - reassign_owned_by, check fail_on_role 128 become_user: "{{ pg_user }}" 129 become: yes 130 postgresql_owner: 131 login_user: "{{ pg_user }}" 132 db: acme 133 new_owner: alice 134 reassign_owned_by: non_existent 135 fail_on_role: no 136 register: result 137 138- assert: 139 that: 140 - result.failed == false 141 142# check_mode: 143- name: postgresql_owner - reassign_owned_by in check_mode 144 become_user: "{{ pg_user }}" 145 become: yes 146 postgresql_owner: 147 login_user: "{{ pg_user }}" 148 db: acme 149 new_owner: alice 150 reassign_owned_by: bob 151 check_mode: yes 152 register: result 153 154- assert: 155 that: 156 - result is changed 157 - result.queries == ['REASSIGN OWNED BY "bob" TO "alice"'] 158 159# Check, rowcount must be 0 160- name: postgresql_owner - check that nothing changed after the previous step 161 become_user: "{{ pg_user }}" 162 become: yes 163 postgresql_query: 164 db: acme 165 login_user: "{{ pg_user }}" 166 query: "SELECT 1 FROM pg_tables WHERE tablename = 'my_table' AND tableowner = 'alice'" 167 ignore_errors: yes 168 register: result 169 170- assert: 171 that: 172 - result.rowcount == 0 173 174# True mode: 175- name: postgresql_owner - reassign_owned_by 176 become_user: "{{ pg_user }}" 177 become: yes 178 postgresql_owner: 179 login_user: "{{ pg_user }}" 180 db: acme 181 new_owner: alice 182 reassign_owned_by: bob 183 register: result 184 185- assert: 186 that: 187 - result is changed 188 - result.queries == ['REASSIGN OWNED BY "bob" TO "alice"'] 189 190# Check, rowcount must be 1 191- name: postgresql_owner - check that ownership has been changed after the previous step 192 become_user: "{{ pg_user }}" 193 become: yes 194 postgresql_query: 195 db: acme 196 login_user: "{{ pg_user }}" 197 query: "SELECT 1 FROM pg_tables WHERE tablename = 'my_table' AND tableowner = 'alice'" 198 ignore_errors: yes 199 register: result 200 201- assert: 202 that: 203 - result.rowcount == 1 204 205# 206# Check obj_type for each type 207# 208 209# ############################# 210# check_mode obj_type: database 211- name: postgresql_owner - set db owner in check_mode 212 become_user: "{{ pg_user }}" 213 become: yes 214 postgresql_owner: 215 login_user: "{{ pg_user }}" 216 db: acme 217 new_owner: bob 218 obj_name: acme 219 obj_type: database 220 check_mode: yes 221 register: result 222 223- assert: 224 that: 225 - result is changed 226 - result.queries == ['ALTER DATABASE "acme" OWNER TO "bob"'] 227 228# Check, rowcount must be 0 229- name: postgresql_owner - check that nothing changed after the previous step 230 become_user: "{{ pg_user }}" 231 become: yes 232 postgresql_query: 233 db: acme 234 login_user: "{{ pg_user }}" 235 query: "SELECT 1 FROM pg_database AS d JOIN pg_roles AS r ON d.datdba = r.oid WHERE d.datname = 'acme' AND r.rolname = 'bob'" 236 ignore_errors: yes 237 register: result 238 239- assert: 240 that: 241 - result.rowcount == 0 242 243# true mode obj_type: database 244- name: postgresql_owner - set db owner 245 become_user: "{{ pg_user }}" 246 become: yes 247 postgresql_owner: 248 login_user: "{{ pg_user }}" 249 db: acme 250 new_owner: bob 251 obj_name: acme 252 obj_type: database 253 register: result 254 255- assert: 256 that: 257 - result is changed 258 - result.queries == ['ALTER DATABASE "acme" OWNER TO "bob"'] 259 260# Check, rowcount must be 1 261- name: postgresql_owner - check that db owner has been changed after the previous step 262 become_user: "{{ pg_user }}" 263 become: yes 264 postgresql_query: 265 db: acme 266 login_user: "{{ pg_user }}" 267 query: "SELECT 1 FROM pg_database AS d JOIN pg_roles AS r ON d.datdba = r.oid WHERE d.datname = 'acme' AND r.rolname = 'bob'" 268 ignore_errors: yes 269 register: result 270 271- assert: 272 that: 273 - result.rowcount == 1 274 275# true mode obj_type: database, try to set again 276- name: postgresql_owner - set db owner again 277 become_user: "{{ pg_user }}" 278 become: yes 279 postgresql_owner: 280 login_user: "{{ pg_user }}" 281 db: acme 282 new_owner: bob 283 obj_name: acme 284 obj_type: database 285 register: result 286 287- assert: 288 that: 289 - result is not changed 290 - result.queries == [] 291 292# Check, rowcount must be 1 293- name: postgresql_owner - check that db owner is bob 294 become_user: "{{ pg_user }}" 295 become: yes 296 postgresql_query: 297 db: acme 298 login_user: "{{ pg_user }}" 299 query: "SELECT 1 FROM pg_database AS d JOIN pg_roles AS r ON d.datdba = r.oid WHERE d.datname = 'acme' AND r.rolname = 'bob'" 300 ignore_errors: yes 301 register: result 302 303- assert: 304 that: 305 - result.rowcount == 1 306 307# ########################## 308# check_mode obj_type: table 309- name: postgresql_owner - set table owner in check_mode 310 become_user: "{{ pg_user }}" 311 become: yes 312 postgresql_owner: 313 login_user: "{{ pg_user }}" 314 db: acme 315 new_owner: bob 316 obj_name: my_table 317 obj_type: table 318 check_mode: yes 319 register: result 320 321- assert: 322 that: 323 - result is changed 324 - result.queries == ['ALTER TABLE "my_table" OWNER TO "bob"'] 325 326# Check, rowcount must be 0 327- name: postgresql_owner - check that nothing changed after the previous step 328 become_user: "{{ pg_user }}" 329 become: yes 330 postgresql_query: 331 db: acme 332 login_user: "{{ pg_user }}" 333 query: "SELECT 1 FROM pg_tables WHERE tablename = 'my_table' AND tableowner = 'bob'" 334 ignore_errors: yes 335 register: result 336 337- assert: 338 that: 339 - result.rowcount == 0 340 341# true mode obj_type: table 342- name: postgresql_owner - set db owner 343 become_user: "{{ pg_user }}" 344 become: yes 345 postgresql_owner: 346 login_user: "{{ pg_user }}" 347 db: acme 348 new_owner: bob 349 obj_name: my_table 350 obj_type: table 351 register: result 352 353- assert: 354 that: 355 - result is changed 356 - result.queries == ['ALTER TABLE "my_table" OWNER TO "bob"'] 357 358# Check, rowcount must be 1 359- name: postgresql_owner - check that table owner has been changed after the previous step 360 become_user: "{{ pg_user }}" 361 become: yes 362 postgresql_query: 363 db: acme 364 login_user: "{{ pg_user }}" 365 query: "SELECT 1 FROM pg_tables WHERE tablename = 'my_table' AND tableowner = 'bob'" 366 ignore_errors: yes 367 register: result 368 369- assert: 370 that: 371 - result.rowcount == 1 372 373# true mode obj_type: table again 374- name: postgresql_owner - set db owner again 375 become_user: "{{ pg_user }}" 376 become: yes 377 postgresql_owner: 378 login_user: "{{ pg_user }}" 379 db: acme 380 new_owner: bob 381 obj_name: my_table 382 obj_type: table 383 register: result 384 385- assert: 386 that: 387 - result is not changed 388 - result.queries == [] 389 390# Check, rowcount must be 1 391- name: postgresql_owner - check that table owner is bob 392 become_user: "{{ pg_user }}" 393 become: yes 394 postgresql_query: 395 db: acme 396 login_user: "{{ pg_user }}" 397 query: "SELECT 1 FROM pg_tables WHERE tablename = 'my_table' AND tableowner = 'bob'" 398 ignore_errors: yes 399 register: result 400 401- assert: 402 that: 403 - result.rowcount == 1 404 405# ############################# 406# check_mode obj_type: sequence 407- name: postgresql_owner - set sequence owner in check_mode 408 become_user: "{{ pg_user }}" 409 become: yes 410 postgresql_owner: 411 login_user: "{{ pg_user }}" 412 db: acme 413 new_owner: bob 414 obj_name: test_seq 415 obj_type: sequence 416 check_mode: yes 417 register: result 418 419- assert: 420 that: 421 - result is changed 422 - result.queries == ['ALTER SEQUENCE "test_seq" OWNER TO "bob"'] 423 424# Check, rowcount must be 0 425- name: postgresql_owner - check that nothing changed after the previous step 426 become_user: "{{ pg_user }}" 427 become: yes 428 postgresql_query: 429 db: acme 430 login_user: "{{ pg_user }}" 431 query: "SELECT 1 FROM pg_class AS c JOIN pg_roles AS r ON c.relowner = r.oid WHERE c.relkind = 'S' AND c.relname = 'test_seq' AND r.rolname = 'bob'" 432 ignore_errors: yes 433 register: result 434 435- assert: 436 that: 437 - result.rowcount == 0 438 439# true mode obj_type: sequence 440- name: postgresql_owner - set db owner 441 become_user: "{{ pg_user }}" 442 become: yes 443 postgresql_owner: 444 login_user: "{{ pg_user }}" 445 db: acme 446 new_owner: bob 447 obj_name: test_seq 448 obj_type: sequence 449 register: result 450 451- assert: 452 that: 453 - result is changed 454 - result.queries == ['ALTER SEQUENCE "test_seq" OWNER TO "bob"'] 455 456# Check, rowcount must be 1 457- name: postgresql_owner - check that table owner has been changed after the previous step 458 become_user: "{{ pg_user }}" 459 become: yes 460 postgresql_query: 461 db: acme 462 login_user: "{{ pg_user }}" 463 query: "SELECT 1 FROM pg_class AS c JOIN pg_roles AS r ON c.relowner = r.oid WHERE c.relkind = 'S' AND c.relname = 'test_seq' AND r.rolname = 'bob'" 464 ignore_errors: yes 465 register: result 466 467- assert: 468 that: 469 - result.rowcount == 1 470 471# true mode obj_type: table again 472- name: postgresql_owner - set db owner again 473 become_user: "{{ pg_user }}" 474 become: yes 475 postgresql_owner: 476 login_user: "{{ pg_user }}" 477 db: acme 478 new_owner: bob 479 obj_name: test_seq 480 obj_type: sequence 481 register: result 482 483- assert: 484 that: 485 - result is not changed 486 - result.queries == [] 487 488# Check, rowcount must be 1 489- name: postgresql_owner - check that sequence owner is bob 490 become_user: "{{ pg_user }}" 491 become: yes 492 postgresql_query: 493 db: acme 494 login_user: "{{ pg_user }}" 495 query: "SELECT 1 FROM pg_class AS c JOIN pg_roles AS r ON c.relowner = r.oid WHERE c.relkind = 'S' AND c.relname = 'test_seq' AND r.rolname = 'bob'" 496 497 ignore_errors: yes 498 register: result 499 500- assert: 501 that: 502 - result.rowcount == 1 503 504# ############################# 505# check_mode obj_type: function 506- name: postgresql_owner - set function owner in check_mode 507 become_user: "{{ pg_user }}" 508 become: yes 509 postgresql_owner: 510 login_user: "{{ pg_user }}" 511 db: acme 512 new_owner: bob 513 obj_name: increment 514 obj_type: function 515 check_mode: yes 516 register: result 517 when: postgres_version_resp.stdout is version('10', '>=') 518 519- assert: 520 that: 521 - result is changed 522 - result.queries == ['ALTER FUNCTION increment OWNER TO "bob"'] 523 when: postgres_version_resp.stdout is version('10', '>=') 524 525# Check, rowcount must be 0 526- name: postgresql_owner - check that nothing changed after the previous step 527 become_user: "{{ pg_user }}" 528 become: yes 529 postgresql_query: 530 db: acme 531 login_user: "{{ pg_user }}" 532 query: "SELECT 1 FROM pg_proc AS f JOIN pg_roles AS r ON f.proowner = r.oid WHERE f.proname = 'increment' AND r.rolname = 'bob'" 533 ignore_errors: yes 534 register: result 535 when: postgres_version_resp.stdout is version('10', '>=') 536 537- assert: 538 that: 539 - result.rowcount == 0 540 when: postgres_version_resp.stdout is version('10', '>=') 541 542# true mode obj_type: function 543- name: postgresql_owner - set func owner 544 become_user: "{{ pg_user }}" 545 become: yes 546 postgresql_owner: 547 login_user: "{{ pg_user }}" 548 db: acme 549 new_owner: bob 550 obj_name: increment 551 obj_type: function 552 register: result 553 when: postgres_version_resp.stdout is version('10', '>=') 554 555- assert: 556 that: 557 - result is changed 558 - result.queries == ['ALTER FUNCTION increment OWNER TO "bob"'] 559 when: postgres_version_resp.stdout is version('10', '>=') 560 561# Check, rowcount must be 1 562- name: postgresql_owner - check that func owner has been changed after the previous step 563 become_user: "{{ pg_user }}" 564 become: yes 565 postgresql_query: 566 db: acme 567 login_user: "{{ pg_user }}" 568 query: "SELECT 1 FROM pg_proc AS f JOIN pg_roles AS r ON f.proowner = r.oid WHERE f.proname = 'increment' AND r.rolname = 'bob'" 569 ignore_errors: yes 570 register: result 571 when: postgres_version_resp.stdout is version('10', '>=') 572 573- assert: 574 that: 575 - result.rowcount == 1 576 when: postgres_version_resp.stdout is version('10', '>=') 577 578# true mode obj_type: function again 579- name: postgresql_owner - set func owner again 580 become_user: "{{ pg_user }}" 581 become: yes 582 postgresql_owner: 583 login_user: "{{ pg_user }}" 584 db: acme 585 new_owner: bob 586 obj_name: increment 587 obj_type: function 588 register: result 589 when: postgres_version_resp.stdout is version('10', '>=') 590 591- assert: 592 that: 593 - result is not changed 594 - result.queries == [] 595 when: postgres_version_resp.stdout is version('10', '>=') 596 597# Check, rowcount must be 1 598- name: postgresql_owner - check that function owner is bob 599 become_user: "{{ pg_user }}" 600 become: yes 601 postgresql_query: 602 db: acme 603 login_user: "{{ pg_user }}" 604 query: "SELECT 1 FROM pg_proc AS f JOIN pg_roles AS r ON f.proowner = r.oid WHERE f.proname = 'increment' AND r.rolname = 'bob'" 605 ignore_errors: yes 606 register: result 607 when: postgres_version_resp.stdout is version('10', '>=') 608 609- assert: 610 that: 611 - result.rowcount == 1 612 when: postgres_version_resp.stdout is version('10', '>=') 613 614# ########################### 615# check_mode obj_type: schema 616- name: postgresql_owner - set schema owner in check_mode 617 become_user: "{{ pg_user }}" 618 become: yes 619 postgresql_owner: 620 login_user: "{{ pg_user }}" 621 db: acme 622 new_owner: bob 623 obj_name: test_schema 624 obj_type: schema 625 check_mode: yes 626 register: result 627 628- assert: 629 that: 630 - result is changed 631 - result.queries == ['ALTER SCHEMA "test_schema" OWNER TO "bob"'] 632 633# Check, rowcount must be 0 634- name: postgresql_owner - check that nothing changed after the previous step 635 become_user: "{{ pg_user }}" 636 become: yes 637 postgresql_query: 638 db: acme 639 login_user: "{{ pg_user }}" 640 query: "SELECT 1 FROM information_schema.schemata WHERE schema_name = 'test_schema' AND schema_owner = 'bob'" 641 ignore_errors: yes 642 register: result 643 644- assert: 645 that: 646 - result.rowcount == 0 647 648# true mode obj_type: schema 649- name: postgresql_owner - set schema owner 650 become_user: "{{ pg_user }}" 651 become: yes 652 postgresql_owner: 653 login_user: "{{ pg_user }}" 654 db: acme 655 new_owner: bob 656 obj_name: test_schema 657 obj_type: schema 658 register: result 659 660- assert: 661 that: 662 - result is changed 663 - result.queries == ['ALTER SCHEMA "test_schema" OWNER TO "bob"'] 664 665# Check, rowcount must be 1 666- name: postgresql_owner - check that schema owner has been changed after the previous step 667 become_user: "{{ pg_user }}" 668 become: yes 669 postgresql_query: 670 db: acme 671 login_user: "{{ pg_user }}" 672 query: "SELECT 1 FROM information_schema.schemata WHERE schema_name = 'test_schema' AND schema_owner = 'bob'" 673 ignore_errors: yes 674 register: result 675 676- assert: 677 that: 678 - result.rowcount == 1 679 680# true mode obj_type: schema again 681- name: postgresql_owner - set schema owner again 682 become_user: "{{ pg_user }}" 683 become: yes 684 postgresql_owner: 685 login_user: "{{ pg_user }}" 686 db: acme 687 new_owner: bob 688 obj_name: test_seq 689 obj_type: sequence 690 register: result 691 692- assert: 693 that: 694 - result is not changed 695 - result.queries == [] 696 697# Check, rowcount must be 1 698- name: postgresql_owner - check that schema owner is bob 699 become_user: "{{ pg_user }}" 700 become: yes 701 postgresql_query: 702 db: acme 703 login_user: "{{ pg_user }}" 704 query: "SELECT 1 FROM information_schema.schemata WHERE schema_name = 'test_schema' AND schema_owner = 'bob'" 705 ignore_errors: yes 706 register: result 707 708- assert: 709 that: 710 - result.rowcount == 1 711 712# ########################### 713# check_mode obj_type: view 714- name: postgresql_owner - set view owner in check_mode 715 become_user: "{{ pg_user }}" 716 become: yes 717 postgresql_owner: 718 login_user: "{{ pg_user }}" 719 db: acme 720 new_owner: bob 721 obj_name: test_view 722 obj_type: view 723 check_mode: yes 724 register: result 725 726- assert: 727 that: 728 - result is changed 729 - result.queries == ['ALTER VIEW "test_view" OWNER TO "bob"'] 730 731# Check, rowcount must be 0 732- name: postgresql_owner - check that nothing changed after the previous step 733 become_user: "{{ pg_user }}" 734 become: yes 735 postgresql_query: 736 db: acme 737 login_user: "{{ pg_user }}" 738 query: "SELECT 1 FROM pg_views WHERE viewname = 'test_view' AND viewowner = 'bob'" 739 ignore_errors: yes 740 register: result 741 742- assert: 743 that: 744 - result.rowcount == 0 745 746# true mode obj_type: view 747- name: postgresql_owner - set view owner 748 become_user: "{{ pg_user }}" 749 become: yes 750 postgresql_owner: 751 login_user: "{{ pg_user }}" 752 db: acme 753 new_owner: bob 754 obj_name: test_view 755 obj_type: view 756 register: result 757 758- assert: 759 that: 760 - result is changed 761 - result.queries == ['ALTER VIEW "test_view" OWNER TO "bob"'] 762 763# Check, rowcount must be 1 764- name: postgresql_owner - check that view owner has been changed after the previous step 765 become_user: "{{ pg_user }}" 766 become: yes 767 postgresql_query: 768 db: acme 769 login_user: "{{ pg_user }}" 770 query: "SELECT 1 FROM pg_views WHERE viewname = 'test_view' AND viewowner = 'bob'" 771 ignore_errors: yes 772 register: result 773 774- assert: 775 that: 776 - result.rowcount == 1 777 778# true mode obj_type: view again 779- name: postgresql_owner - set view owner again 780 become_user: "{{ pg_user }}" 781 become: yes 782 postgresql_owner: 783 login_user: "{{ pg_user }}" 784 db: acme 785 new_owner: bob 786 obj_name: test_view 787 obj_type: view 788 register: result 789 790- assert: 791 that: 792 - result is not changed 793 - result.queries == [] 794 795# Check, rowcount must be 1 796- name: postgresql_owner - check that view owner is bob 797 become_user: "{{ pg_user }}" 798 become: yes 799 postgresql_query: 800 db: acme 801 login_user: "{{ pg_user }}" 802 query: "SELECT 1 FROM pg_views WHERE viewname = 'test_view' AND viewowner = 'bob'" 803 ignore_errors: yes 804 register: result 805 806- assert: 807 that: 808 - result.rowcount == 1 809 810# ########################### 811# check_mode obj_type: matview 812- name: postgresql_owner - set matview owner in check_mode 813 become_user: "{{ pg_user }}" 814 become: yes 815 postgresql_owner: 816 login_user: "{{ pg_user }}" 817 db: acme 818 new_owner: bob 819 obj_name: test_mat_view 820 obj_type: matview 821 check_mode: yes 822 register: result 823 when: postgres_version_resp.stdout is version('9.4', '>=') 824 825- assert: 826 that: 827 - result is changed 828 - result.queries == ['ALTER MATERIALIZED VIEW "test_mat_view" OWNER TO "bob"'] 829 when: postgres_version_resp.stdout is version('9.4', '>=') 830 831# Check, rowcount must be 0 832- name: postgresql_owner - check that nothing changed after the previous step 833 become_user: "{{ pg_user }}" 834 become: yes 835 postgresql_query: 836 db: acme 837 login_user: "{{ pg_user }}" 838 query: "SELECT 1 FROM pg_matviews WHERE matviewname = 'test_view' AND matviewowner = 'bob'" 839 ignore_errors: yes 840 register: result 841 when: postgres_version_resp.stdout is version('9.4', '>=') 842 843- assert: 844 that: 845 - result.rowcount == 0 846 when: postgres_version_resp.stdout is version('9.4', '>=') 847 848# true mode obj_type: matview 849- name: postgresql_owner - set matview owner 850 become_user: "{{ pg_user }}" 851 become: yes 852 postgresql_owner: 853 login_user: "{{ pg_user }}" 854 db: acme 855 new_owner: bob 856 obj_name: test_mat_view 857 obj_type: matview 858 register: result 859 when: postgres_version_resp.stdout is version('9.4', '>=') 860 861- assert: 862 that: 863 - result is changed 864 - result.queries == ['ALTER MATERIALIZED VIEW "test_mat_view" OWNER TO "bob"'] 865 when: postgres_version_resp.stdout is version('9.4', '>=') 866 867# Check, rowcount must be 1 868- name: postgresql_owner - check that matview owner has been changed after the previous step 869 become_user: "{{ pg_user }}" 870 become: yes 871 postgresql_query: 872 db: acme 873 login_user: "{{ pg_user }}" 874 query: "SELECT 1 FROM pg_matviews WHERE matviewname = 'test_mat_view' AND matviewowner = 'bob'" 875 ignore_errors: yes 876 register: result 877 when: postgres_version_resp.stdout is version('9.4', '>=') 878 879- assert: 880 that: 881 - result.rowcount == 1 882 when: postgres_version_resp.stdout is version('9.4', '>=') 883 884# true mode obj_type: matview again 885- name: postgresql_owner - set matview owner again 886 become_user: "{{ pg_user }}" 887 become: yes 888 postgresql_owner: 889 login_user: "{{ pg_user }}" 890 db: acme 891 new_owner: bob 892 obj_name: test_mat_view 893 obj_type: matview 894 register: result 895 when: postgres_version_resp.stdout is version('9.4', '>=') 896 897- assert: 898 that: 899 - result is not changed 900 - result.queries == [] 901 when: postgres_version_resp.stdout is version('9.4', '>=') 902 903# Check, rowcount must be 1 904- name: postgresql_owner - check that matview owner is bob 905 become_user: "{{ pg_user }}" 906 become: yes 907 postgresql_query: 908 db: acme 909 login_user: "{{ pg_user }}" 910 query: "SELECT 1 FROM pg_matviews WHERE matviewname = 'test_mat_view' AND matviewowner = 'bob'" 911 ignore_errors: yes 912 register: result 913 when: postgres_version_resp.stdout is version('9.4', '>=') 914 915- assert: 916 that: 917 - result.rowcount == 1 918 when: postgres_version_resp.stdout is version('9.4', '>=') 919 920# ########################### 921# check_mode obj_type: tablespace 922- name: postgresql_owner - set tablespace owner in check_mode 923 become_user: "{{ pg_user }}" 924 become: yes 925 postgresql_owner: 926 login_user: "{{ pg_user }}" 927 db: acme 928 new_owner: bob 929 obj_name: acme 930 obj_type: tablespace 931 check_mode: yes 932 register: result 933 934- assert: 935 that: 936 - result is changed 937 - result.queries == ['ALTER TABLESPACE "acme" OWNER TO "bob"'] 938 939# Check, rowcount must be 0 940- name: postgresql_owner - check that nothing changed after the previous step 941 become_user: "{{ pg_user }}" 942 become: yes 943 postgresql_query: 944 db: acme 945 login_user: "{{ pg_user }}" 946 query: "SELECT 1 FROM pg_tablespace AS t JOIN pg_roles AS r ON t.spcowner = r.oid WHERE t.spcname = 'acme' AND r.rolname = 'bob'" 947 ignore_errors: yes 948 register: result 949 950- assert: 951 that: 952 - result.rowcount == 0 953 954# true mode obj_type: tablespace 955- name: postgresql_owner - set tablespace owner 956 become_user: "{{ pg_user }}" 957 become: yes 958 postgresql_owner: 959 login_user: "{{ pg_user }}" 960 db: acme 961 new_owner: bob 962 obj_name: acme 963 obj_type: tablespace 964 register: result 965 966- assert: 967 that: 968 - result is changed 969 - result.queries == ['ALTER TABLESPACE "acme" OWNER TO "bob"'] 970 971# Check, rowcount must be 1 972- name: postgresql_owner - check that tablespace owner has been changed after the previous step 973 become_user: "{{ pg_user }}" 974 become: yes 975 postgresql_query: 976 db: acme 977 login_user: "{{ pg_user }}" 978 query: "SELECT 1 FROM pg_tablespace AS t JOIN pg_roles AS r ON t.spcowner = r.oid WHERE t.spcname = 'acme' AND r.rolname = 'bob'" 979 ignore_errors: yes 980 register: result 981 982- assert: 983 that: 984 - result.rowcount == 1 985 986# true mode obj_type: tablespace again 987- name: postgresql_owner - set tablespace owner again 988 become_user: "{{ pg_user }}" 989 become: yes 990 postgresql_owner: 991 login_user: "{{ pg_user }}" 992 db: acme 993 new_owner: bob 994 obj_name: acme 995 obj_type: tablespace 996 register: result 997 998- assert: 999 that: 1000 - result is not changed 1001 - result.queries == [] 1002 1003# Check, rowcount must be 1 1004- name: postgresql_owner - check that tablespace owner is bob 1005 become_user: "{{ pg_user }}" 1006 become: yes 1007 postgresql_query: 1008 db: acme 1009 login_user: "{{ pg_user }}" 1010 query: "SELECT 1 FROM pg_tablespace AS t JOIN pg_roles AS r ON t.spcowner = r.oid WHERE t.spcname = 'acme' AND r.rolname = 'bob'" 1011 ignore_errors: yes 1012 register: result 1013 1014- assert: 1015 that: 1016 - result.rowcount == 1 1017 1018# 1019# Crean up 1020# 1021 1022# Drop test database: 1023- name: postgresql_owner - create test database 1024 become_user: "{{ pg_user }}" 1025 become: yes 1026 postgresql_db: 1027 login_user: "{{ pg_user }}" 1028 db: acme 1029 state: absent 1030 1031# Drop test tablespace: 1032- name: postgresql_owner - drop test tablespace 1033 become_user: "{{ pg_user }}" 1034 become: yes 1035 postgresql_tablespace: 1036 db: postgres 1037 login_user: "{{ pg_user }}" 1038 name: acme 1039 state: absent 1040