How can I connect to a PostGIS database from ArcMap? Solutions for ArcGIS 9.3, 10.0 and later are acceptable.

Ideally, I would like to be able to perform spatially enabled queries and receive the results back (e.g. spatial and non-spatial joins, filtering etc.) rather than just dumping the contents of a table.

To clarify, I don't want to use the ArcSDE spatial extensions, I want to use the PostGIS spatial extensions in ArcGIS.