postgresql_info fails when extracting databases as a non-superuser

[HauserV]

SUMMARY

Module postgresql_info fails to extract "databases" info when using a non-superuser login because the superuser privilege is required to access the subconninfo column in pg_catalog.pg_subscription.

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

ISSUE TYPE

• Bug Report

COMPONENT NAME

postgresql_info

ANSIBLE VERSION

ansible [core 2.14.2]
  config file = /etc/ansible/ansible.cfg
  configured module search path = ['/home/hauserv/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /usr/lib/python3/dist-packages/ansible
  ansible collection location = /home/hauserv/.ansible/collections:/usr/share/ansible/collections
  executable location = /usr/bin/ansible
  python version = 3.10.6 (main, Nov 14 2022, 16:10:14) [GCC 11.3.0] (/usr/bin/python3)
  jinja version = 3.1.2
  libyaml = Tru

COLLECTION VERSION

Collection           Version
-------------------- -------
community.postgresql 2.3.2

CONFIGURATION

CONFIG_FILE() = /etc/ansible/ansible.cfg

There is nothing but comments in /etc/ansible/ansible.cfg, though.

OS / ENVIRONMENT

test => SELECT version();
                                                version
--------------------------------------------------------------------------------------------------------
 PostgreSQL 14.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-4), 64-bit
(1 row)

The database is managed by an external team (DBaaS) and I don't have superuser access.

STEPS TO REPRODUCE

1. Install & start a default PostgreSQL 14.2 instance on a machine.
2. Execute the following playbook against the machine.

- hosts: all
  tasks:
  - name: Create a new database
    become: true
    become_user: postgres
    community.postgresql.postgresql_db:
      name: test
  - name: Create a normal user
    become: true
    become_user: postgres
    community.postgresql.postgresql_user:
      db: test
      name: johnny
      password: johnny_rocks
  - name: Collect PostgreSQL info
    community.postgresql.postgresql_info:
      login_host: localhost
      login_user: johnny
      login_password: johnny_rocks
      login_db: test

EXPECTED RESULTS

The playbook should execute without errors.

PLAY [all] ****************************************************************************************************************************************************************************************************************************************

TASK [Gathering Facts] ****************************************************************************************************************************************************************************************************************************
ok: [buntu]

TASK [Create a new database] **********************************************************************************************************************************************************************************************************************
changed: [buntu]

TASK [Create a normal user] ***********************************************************************************************************************************************************************************************************************
changed: [buntu]

TASK [Collect PostgreSQL info] ********************************************************************************************************************************************************************************************************************
ok: [buntu]

PLAY RECAP ****************************************************************************************************************************************************************************************************************************************
buntu                      : ok=4    changed=2    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0

ACTUAL RESULTS

The module attempts to fetch all columns (note the s.*) from pg_catalog.pg_subscription via the following select and fails.

community.postgresql/plugins/modules/postgresql_info.py

Lines 685 to 692 in 04c04c4

	query = ("SELECT s.*, r.rolname AS ownername, d.datname AS dbname "
	"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")
	result = self.__exec_sql(query)

PLAY [all] ****************************************************************************************************************************************************************************************************************************************

TASK [Gathering Facts] ****************************************************************************************************************************************************************************************************************************
ok: [buntu]

TASK [Create a new database] **********************************************************************************************************************************************************************************************************************
changed: [buntu]

TASK [Create a normal user] ***********************************************************************************************************************************************************************************************************************
changed: [buntu]

TASK [Collect PostgreSQL info] ********************************************************************************************************************************************************************************************************************
fatal: [buntu]: FAILED! => {"changed": false, "msg": "Cannot execute SQL 'SELECT s.*, r.rolname AS ownername, d.datname AS dbname 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': permission denied for table pg_subscription\n"}

PLAY RECAP ****************************************************************************************************************************************************************************************************************************************
buntu                      : ok=3    changed=2    unreachable=0    failed=1    skipped=0    rescued=0    ignored=0   

POSSIBLE SOLUTION

One could use the information_schema to identify columns available to the current user (i.e., all except subconninfo in our case) and select only those.

test=> SELECT
test->   s.*,
test->   r.rolname AS ownername,
test->   d.datname AS dbname
test-> FROM
test->   pg_catalog.pg_subscription s
test->   JOIN pg_catalog.pg_database d ON s.subdbid = d.oid
test->   JOIN pg_catalog.pg_roles AS r ON s.subowner = r.oid;
ERROR:  permission denied for table pg_subscription

test=> SELECT
test->   column_name
test-> FROM
test->   information_schema.columns
test-> WHERE
test->   table_schema = 'pg_catalog'
test->   AND table_name = 'pg_subscription';
   column_name
-----------------
 oid
 subdbid
 subname
 subowner
 subenabled
 subbinary
 substream
 subslotname
 subsynccommit
 subpublications
(10 rows)

test=> SELECT
test->   s.oid,
test->   s.subdbid,
test->   s.subname,
test->   s.subowner,
test->   s.subenabled,
test->   s.subbinary,
test->   s.substream,
test->   s.subslotname,
test->   s.subsynccommit,
test->   s.subpublications,
test->   r.rolname AS ownername,
test->   d.datname AS dbname
test-> FROM
test->   pg_catalog.pg_subscription s
test->   JOIN pg_catalog.pg_database d ON s.subdbid = d.oid
test->   JOIN pg_catalog.pg_roles AS r ON s.subowner = r.oid;
 oid | subdbid | subname | subowner | subenabled | subbinary | substream | subslotname | subsynccommit | subpublications | ownername | dbname
-----+---------+---------+----------+------------+-----------+-----------+-------------+---------------+-----------------+-----------+--------
(0 rows)

[Andersson007]

@HauserV hello, sorry for the late response, was on PTO. Thanks for opening the issue, the point sounds fair.
I'll put the help_wanted label. As soon as there are volunteers, the issue will be investigated and solved.

[aleksvagachev]

@Andersson007 Hello. I can take this task.

[Andersson007]

closed via #437

Thanks everyone!