PostgreSQL 시작하기

이번에 장고를 쓰면서 DB로 PostgreSQL을 쓰게 되었다. MySQL이랑 별 다를 게 없을 줄 알았는데 의외로 DB랑 User를 만들어서 shell에 들어가는 것부터 어려움을 겪어서, 처음 설치하고 나서부터 했던 작업을 기록해보았다.

---

1. Superuser로 shell에 접속하기

sudo -u postgres psql

우선 superuser 계정인 postgres로 접속한 후, DB와 유저를 생성했다.

2. 유저 생성 및 권한 부여하기

# alice 라는 계정 생성
CREATE USER alice WITH PASSWORD 'alice';

# alice 계정에 CREATEDB, REPLICATION 권한(role) 부여
ALTER ROLE elice CREATEDB REPLICATION;

# all in one
# WITH으로 여러 옵션 연결 가능
CREATE USER alice WITH CREATEDB REPLICATION PASSWORD 'alice';

# user 조회하기
\du
or
SELECT * FROM pg_user;
or
SELECT * FROM pg_shadow;

# user 삭제
DROP USER alice;

참고 mysql의 privilege == postgresql의 role

3. DB 생성하기

# DB 생성하기
CREATE DATABASE rabbithole OWNER alice;

# DB 조회하기
\l
or
SELECT * FROM pg_database;

4. DB, 유저 바꾸기

shell에서 DB와 유저를 바꾸려면 아래의 명령어를 shell에서 입력하면 된다.

\c db_name user_name

5. 에러 해결

DB와 유저를 만들고 나서 새로 생성한 유저 alice로 psql에 접속하려고 했는데, Peer authentication failed with user "alice"라는 에러가 뜨면서 접속이 되지 않았다. 구글링해봤더니 config 파일의 authentication 수준에 관련된 문제였다. 그래서 이 글을 참고해서 config 파일의 설정을 바꿔주었다.

user authentication 타입에는 3가지가 있는데, peer/trust/md5로 나뉜다. 디폴트값은 peer인데, 유저의 OS user name을 데이터베이스의 username으로 인식한다는데 무슨 소리인지 잘 모르겠다. trust는 아무 유저나 allow하는 것이고, md5는 패스워드 기반의 인증 타입이다.

superuser인 postgres의 패스워드가 아직 설정되어있지 않으므로, 우선 인증 타입을 trust로 바꿔서 postgres의 패스워드를 설정한 다음에 인증 타입을 비밀번호 기반의 md5로 바꿔서 접속했다.

1. /etc.postgresql/version_number/main으로 이동하기

cd /etc.postgresql/version_number/main

2. config 파일 열기

sudo nano pg_hba.conf

3. 파일 내용 수정하기

local   all     postgres      peer
을
local   all     postgres      trust
로 바꿔주기

4. superuser의 password 설정하기

# shell에 들어가기
psql -U postgres 

# postgres 비밀번호 설정하기 
ALTER USER postgres WITH PASSWORD 'password';

5. postgresql 재시작하기

sudo service postgresql restart

6. 다시 config 파일 설정 바꾸기

sudo nano pg_hba.conf
local   all     postgres      md5

7. alice 유저로 psql shell 접속하기

# options : -U user, -h host, -d DB
psql -U elice -h localhost db_name
or
psql -d db_name -U elice -h localhost

---

이상하게 host 옵션을 붙이지 않으면 alice 유저로 접속이 되지 않았다. 왜 그런지는 모르겠다. postgresql에 익숙해지도록 이것저것 더 만져봐야겠다.

참고

https://stackoverflow.com/questions/18664074/getting-error-peer-authentication-failed-for-user-postgres-when-trying-to-ge