※ 테스트 환경
OS: CentOS 7, Windows Server 2012
DB : Postgresql-9.6, Postgresql-13
● Linux postgresql 접속하기
- su postgres
- psql
● Windows postgresql 접속하기
- sql shell 찾아 실행 (또는 psql)
※ encoding 오류 발생 시
- show server_encoding;
- set client_encoding to 'utf8';
● 파일 경로 확인하기
- select * from pg_config;
- select * from pg_config where name = 'LIBDIR';
※ $datadir 확인하기
- show data_directory;
● 계정 비밀번호 여부 확인하기
- select * from pg_shadow; 으로 DB 계정의 비밀번호 사용 여부를 알 수 있다.
※ select * from pg_user으로는 비밀번호 적용 여부를 확인할 수 없음
-> passwd 칼럼에 모두 동일하게 표시됨
● 비밀번호 생성/변경
- create user '계정' with encrypted password '패스워드';
- alter user '계정' with encrypted password '패스워드';
● 비밀번호 패스워드 저장 방식
- 패스워드 저장 방식 선택
9.6버전 : 선택 불가 (on/off)만 선택 가능
13버전 : md5와 SCRAM-SHA-256 중에서 선택 가능
- postgresql.conf 파일에서 변경 가능
- Default 저장 방식
9.6버전 : md5
13버전 : SCRAM-SHA-256
● 비밀번호 복잡도 설정
1. passwordcheck 모듈 사용
※ 리눅스는 postgresql"version"-contrib 설치 필요
▶ passwordcheck Default 설정 값
- 최소 8자리 이상
- password에 username 포함 불가
- password에 필요한 최소 소문자 및 대문자 수 : 각각 1자리
- password에 필요한 최소 숫자 수 : 1자리
- password에 필요한 최소 특수문자 수 : 없음
※ 설정 값은 소스코드를 수정하지 않는 이상 변경이 불가능
1) postgresql.conf 파일에서 shared_preload_libraries 값에 '$libdir/passwordcheck' 추가
ex) shared_preload_libraries = '$libdir/passwordcheck'
2) postgresql 재시작
- systemctl restart postgresql-9.6
※ RESULT
2. github에 있는 passwordpolicy 사용
passwordcheck는 설정 값을 변경할 수 없지만 passwordpolicy를 사용하면 변경이 가능
https://github.com/eendroroy/passwordpolicy 에서 다운로드 가능 (설치 방법 포함)
▶ passwordpolicy Default 설정 값 (postgresql.conf에 설정 값을 적지 않았을 때)
- 최소 길이 : 8자
- 최소 소(대)문자 수 : 2
- 최소 숫자 수 : 2
- 최소 특수문자 수 : 2
1) yum -y install postgresql'버전'-server postgresql'버전'-libs postgresql'버전'-devel postgresql'버전'-contrib
2) yum -y install cracklib cracklib-devel cracklib-dicts words
3) mkdict /usr/share/dict/* | packer /usr/lib/cracklib_dict
4) /usr/pgsql-'버전'/bin/postgresql-'버전'-setup initdb
5) /usr/pgsql-9.6/share/contrib/ 디렉터리 안에 다운로드
git clone https://github.com/eendroroy/passwordpolicy.git
6) vim Makefile -> pg_config 경로 지정
7) make (gcc 설치 필요)
만약 PGXS 에러가 난다면 postgresql'버전'-devel을 설치했는지, 경로는 올바른지 확인할 것
8) make install
9) postgresql.conf 파일 설정
shared_preload_libraries = '$libdir/passwordpolicy' 추가
ex) 따로 값 변경 시, postgresql.conf 에 추가
p_policy.min_password_len = 9
p_policy.min_special_chars = 3
p_policy.min_number = 3
p_policy.min_uppercase_letters = 3
p_policy.min_lowercase_letters = 3
-> 값을 바꿔 사용할 수 있음
※ 9번에서 숫자값을 변경하면 되지만,
테스트 결과 최소 길이, 최소 특수문자 수의 값 변경은 적용이 되지만
최소 대문자 수, 최소 대문자 수, 최소 숫자 수는 적용이 되지 않음
※ 모듈 적용 확인
- select * from pg_available_extensions where name = 'passwordpolicy';
3. 함수를 통해 비밀번호 체크 후 계정 생성
https://www.programmersought.com/article/45011497239/ 참고
- 함수를 통해서 계정과 비밀번호를 생성하는 방법
비밀번호 복잡성을 체크하여 계정을 생성하는 함수를 만들어, 그 함수를 통해서만 계정을 생성
상용화는 어려울 듯..
● 데이터 암호화 (pgcrypto 모듈)
중요 정보 암호화 필요 시 사용 가능
- create extension pgcrypto;
- create extension pgcrypto; 로 모듈이 적용됐는지 확인
www.postgresql.org/docs/9.6/pgcrypto.html 참고하여 암호화
Ex) 암호화 및 복호화 qeury
- 데이터 암호화
insert into [테이블] ([칼럼1], [칼럼2]) values (2, encode(encrypt(convert_to('[암호화 칼럼]','utf8'), 'ENC_KEY','aes'), 'hex'));
- 데이터 복호화
select 칼럼1, convert_from (decrypt(decode([칼럼2],'hex'),'ENC_KEY', 'aes'), 'utf8') from [테이블];
