PostgreSQL に外部から接続する方法

前提

ローカルで接続できることを確認

パスワードなし

$ psql -U scott city
psql (15.2 (Ubuntu 15.2-1))
Type "help" for help.

city=>

localhost を指定して

$ psql -U scott city -h localhost
Password for user scott: 
psql (15.2 (Ubuntu 15.2-1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

city=>

トライ

1. そのまま外部から接続

$ psql -U scott  city -h 10.39.166.1
psql: error: connection to server at "10.39.166.1", port 5432 failed: Connection refused
	Is the server running on that host and accepting TCP/IP connections?

2. /etc/postgresql/15/main/postgresql.conf を修正
/etc/postgresql/15/main/postgresql.conf

(省略)
listen_addresses = '*'
(省略)

$ psql -U scott  city -h 10.39.166.1
psql: error: connection to server at "10.39.166.1", port 5432 failed: FATAL:  no pg_hba.conf entry for host "10.39.166.61", user "scott", database "city", SSL encryption
connection to server at "10.39.166.1", port 5432 failed: FATAL:  no pg_hba.conf entry for host "10.39.166.61", user "scott", database "city", no encryption

3. /etc/postgresql/15/main/pg_hba.conf を修正
/etc/postgresql/15/main/pg_hba.conf

(省略)
host    all             all             0.0.0.0/0            trust
(省略)

$ psql -U scott  city -h 10.39.166.1
psql (15.2)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

city=>