Properly document the permissions required for PostgreSQL connector (metadata agent)

[ecerulm-st]

Is some content missing, wrong or not clear?
The https://docs.open-metadata.org/latest/connectors/database/postgres does not clearly state:

• which privileges do I need to GRANT to the openmetadata user in postgres.
• what is being used to collect the metadata? It should mention that both pg_* and information_schema.* are used.
• What grants to I need (if any) for OM to get the information it needs from information_schema.*

Describe the solution you'd like

I would like that the documentation

• explicitly mentions the pg_* and states that no grant are needed for OpenMetadata to read from those
• explicitly mention that INFORMATION_SCHEMA.* is also accessed by the Openmetadata agent and what access privileges need to be granted for that.

Additional context
After back and forth on slack #support I think the situtation is that

• The OM postgresql connector uses mostly pg_stat_statements, pg_class, pg_namespace , pg_tables , pg_partitioned_table , pg_policy, pg_sequence, pg_attrdef, pg_attribute, pg_description, pg_contraint, pg_database, pg_proc, etc.
• the pg_* are readable by any user, so they don't require any special GRANT, except GRANT pg_read_all_stats to be able to read pg_stat_statements
• But there are references to information_schema.tables, information_schema.columns , information_schema only shows objects for this the user/role already has access privileges on, so it's not clear if I need to give some GRANT or not**, or what I'm missing if the user does not have any grants.

Adding a explicit mention in the documentation will allow the openmetadata support bot to give more correct responses, for example it incorrectly says now that I need GRANT SELECT ON ALL TABLES IN SCHEMA public TO your_user; which I believe is not true.