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