Foreign server does not work, works fine with psql

[marcoalban-hidalgo]

Description

Summary: Attempting simple query on a foreign server, docs: https://www.postgresql.org/docs/current/catalog-pg-foreign-server.html

Detail:
I have this schema:

my_db> \dn
+----------+-------------------+
| Name     | Owner             |
|----------+-------------------|
| partman  | my_usr      |
| foo      | root              |
| public   | admin          |
+----------+-------------------+

Note my postgres user has access to all these schemas. All queries that I'm about to describe work as expected in psql.

Note that foo is actually a foreign server:

select 
    srvname as name, 
    srvowner::regrole as owner, 
    fdwname as wrapper, 
    srvoptions as options
from pg_foreign_server
join pg_foreign_data_wrapper w on w.oid = srvfdw;

returns:

   name   | owner |   wrapper    |                          options                           
----------+-------+--------------+------------------------------------------------------------
 bar | root  | postgres_fdw | {host=myvpn.com,dbname=my_db,port=5432}

Queries work as expected with the partman schema, even autocomplete works fine:

select * from partman.custom_time_partitions;

Hitting Enter, this query succeeds.

Interestingly, if I try the same thing for bar schema, autocomplete still works to auto-complete the table in that schema. Even \d bar.accounts works fine:

\d bar.accounts

Shows:

+--------------------+--------------------------+-----------+-------------+
| Column             | Type                     | Modifiers | FDW Options |
|--------------------+--------------------------+-----------+-------------|
| account_id         | text                     |  not null | 8           |
| account_number     | text                     |  not null | 8           |
...

However, when I try to run:

select * from bar.accounts

The repl hangs for a couple of minutes and then I get this:

could not connect to server "bar"
DETAIL:  could not connect to server: Connection timed out
	Is the server running on host "myvpn.com" (10.10.21.182) and accepting
	TCP/IP connections on port 5432?

This works w/ psql

Note also that starting pgcli with that db works:

pgcli -h myvpn.com -U root -d bar

All works as expected thereafter

DEBUG LOGGING

Turning on debug logging I see this:

2022-12-31 02:17:29,065 (99983/MainThread) pgcli.pgexecute DEBUG - Trying a pgspecial command. sql: 'select * from bar.accounts'
2022-12-31 02:17:29,065 (99983/MainThread) pgcli.pgexecute DEBUG - Regular sql statement. sql: 'select * from bar.accounts'
2022-12-31 02:19:39,946 (99983/MainThread) pgcli.pgexecute ERROR - sql: 'select * from bar.accounts', error: SqlclientUnableToEstablishSqlconnection('could not connect to server "bar"\nDETAIL:  could not connect to server: Connection timed out\n\tIs the server running on host "myvpn.com" (10.10.23.182) and accepting\n\tTCP/IP connections on port 5432?')
2022-12-31 02:19:39,957 (99983/MainThread) pgcli.pgexecute ERROR - traceback: 'Traceback (most recent call last):\n  File "/Users/.../pyenv/lib/python3.10/site-packages/pgcli/pgexecute.py", line 373, in run\n    yield self.execute_normal_sql(sql) + (sql, True, False)\n  File "/Users/.../pyenv/lib/python3.10/site-packages/pgcli/pgexecute.py", line 423, in execute_normal_sql\n    cur.execute(split_sql)\n  File "/Users/..../pyenv/lib/python3.10/site-packages/pgcli/pgexecute.py", line 69, in execute\n    super().execute(*args, **kwargs)\n  File "/Users/.../pyenv/lib/python3.10/site-packages/psycopg/cursor.py", line 728, in execute\n    raise ex.with_traceback(None)\npsycopg.errors.SqlclientUnableToEstablishSqlconnection: could not connect to server "bar"\nDETAIL:  could not connect to server: Connection timed out\n\tIs the server running on host "myvpn.com" (10.10.23.182) and accepting\n\tTCP/IP connections on port 5432?\n'
2022-12-31 02:19:39,958 (99983/MainThread) pgcli.main DEBUG - headers: None
2022-12-31 02:19:39,958 (99983/MainThread) pgcli.main DEBUG - rows: None
2022-12-31 02:19:39,958 (99983/MainThread) pgcli.main DEBUG - status: '\x1b[31mcould not connect to server "bar"\nDETAIL:  could not connect to server: Connection timed out\n\tIs the server running on host "myvpn.com" (10.10.23.182) and accepting\n\tTCP/IP connections on port 5432?\x1b[0m'

But, to emphasize, I can reach that domain. Even by just opening another terminal and trying w/ pgcli as mentioned above. I think it's something about proxying the connection. bar.accounts is behind a vpn. So connecting directly with

pgcli -h myvpn.com -U root -d bar

works fine but whatever connection is being tried internally, seems to try to connect with the IP address of the db in the private network.

Your environment

• Please provide your OS and version information.
  Mac OS 12.1
• Please provide your CLI version.
  3.5.0
• What is the output of pip freeze command.

anyio==3.6.2
appnope==0.1.3
argon2-cffi==21.3.0
argon2-cffi-bindings==21.2.0
asttokens==2.0.8
attrs==22.1.0
backcall==0.2.0
beautifulsoup4==4.11.1
bleach==5.0.1
certifi==2022.6.15
cffi==1.15.1
charset-normalizer==2.1.1
cli-helpers==2.3.0
click==8.1.3
configobj==5.0.6
debugpy==1.6.3
decorator==5.1.1
deepmerge==1.0.1
defusedxml==0.7.1
entrypoints==0.4
executing==1.1.1
fastjsonschema==2.16.2
idna==3.3
importlib-metadata==5.2.0
ipykernel==6.16.1
ipython==8.5.0
ipython-genutils==0.2.0
ipywidgets==8.0.2
jaraco.classes==3.2.3
jedi==0.18.1
Jinja2==3.1.2
jsonpath-ng==1.5.3
jsonschema==4.15.0
jupyter==1.0.0
jupyter-console==6.4.4
jupyter-server==1.21.0
jupyter_client==7.4.3
jupyter_core==4.11.2
jupyterlab-pygments==0.2.2
jupyterlab-widgets==3.0.3
keyring==23.13.1
MarkupSafe==2.1.1
matplotlib-inline==0.1.6
mistune==2.0.4
more-itertools==9.0.0
nbclassic==0.4.5
nbclient==0.7.0
nbconvert==7.2.2
nbformat==5.7.0
nest-asyncio==1.5.6
notebook==6.5.1
notebook_shim==0.2.0
numpy==1.23.5
oasapi==0.1.17
packaging==21.3
pandas==1.5.2
pandocfilters==1.5.0
parso==0.8.3
pendulum==2.1.2
pexpect==4.8.0
pgcli==3.5.0
pgspecial==2.0.1
pickleshare==0.7.5
ply==3.11
prometheus-client==0.15.0
prompt-toolkit==3.0.31
psutil==5.9.1
psycopg==3.1.7
psycopg2==2.9.5
ptyprocess==0.7.0
pure-eval==0.2.2
pycparser==2.21
Pygments==2.13.0
pyparsing==3.0.9
pyrsistent==0.18.1
python-dateutil==2.8.2
pytz==2022.4
pytzdata==2020.1
PyYAML==6.0
pyzmq==24.0.1
qtconsole==5.3.2
QtPy==2.2.1
requests==2.28.1
ruamel.yaml==0.17.21
ruamel.yaml.clib==0.2.6
Send2Trash==1.8.0
setproctitle==1.3.2
six==1.16.0
sniffio==1.3.0
soupsieve==2.3.2.post1
sqlparse==0.4.3
stack-data==0.5.1
tabulate==0.9.0
terminado==0.16.0
tinycss2==1.2.1
tornado==6.2
traitlets==5.5.0
typing_extensions==4.4.0
urllib3==1.26.11
wcwidth==0.2.5
webencodings==0.5.1
websocket-client==1.4.1
widgetsnbextension==4.0.3
zipp==3.11.0