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# Integration tests for postgresql_user module. 4 5- vars: 6 test_user: hello_user 7 test_group1: group1 8 test_group2: group2 9 test_table: test 10 task_parameters: &task_parameters 11 become_user: '{{ pg_user }}' 12 become: yes 13 register: result 14 pg_parameters: &pg_parameters 15 login_user: '{{ pg_user }}' 16 login_db: postgres 17 18 block: 19 # 20 # Common tests 21 # 22 - name: Create role in check_mode 23 <<: *task_parameters 24 check_mode: yes 25 postgresql_user: 26 <<: *pg_parameters 27 name: '{{ test_user }}' 28 29 - assert: 30 that: 31 - result is changed 32 - result.user == '{{ test_user }}' 33 34 - name: check that the user doesn't exist 35 <<: *task_parameters 36 postgresql_query: 37 <<: *pg_parameters 38 query: "SELECT rolname FROM pg_roles WHERE rolname = '{{ test_user }}'" 39 40 - assert: 41 that: 42 - result.rowcount == 0 43 44 - name: Create role in actual mode 45 <<: *task_parameters 46 postgresql_user: 47 <<: *pg_parameters 48 name: '{{ test_user }}' 49 50 - assert: 51 that: 52 - result is changed 53 - result.user == '{{ test_user }}' 54 55 - name: check that the user exists 56 <<: *task_parameters 57 postgresql_query: 58 <<: *pg_parameters 59 query: "SELECT rolname FROM pg_roles WHERE rolname = '{{ test_user }}'" 60 61 - assert: 62 that: 63 - result.rowcount == 1 64 65 - name: Try to create role again in check_mode 66 <<: *task_parameters 67 check_mode: yes 68 postgresql_user: 69 <<: *pg_parameters 70 name: '{{ test_user }}' 71 72 - assert: 73 that: 74 - result is not changed 75 - result.user == '{{ test_user }}' 76 77 - name: check that the user exists 78 <<: *task_parameters 79 postgresql_query: 80 <<: *pg_parameters 81 query: "SELECT rolname FROM pg_roles WHERE rolname = '{{ test_user }}'" 82 83 - assert: 84 that: 85 - result.rowcount == 1 86 87 - name: Try to create role again 88 <<: *task_parameters 89 postgresql_user: 90 <<: *pg_parameters 91 name: '{{ test_user }}' 92 93 - assert: 94 that: 95 - result is not changed 96 - result.user == '{{ test_user }}' 97 98 - name: check that the user exists 99 <<: *task_parameters 100 postgresql_query: 101 <<: *pg_parameters 102 query: "SELECT rolname FROM pg_roles WHERE rolname = '{{ test_user }}'" 103 104 - assert: 105 that: 106 - result.rowcount == 1 107 108 - name: Drop role in check_mode 109 <<: *task_parameters 110 check_mode: yes 111 postgresql_user: 112 <<: *pg_parameters 113 name: '{{ test_user }}' 114 state: absent 115 116 - assert: 117 that: 118 - result is changed 119 - result.user == '{{ test_user }}' 120 121 - name: check that the user actually exists 122 <<: *task_parameters 123 postgresql_query: 124 <<: *pg_parameters 125 query: "SELECT rolname FROM pg_roles WHERE rolname = '{{ test_user }}'" 126 127 - assert: 128 that: 129 - result.rowcount == 1 130 131 - name: Drop role in actual mode 132 <<: *task_parameters 133 postgresql_user: 134 <<: *pg_parameters 135 name: '{{ test_user }}' 136 state: absent 137 138 - assert: 139 that: 140 - result is changed 141 - result.user == '{{ test_user }}' 142 143 - name: check that the user doesn't exist 144 <<: *task_parameters 145 postgresql_query: 146 <<: *pg_parameters 147 query: "SELECT rolname FROM pg_roles WHERE rolname = '{{ test_user }}'" 148 149 - assert: 150 that: 151 - result.rowcount == 0 152 153 - name: Try to drop role in check mode again 154 <<: *task_parameters 155 check_mode: yes 156 postgresql_user: 157 <<: *pg_parameters 158 name: '{{ test_user }}' 159 state: absent 160 161 - assert: 162 that: 163 - result is not changed 164 - result.user == '{{ test_user }}' 165 166 - name: Try to drop role in actual mode again 167 <<: *task_parameters 168 postgresql_user: 169 <<: *pg_parameters 170 name: '{{ test_user }}' 171 state: absent 172 173 - assert: 174 that: 175 - result is not changed 176 - result.user == '{{ test_user }}' 177 178 # 179 # password, no_password_changes, encrypted, expires parameters 180 # 181 182 - name: Create role with password, passed as hashed md5 183 <<: *task_parameters 184 postgresql_user: 185 <<: *pg_parameters 186 name: '{{ test_user }}' 187 password: md59543f1d82624df2b31672ec0f7050460 188 189 - assert: 190 that: 191 - result is changed 192 - result.user == '{{ test_user }}' 193 194 - name: Check that the user exist with a proper password 195 <<: *task_parameters 196 postgresql_query: 197 <<: *pg_parameters 198 query: "SELECT rolname FROM pg_authid WHERE rolname = '{{ test_user }}' and rolpassword = 'md59543f1d82624df2b31672ec0f7050460'" 199 200 - assert: 201 that: 202 - result.rowcount == 1 203 204 - name: Test no_password_changes 205 <<: *task_parameters 206 postgresql_user: 207 <<: *pg_parameters 208 name: '{{ test_user }}' 209 password: u123 210 no_password_changes: yes 211 212 - assert: 213 that: 214 - result is not changed 215 - result.user == '{{ test_user }}' 216 217 218 - name: Check that nothing changed 219 <<: *task_parameters 220 postgresql_query: 221 <<: *pg_parameters 222 query: "SELECT rolname FROM pg_authid WHERE rolname = '{{ test_user }}' and rolpassword = 'md59543f1d82624df2b31672ec0f7050460'" 223 224 - assert: 225 that: 226 - result.rowcount == 1 227 228 # Storing unencrypted passwords is not available from PostgreSQL 10 229 - name: Change password, passed as unencrypted 230 <<: *task_parameters 231 postgresql_user: 232 <<: *pg_parameters 233 name: '{{ test_user }}' 234 password: myunencryptedpass 235 encrypted: no 236 when: postgres_version_resp.stdout is version('10', '<') 237 238 - assert: 239 that: 240 - result is changed 241 - result.user == '{{ test_user }}' 242 when: postgres_version_resp.stdout is version('10', '<') 243 244 - name: Check that the user exist with the unencrypted password 245 <<: *task_parameters 246 postgresql_query: 247 <<: *pg_parameters 248 query: "SELECT rolname FROM pg_authid WHERE rolname = '{{ test_user }}' and rolpassword = 'myunencryptedpass'" 249 when: postgres_version_resp.stdout is version('10', '<') 250 251 - assert: 252 that: 253 - result.rowcount == 1 254 when: postgres_version_resp.stdout is version('10', '<') 255 256 - name: Change password, explicit encrypted=yes 257 <<: *task_parameters 258 postgresql_user: 259 <<: *pg_parameters 260 name: '{{ test_user }}' 261 password: myunencryptedpass 262 encrypted: yes 263 264 - assert: 265 that: 266 - result is changed 267 - result.user == '{{ test_user }}' 268 269 - name: Check that the user exist with encrypted password 270 <<: *task_parameters 271 postgresql_query: 272 <<: *pg_parameters 273 query: "SELECT rolname FROM pg_authid WHERE rolname = '{{ test_user }}' and rolpassword != 'myunencryptedpass'" 274 275 - assert: 276 that: 277 - result.rowcount == 1 278 279 - name: Change rolvaliduntil attribute 280 <<: *task_parameters 281 postgresql_user: 282 <<: *pg_parameters 283 name: '{{ test_user }}' 284 expires: 'Jan 31 2020' 285 286 - assert: 287 that: 288 - result is changed 289 - result.user == '{{ test_user }}' 290 291 - name: Check the prev step 292 <<: *task_parameters 293 postgresql_query: 294 <<: *pg_parameters 295 query: > 296 SELECT rolname FROM pg_authid WHERE rolname = '{{ test_user }}' 297 AND rolvaliduntil::text like '2020-01-31%' 298 299 - assert: 300 that: 301 - result.rowcount == 1 302 303 - name: Try to set the same rolvaliduntil value again 304 <<: *task_parameters 305 postgresql_user: 306 <<: *pg_parameters 307 name: '{{ test_user }}' 308 expires: 'Jan 31 2020' 309 310 - assert: 311 that: 312 - result is not changed 313 - result.user == '{{ test_user }}' 314 315 - name: Check that nothing changed 316 <<: *task_parameters 317 postgresql_query: 318 <<: *pg_parameters 319 query: > 320 SELECT rolname FROM pg_authid WHERE rolname = '{{ test_user }}' 321 AND rolvaliduntil::text like '2020-01-31%' 322 323 - assert: 324 that: 325 - result.rowcount == 1 326 327 # 328 # role_attr_flags 329 # 330 - name: Set role attributes 331 <<: *task_parameters 332 postgresql_user: 333 <<: *pg_parameters 334 name: '{{ test_user }}' 335 role_attr_flags: CREATEROLE,CREATEDB 336 337 - assert: 338 that: 339 - result is changed 340 - result.user == '{{ test_user }}' 341 342 - name: Check the prev step 343 <<: *task_parameters 344 postgresql_query: 345 <<: *pg_parameters 346 query: > 347 SELECT rolname FROM pg_authid WHERE rolname = '{{ test_user }}' 348 AND rolcreaterole = 't' and rolcreatedb = 't' 349 350 - assert: 351 that: 352 - result.rowcount == 1 353 354 - name: Set the same role attributes again 355 <<: *task_parameters 356 postgresql_user: 357 <<: *pg_parameters 358 name: '{{ test_user }}' 359 role_attr_flags: CREATEROLE,CREATEDB 360 361 - assert: 362 that: 363 - result is not changed 364 - result.user == '{{ test_user }}' 365 366 - name: Check the prev step 367 <<: *task_parameters 368 postgresql_query: 369 <<: *pg_parameters 370 query: > 371 SELECT rolname FROM pg_authid WHERE rolname = '{{ test_user }}' 372 AND rolcreaterole = 't' and rolcreatedb = 't' 373 374 - name: Set role attributes 375 <<: *task_parameters 376 postgresql_user: 377 <<: *pg_parameters 378 name: '{{ test_user }}' 379 role_attr_flags: NOCREATEROLE,NOCREATEDB 380 381 - assert: 382 that: 383 - result is changed 384 - result.user == '{{ test_user }}' 385 386 - name: Check the prev step 387 <<: *task_parameters 388 postgresql_query: 389 <<: *pg_parameters 390 query: > 391 SELECT rolname FROM pg_authid WHERE rolname = '{{ test_user }}' 392 AND rolcreaterole = 'f' and rolcreatedb = 'f' 393 394 - assert: 395 that: 396 - result.rowcount == 1 397 398 - name: Set role attributes 399 <<: *task_parameters 400 postgresql_user: 401 <<: *pg_parameters 402 name: '{{ test_user }}' 403 role_attr_flags: NOCREATEROLE,NOCREATEDB 404 405 - assert: 406 that: 407 - result is not changed 408 - result.user == '{{ test_user }}' 409 410 - name: Check the prev step 411 <<: *task_parameters 412 postgresql_query: 413 <<: *pg_parameters 414 query: > 415 SELECT rolname FROM pg_authid WHERE rolname = '{{ test_user }}' 416 AND rolcreaterole = 'f' and rolcreatedb = 'f' 417 418 # 419 # priv 420 # 421 - name: Create test table 422 <<: *task_parameters 423 postgresql_table: 424 <<: *pg_parameters 425 name: '{{ test_table }}' 426 columns: 427 - id int 428 429 - name: Insert data to test table 430 <<: *task_parameters 431 postgresql_query: 432 query: "INSERT INTO {{ test_table }} (id) VALUES ('1')" 433 <<: *pg_parameters 434 435 - name: Check that test_user is not allowed to read the data 436 <<: *task_parameters 437 postgresql_query: 438 db: postgres 439 login_user: '{{ pg_user }}' 440 session_role: '{{ test_user }}' 441 query: 'SELECT * FROM {{ test_table }}' 442 ignore_errors: yes 443 444 - assert: 445 that: 446 - result is failed 447 - "'permission denied' in result.msg" 448 449 - name: Grant privileges 450 <<: *task_parameters 451 postgresql_user: 452 <<: *pg_parameters 453 name: '{{ test_user }}' 454 priv: '{{ test_table }}:SELECT' 455 456 - assert: 457 that: 458 - result is changed 459 460 - name: Check that test_user is allowed to read the data 461 <<: *task_parameters 462 postgresql_query: 463 db: postgres 464 login_user: '{{ pg_user }}' 465 session_role: '{{ test_user }}' 466 query: 'SELECT * FROM {{ test_table }}' 467 468 - assert: 469 that: 470 - result.rowcount == 1 471 472 - name: Grant the same privileges again 473 <<: *task_parameters 474 postgresql_user: 475 <<: *pg_parameters 476 name: '{{ test_user }}' 477 priv: '{{ test_table }}:SELECT' 478 479 - assert: 480 that: 481 - result is not changed 482 483 - name: Remove test table 484 <<: *task_parameters 485 postgresql_table: 486 <<: *pg_parameters 487 name: '{{ test_table }}' 488 state: absent 489 490 # 491 # fail_on_user 492 # 493 - name: Create test table, set owner as test_user 494 <<: *task_parameters 495 postgresql_table: 496 <<: *pg_parameters 497 name: '{{ test_table }}' 498 owner: '{{ test_user }}' 499 500 - name: Test fail_on_user 501 <<: *task_parameters 502 postgresql_user: 503 <<: *pg_parameters 504 name: '{{ test_user }}' 505 state: absent 506 ignore_errors: yes 507 508 - assert: 509 that: 510 - result is failed 511 - result.msg == 'Unable to remove user' 512 513 - name: Test fail_on_user 514 <<: *task_parameters 515 postgresql_user: 516 <<: *pg_parameters 517 name: '{{ test_user }}' 518 fail_on_user: no 519 520 - assert: 521 that: 522 - result is not changed 523 524 # 525 # Test groups parameter 526 # 527 - name: Create test group 528 <<: *task_parameters 529 postgresql_user: 530 <<: *pg_parameters 531 name: '{{ test_group2 }}' 532 role_attr_flags: NOLOGIN 533 534 - name: Create role test_group1 and grant test_group2 to test_group1 in check_mode 535 <<: *task_parameters 536 postgresql_user: 537 <<: *pg_parameters 538 name: '{{ test_group1 }}' 539 groups: '{{ test_group2 }}' 540 role_attr_flags: NOLOGIN 541 check_mode: yes 542 543 - assert: 544 that: 545 - result is changed 546 - result.user == '{{ test_group1 }}' 547 - result.queries == ['CREATE USER "{{ test_group1 }}" NOLOGIN', 'GRANT "{{ test_group2 }}" TO "{{ test_group1 }}"'] 548 549 - name: check that the user doesn't exist 550 <<: *task_parameters 551 postgresql_query: 552 <<: *pg_parameters 553 query: "SELECT rolname FROM pg_roles WHERE rolname = '{{ test_group1 }}'" 554 555 - assert: 556 that: 557 - result.rowcount == 0 558 559 - name: check membership 560 <<: *task_parameters 561 postgresql_query: 562 <<: *pg_parameters 563 query: "SELECT grolist FROM pg_group WHERE groname = '{{ test_group2 }}' AND grolist != '{}'" 564 565 - assert: 566 that: 567 - result.rowcount == 0 568 569 - name: Create role test_group1 and grant test_group2 to test_group1 570 <<: *task_parameters 571 postgresql_user: 572 <<: *pg_parameters 573 name: '{{ test_group1 }}' 574 groups: '{{ test_group2 }}' 575 role_attr_flags: NOLOGIN 576 577 - assert: 578 that: 579 - result is changed 580 - result.user == '{{ test_group1 }}' 581 - result.queries == ['CREATE USER "{{ test_group1 }}" NOLOGIN', 'GRANT "{{ test_group2 }}" TO "{{ test_group1 }}"'] 582 583 - name: check that the user exists 584 <<: *task_parameters 585 postgresql_query: 586 <<: *pg_parameters 587 query: "SELECT rolname FROM pg_roles WHERE rolname = '{{ test_group1 }}'" 588 589 - assert: 590 that: 591 - result.rowcount == 1 592 593 - name: check membership 594 <<: *task_parameters 595 postgresql_query: 596 <<: *pg_parameters 597 query: "SELECT grolist FROM pg_group WHERE groname = '{{ test_group2 }}' AND grolist != '{}'" 598 599 - assert: 600 that: 601 - result.rowcount == 1 602 603 - name: Grant test_group2 to test_group1 again 604 <<: *task_parameters 605 postgresql_user: 606 <<: *pg_parameters 607 name: '{{ test_group1 }}' 608 groups: '{{ test_group2 }}' 609 610 - assert: 611 that: 612 - result is not changed 613 - result.user == '{{ test_group1 }}' 614 615 - name: check membership 616 <<: *task_parameters 617 postgresql_query: 618 <<: *pg_parameters 619 query: "SELECT grolist FROM pg_group WHERE groname = '{{ test_group2 }}' AND grolist != '{}'" 620 621 - assert: 622 that: 623 - result.rowcount == 1 624 625 - name: Grant groups to existent role 626 <<: *task_parameters 627 postgresql_user: 628 <<: *pg_parameters 629 name: '{{ test_user }}' 630 groups: 631 - '{{ test_group1 }}' 632 - '{{ test_group2 }}' 633 634 - assert: 635 that: 636 - result is changed 637 - result.user == '{{ test_user }}' 638 - result.queries == ['GRANT "{{ test_group1 }}" TO "{{ test_user }}"', 'GRANT "{{ test_group2 }}" TO "{{ test_user }}"'] 639 640 - name: check membership 641 <<: *task_parameters 642 postgresql_query: 643 <<: *pg_parameters 644 query: "SELECT * FROM pg_group WHERE groname in ('{{ test_group1 }}', '{{ test_group2 }}') AND grolist != '{}'" 645 646 - assert: 647 that: 648 - result.rowcount == 2 649 650 always: 651 # 652 # Clean up 653 # 654 - name: Drop test table 655 <<: *task_parameters 656 postgresql_table: 657 <<: *pg_parameters 658 name: '{{ test_table }}' 659 state: absent 660 661 - name: Drop test user 662 <<: *task_parameters 663 postgresql_user: 664 <<: *pg_parameters 665 name: '{{ item }}' 666 state: absent 667 loop: 668 - '{{ test_user }}' 669 - '{{ test_group1 }}' 670 - '{{ test_group2 }}' 671