외부 서버에서 postgresql 접속하기

PostgreSQL을 기본 설치하면 외부에서는 접속할 수가 없다. config를 수정해 줘야 한다.

우선 Ubuntu에서 열려있는 포트를 확인해 보자.

포트 확인은 netstat -ntlp로 확인하자

1
2
3
4
5
6
7
8
9
$ netstat -ntlp
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 127.0.1.1:53            0.0.0.0:*               LISTEN      -               
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      -               
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      -               
tcp6       0      0 :::22                   :::*                    LISTEN      -
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

postgres의 기본 포트는 5432이다. 위 결과처럼 127.0.0.1:5432로 되어 있다면 5432포트는 내부에서만 접속할 수 있다는 뜻이다.

Postgres 설정을 변경하려면 postgresql.conf에서 변경해 줘야 한다.

1
$ sudo vim /etc/postgresql/9.5/main/postgresql.conf
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

postgresql.conf에서 listen_addresses를 찾는다. 기본으로 localhost로 설정되어 있을 것이다.

이걸 '*'로 변경해 어디서든 접근할 수 있도록 해준다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#------------------------------------------------------------------------------
# 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)
port = 5432                             # (change requires restart)
max_connections = 100                   # (change requires restart)
#superuser_reserved_connections = 3     # (change requires restart)
unix_socket_directories = '/var/run/postgresql' # comma-separated list of directories
                                        # (change requires restart)
#unix_socket_group = ''                 # (change requires restart)
#unix_socket_permissions = 0777         # begin with 0 to use octal notation
                                        # (change requires restart)
#bonjour = off                          # advertise server via Bonjour
                                        # (change requires restart)
#bonjour_name = ''                      # defaults to the computer name
                                        # (change requires restart)
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

수정했다면 postgresql을 재시작 한다.

1
$ sudo /etc/init.d/postgresql restart
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

그다음 포트를 다시 확인한다.

1
2
3
4
5
6
7
8
9
10
$ netstat -ntlp
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 127.0.1.1:53            0.0.0.0:*               LISTEN      -               
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      -               
tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      -               
tcp6       0      0 :::22                   :::*                    LISTEN      -               
tcp6       0      0 :::5432                 :::*                    LISTEN      -
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

5432 포트가 0.0.0.0:5432로 되어있는 것을 확인할 수 있으며 이는 외부 에서 접속이 가능하다는 뜻이다.

외부 Windows 서버에서 telnet 접속으로 연결이 되는지 확인한다.

pg_hba.conf 파일의 설정도 변경해야 한다.

postgresql.conf와 같은 위치에 있으며 아래와 같이 파일을 연다.

1
$ sudo vim /etc/postgresql/9.5/main/pg_hba.conf
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

파일 내용중 IPv4 local connections 부분을 찾아 아래와같이 모든 유저의 계정으로 모든 데이터베이스에 모든 외부접속을 허용하도록 수정한다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
# DO NOT DISABLE!
# If you change this first entry you will need to make sure that the
# database superuser can access the database using some other method.
# Noninteractive access to all databases is required during automatic
# maintenance (custom daily cronjobs, replication, and similar tasks).
#
# Database administrative login by Unix domain socket
local   all             postgres                                peer
# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             0.0.0.0/0               md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                peer
#host    replication     postgres        127.0.0.1/32            md5
#host    replication     postgres        ::1/128                 md5
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

수정했다면 다시 postgresql을 재시작 해준다.

1
$ sudo /etc/init.d/postgresql restart
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

이제 외부에서 접속이 되는지 확인한다.