[Postgresql] 외부에서 접속하기

연결 유무 확인

텔넷으로 IP, port를 명시하면 해당 서버에 저 포트가 열려 있는지 간단히 확인

$ telnet 127.0.0.1 5432

Trying 127.0.0.1...

telnet: Unable to connect to remote host: Connection refused



$ telnet 127.0.0.1 22

Trying 127.0.0.1...

Connected to 127.0.0.1.

Escape character is '^]'. SSH-2.0-OpenSSH_7.6p1 Ubuntu-4ubuntu0.1

^C^]

telnet> quit

Connection closed.

 

포트확인

netstat -ntlp | grep 5432
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      560/postgres

  - DB에서 사용하는 포트 = 5432

  - 127.0.0.1:5432 -> 내부에서만 사용 가능

 

 

Config 변경

 - postgresql.conf 파일 변경

cd /etc/postgresql/10/main
vi postgresql.conf

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'                  # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
                                        
#  listen_addresses = 'localhost'에서 변경

 

DB 재시작

root@django:/etc/postgresql/10/main# /etc/init.d/postgresql restart

 

포트 재확인

netstat -ntlp|grep 5432
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      560/postgres

 

 

DBeaver 접근시 에러 발생

psql: FATAL: no pg_hba.conf entry for host "...", user "...", database "...", SSL on

 

 

pg_hba.conf 수정

vi /etc/postgresql/10/main/pg_hba.conf

# 아래항목에 아래 내용 적기
host    all             all             all                     md5

# psql 재시작
/etc/init.d/postgresql restart