Documentation missing certificate use examples

[v2x2799]

SUMMARY

Existing documentation does not provide any examples of how to connect to a database using a certificate/PEM file.
For example, AWS RDS database connections require use of an Amazon certificate/PEM file. The PEM file is registered in the execution environment's cert store, but the exact syntax on how to use/reference the PEM is not clearly defined in any of the connection examples.

ISSUE TYPE

• Documentation Report

COMPONENT NAME

Module: postgresql_query Commands: ssl_cert, ca_cert

ANSIBLE VERSION

ansible [core 2.15.13]
  config file = /etc/ansible/ansible.cfg
  configured module search path = ['/root/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /usr/local/lib/python3.9/site-packages/ansible
  ansible collection location = /root/.ansible/collections:/usr/share/ansible/collections
  executable location = /usr/local/bin/ansible
  python version = 3.9.20 (main, Sep 26 2024, 20:59:47) [GCC 8.5.0 20210514 (Red Hat 8.5.0-22)] (/usr/bin/python3.9)
  jinja version = 3.1.5
  libyaml = True

[betanummeric]

@v2x2799 standard evironment variables should work, something like this:

- name: example query
  community.postgresql.postgresql_query:
    db: example_db
    query: SELECT version()
  environment:
    PGSSLCERT: ~/.postgresql/postgresql.crt
    PGSSLKEY: ~/.postgresql/postgresql.key
    PGSSLMODE: verify-full
    PGSSLROOTCERT: ~/.postgresql/root.crt

[Andersson007]

@v2x2799 @betanummeric hi, thanks for reporting and discussing this!
There are ssL_* and ca_ args indeed, does anyone want to add working examples to the docs? You could just click the Edit button here against files under plugins/modules/*. You can find a couple of working ones in https://github.com/ansible-collections/community.postgresql/blob/main/tests/integration/targets/postgresql_ping/tasks/postgresql_ping_initial.yml, for example this task

@v2x2799 would you like to add them?

[v2x2799]

@Andersson007 Unfortunately, I don't have much experience in Ansible, and I haven't had much luck using the certificate args. It's very possible I'm not configuring my execution environment correctly. I'm trying to connect to an AWS RDS instance using an AWS PEM file. I've added the PEM file to /etc/ssl/certs within the EE using Ansible Builder, but I can't seem to make the connection. I've tried referencing this PEM in my playbook in every conceivable way, but it just doesn't seem to use it.

I've tried using args similar to below. PEM file is not installed on the Ansible Control Node.

community.postgresql.postgresql_query:
ssl_mode: prefer
login_host: ""
ssl_cert: "/etc/ssl/certs/us-east-2-bundle.pem"
ca_cert: "/etc/ssl/certs/us-east-2-bundle.pem"
login_user: ""
login_password: ""
db:

My code always returns this error:

msg: "unable to connect to database: connection to server at "" <(IP ADDRESS)>, port 5432 failed: Connection refused\n\tIs the server running on that host and accepting TCP/IP connections?\n"
invocation:

RDS Logs always mention invalid CA.

Connections work from any other source/application.

[Andersson007]

@v2x2799 so do i understand correctly that the task from your comment with the same argument and the same certs works when you run it w/o EE?

[toydarian]

@v2x2799 The error message unable to connect to database: connection to server at "" <(IP ADDRESS)>, port 5432 failed: Connection refused\n\tIs the server running on that host and accepting TCP/IP connections? suggests that the issue is not with the certificate, but that you can't establish a TCP connection to the server. Maybe because the server is not online, listening on a different port or because a firewall / security group is blocking access to it.

[v2x2799]

We were able to resolve our connectivity issue by adding username and password under the connect_params argument, By default, login_user and login_password seems to be ignored and the connection defaults to SSL. However, I believe connect_params takes precedence and overrides default SSL mode to allow passing credentials e.g.

community.postgresql.postgresql_query:
connect_params:
user: "{{ lookup('env', 'DB_USER') }}"
password: "{{ lookup('env', 'DB_PASSWORD') }}"
login_host: "{{ lookup('env', 'DB_HOST') }}"
db: "{{ lookup('env', 'DB_NAME') }}"

We've noted that (in our case), using the AWS supplied PEM key was not necessarily a requirement to connect to our RDS instance although we will ultimately want to get that worked out.

[Andersson007]

@v2x2799 thanks for the feedback!
Is there anything we can / should do within this task? Any notes, any doc examples?
Any improvements would be much appreciated.