private망에 ec2를 생성 후 postgresql을 설치 후 pgadmin으로 접근

# EC2에 Postgresql을 설치한다.

이유는 여러가지가 있지만 비용 문제가 가장 크다.

진행은 EC2 인스턴스 생성(아마도 t3.medium 정도는 만들어야 할지 싶다.) ▶ Postgresql 설치 ▶ pgadmin을 통한 접근

1. EC2 인스턴스 생성 (t3.medium)

- 일단 DB들이 올라갈 인스턴스 이므로 서브넷은 Private로 진행 (생략)

- Private 서브넷에 존재하는 DB를 위한 ssh 터널링 작업 진행

- Pgadmin tool을 활용하여 서비스에 접근

2. 프라이빗 서브넷에 존재하는 EC2 인스턴스에 접근

2022.05.03 - [⭐ AWS/AWS Network] - Private Subnet에 존재하는 EC2에 접근하기

Private Subnet에 존재하는 EC2에 접근하기

3. Postgresql 설치

- PostgreSQL 설치

apt-get을 업데이트하고 postgresql과 postgresql-contrib을 다운로드 한다.

sudo apt-get update
sudo apt-get install -y postgresql postgresql-contrib

postgresql-contrib은 postgresql을 사용하기 위한 확장팩이라고 생각하면 된다.

- 계정 및 데이터베이스 생성

postgresql을 다운받으면 자동으로 postgres라는 유저가 생성된다.

아래 명령어를 통해 비밀번호를 설정한다.

sudo passwd postgres

$ sudo passwd postgres
New password:
Retype new password:

-postgres로 로그인을 한다.

sudo su postgres

아래의 명령어를 통해 데이터베이스에 접근한다.

psql

postgresql shell 접근 완료

- 데이터 베이스 및 유저 생성

이제 데이터 베이스 인스턴스를 생성해보자! 

CREATE DATABASE test_test;

test_test라는 이름을 가진 데이터 베이스가 생성되었다. DB 리스트를 보려면 아래 명령어를 적어준다.

\list

데이터 베이스 리스트

이제 데이터 베이스에 접근 가능한 유저를 생성해주자, 만약 해당 쿼리로 생성이 안되면 아래의 다른방법의 DB USER 생성 하는 방법을 사용하자. (ubuntu 18.04 lts 버전은 postgresql 버전이 10버전으로 설정 되므로 아래의 쿼리가 실행 안될 수 도 있다.)

CREATE ROLE admin WITH LOGIN PASSWORD 'test1234';

나의 경우에는 admin이라는 이름을 가진 유저를 만들어 줬고 비밀번호는 test1234로 설정했다.

정상적으로 유저가 생성되었는지 확인 하기 위해서는 아래 명령어를 통해 확인해 보자!

SELECT rolname FROM pg_roles;

- 다른방법의 DB USER 생성 및 비밀번호 설정 과 DB 권한을 부여하는 방법

CREATE USER user_name WITH ENCRYPTED PASSWORD 'user_name_password';

GRANT ALL PRIVILEGES ON DATABASE db_name TO user_name;

- 결과는 아래와 같다.

정상적으로 생성되었다.

- postgres에서 빠져나오는 방법

\q

- 생성한 유저에 관리자 권한 부여하기

GRANT ALL PRIVILEGES ON DATABASE db_name TO user_name;

4. Postgresql 외부접근 허용하기

- 가장먼저 해줘야할 일은 보안그룹을 오픈해 줘야한다.

여기서는 AWS EC2 인스턴스에 작업을 진행하는 것이므로, AWS BastionHost의 보안그룹과 실제 Postgresql이 설치된 서버의 보안그룹을 열어줘야 정상 접근이 가능하다.

postgresql의 외부접속을 허용해주기 위해선 아래 설정 파일을 수정해줘야한다.

postgresql.conf는 postgresql의 전반적인 설정을 담고 있는 파일이다. 

# 14라는 숫자는 버전이기 때문에 다른버전의 설정은 버전 숫자만 바꿔주면 된다.

sudo vi /etc/postgresql/14/main/postgresql.conf

확인할 내용은 아래와 같다.

여기서 listen_addresses를 변경해주면 된다. 만약 모든 ip 주소에대해 허용하고 싶으면 listen_addresses = '0.0.0.0' 으로 해주면 된다. 설명을 보면 # defaults to 'localhost'; use '*' for all 라고 되어 있다. 모든 접속을 허용하려면 listen_addresses 를 '0.0.0.0'으로 하거나 '*'로 설정하면 될거 같다.

다음은 해당 아이피에 권한을 부과해줘야한다. 아래 설정 파일을 수정하도록 하자 이 설정 파일은 인증시스템 관련 설정을 담고 있다. 

sudo vi /etc/postgresql/14/main/pg_hba.conf

 

- 위의 파일의 가장 아랫부분 중 IPv4 local connection을 아래와 같이 작성해준다.

ip_address에는 원하는 ip주소를 적어주면된다. (예를 들어 위에서 설정했던 0.0.0.0/0)

# IPv4 local connections:
host    all             all             0.0.0.0/0            md5

이제 설정을 완료하였다. postgresql을 재시작 한다.

sudo systemctl restart postgresql
sudo systemctl status postgresql

# 서버가 재시작 해도 postgresql을 자동으로 재시작
sudo systemctl enable postgresql.service

마지막으로 port가 열렸는지 확인해보자

netstat -ntlp | grep 5432

아래처럼 나오면 포트가 개방된거고 이제 외부에서 접근해보면 된다.

$ sudo netstat -ntlp | grep 5432
tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      4103/postgres

5. 터널링을 통해 Private 서브넷에 존재하는 Postgresql에 접근하기

- dbeaver 커뮤니터 버전 설치

https://dbeaver.io/download/

Download | DBeaver Community

- pgadmin 설치

https://www.pgadmin.org/download/

Download

- pgadmin4으로 접속하기

Register → Server 클릭 아래의 내용과 같이 세팅을 진행한다. Username과 Password는 위에서 설정한 값을 넣는다.

- 접속 확인: 아래와 같이 접속된 것을 확인 할 수있다.

6. 다른 서버 및 원격 환경에서 접근하기

- 접근 명령어

psql -h 10.10.10.10 -U admin -d test_test

psql -h {접근ip주소} -U {user_name} -d {db_name}

- 지금까지... private망에 서버에 postgresql을 설치하고 public 망의 bastion host 서버를 통해 private 망의 존재하는 postgre를 pgadmin을 통해 접근하는 방법에 대해서 알아 보았다.

7. Postgresql 비밀번호 변경하는 방법

- postgres 계정은 관리자 계정이므로 모든 DB에 접근을 할수 있는 SUPERUSER 권한을 가지고 있다. 해당 postgres 계정을 활용하여 DB를 조회할 수 있다. 아래는 비밀번호를 변경하는 쿼리이다.

alter user postgres with password 'test1234';

8. psql: error: FATAL: database “XXX” does not exist 오류가 발생하는 경우

psql: error: FATAL: database “XXX” does not exist

- 만약 psql: error: FATAL: database “XXX” does not exist 와 같은 오류가 발생한다면, 그리고 실제 DB에서는 DB가 생성 되어 있는 경우라면, 수동으로 DBeaver에서 생성해 주는것이 바람직 하다.

- 끝 -