console.log(lapras)

1. PostgreSQL 설치하기

sudo apt-get update
sudo apt install postgresql

  위와 같이 입력해서 PostgreSQL을 설치해 줍니다.

sudo systemctl is-enabled postgresql

# disabled일 경우
sudo systemctl enable postgresql

  설치가 완료되면 서버를 재시작할 때마다 PostgreSQL이 자동으로 실행되도록 설정이 되어 있을 것입니다. 위의 명령어를 입력해서 확인한 후에 만약 disabled라고 뜬다면 자동으로 시작되도록 설정해 주세요.

2. 포트 변경하고 방화벽 설정하기

  PostgreSQL은 기본적으로 5432 포트를 사용합니다. 5432 포트는 잘 알려져 있는 포트이기 때문에 보안 상 좋지 않으므로 다른 포트를 사용해 주는 것이 좋습니다.

sudo vim /etc/postgresql/<Version>/main/postgresql.conf # 16 버전이면 /etc/postgresql/16/main

port = 5432 # 다른 포트 번호로 수정

  위와 같이 에디터를 열어서 'port=5432' 부분을 찾아 포트를 수정해 줍니다.

sudo systemctl restart postgresql

  포트를 수정한 후 PostgreSQL을 재시작해주세요.

sudo ufw allow 5432/tcp # 다른 포트로 수정한 경우 다른 포트 입력
sudo ufw status
sudo ufw reload

  위와 같이 입력해서 방화벽에서 포트를 허용해 줍니다. 5432 포트 대신 다른 포트를 설정했다면 변경한 포트 번호를 입력합니다.

3. 외부 접속 허용하기

  DBeaver와 같은 외부 프로그램을 이용해서 접속하려면 외부 접속을 허용해주어야 합니다. 이후 SSL 인증서나 특정 IP만 접속 가능하게 하는 등 절차를 추가하는 것이 좋습니다.

sudo vim /etc/postgresql/<Version>/main/postgresql.conf

listen_addresses = '*'

  PostgreSQL 설정 파일을 에디터로 열고 listen_addresses 항목을 '*'로 설정한 후 저장해 주세요. 기본값은 'localhost'로 되어 있어 localhost만 접속이 가능하지만 '*'으로 설정하면 모든 ip의 연결을 허용합니다. 모든 IP가 아닌 특정 IP의 연결만 허용하고 싶다면 특정 IP를 적어주면 됩니다. 여러 IP의 연결을 허용하고 싶다면 콤마(,)로 구분하면 됩니다.

sudo vim /etc/postgresql/16/main/pg_hba.conf

  listen_addresses 설정이 끝났으면 접근 권한을 설정해주어야 합니다. pg_hba.conf 파일을 에디터로 열어주세요.

# 모든 IP를 허용하려면
host all all 0.0.0.0/0 scram-sha-256

# 특정 IP를 허용하려면
host all all <허용할 IP>/32 scram-sha-256

  모든 IP를 허용하려면 0.0.0.0/0을 입력하면 되지만 당연히 보안 상 좋지 않으므로 특정 IP만 허용하는 것이 좋습니다. IP 뒤의 '/0', '/32'는 서브넷 마스크로 '/24'는 0부터 255까지, '/32'는 한 개의 IP만을 의미합니다. 예를 들어 '192.168.0.0/24'라고 입력한다면 192.168.0.0부터 192.168.0.255까지의 범위를 의미하고, '192.168.0.1/32'라고 입력한다면 192.168.0.1 하나를 의미합니다.

  host 뒤의 첫 번째 all 부분은 허용할 Database, 두 번째 all 부분은 허용할 사용자를 의미합니다. 두 부분을 모두 all로 설정하면 모든 데이터베이스에 모든 사용자가 접근을 할 수 있습니다.

  scram-sha-256 부분은 인증 방법을 설정할 수 있습니다. trust, password, md5 등으로 설정을 할 수도 있습니다. trust는 암호나 인증 없이 접속이 가능하고, password는 암호화되지 않은 암호로 접속이 가능합니다. md5는 md5로 암호화된 암호를 요구하지만 오래된 암호화 방식으로 보안상 취약점이 있으므로 scram-sha-256으로 설정하는 것이 좋습니다.

4. postgres 계정 비밀번호 설정하기

sudo -u postgres psql
ALTER USER postgres WITH PASSWORD '변경할 password';

# exit or \q 로 psql을 종료한 후
sudo systemctl restart postgresql

  위와 같이 psql에 접속한 후 비밀번호를 변경합니다. 만약 기본 비밀번호를 입력하라고 나와서 변경할 수가 없다면 pg_hba.conf에서 잠깐 trust로 변경하여 모든 접속을 허용을 한 후에 비밀번호를 변경하고 다시 scram-sha-256으로 변경합니다.

5. 데이터베이스, 테이블, 사용자 생성 후 권한 부여

1) 데이터베이스 생성

sudo -i -u postgres
psql

CREATE DATABASE example1;

\l # 데이터베이스 목록 확인
\q # psql 종료

  위와 같이 psql에 접속을 한 후 데이터베이스를 생성해 줍니다. 데이터베이스의 목록을 보려면 '\l'을 입력하면 됩니다.

2) 테이블 생성

\c example1 # example1 데이터베이스 접속

CREATE TABLE example1_table (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

\dt # 테이블 목록 확인

  psql에 접속 후 '\c 데이터베이스명'을 입력하면 데이터베이스에 접속할 수 있습니다. CREATE TABLE 명령어를 이용하여 테이블을 생성한 후 '\dt'를 입력하여 제대로 생성되었는지 확인할 수 있습니다.

3) 사용자 생성 및 권한 부여

CREATE USER username1 WITH PASSWORD 'password';
GRANT CONNECT ON DATABASE example1 TO username1;

\du # 유저 목록 확인

  위와 같이 유저를 생성하고 특정 데이터베이스에 접근할 수 있는 권한을 부여할 수 있습니다. 유저 생성 후 '\du'를 입력하여 제대로 생성이 되었는지 확인할 수 있습니다.

\c example1 # example1 데이터베이스 접속

# 앞으로 생성될 테이블에도 권한 부여
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO username1;

# 이미 존재하는 모든 테이블에만 권한 부여
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO username1;

# 특정 테이블에만 권한 부여
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE public.example1_table TO username1;

# 부여된 권한 조회
SELECT * FROM information_schema.role_table_grants WHERE grantee = 'username1';

  데이터베이스에 접속한 후 위와 같이 입력하면 특정 유저에게 테이블과 관련된 권한을 부여할 수 있습니다. GRANT 명령어만 쓰면 이미 존재하는 테이블에만 권한이 부여되므로 앞으로 생성될 테이블에도 권한이 부여가 되도록 하려면 ALTER DEFAULT PRIVILEGES 명령어를 사용하면 됩니다.

6. 기타 유용한 보안 설정 (로그 활성화, 환경 설정 파일 권한 변경, public 사용 제한)

1) 로그 활성화하기

sudo vim /etc/postgresql/16/main/postgresql.conf

# all(모든 로그), ddl(CREATE, ALTER, DROP 등), mod(ddl + INSERT, UPDATE, DELETE 등)
log_statement = 'all'

# 100 milliseconds 이상 걸리는 모든 로그 저장, 0으로 설정하면 모든 로그 저장
log_min_duration_statement = 100 

logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

sudo systemctl restart postgresql

  로그를 활성화하여 침입 유무를 확인하거나 침입 시도 의심 사례를 분석할 수 있습니다. PostgreSQL 설정 파일을 에디터로 열고 위와 같이 수정해 주세요. log_statement를 'ddl'로 설정하면 CREATE, ALTER , DROP과 같은 DDL(Data Definition Language) 쿼리만 로그에 기록되고, 'mod'로 설정하면 DDL 쿼리와 함께 INSERT, UPDATE, DELETE 같은 DML(Data Manipulation Language)도 함께 저장이 됩니다. 'all'로 설정하면 SELECT, REVOKE와 같은 모든 로그를 저장합니다. 

sudo -i -u postgres
cd <PostgreSQL Version>/main/log

  postgres 계정으로 접속해서 로그 폴더에 들어간 후 ls 명령어로 파일 목록을 확인해 보면 로그 파일이 생성되는 것을 볼 수 있습니다. cat 명령어 등을 사용하여 로그 내용을 확인할 수 있습니다.

2) 환경 설정 파일 권한 변경

cd /etc/postgresql/<Version>/main

ls -l # 권한 확인

sudo chmod 600 pg_hba.conf
sudo chmod 600 pg_ident.conf
sudo chmod 600 postgresql.conf
sudo chmod 600 pg_ctl.conf

  환경 설정 파일이 허가되지 않은 사용자에 의해서 변경되지 않도록 설정 파일의 권한을 600으로 변경합니다.

3) public 사용 제한

  PostgreSQL에서 DB를 생성할 경우 Default로 public 스키마가 생성됩니다. 다른 스키마를 생성하지 않고 테이블을 생성할 경우 기본적으로 public 스키마 안에 생성되고, public 스키마는 모든 개체에서 접근이 가능하므로 정보 유출 등의 가능성이 있습니다.

\c example1 # example1 데이터베이스 접속

# public 스키마에서 모든 사용자의 권한 제거
REVOKE ALL ON SCHEMA public FROM public;

# public schemad에서 특정 사용자에게 스키마 사용 권한 부여
GRANT USAGE ON SCHEMA public TO specific_user;

  위와 같은 절차는 DB별로 해주어야 하며, 추후에 DB가 생성되면 생성된 DB에도 해주어야 합니다. 이 설정들은 한국인터넷진흥원(KISA)의 클라우드 취약점 점검 가이드의 PostgreSQL 항목 중 일부를 발췌했습니다. 이외에도 유용한 설정들이 많으니 클라우드 취약점 점검 가이드를 참고해 주세요.

  pg 라이브러리를 사용해서 express에서 PostgreSQL을 사용하기 위해 공식 문서를 읽어보던 중, 매개변수화된 쿼리(Parameterized query) 항목을 보고 흥미가 생겼습니다. 우선 일반적인 쿼리문과는 다른 형태를 하고 있었고, SQL Injection 공격을 방지한다고 되어 있어서 일반적인 쿼리문보다 좋아 보였습니다. 하지만 공식 문서만 보고는 SQL Injection 공격이 무엇인지, 그리고 매개변수화된 쿼리가 어떻게 SQL Injection 공격에 더 안전한지 알 수가 없어서 관련 정보들을 찾아봤습니다.

1. SQL Injection 이란?

  SQL Injection은 가장 흔한 웹 해킹 기법 중 하나로써 웹 페이지 입력을 통해 SQL 문에 악성 코드를 삽입해서 데이터 베이스를 파괴할 수 있는 기법입니다. SQL Injection은 "1=1"이 항상 true 값을 반환한다는 것을 기반으로 합니다.

SELECT * FROM Users WHERE UserId = 105 OR 1=1;

  "1=1"이 항상 true이기 때문에, UserId = 105와 상관없이 항상 true가 되어서 테이블의 모든 행을 반환합니다.

  직접 SQL 쿼리를 입력해 보면 이해하기 쉬울 것입니다. 만약 테이블에 user 이름과 비밀번호가 포함되어 있다면 해커는 105 OR 1=1을 삽입하여 데이터베이스의 모든 사용자 이름과 비밀번호에 액세스 할 수 있습니다.

2. 매개변수화된 쿼리(Parameterized query)

  매개변수화된 쿼리는 SQL Injection을 방지하는 효과적인 방법 중 하나입니다. 그 이유는 쿼리 구조와 데이터를 분리하기 때문입니다. 매개변수화된 쿼리의 구조는 다음과 같습니다.

const { pool } = require('../db');

const { UserId1, UserId2 } = req.query;

// 매개변수화된 쿼리 (Parameterized query)
const query = '
SELECT * FROM Users 
WHERE UserId = $1 OR UserId = $2
';
const values = [UserId1, UserId2];

const res = await pool.query(query, values);
console.log(res.rows);

  위 코드와 같이 UserId 값은 쿼리에서 직접 입력되는 것이 아니라, 별도의 값으로 전달이 됩니다. 따라서, 데이터는 쿼리의 일부로 해석되지 않습니다. 따라서, 일반적인 쿼리문을 쓰는 것보다 매개변수화된 쿼리를 사용하면 데이터베이스의 안전성을 높이고, SQL Injection 공격을 효과적으로 방어할 수 있습니다.

참고문헌

1. Parameterized Query - node-postgres 공식 문서 (링크)

2. SQL Injection - W3 Schools (링크)

  PostgreSQL을 설치할 때 비밀번호를 설정해주는데, 이 비밀번호를 변경하는 방법을 알아보겠습니다.

1. 서비스에서 postgresql-x64-16 시작하기

  검색창을 열어서 서비스를 입력하거나 윈도우 키+R을 눌러서 "services.msc"를 입력해서 서비스 메뉴에 들어갑니다.

  postgresql-x64-16 서비스가 시작이 되어있지 않다면 마우스 우클릭을 해서 시작을 눌러줍니다.

2. 환경 변수 등록하기

  PowerShell이나 터미널에서 아래와 같이 입력을 해줍니다.

psql -U postgres

  만약 psql 명령어를 찾지 못한다는 문구가 뜬다면 환경변수를 등록해줘야 합니다. 환경 변수 등록을 위해서 PostgreSQL이 설치된 폴더를 찾아야 합니다. (psql 명령어가 잘 작동한다면 이 단계는 패스하시고 다음 단계를 진행하시면 됩니다.)

C:\Program Files\PostgreSQL\<Version>\bin

# Example
# C:\Program Files\PostgreSQL\16\bin

  저의 경우에는 16 version이라, 디렉토리 경로는 "C:\Program Files\PostgreSQL\16\bin"이었습니다.

  경로를 찾았으면 윈도우 검색 창에서 시스템 환경 변수 편집을 검색하거나, 윈도우키 + R을 눌러서 "sysdm.cpl ,3"을 입력해서 시스템 환경 변수 편집 메뉴로 진입해 줍니다. 시스템 속성 메뉴가 뜨면 환경 변수(N)라고 되어 있는 메뉴를 클릭합니다.

  환경 변수 창이 뜨면 시스템 변수(S)라고 써져 있는 곳에서 Path 부분을 찾아서 더블 클릭을 합니다.

  새로 만들기(N)를 눌러서 아까 찾은 PostgreSQL 디렉토리 경로를 입력해 주고 확인을 눌러주세요. 그리고 재부팅을 해야 추가된 환경 변수가 적용이 됩니다.

3. PowerShell이나 터미널에서 비밀번호 변경

  환경 변수 등록을 마치고 재부팅을 했다면, 이제 PowerShell이나 터미널에서 psql 명령어가 잘 작동할 것입니다.

psql -U postgres

  터미널에 접속해서 위와 같이 입력하고 프로그램을 설치할 때 입력했던 비밀번호를 입력하면, PostgreSQL에 접속하실 수 있습니다.

ALTER USER postgres WITH PASSWORD "변경할 비밀번호";

# Example
# ALTER USER postgres WITH PASSWORD "1234";

  접속하신 후에, 위와 같이 명령어를 입력해 주면 비밀번호 변경이 완료가 됩니다.

  ALTER ROLE 문구가 뜨면 비밀번호 변경이 완료된 것입니다.