01. 설치 및 기본 설정

패키지 설치

sudo apt-get install postgresql postgresql-contrib

ProgreSQL 관리 콘솔

postgres 사용자로 psql 접속

PostgreSQL 관리를 위해 psql 콘솔을 아래와 같이 실행한다.

sudo -u postgres psql
psql (9.5.5)
Type "help" for help.

postgres=#

위 명령어는 postgres 사용자 권한으로 psql을 실행하고 postgres 기본 데이터베이스에 연결한다.

반면에 postgres 사용자 권한으로 다른 데이터베이스 testdb에 연결하려면 아래와 같이 명령한다.

sudo -u postgres psql testdb

역할 목록

PostgreSQL에서 역할(role)은 MySQL의 사용자와 비슷한 개념이다. 역할 목록은 \du 명령어로 아래와 같이 확인할 수 있다.

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

사용자 매핑 목록

사용자 매핑 목록을 \deu+ 명령어로 아래와 같이 확인할 수 있다.

postgres=# \deu+
      List of user mappings
 Server | User name | FDW Options
--------+-----------+-------------
(0 rows)

데이터베이스 목록

데이터베이스 목록은 \l 명령어로 아래와 같이 확인할 수 있다.

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

데이터베이스 접속/변경

psql 콘솔 접속할 때 데이터베이스를 명시해서 바로 접속할 수 있지만 \c 명령어로 접속 데이터베이스를 변경할 수도 있다.

postgres=# \c testdb
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
You are now connected to database "testdb" as user "postgres".

테이블 목록 확인

\dt 명령어로 테이블의 목록을 확인할 수 있는데 postgres 데이터베이스엔 아직 테이블이 없다.

postgres=# \dt
No relations found.

테이블 정의 확인

\d+ 명령어로 테이블의 정의를 확인할 수 있다.

postgres=# \d+ tablename

postgres 계정의 비밀번호 관리

시스템 사용자 postgres의 비밀번호를 $ sudo passwd postgres 명령으로 바꾸는 것을 보안상 권장하지 않는다.

기본적으로 시스템 사용자 postgres는 잠겨 있고 이 사용자는 시스템에 접속할 수 없다. 그런데 비밀번호 변경할 경우 postgres 사용자는 시스템 로그인이 가능해져 postgres 사용자 무작위 로그인 공격에 노출될 수 있다.

그러나 postgres 시스템 사용자가 아니라 역할 postgres에는 비밀번호를 부여할 수 있다.

$ sudo -u postgres psql postgres
postgres=# \password
Enter new password:
Enter it again:

PostgreSQL 서버에 접속하는 방법은 크게 두 가지이다.

  1. 시스템 사용자가 psql 명령어를 실행하여 IDENT/PEER 인증으로 접속하는 방법
  2. PostgreSQL 서버 자체 관리하는 역할/비밀번호로 TCP 인증하여 TCP/IP 접속하는 방법

콘솔 종료

아래와 같이 콘솔을 빠져나올 수 있다.

postgres=# \q

데이터베이스 및 사용자 추가

  • 데이터베이스 이름: testdb
  • 사용자 역할 이름: egg

psql 콘솔에 진입하여 아래와 같이 명령한다.

모든 명령어의 끝은 세미콜론(;)으로 끝난다. 세미콜론을 빼먹으면 명령이 실행되지 않는다.

testdb 데이터베이스 추가

testdb 데이터베이스를 아래와 같이 만들 수 있다.

CREATE DATABASE testdb;

egg 사용자 생성 및 testdb 데이터베이스 접속 권한 부여

psql 콘솔에 접속 가능한 egg 사용자를 만들고 testdb 사용 권한을 부여할 수 있다.

CREATE ROLE egg WITH LOGIN PASSWORD '비밀번호';
GRANT ALL PRIVILEGES ON DATABASE testdb TO egg;

egg 사용자로 testdb 데이터베이스 접속

egg 사용자로 testdb 데이터베이스에 연결한다. 이때 psql은 어쨌거나 postgres 시스템 사용자 권한으로 실행해야 한다.

 sudo -u postgres psql -U egg testdb

psql: FATAL: Peer authentication failed for user "egg" 이와 같이 에러가 발생할 경우 /etc/postgresql/9.5/main/pg_hba.conf 파일에서 md5 방법으로 접속 가능 사용자를 지정한다.

# "local" is for Unix domain socket connections only
local   all             egg                                     md5
local   all             all                                     peer

이렇게 예외 사용자를 추가하기 위해서는 반드시 윗줄에 적어야 한다.

만약 /etc/postgresql/9.5/main/pg_hba.conf 파일 수정이 어려울 경우 명시적으로 호스트 접속으로 문제를 해결할 수도 있다.

sudo -u postgres psql -h 127.0.0.1 -U egg testdb

egg 역할의 비밀번호 변경

egg 역할의 비밀번호를 아래와 같이 변경할 수 있다.

ALTER USER "egg" WITH PASSWORD '비밀번호';

postgres 역할의 비밀번호는 변경하더라도 특별히 비밀번호를 묻지 않고 그대로 진입한다.

egg 역할의 로그인 허용/허용안함

만약에 egg 역할을 삭제하는 것이 아니라 로그인만 허용하고 싶지 않다면 아래와 같이 명령할 수 있다.

ALTER ROLE egg NOLOGIN;

만약 다시 로그인을 허용하고 싶다면 아래와 같이 명령한다.

ALTER ROLE egg NOLOGIN;

egg 로그인 역할 삭제

로그인 역할을 가진 egg 역할을 아래와 같이 삭제할 수 있다.

DROP ROLE egg;

testdb 데이터베이스 삭제

testdb 데이터베이스를 삭제할 수도 있다.

DROP DATABASE testdb;

Django 최적화 옵션

ALTER ROLE egg SET client_encoding TO 'utf8';
ALTER ROLE egg SET default_transaction_isolation TO 'read committed';
ALTER ROLE egg SET timezone TO 'UTC';

데이터베이스와 사용자를 추가하고 권한까지 부여했으므로 \q를 눌러 psql 사용자에서 빠져나온다.

원격 접속 허용

/etc/postgresql/9.5/main/postgresql.conf 파일 수정

listen_addresses = '*' 
port = 5432
max_connections = 100

/etc/postgresql/9.5/main/pg_hba.conf 파일 수정

host    all             all             0.0.0.0/0               md5

Last Modified: 2019-06-25 01:06

blog comments powered by Disqus