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