그동안 PostgreSQL을 사용하면서 우분투의 GUI 덕분에, pgAdmin나 HeidiSQL과 같은 프로그램 덕에 DB를 관리하는 것이 아주 수월했다.
그러나, 클라우드 인프라의 DB서버를 다루게 되면서 완전한 CLI 환경에서의 데이터베이스 조작 방법을 알아두는게 나을 것 같아 정리하기로 한다.
(인프라 구조상 DMZ에 위치한 서버를 거쳐 DB서버에 접속을 해야하는데, 인프라 담당측에서 SSH 터널링을 사용하지 못하게 해 HeidiSQL의 SSH 터널링 연결을 할 수 없었다.. SSH에 대해 알아보기 →)
1. Linux OS에 PostgreSQL 설치하기
2. PostgreSQL 접속하기
3. 권한 설정하기
1. Linux OS에 PostgreSQL 설치하기
sudo apt update먼저 sudo(Super User Do: 관리자 권한) apt(Advanced Package Tool: 리눅스 패키지 관리 도구) update 명령어로 패키지 목록을 업데이트 합니다.
sudo apt install postgresql postgresql-contrib위 명령어로 postgresql-contrib(PostgreSQL Contributed Modules)를 설치합니다. 기본 postgresql 패키지는 핵심 데이터베이스 기능만 제공하므로, 확장 모듈(pg_stat_statements: SLQ 실행 통계 수집 및 분석 모듈 등)을 포함한 postgresql-contrib을 설치합니다(선택사항).
sudo systemctl start postgresql
sudo systemctl enable postgresql
psql --versionsystemctl(System Control) 명령어를 이용해 PostgreSQL 서비스를 시작(start)하고, 자동 시작 설정(enable)을 해줍니다.
psql --version 명령어로 설치가 잘 되었는지, 버전을 확인해 줍니다.
2. PostgreSQL 접속하기
DB관리를 위해 PostgreSQL 데이터베이스에 접속하는 대표적인 두가지 방법을 소개합니다.
# 첫번째 방법
sudo -i -u postgres
psql
# 두번째 방법
psql -U {username} -d {database_name} -h {localhost} -p {port_number}첫번째 방법은 PostgreSQL 설치 후 기본적으로 생성되는 postgres 시스템 사용자로 전환 하는 방법입니다.
이 사용자는 PostgreSQL DB의 최고 관리자 역할을 수행합니다. 이후 psql을 실행하면, 별도로 사용자나 DB 이름을 지정하지 않아도 기본적으로 postgres DB에 접속됩니다.
두번째 방법은 특정 사용자 계정으로 접속하는 방법입니다.
사용자는 -U로, 데이터베이스는 -d로 지정하고, -h와 -p를 사용해 원격 또는 로컬 서버의 호스트와 포트를 지정할 수 있습니다.(이 방법을 이용하려면 pg_hba.conf 파일에서 해당 사용자와 호스트에 대한 적절한 접근 권한을 설정하거나, 첫번째 방법을 통해 사용자를 생성하고 권한을 할당한 뒤 사용하면 됩니다.)
| 항목 | 첫번째 방법 | 두번째 방법 |
| 접속 계정 | PostgreSQL의 슈퍼유저(postgres)계정 | 사용자가 지정한 특정 계정 |
| 권한 | 최고 권한 | 사용자가 가진 권한에 한정됨 |
| 용도 | 데이터베이스 관리 (DB 생성, 유저 관리 등) | 일반적엔 데이터 작업(쿼리 실행 등) |
| 접속 환경 | 로컬에서만 접속 가능 | 원격 접속 가능(-h, -p 옵션 사용) |
| 보안 인증 | 인증 불필요 (로컬 postgres 사용자로 접속) | pg_hba.conf 설정 및 비밀번호 필요 |
| 사용 편의성 | 관리자 권한이 있어야 실행 가능 | 일반 사용자도 실행 가능 |
3. 권한 설정하기
사용자 계정을(스키마)를 만들고 권한을 관리하는 방법에 대해 알아봅니다.
DDL, DML 등 데이터베이스 용어에 관련한 것은 정보처리기사에서도 다루고 쿼리 실행문은 크게 다르지 않습니다.
그리고 psql 명령어는 아래의 의미를 참조하면 기억하기 쉬울 것 같습니다.
| 명령어 | 의미 | 설명 |
| \l | list | 데이터베이스 목록을 보여줍니다. |
| \c | connet | 특정 데이터베이스로 접속합니다. |
| \dt | display tables | 테이블 목록을 표시합니다. |
| \d | describe | 특정 테이블/뷰의 구조를 보여줍니다. |
| \du | display users | 사용자(roles) 목록을 표시합니다. |
| \q | quit | psql 세션을 종료합니다. |
| \x | expand | 출력 형식을 확장 모드로 전환합니다. |
| \timing | timing | 쿼리 실행 시간을 측정 및 출력합니다. |
| \? | help | psql 메타 명령어 도움말을 표시합니다. |
| \set | set | 내부 변수를 설정합니다. |
- l이 들어가면 list, c가 들어가면 connect, d가 들어가면 display 혹은 describe, q는 quit으로 이해합니다.
CREATE USER {username} WITH PASSWORD '{password}';사용할 사용자 이름과 비밀번호를 가지고 위 명령어로 우선 사용자를 만듭니다.
이후, \du 명령어를 사용해 사용자가 잘 생성되었는지 확인합니다.
예시로 web_user로 사용자를 생성했습니다. 권한은 없는 상태입니다.
여기서의 권한은 데이터베이스 관리 관련 권한입니다. DML(SELECT, INSERT ...)같은 데이터 조작과는 관련이 없습니다 !
테스트를 위해 데이터베이스 생성 권한(Create DB)를 주었다가 삭제해보도록 합니다.
ALTER ROLE web_user CREATEDB;
위 명령어로 Create DB 권한을 줍니다.
ALTER ROLE web_user NOCREATEDB;
위 명령어로 Create DB 권한을 해제합니다.
권한을 주고 제거하는 데에는 사실 큰 어려움이 따르지 않습니다. 왜냐하면 현재 관리자 권한을 가진 postgres계정을 이용하고 있기 때문입니다.
web_user 계정을 이용하기 시작하면, postgres 연결부터 어려움이 발생하기 시작합니다.
위에서 언급했던 pg_hba.conf 파일을 확인해야 합니다.
hba(Host-Based Authentication): 클라이언트가 데이터베이스에 접속하려 할 때, 어떤 인증 방식을 사용할지 결정합니다.
내부는 이렇게 생겼고 구조는 다음과 같습니다.
<접속유형> <데이터베이스> <사용자> <클라이언트 주소> <인증 방식>
그리고 각 필드에 들어가는 속성(사진에 보이는 local, all, replication 등)의 의미는 아래와 같습니다.
- 접속 유형(TYPE)
- local: 유닉스 도메인 소켓을 사용하는 로컬 접속.
- host: TCP/IP를 사용하는 원격 접속.
- hostssl: SSL을 사용하는 TCP/IP 접속.
- hostnossl: SSL을 사용하지 않는 TCP/IP 접속.
- 데이터베이스(DATABASE)
- 인증이 적용될 데이터베이스를 지정합니다.
- all: 모든 데이터베이스에 적용합니다.
- 특정 데이터베이스 이름을 입력할 수도 있습니다(예: mydatabase).
- replicatoin: 복제를 위한 접속을 나타냅니다.
- 사용자(USER)
- 인증이 적용될 PostgreSQL 사용자를 지정합니다.
- all: 모든 사용자를 지정합니다.
- 특정 사용자 이름을 지정할 수도 있습니다(예: myuser).
- 클라이언트 주소(ADDRESS)
- 접근을 허용하거나 차단할 클라이언트 IP 범위
- 예: 127.0.0.1/32: 로컬호스트(IPv4), ::1/128: 로컬호스트(IPv6), 0.0.0.0/0: 모든 IPv4 주소, ::/0: 모든 IPv6 주소
- 인증 방식(METHOD)
- 인증 방식을 지정합니다.
- peer: 리눅스 사용자 계정과 PostgreSQL 계정을 매핑하는 방식.
- md5: 비밀번호를 MD5 해시로 확인하는 방식.
- scram-sha-256: 더 강력한 SHA-256 비밀번호 인증 방식.
- trust: 비밀번호 없이 접속을 허용하는 방식.
- reject: 접속 차단
그래서, WAS(내부 망에 함께 속한)로부터 연결되는 사용자에게 접근권한을 주고 싶다면.
# IPv4 local connections: << 이 항목 아래에
host DB이름 사용자(스키마)이름 해당WASIP 인증방식
을 추가해주면 됩니다.
테스트를 위해 위에서 만들었던 web_user를 이용하기 위해 아래와 같이 작성하겠습니다.
기존의 관리자 계정 아래에 web_user 항목을 작성하고 인증방식은 md5로 해두었습니다.(peer 인증은 리눅스 사용자와 PostgreSQL 사용자가 일치해야 하므로 테스트 환경에서 번거로워, md5로 하고 web_user에 비밀번호를 사용합니다.)
(수정 이후 sudo systemctl reload postgresql 혹은 sudo systemctl restart postgresql로 재시작을 꼭 해야합니다.)
이후 위에 접속하기의 두번째 방법을 사용하면 정상적으로 PostgreSQL을 해당 사용자로 이용할 수 있습니다.
마지막으로 DB의 테이블별 사용자 권한을 확인하는 방법을 알아보겠습니다.
\z 혹은 \z 테이블명 명령어로 DB에 있는 테이블들의 권한을 확인하거나 특정 테이블의 권한을 확인할 수 있습니다.
각 항목들의 의미는 다음과 같습니다.
- Schema: 테이블이 속한 스키마.
- Name: 테이블 이름.
- Type: 객체 유형(예: 테이블, 뷰, 시퀀스 등).
- Access privileges: 테이블에 부여된 권한.
- web_user=arwd/postgres => 권한/권한을 부여한 사용자
권한에 대해 자세히 알아보면 다음과 같습니다.
| 코드 | 의미 | 설명 |
| a | INSERT | 데이터 삽입 권한 |
| r | SELECT | 데이터 조회 권한 |
| w | UPDATE | 데이터 업데이트 권한 |
| d | DELETE | 데이터 삭제 권한 |
| D | TRUNCATE | 테이블 데이터 전체 삭제 권한 |
| x | REFERENCES | 외래 키 참조 권한 |
| t | TRIGGER | 트리거 생성 궈한 |
권한을 주고 삭제하는 방법은 정보처리기사나 기본적으로 거의 모든 DB에 사용되는 GRANT ON TO ~, REVOKE ON FROM ~ 명령어를 사용하시면 됩니다 !