search

ansible-collections / community.postgresql

Manage PostgreSQL with Ansible
https://galaxy.ansible.com/ui/repo/published/community/postgresql/
Other
111 stars 90 forks source link

postgresql_subscription does not work when pg_subscription.subconninfo column unavailable #726

Closed nickgsc closed 2 months ago

nickgsc commented 2 months ago
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
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"
}