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

Handles subscriptions when subconninfo column is unavailable #727

Closed nickgsc closed 2 months ago

nickgsc commented 2 months ago
SUMMARY

Fixes #726

ISSUE TYPE
COMPONENT NAME

postgresql_subscription

ADDITIONAL INFORMATION

Using https://github.com/ansible-collections/community.postgresql/pull/437 as a bit of an example of how to address this limitation. I've modified the module to first check to see which columns are available (per the information schema) and then adjusting the query accordingly. If the subconninfo column is not available, we deal with that by allowing the subscription to be created, but then logging a warning on subsequent runs if we are looking at a subscription that already exists. Without access to subconninfo, we have no way to compare the connection info provided to the module versus what exists in the database, so we simply skip.

I am having a hell of a time devising tests that will prove out that this fix works. I've tested it in an actual CloudSQL environment, where there lack of access to the subconninfo - but in the integration tests, I haven't been able to devise a way to replicate this restriction because creating a subscription requires superuser access in Postgres versions prior to 16. Using the superuser then bypasses all ACL checks, which renders the test moot (i.e. the problem cannot be reproduced). It should be possible to reproduce and test this with Postgres 16 by using the lesser built-in pg_create_subscription role - but the test harness as of now is on PG 15.

Google must have come up with some way to work around this restriction, since they do not give you superuser access yet you can still create subscriptions there using a lesser built-in role they provide, in all versions of Postgres that support logical replication. It's a bit beyond my understanding of Postgres internals to figure out how to replicate Google's configuration in a test scenario. EDIT: Looking at the Postgres source code where the ACL check lives for creating subscriptions, and Google must be running their own slightly customized version of Postgres to support their pseudo-superuser permissions scheme.

Open to any suggestions on how to approach testing this.

[WARNING]: 'connparams' is ignored when existing subscription connparms are unknowable
hunleyd commented 2 months ago

I'm going to hold off on reviewing until @Andersson007 has an opportunity to weigh in

Andersson007 commented 2 months ago

@hunleyd the PR lgtm, any suggestions/concerns?

Andersson007 commented 2 months ago

@nickgsc thanks for the contribution! @hunleyd thanks for reviewing!

@nickgsc please check out our communication guide if you haven't already done that to see other ways how to get in touch with the community

Andersson007 commented 2 months ago

FYI The community.postgresql collection version 3.6.0 has been released! Among other changes, it contains fixes for some modules to work with PostgreSQL 17 beta. Thanks to everyone involved!