1# 2# Create and destroy db 3# 4- name: Create DB 5 become_user: "{{ pg_user }}" 6 become: yes 7 postgresql_db: 8 state: present 9 name: "{{ db_name }}" 10 login_user: "{{ pg_user }}" 11 register: result 12 13- name: assert that module reports the db was created 14 assert: 15 that: 16 - result is changed 17 - "result.db == db_name" 18 19- name: Check that database created 20 become_user: "{{ pg_user }}" 21 become: yes 22 shell: echo "select datname from pg_database where datname = '{{ db_name }}';" | psql -d postgres 23 register: result 24 25- assert: 26 that: 27 - "result.stdout_lines[-1] == '(1 row)'" 28 29- name: Run create on an already created db 30 become_user: "{{ pg_user }}" 31 become: yes 32 postgresql_db: 33 state: present 34 name: "{{ db_name }}" 35 login_user: "{{ pg_user }}" 36 register: result 37 38- name: assert that module reports the db was unchanged 39 assert: 40 that: 41 - result is not changed 42 43- name: Destroy DB 44 become_user: "{{ pg_user }}" 45 become: yes 46 postgresql_db: 47 state: absent 48 name: "{{ db_name }}" 49 login_user: "{{ pg_user }}" 50 register: result 51 52- name: assert that module reports the db was changed 53 assert: 54 that: 55 - result is changed 56 57- name: Check that database was destroyed 58 become_user: "{{ pg_user }}" 59 become: yes 60 shell: echo "select datname from pg_database where datname = '{{ db_name }}';" | psql -d postgres 61 register: result 62 63- assert: 64 that: 65 - "result.stdout_lines[-1] == '(0 rows)'" 66 67- name: Destroy DB 68 become_user: "{{ pg_user }}" 69 become: yes 70 postgresql_db: 71 state: absent 72 name: "{{ db_name }}" 73 login_user: "{{ pg_user }}" 74 register: result 75 76- name: assert that removing an already removed db makes no change 77 assert: 78 that: 79 - result is not changed 80 81 82# This corner case works to add but not to drop. This is sufficiently crazy 83# that I'm not going to attempt to fix it unless someone lets me know that they 84# need the functionality 85# 86# - postgresql_db: 87# state: 'present' 88# name: '"silly.""name"' 89# - shell: echo "select datname from pg_database where datname = 'silly.""name';" | psql 90# register: result 91# 92# - assert: 93# that: "result.stdout_lines[-1] == '(1 row)'" 94# - postgresql_db: 95# state: absent 96# name: '"silly.""name"' 97# - shell: echo "select datname from pg_database where datname = 'silly.""name';" | psql 98# register: result 99# 100# - assert: 101# that: "result.stdout_lines[-1] == '(0 rows)'" 102 103# 104# Test conn_limit, encoding, collate, ctype, template options 105# 106- name: Create a DB with conn_limit, encoding, collate, ctype, and template options 107 become_user: "{{ pg_user }}" 108 become: yes 109 postgresql_db: 110 name: '{{ db_name }}' 111 state: 'present' 112 conn_limit: '100' 113 encoding: 'LATIN1' 114 lc_collate: 'pt_BR{{ locale_latin_suffix }}' 115 lc_ctype: 'es_ES{{ locale_latin_suffix }}' 116 template: 'template0' 117 login_user: "{{ pg_user }}" 118 119- name: Check that the DB has all of our options 120 become_user: "{{ pg_user }}" 121 become: yes 122 shell: echo "select datname, datconnlimit, pg_encoding_to_char(encoding), datcollate, datctype from pg_database where datname = '{{ db_name }}';" | psql -d postgres 123 register: result 124 125- assert: 126 that: 127 - "result.stdout_lines[-1] == '(1 row)'" 128 - "'LATIN1' in result.stdout_lines[-2]" 129 - "'pt_BR' in result.stdout_lines[-2]" 130 - "'es_ES' in result.stdout_lines[-2]" 131 - "'UTF8' not in result.stdout_lines[-2]" 132 - "'en_US' not in result.stdout_lines[-2]" 133 - "'100' in result.stdout_lines[-2]" 134 135- name: Check that running db creation with options a second time does nothing 136 become_user: "{{ pg_user }}" 137 become: yes 138 postgresql_db: 139 name: '{{ db_name }}' 140 state: 'present' 141 conn_limit: '100' 142 encoding: 'LATIN1' 143 lc_collate: 'pt_BR{{ locale_latin_suffix }}' 144 lc_ctype: 'es_ES{{ locale_latin_suffix }}' 145 template: 'template0' 146 login_user: "{{ pg_user }}" 147 register: result 148 149- assert: 150 that: 151 - result is not changed 152 153 154- name: Check that attempting to change encoding returns an error 155 become_user: "{{ pg_user }}" 156 become: yes 157 postgresql_db: 158 name: '{{ db_name }}' 159 state: 'present' 160 encoding: 'UTF8' 161 lc_collate: 'pt_BR{{ locale_utf8_suffix }}' 162 lc_ctype: 'es_ES{{ locale_utf8_suffix }}' 163 template: 'template0' 164 login_user: "{{ pg_user }}" 165 register: result 166 ignore_errors: yes 167 168- assert: 169 that: 170 - result is failed 171 172- name: Check that changing the conn_limit actually works 173 become_user: "{{ pg_user }}" 174 become: yes 175 postgresql_db: 176 name: '{{ db_name }}' 177 state: 'present' 178 conn_limit: '200' 179 encoding: 'LATIN1' 180 lc_collate: 'pt_BR{{ locale_latin_suffix }}' 181 lc_ctype: 'es_ES{{ locale_latin_suffix }}' 182 template: 'template0' 183 login_user: "{{ pg_user }}" 184 register: result 185 186- assert: 187 that: 188 - result is changed 189 190- name: Check that conn_limit has actually been set / updated to 200 191 become_user: "{{ pg_user }}" 192 become: yes 193 shell: echo "SELECT datconnlimit AS conn_limit FROM pg_database WHERE datname = '{{ db_name }}';" | psql -d postgres 194 register: result 195 196- assert: 197 that: 198 - "result.stdout_lines[-1] == '(1 row)'" 199 - "'200' == '{{ result.stdout_lines[-2] | trim }}'" 200 201- name: Cleanup test DB 202 become_user: "{{ pg_user }}" 203 become: yes 204 postgresql_db: 205 name: '{{ db_name }}' 206 state: 'absent' 207 login_user: "{{ pg_user }}" 208 209- shell: echo "select datname, pg_encoding_to_char(encoding), datcollate, datctype from pg_database where datname = '{{ db_name }}';" | psql -d postgres 210 become_user: "{{ pg_user }}" 211 become: yes 212 register: result 213 214- assert: 215 that: 216 - "result.stdout_lines[-1] == '(0 rows)'" 217 218# 219# Create and destroy user, test 'password' and 'encrypted' parameters 220# 221# unencrypted values are not supported on newer versions 222# do not run the encrypted: no tests if on 10+ 223- set_fact: 224 encryption_values: 225 - 'yes' 226 227- set_fact: 228 encryption_values: '{{ encryption_values + ["no"]}}' 229 when: postgres_version_resp.stdout is version('10', '<=') 230 231- include_tasks: test_password.yml 232 vars: 233 encrypted: '{{ loop_item }}' 234 db_password1: 'secretù' # use UTF-8 235 loop: '{{ encryption_values }}' 236 loop_control: 237 loop_var: loop_item 238 239# BYPASSRLS role attribute was introduced in PostgreSQL 9.5, so 240# we want to test attribute management differently depending 241# on the version. 242- set_fact: 243 bypassrls_supported: "{{ postgres_version_resp.stdout is version('9.5.0', '>=') }}" 244 245# test 'no_password_change' and 'role_attr_flags' parameters 246- include_tasks: test_no_password_change.yml 247 vars: 248 no_password_changes: '{{ loop_item }}' 249 loop: 250 - 'yes' 251 - 'no' 252 loop_control: 253 loop_var: loop_item 254 255### TODO: fail_on_user 256 257# 258# Test db ownership 259# 260- name: Create an unprivileged user to own a DB 261 become_user: "{{ pg_user }}" 262 become: yes 263 postgresql_user: 264 name: "{{ db_user1 }}" 265 encrypted: 'yes' 266 password: "md55c8ccfd9d6711fc69a7eae647fc54f51" 267 login_user: "{{ pg_user }}" 268 db: postgres 269 270- name: Create db with user ownership 271 become_user: "{{ pg_user }}" 272 become: yes 273 postgresql_db: 274 name: "{{ db_name }}" 275 state: "present" 276 owner: "{{ db_user1 }}" 277 login_user: "{{ pg_user }}" 278 279- name: Check that the user owns the newly created DB 280 become_user: "{{ pg_user }}" 281 become: yes 282 shell: echo "select pg_catalog.pg_get_userbyid(datdba) from pg_catalog.pg_database where datname = '{{ db_name }}';" | psql -d postgres 283 register: result 284 285- assert: 286 that: 287 - "result.stdout_lines[-1] == '(1 row)'" 288 - "'{{ db_user1 }}' == '{{ result.stdout_lines[-2] | trim }}'" 289 290- name: Change the owner on an existing db 291 become_user: "{{ pg_user }}" 292 become: yes 293 postgresql_db: 294 name: "{{ db_name }}" 295 state: "present" 296 owner: "{{ pg_user }}" 297 login_user: "{{ pg_user }}" 298 register: result 299 300- name: assert that ansible says it changed the db 301 assert: 302 that: 303 - result is changed 304 305- name: Check that the user owns the newly created DB 306 become_user: "{{ pg_user }}" 307 become: yes 308 shell: echo "select pg_catalog.pg_get_userbyid(datdba) from pg_catalog.pg_database where datname = '{{ db_name }}';" | psql -d postgres 309 register: result 310 311- assert: 312 that: 313 - "result.stdout_lines[-1] == '(1 row)'" 314 - "'{{ pg_user }}' == '{{ result.stdout_lines[-2] | trim }}'" 315 316- name: Cleanup db 317 become_user: "{{ pg_user }}" 318 become: yes 319 postgresql_db: 320 name: "{{ db_name }}" 321 state: "absent" 322 login_user: "{{ pg_user }}" 323 324- name: Check that database was destroyed 325 become_user: "{{ pg_user }}" 326 become: yes 327 shell: echo "select datname from pg_database where datname = '{{ db_name }}';" | psql -d postgres 328 register: result 329 330- assert: 331 that: 332 - "result.stdout_lines[-1] == '(0 rows)'" 333 334- name: Cleanup test user 335 become_user: "{{ pg_user }}" 336 become: yes 337 postgresql_user: 338 name: "{{ db_user1 }}" 339 state: 'absent' 340 login_user: "{{ pg_user }}" 341 db: postgres 342 343- name: Check that they were removed 344 become_user: "{{ pg_user }}" 345 become: yes 346 shell: echo "select * from pg_user where usename='{{ db_user1 }}';" | psql -d postgres 347 register: result 348 349- assert: 350 that: 351 - "result.stdout_lines[-1] == '(0 rows)'" 352 353# 354# Test settings privileges 355# 356- name: Create db 357 become_user: "{{ pg_user }}" 358 become: yes 359 postgresql_db: 360 name: "{{ db_name }}" 361 state: "present" 362 login_user: "{{ pg_user }}" 363 364- name: Create some tables on the db 365 become_user: "{{ pg_user }}" 366 become: yes 367 shell: echo "create table test_table1 (field text);" | psql {{ db_name }} 368 369- become_user: "{{ pg_user }}" 370 become: yes 371 shell: echo "create table test_table2 (field text);" | psql {{ db_name }} 372 373- vars: 374 db_password: 'secretù' # use UTF-8 375 block: 376 - name: Create a user with some permissions on the db 377 become_user: "{{ pg_user }}" 378 become: yes 379 postgresql_user: 380 name: "{{ db_user1 }}" 381 encrypted: 'yes' 382 password: "md5{{ (db_password ~ db_user1) | hash('md5')}}" 383 db: "{{ db_name }}" 384 priv: 'test_table1:INSERT,SELECT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER/test_table2:INSERT/CREATE,CONNECT,TEMP' 385 login_user: "{{ pg_user }}" 386 387 - include_tasks: pg_authid_not_readable.yml 388 389- name: Check that the user has the requested permissions (table1) 390 become_user: "{{ pg_user }}" 391 become: yes 392 shell: echo "select privilege_type from information_schema.role_table_grants where grantee='{{ db_user1 }}' and table_name='test_table1';" | psql {{ db_name }} 393 register: result_table1 394 395- name: Check that the user has the requested permissions (table2) 396 become_user: "{{ pg_user }}" 397 become: yes 398 shell: echo "select privilege_type from information_schema.role_table_grants where grantee='{{ db_user1 }}' and table_name='test_table2';" | psql {{ db_name }} 399 register: result_table2 400 401- name: Check that the user has the requested permissions (database) 402 become_user: "{{ pg_user }}" 403 become: yes 404 shell: echo "select datacl from pg_database where datname='{{ db_name }}';" | psql {{ db_name }} 405 register: result_database 406 407- assert: 408 that: 409 - "result_table1.stdout_lines[-1] == '(7 rows)'" 410 - "'INSERT' in result_table1.stdout" 411 - "'SELECT' in result_table1.stdout" 412 - "'UPDATE' in result_table1.stdout" 413 - "'DELETE' in result_table1.stdout" 414 - "'TRUNCATE' in result_table1.stdout" 415 - "'REFERENCES' in result_table1.stdout" 416 - "'TRIGGER' in result_table1.stdout" 417 - "result_table2.stdout_lines[-1] == '(1 row)'" 418 - "'INSERT' == '{{ result_table2.stdout_lines[-2] | trim }}'" 419 - "result_database.stdout_lines[-1] == '(1 row)'" 420 - "'{{ db_user1 }}=CTc/{{ pg_user }}' in result_database.stdout_lines[-2]" 421 422- name: Add another permission for the user 423 become_user: "{{ pg_user }}" 424 become: yes 425 postgresql_user: 426 name: "{{ db_user1 }}" 427 encrypted: 'yes' 428 password: "md55c8ccfd9d6711fc69a7eae647fc54f51" 429 db: "{{ db_name }}" 430 priv: 'test_table2:select' 431 login_user: "{{ pg_user }}" 432 register: result 433 434- name: Check that ansible reports it changed the user 435 assert: 436 that: 437 - result is changed 438 439- name: Check that the user has the requested permissions (table2) 440 become_user: "{{ pg_user }}" 441 become: yes 442 shell: echo "select privilege_type from information_schema.role_table_grants where grantee='{{ db_user1 }}' and table_name='test_table2';" | psql {{ db_name }} 443 register: result_table2 444 445- assert: 446 that: 447 - "result_table2.stdout_lines[-1] == '(2 rows)'" 448 - "'INSERT' in result_table2.stdout" 449 - "'SELECT' in result_table2.stdout" 450 451 452# 453# Test priv setting via postgresql_privs module 454# (Depends on state from previous _user privs tests) 455# 456 457- name: Revoke a privilege 458 become_user: "{{ pg_user }}" 459 become: yes 460 postgresql_privs: 461 type: "table" 462 state: "absent" 463 roles: "{{ db_user1 }}" 464 privs: "INSERT" 465 objs: "test_table2" 466 db: "{{ db_name }}" 467 login_user: "{{ pg_user }}" 468 register: result 469 470- name: Check that ansible reports it changed the user 471 assert: 472 that: 473 - result is changed 474 475- name: Check that the user has the requested permissions (table2) 476 become_user: "{{ pg_user }}" 477 become: yes 478 shell: echo "select privilege_type from information_schema.role_table_grants where grantee='{{ db_user1 }}' and table_name='test_table2';" | psql {{ db_name }} 479 register: result_table2 480 481- assert: 482 that: 483 - "result_table2.stdout_lines[-1] == '(1 row)'" 484 - "'SELECT' == '{{ result_table2.stdout_lines[-2] | trim }}'" 485 486- name: Revoke many privileges on multiple tables 487 become_user: "{{ pg_user }}" 488 become: yes 489 postgresql_privs: 490 state: "absent" 491 roles: "{{ db_user1 }}" 492 privs: "INSERT,select,UPDATE,TRUNCATE,REFERENCES,TRIGGER,delete" 493 objs: "test_table2,test_table1" 494 db: "{{ db_name }}" 495 login_user: "{{ pg_user }}" 496 register: result 497 498- name: Check that ansible reports it changed the user 499 assert: 500 that: 501 - result is changed 502 503- name: Check that permissions were revoked (table1) 504 become_user: "{{ pg_user }}" 505 become: yes 506 shell: echo "select privilege_type from information_schema.role_table_grants where grantee='{{ db_user1 }}' and table_name='test_table1';" | psql {{ db_name }} 507 register: result_table1 508 509- name: Check that permissions were revoked (table2) 510 become_user: "{{ pg_user }}" 511 become: yes 512 shell: echo "select privilege_type from information_schema.role_table_grants where grantee='{{ db_user1 }}' and table_name='test_table2';" | psql {{ db_name }} 513 register: result_table2 514 515- assert: 516 that: 517 - "result_table1.stdout_lines[-1] == '(0 rows)'" 518 - "result_table2.stdout_lines[-1] == '(0 rows)'" 519 520- name: Revoke database privileges 521 become_user: "{{ pg_user }}" 522 become: yes 523 postgresql_privs: 524 type: "database" 525 state: "absent" 526 roles: "{{ db_user1 }}" 527 privs: "Create,connect,TEMP" 528 objs: "{{ db_name }}" 529 db: "{{ db_name }}" 530 login_user: "{{ pg_user }}" 531 532- name: Check that the user has the requested permissions (database) 533 become_user: "{{ pg_user }}" 534 become: yes 535 shell: echo "select datacl from pg_database where datname='{{ db_name }}';" | psql {{ db_name }} 536 register: result_database 537 538- assert: 539 that: 540 - "result_database.stdout_lines[-1] == '(1 row)'" 541 - "'{{ db_user1 }}' not in result_database.stdout" 542 543- name: Grant database privileges 544 become_user: "{{ pg_user }}" 545 become: yes 546 postgresql_privs: 547 type: "database" 548 state: "present" 549 roles: "{{ db_user1 }}" 550 privs: "CREATE,connect" 551 objs: "{{ db_name }}" 552 db: "{{ db_name }}" 553 login_user: "{{ pg_user }}" 554 register: result 555 556- name: Check that ansible reports it changed the user 557 assert: 558 that: 559 - result is changed 560 561- name: Check that the user has the requested permissions (database) 562 become_user: "{{ pg_user }}" 563 become: yes 564 shell: echo "select datacl from pg_database where datname='{{ db_name }}';" | psql {{ db_name }} 565 register: result_database 566 567- assert: 568 that: 569 - "result_database.stdout_lines[-1] == '(1 row)'" 570 - "'{{ db_user1 }}=Cc' in result_database.stdout" 571 572- name: Grant a single privilege on a table 573 become_user: "{{ pg_user }}" 574 become: yes 575 postgresql_privs: 576 state: "present" 577 roles: "{{ db_user1 }}" 578 privs: "INSERT" 579 objs: "test_table1" 580 db: "{{ db_name }}" 581 login_user: "{{ pg_user }}" 582 583- name: Check that permissions were added (table1) 584 become_user: "{{ pg_user }}" 585 become: yes 586 shell: echo "select privilege_type from information_schema.role_table_grants where grantee='{{ db_user1 }}' and table_name='test_table1';" | psql {{ db_name }} 587 register: result_table1 588 589- assert: 590 that: 591 - "result_table1.stdout_lines[-1] == '(1 row)'" 592 - "'{{ result_table1.stdout_lines[-2] | trim }}' == 'INSERT'" 593 594- name: Grant many privileges on multiple tables 595 become_user: "{{ pg_user }}" 596 become: yes 597 postgresql_privs: 598 state: "present" 599 roles: "{{ db_user1 }}" 600 privs: 'INSERT,SELECT,UPDATE,DELETE,TRUNCATE,REFERENCES,trigger' 601 objs: "test_table2,test_table1" 602 db: "{{ db_name }}" 603 login_user: "{{ pg_user }}" 604 605- name: Check that permissions were added (table1) 606 become_user: "{{ pg_user }}" 607 become: yes 608 shell: echo "select privilege_type from information_schema.role_table_grants where grantee='{{ db_user1 }}' and table_name='test_table1';" | psql {{ db_name }} 609 register: result_table1 610 611- name: Check that permissions were added (table2) 612 become_user: "{{ pg_user }}" 613 become: yes 614 shell: echo "select privilege_type from information_schema.role_table_grants where grantee='{{ db_user1 }}' and table_name='test_table2';" | psql {{ db_name }} 615 register: result_table2 616 617- assert: 618 that: 619 - "result_table1.stdout_lines[-1] == '(7 rows)'" 620 - "'INSERT' in result_table1.stdout" 621 - "'SELECT' in result_table1.stdout" 622 - "'UPDATE' in result_table1.stdout" 623 - "'DELETE' in result_table1.stdout" 624 - "'TRUNCATE' in result_table1.stdout" 625 - "'REFERENCES' in result_table1.stdout" 626 - "'TRIGGER' in result_table1.stdout" 627 - "result_table2.stdout_lines[-1] == '(7 rows)'" 628 - "'INSERT' in result_table2.stdout" 629 - "'SELECT' in result_table2.stdout" 630 - "'UPDATE' in result_table2.stdout" 631 - "'DELETE' in result_table2.stdout" 632 - "'TRUNCATE' in result_table2.stdout" 633 - "'REFERENCES' in result_table2.stdout" 634 - "'TRIGGER' in result_table2.stdout" 635 636# 637# Cleanup 638# 639- name: Cleanup db 640 become_user: "{{ pg_user }}" 641 become: yes 642 postgresql_db: 643 name: "{{ db_name }}" 644 state: "absent" 645 login_user: "{{ pg_user }}" 646 647- name: Check that database was destroyed 648 become_user: "{{ pg_user }}" 649 become: yes 650 shell: echo "select datname from pg_database where datname = '{{ db_name }}';" | psql -d postgres 651 register: result 652 653- assert: 654 that: 655 - "result.stdout_lines[-1] == '(0 rows)'" 656 657- name: Cleanup test user 658 become_user: "{{ pg_user }}" 659 become: yes 660 postgresql_user: 661 name: "{{ db_user1 }}" 662 state: 'absent' 663 login_user: "{{ pg_user }}" 664 db: postgres 665 666- name: Check that they were removed 667 become_user: "{{ pg_user }}" 668 become: yes 669 shell: echo "select * from pg_user where usename='{{ db_user1 }}';" | psql -d postgres 670 register: result 671 672- assert: 673 that: 674 - "result.stdout_lines[-1] == '(0 rows)'" 675 676# 677# Test login_user functionality 678# 679- name: Create a user to test login module parameters 680 become: yes 681 become_user: "{{ pg_user }}" 682 postgresql_user: 683 name: "{{ db_user1 }}" 684 state: "present" 685 encrypted: 'yes' 686 password: "password" 687 role_attr_flags: "CREATEDB,LOGIN,CREATEROLE" 688 login_user: "{{ pg_user }}" 689 db: postgres 690 691- name: Create db 692 postgresql_db: 693 name: "{{ db_name }}" 694 state: "present" 695 login_user: "{{ db_user1 }}" 696 login_password: "password" 697 login_host: "localhost" 698 699- name: Check that database created 700 become: yes 701 become_user: "{{ pg_user }}" 702 shell: echo "select datname from pg_database where datname = '{{ db_name }}';" | psql -d postgres 703 register: result 704 705- assert: 706 that: 707 - "result.stdout_lines[-1] == '(1 row)'" 708 709- name: Create a user 710 postgresql_user: 711 name: "{{ db_user2 }}" 712 state: "present" 713 encrypted: 'yes' 714 password: "md55c8ccfd9d6711fc69a7eae647fc54f51" 715 db: "{{ db_name }}" 716 login_user: "{{ db_user1 }}" 717 login_password: "password" 718 login_host: "localhost" 719 720- name: Check that it was created 721 become: yes 722 become_user: "{{ pg_user }}" 723 shell: echo "select * from pg_user where usename='{{ db_user2 }}';" | psql -d postgres 724 register: result 725 726- assert: 727 that: 728 - "result.stdout_lines[-1] == '(1 row)'" 729 730- name: Grant database privileges 731 postgresql_privs: 732 type: "database" 733 state: "present" 734 roles: "{{ db_user2 }}" 735 privs: "CREATE,connect" 736 objs: "{{ db_name }}" 737 db: "{{ db_name }}" 738 login: "{{ db_user1 }}" 739 password: "password" 740 host: "localhost" 741 742- name: Check that the user has the requested permissions (database) 743 become: yes 744 become_user: "{{ pg_user }}" 745 shell: echo "select datacl from pg_database where datname='{{ db_name }}';" | psql {{ db_name }} 746 register: result_database 747 748- assert: 749 that: 750 - "result_database.stdout_lines[-1] == '(1 row)'" 751 - "db_user2 ~ '=Cc' in result_database.stdout" 752 753- name: Remove user 754 postgresql_user: 755 name: "{{ db_user2 }}" 756 state: 'absent' 757 priv: "ALL" 758 db: "{{ db_name }}" 759 login_user: "{{ db_user1 }}" 760 login_password: "password" 761 login_host: "localhost" 762 763- name: Check that they were removed 764 become: yes 765 become_user: "{{ pg_user }}" 766 shell: echo "select * from pg_user where usename='{{ db_user2 }}';" | psql -d postgres 767 register: result 768 769- assert: 770 that: 771 - "result.stdout_lines[-1] == '(0 rows)'" 772 773- name: Destroy DB 774 postgresql_db: 775 state: absent 776 name: "{{ db_name }}" 777 login_user: "{{ db_user1 }}" 778 login_password: "password" 779 login_host: "localhost" 780 781- name: Check that database was destroyed 782 become: yes 783 become_user: "{{ pg_user }}" 784 shell: echo "select datname from pg_database where datname = '{{ db_name }}';" | psql -d postgres 785 register: result 786 787- assert: 788 that: 789 - "result.stdout_lines[-1] == '(0 rows)'" 790