community.postgresql.postgresql_info does not work in AWS RDS hosted postgresql

[marcosdiez]

the table pg_authid is not available on AWS RDS hosted postgresql.

It just gives the following error: PostgreSQL said: permission denied for relation pg_authid

The problem is that in the AWS managed RDS, once can't have the full superuser, just some aws admin user that can't access this table.

It seems that AWS consides this a feature, not a bug, source: https://stackoverflow.com/questions/55143544/aws-rds-postgresql-access-to-pg-catalog-pg-authid-forbidden-in-all-contexts

Since postgres_info uses a query that involves this table: https://github.com/ansible-collections/community.postgresql/blob/main/plugins/modules/postgresql_info.py#L711

that command does not work on aws rds.

[MichaelDBA]

Need to remove references to pg_audit and use this function on the OID, pg_catalog.pg_get_userbyid(spcowner) AS "Owner", to get the same info. This way we can be compatible with AWS and OnPrem.

This should be changed in module, community.postgresql/plugins/modules/postgresql_info.py.

So for the case where the SQL is this:
SELECT s.spcname, a.rolname, s.spcacl FROM pg_tablespace AS s JOIN pg_authid AS a ON s.spcowner = a.oid;
Change it to this:
SELECT spcname, pg_catalog.pg_get_userbyid(spcowner) AS rolname, spcacl FROM pg_catalog.pg_tablespace;

There are other pg_audit references in this module that need to follow this pattern for removing it.

[marcosdiez]

it worked. I'll send a PR soon! thank you!