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