postgresql_privs: Support FOREIGN DATA WRAPPER and FOREIGN SERVER (#38803)

* Support FOREIGN DATA WRAPPER and FOREIGN SERVER in postgresql_privs module
* Added available from note to fdw and fs object types
* Integration tests, examples in documentation
* Complete integration tests

2 files changed, 242 insertions, 0 deletions

diff --git a/test/integration/targets/postgresql/tasks/main.yml b/test/integration/targets/postgresql/tasks/main.yml
index 5dd45350a7..2f35a61fd9 100644
--- a/test/integration/targets/postgresql/tasks/main.yml
+++ b/test/integration/targets/postgresql/tasks/main.yml
@@ -777,6 +777,9 @@
 # Test postgresql_tablespace module
 - include: postgresql_tablespace.yml
 
+# Test postgresql_privs
+- include: postgresql_privs.yml
+
 # dump/restore tests per format
 # ============================================================
 - include: state_dump_restore.yml test_fixture=user file=dbdata.sql
diff --git a/test/integration/targets/postgresql/tasks/postgresql_privs.yml b/test/integration/targets/postgresql/tasks/postgresql_privs.yml
new file mode 100644
index 0000000000..f77cd0ea6f
--- /dev/null
+++ b/test/integration/targets/postgresql/tasks/postgresql_privs.yml
@@ -0,0 +1,239 @@
+---
+
+######################################################
+# Test foreign data wrapper and foreign server privs #
+######################################################
+
+- name: Create DB
+  become_user: "{{ pg_user }}"
+  become: True
+  postgresql_db:
+    state: present
+    name: "{{ db_name }}"
+    login_user: "{{ pg_user }}"
+  register: result
+
+- name: Create test role
+  become: True
+  become_user: "{{ pg_user }}"
+  shell: echo "CREATE ROLE fdw_test" | psql -d "{{ db_name }}"
+
+- name: Create fdw extension
+  become: True
+  become_user: "{{ pg_user }}"
+  shell: echo "CREATE EXTENSION postgres_fdw" | psql -d "{{ db_name }}"
+
+- name: Create foreign data wrapper
+  become: True
+  become_user: "{{ pg_user }}"
+  shell: echo "CREATE FOREIGN DATA WRAPPER dummy" | psql -d "{{ db_name }}"
+
+- name: Create foreign server
+  become: True
+  become_user: "{{ pg_user }}"
+  shell: echo "CREATE SERVER dummy_server FOREIGN DATA WRAPPER dummy" | psql -d "{{ db_name }}"
+
+- name: Grant foreign data wrapper privileges
+  postgresql_privs:
+    state: present
+    type: foreign_data_wrapper
+    roles: fdw_test
+    privs: ALL
+    objs: dummy
+    db: "{{ db_name }}"
+    login_user: "{{ pg_user }}"
+  register: result
+  ignore_errors: yes
+
+- assert:
+    that:
+      - "result.changed == true"
+
+- name: Get foreign data wrapper privileges
+  become: True
+  become_user: "{{ pg_user }}"
+  shell: echo "{{ fdw_query }}" | psql -d "{{ db_name }}"
+  vars:
+    fdw_query: >
+      SELECT fdwacl FROM pg_catalog.pg_foreign_data_wrapper
+      WHERE fdwname = ANY (ARRAY['dummy']) ORDER BY fdwname
+  register: fdw_result
+
+- assert:
+    that:
+      - "fdw_result.stdout_lines[-1] == '(1 row)'"
+      - "'fdw_test' in fdw_result.stdout_lines[-2]"
+
+- name: Grant foreign data wrapper privileges second time
+  postgresql_privs:
+    state: present
+    type: foreign_data_wrapper
+    roles: fdw_test
+    privs: ALL
+    objs: dummy
+    db: "{{ db_name }}"
+    login_user: "{{ pg_user }}"
+  register: result
+  ignore_errors: yes
+
+- assert:
+    that:
+      - "result.changed == false"
+
+- name: Revoke foreign data wrapper privileges
+  postgresql_privs:
+    state: absent
+    type: foreign_data_wrapper
+    roles: fdw_test
+    privs: ALL
+    objs: dummy
+    db: "{{ db_name }}"
+    login_user: "{{ pg_user }}"
+  register: result
+  ignore_errors: yes
+
+- assert:
+    that:
+      - "result.changed == true"
+
+- name: Get foreign data wrapper privileges
+  become: True
+  become_user: "{{ pg_user }}"
+  shell: echo "{{ fdw_query }}" | psql -d "{{ db_name }}"
+  vars:
+    fdw_query: >
+      SELECT fdwacl FROM pg_catalog.pg_foreign_data_wrapper
+      WHERE fdwname = ANY (ARRAY['dummy']) ORDER BY fdwname
+  register: fdw_result
+
+- assert:
+    that:
+      - "fdw_result.stdout_lines[-1] == '(1 row)'"
+      - "'fdw_test' not in fdw_result.stdout_lines[-2]"
+
+- name: Revoke foreign data wrapper privileges for second time
+  postgresql_privs:
+    state: absent
+    type: foreign_data_wrapper
+    roles: fdw_test
+    privs: ALL
+    objs: dummy
+    db: "{{ db_name }}"
+    login_user: "{{ pg_user }}"
+  register: result
+  ignore_errors: yes
+
+- assert:
+    that:
+      - "result.changed == false"
+
+- name: Grant foreign server privileges
+  postgresql_privs:
+    state: present
+    type: foreign_server
+    roles: fdw_test
+    privs: ALL
+    objs: dummy_server
+    db: "{{ db_name }}"
+    login_user: "{{ pg_user }}"
+  register: result
+  ignore_errors: yes
+
+- assert:
+    that:
+      - "result.changed == true"
+
+- name: Get foreign server privileges
+  become: True
+  become_user: "{{ pg_user }}"
+  shell: echo "{{ fdw_query }}" | psql -d "{{ db_name }}"
+  vars:
+    fdw_query: >
+      SELECT srvacl FROM pg_catalog.pg_foreign_server
+      WHERE srvname = ANY (ARRAY['dummy_server']) ORDER BY srvname
+  register: fs_result
+
+- assert:
+    that:
+      - "fs_result.stdout_lines[-1] == '(1 row)'"
+      - "'fdw_test' in fs_result.stdout_lines[-2]"
+
+- name: Grant foreign server privileges for second time
+  postgresql_privs:
+    state: present
+    type: foreign_server
+    roles: fdw_test
+    privs: ALL
+    objs: dummy_server
+    db: "{{ db_name }}"
+    login_user: "{{ pg_user }}"
+  register: result
+  ignore_errors: yes
+
+- assert:
+    that:
+      - "result.changed == false"
+
+- name: Revoke foreign server privileges
+  postgresql_privs:
+    state: absent
+    type: foreign_server
+    roles: fdw_test
+    privs: ALL
+    objs: dummy_server
+    db: "{{ db_name }}"
+    login_user: "{{ pg_user }}"
+  register: result
+  ignore_errors: yes
+
+- assert:
+    that:
+      - "result.changed == true"
+
+- name: Get foreign server privileges
+  become: True
+  become_user: "{{ pg_user }}"
+  shell: echo "{{ fdw_query }}" | psql -d "{{ db_name }}"
+  vars:
+    fdw_query: >
+      SELECT srvacl FROM pg_catalog.pg_foreign_server
+      WHERE srvname = ANY (ARRAY['dummy_server']) ORDER BY srvname
+  register: fs_result
+
+- assert:
+    that:
+      - "fs_result.stdout_lines[-1] == '(1 row)'"
+      - "'fdw_test' not in fs_result.stdout_lines[-2]"
+
+- name: Revoke foreign server privileges for second time
+  postgresql_privs:
+    state: absent
+    type: foreign_server
+    roles: fdw_test
+    privs: ALL
+    objs: dummy_server
+    db: "{{ db_name }}"
+    login_user: "{{ pg_user }}"
+  register: result
+  ignore_errors: yes
+
+- assert:
+    that:
+      - "result.changed == false"
+
+- name: Cleanup
+  become: True
+  become_user: "{{ pg_user }}"
+  shell: echo "{{ item }}" | psql -d "{{ db_name }}"
+  with_items:
+    - DROP ROLE fdw_test
+    - DROP FOREIGN DATA WRAPPER dummy
+    - DROP SERVER dummy_server
+
+- name: Destroy DB
+  become_user: "{{ pg_user }}"
+  become: True
+  postgresql_db:
+    state: absent
+    name: "{{ db_name }}"
+    login_user: "{{ pg_user }}"