postgresql_privs does not perform grants on foreign tables

[nickgsc]

SUMMARY

The postgresql_privs module does not currently work with issuing standard grants/revokes on foreign tables. As far as postgres is concerned, the way that grants are handled here is identical to regular tables. Interestingly, using postgreql_privs to create default_privs does work for foreign tables that exist in a schema.

The issue is purely because the get_table_acls function when querying pg_class is not considering foreign tables at all relkind = 'f', thus the acls for the foreign table never attempt to get generated.

ISSUE TYPE

• Bug Report

COMPONENT NAME

postgresql_privs

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

- name: Grant a single privilege on foreign table
  postgresql_privs:
    state: "present"
    roles: "user_to_grant"
    privs: "INSERT"
    objs: "foreign_table1"
    db: "db_name"

EXPECTED RESULTS

Expect to see a grant issued for the specified table

ACTUAL RESULTS

ok: [127.0.0.1] => {
    "changed": false,
    "invocation": {
        "module_args": {
            "ca_cert": null,
            "connect_params": {},
            "database": "ansible_test",
            "db": "ansible_test",
            "fail_on_role": true,
            "grant_option": null,
            "login_host": "127.0.0.1",
            "login_password": "",
            "login_unix_socket": "",
            "login_user": "nickwilson",
            "objs": "foreign_table1",
            "password": "",
            "port": 5432,
            "privs": "INSERT",
            "roles": "user_to_grant",
            "schema": null,
            "session_role": null,
            "ssl_cert": null,
            "ssl_key": null,
            "ssl_mode": "prefer",
            "state": "present",
            "target_roles": null,
            "trust_input": false,
            "type": "table"
        }
    },
    "queries": [
        "GRANT INSERT ON table \"public\".\"foreign_table1\" TO \"user_to_grant\";"
    ]
}

The query for the grant is correctly generated, but never executed, hence the "changed": false because the get_table_acls function is excluding it from consideration

[hunleyd]

Thanks for the report @nickgsc ! Did you want to tackle the fix yourself by chance?
Just saw your PR :)