패키지 설치
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 서버에 접속하는 방법은 크게 두 가지이다.
- 시스템 사용자가
psql명령어를 실행하여 IDENT/PEER 인증으로 접속하는 방법 - 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
