Invalid table-valued function EXTERNAL_QUERY Connect to PostgreSQL server failed: server closed the connection unexpectedly

Question

I need to perform external queries from BigQuery to a Postgres instance. I've set up a Postgres instance with a Public IP and an external connection on the BigQuery side in the same region (Frankfurt).

Whenever I try to perform a test query I get the following error:

Invalid table-valued function EXTERNAL_QUERY Connect to PostgreSQL server failed: server closed the connection unexpectedly
This probably means the server terminated abnormally before or while processing the request. at [1:15]

Interestingly enough, a similar setup works well in another project. I don't understand what the difference is. There are no records in the error log of Postgres.

How could I debug what is going on?

Answer 1

As @kiranmathew suggested, a service account service-<projectNumber>@gcp-sa-bigqueryconnection.iam.gserviceaccount.com was missing. I tried to turn on and off the BigQuery connection API, but the account didn't appear. So I added it manually with the BigQuery Connection Service Agent role and my queries from the GCP console started to work.