postgresql_subscription does not work when pg_subscription.subconninfo column unavailable

[nickgsc]

SUMMARY

The subconninfo column of the pg_subscription catalog table contains the sensitive connection information needed to establish a logical replication subscription. According to the documentation:

Access to the column subconninfo is revoked from normal users, because it could contain plain-text passwords.

In Google Cloud Platform's CloudSQL, this column is not accessible at all, as mentioned in their documentation here

Limitations on native PostgreSQL logical replication
Access to the subconninfo column of the pg_subscription system table is unavailable.

This limitation may exist in other cloud providers as well.

As a result, the postgresql_subscription module is unable to manage subscriptions in CloudSQL, as the query of the pg_subscription catalog in function __get_general_subscr_info fails.

ISSUE TYPE

• Bug Report

COMPONENT NAME

postgresql_subscription

ANSIBLE VERSION

ansible [core 2.17.1]
  config file = None
  configured module search path = ['/Users/nickwilson/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /Users/nickwilson/.venv/ansible/lib/python3.12/site-packages/ansible
  ansible collection location = /Users/nickwilson/.ansible/collections:/usr/share/ansible/collections
  executable location = /Users/nickwilson/.venv/ansible/bin/ansible
  python version = 3.12.5 (main, Aug  6 2024, 19:08:49) [Clang 15.0.0 (clang-1500.3.9.4)] (/Users/nickwilson/.venv/ansible/bin/python3.12)
  jinja version = 3.1.4
  libyaml = True

COLLECTION VERSION

# /Users/nickwilson/.ansible/collections/ansible_collections
Collection           Version
-------------------- -------
community.postgresql 3.5.0

# /Users/nickwilson/.venv/ansible/lib/python3.12/site-packages/ansible_collections
Collection           Version
-------------------- -------
community.postgresql 3.4.1

CONFIGURATION

CONFIG_FILE() = /Users/nickwilson/git/ansible-databases-v2/ansible.cfg
INVENTORY_ENABLED(/Users/nickwilson/git/ansible-databases-v2/ansible.cfg) = ['gcp_compute', 'host_list', 'script', 'auto', 'yaml', 'ini', 'toml', 'advanced_host_list', 'constructed']
PAGER(env: PAGER) = less

OS / ENVIRONMENT

MacOS 14.6.1 and AWX 21.8.0
This is reproducible on managed databases where we are connecting via host/port, so the OS details are not particularly relevant.

STEPS TO REPRODUCE

Create a subscription where the login_user does not have access to the subconninfo column of pg_catalog.pg_subscription

    community.postgresql.postgresql_subscription:
      login_user: "testuser"
      login_host: "127.0.0.1"
      db: "ansible_test"
      name: "test_subscription"
      publications: "test_pub"
      connparams:
        host: "127.0.0.1"
        port: "5432"
        user: "sub_user"
        password: "password"
        dbname: "ansible_test2"
      subsparams:
        slot_name: "test_slot"
        create_slot: true
        copy_data: true

EXPECTED RESULTS

Subscription is created as expected

ACTUAL RESULTS

The full traceback is:
  File "/var/folders/j0/frglw1v57yx71b11nrtcp24c0000gn/T/ansible_community.postgresql.postgresql_subscription_payload_vbd5cauy/ansible_community.postgresql.postgresql_subscription_payload.zip/ansible_collections/community/postgresql/plugins/module_utils/postgres.py", line 233, in exec_sql
    obj.cursor.execute(query, query_params)
  File "/Users/nickwilson/.venv/ansible/lib/python3.12/site-packages/psycopg2/extras.py", line 146, in execute
    return super().execute(query, vars)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
fatal: [127.0.0.1]: FAILED! => {
    "changed": false,
    "invocation": {
        "module_args": {
            "ca_cert": null,
            "cascade": false,
            "comment": null,
            "connect_params": {},
            "connparams": {
                "dbname": "ansible_test2",
                "host": "127.0.0.1",
                "password": "password",
                "port": "5432",
                "user": "sub_user"
            },
            "db": "ansible_test",
            "login_host": "127.0.0.1",
            "login_password": "",
            "login_unix_socket": "",
            "login_user": "testuser",
            "name": "test_subscription",
            "owner": null,
            "port": 5432,
            "publications": [
                "test_pub"
            ],
            "session_role": null,
            "ssl_cert": null,
            "ssl_key": null,
            "ssl_mode": "prefer",
            "state": "present",
            "subsparams": {
                "copy_data": true,
                "create_slot": true,
                "slot_name": "test_slot"
            },
            "trust_input": true
        }
    },
    "msg": "Cannot execute SQL 'SELECT obj_description(s.oid, 'pg_subscription') AS comment, d.datname, r.rolname, s.subenabled, s.subconninfo, s.subslotname, s.subsynccommit, s.subpublications FROM pg_catalog.pg_subscription s JOIN pg_catalog.pg_database d ON s.subdbid = d.oid JOIN pg_catalog.pg_roles AS r ON s.subowner = r.oid WHERE s.subname = %(name)s AND d.datname = %(db)s': permission denied for table pg_subscription\n"
}