1. 사용자 생성
현재 사용자 정보를 확인할 때는 아래와 같이 pg_shadow나 pg_user를 사용하면 된다. pg_shadow 는 8.1 이하 버전의 하위 호환성을 위해 남아 있고, 최신 버전의 경우 pg_user를 사용하길 권장한다. 뭐... 큰 차이는 없다. pg_user에서는 passwd Field를 항상 '*'으로 표기하고, pg_shadow에서는 Encryped된 값 혹은 Null로 표시하는 정도... 깊이 들어가면 좀 더 다른 점이 있는데, 이 정도만 알아도 큰 문제 없는 것 같다.
postgres=# SELECT * FROM PG_USER;
usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls | passwd | valuntil | useconfig
----------+----------+-------------+----------+---------+--------------+----------+----------+-----------
postgres | 10 | t | t | t | t | ******** | |
(1개 행)
postgres=# SELECT * FROM PG_SHADOW;
usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls | passwd | valuntil | useconfig
----------+----------+-------------+----------+---------+--------------+--------+----------+-----------
postgres | 10 | t | t | t | t | | |
(1개 행)
사용자 추가하는 psql 명령어는 아래와 같고, 여기에 좀 더 자세한 설명이 있다.(그리 자세하지는 않다. PostgreSQL 공식 Document는 좀 부실한 것 같다.)
CREATE USER name [ [ WITH ] option [ ... ] ]
Option들 중 중요한 것들만 간략하게 설명하면 다음과 같다.
- SUPERUSER | NOSUPERUSER ; Superuser 여부. 기본값은 NOSUPERUSER이다.
- CREATEDB | NOCREATEDB ; DB생성 권한 부여 여부. 기본값은 권한 없음 이다.
- CREATEUSER | NOCREATEUSER ; User생성 권한 부여 여부. 기본값은 권한 없음 이다.
- PASSWORD 'password' ; Password 설정
Superuser의 권한을 가진 Password가 'brown'인 'brown' User는 다음과 같이 만들 수 있다.
postgres=# CREATE USER brown PASSWORD 'brown' SUPERUSER;
CREATE ROLE
postgres=# SELECT * FROM PG_USER;
usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls | passwd | valuntil | useconfig
----------+----------+-------------+----------+---------+--------------+----------+----------+-----------
postgres | 10 | t | t | t | t | ******** | |
brown | 16384 | f | t | f | f | ******** | |
(2개 행)
동일한 Operation을 Shell 에서도 진행할 수 있다. (단, Password는 Prompt를 통해서 입력해야 한다.) 그런데, 동일하게 Superuser 권한만 부여한 것 같은데 Shell에서 만들었을 때는 DB 생성 권한, Role 생성 권한들이 추가로 주어진다. 차후에 확인해봐야겠다. createuser에 대한 내용은 여기를 참조하면 된다.
...
[brown@ha-test-1 /]$ sudo -u postgres createuser -h localhost -s -P brown
새 롤의 암호:
암호 확인:
[brown@ha-test-1 /]
...
postgres=# SELECT * FROM PG_USER;
usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls | passwd | valuntil | useconfig
----------+----------+-------------+----------+---------+--------------+----------+----------+-----------
postgres | 10 | t | t | t | t | ******** | |
brown | 16385 | t | t | f | f | ******** | |
(2개 행)
...
추가로 psql에서 \du를 실행하면 유저 목록과 권한들을 요약해서 볼 수 있다.
...
postgres=# \du
롤 목록
롤 이름 | 속성 | 소속 그룹:
----------+--------------------------------------------------+------------
brown | 슈퍼유저, 롤 만들기, DB 만들기 | {}
postgres | 슈퍼유저, 롤 만들기, DB 만들기, 복제, Bypass RLS | {}
...
2. Database 생성
Postgresql에서는 Table, View, Sequence, Function 등의 Object들을 묶어 Schema라는 논리적인 집합을 구성하고, 이런 Schema들이 모여 하나의 Database를 구성한다. 그리고 다시 이런 Database들과 User/Group 정보, Table Space 정보들을 묶어 DB Cluster를 구성한다. PGAdmin이 이런 구조를 잘 보여준다.
[PGAdmin으로 확인한 DB Cluster 구조]
즉, 우리가 Database를 만들면 Database들 중에 하나가 추가되고, 그 안에 public이라는 Default Schema가 만들어 지게 된다. psql을 통해 DB를 생성할 때 명령어는 아래와 같고, Document는 여기에 있다.
CREATE DATABASE name [ [ WITH ] [ OWNER [=] user_name ]
[ TEMPLATE [=] template ]
[ ENCODING [=] encoding ]
[ LC_COLLATE [=] lc_collate ]
[ LC_CTYPE [=] lc_ctype ]
[ TABLESPACE [=] tablespace_name ]
[ ALLOW_CONNECTIONS [=] allowconn ]
[ CONNECTION LIMIT [=] connlimit ]
[ IS_TEMPLATE [=] istemplate ] ]
Option들을 간단하게 살펴보면 다음과 같다.
- OWENR : DB owner. Owner 외에 다른 계정은 역할 제한이 있다.
- TEMPLATE : DB Template에 의해 생성될 때 Template 이름이다. 기본값은 template1이다.
- ENCODING : Data Encoding 방법. 값을 지정할 때 LC_CTYPE, LC_COLLATE value와 연계되기 때문에 주의해야 한다.
- LC_COLLATE : String Data를 기준으로 정렬할 때 정렬 기준. 예를 들면 ko_KR.UTF-8은 기본적으로 한글 기준으로 정렬하되, 한글 외의 문자는 UTF-8에 의해 정렬하라는 의미다. 본 시스템 설치 시 ko_KR.UTF-8이 기본값으로 설정되어 있다. (template1의 기본값)
- LC_CTYPE : 대, 소문자, 숫자 등과 같은 문자 분류를 위한 설정.
- TABLESPACE : Table Space를 임의로 설정할 때 사용.
- ALLOW_CONNECTIONS : 외부에서 접속 가능 여부 설정
- CONNECTION LIMIT : DB 접속 제한 설정.
- IS_TEMPLATE : DB Template 인지 여부 설정
psql에서 추가된 'brown' user가 Owner인 dwarf라는 DB는 다음과 같이 만들 수 있다.
...
postgres=# CREATE DATABASE dwarf OWNER brown;
CREATE DATABASE
psql에서 '\l'이나 pg_database를 조회하는 방법으로 Database가 만들어진 것을 확인할 수 있다.
...
postgres=# \l
데이터베이스 목록
이름 | 소유주 | 인코딩 | Collate | Ctype | 액세스 권한
-----------+----------+--------+-------------+-------------+-----------------------
dwarf | brown | UTF8 | ko_KR.UTF-8 | ko_KR.UTF-8 |
postgres | postgres | UTF8 | ko_KR.UTF-8 | ko_KR.UTF-8 |
template0 | postgres | UTF8 | ko_KR.UTF-8 | ko_KR.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | ko_KR.UTF-8 | ko_KR.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4개 행)
...
postgres=# SELECT * FROM pg_database;
datname | datdba | encoding | datcollate | datctype | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | datminmxid | dattablespace | datacl
-----------+--------+----------+-------------+-------------+---------------+--------------+--------------+---------------+--------------+------------+---------------+-------------------------------------
postgres | 10 | 6 | ko_KR.UTF-8 | ko_KR.UTF-8 | f | t | -1 | 13322 | 1750 | 1 | 1663 |
dwarf | 16385 | 6 | ko_KR.UTF-8 | ko_KR.UTF-8 | f | t | -1 | 13322 | 1750 | 1 | 1663 |
template1 | 10 | 6 | ko_KR.UTF-8 | ko_KR.UTF-8 | t | t | -1 | 13322 | 1750 | 1 | 1663 | {=c/postgres,postgres=CTc/postgres}
template0 | 10 | 6 | ko_KR.UTF-8 | ko_KR.UTF-8 | t | f | -1 | 13322 | 1750 | 1 | 1663 | {=c/postgres,postgres=CTc/postgres}
(4개 행)
...
3. Table 생성
Table 생성 관련 Document는 여기를 참조하면 된다. 여기서는 아래와 같은 구조의 Table을 만든다고 가정해보자
star |
id (PK) name class age radius lum magnt |
일단 psql command 창에서 brown 사용자로 dwarf Database에 접속한다.
...
postgres=# \c dwarf brown
접속정보: 데이터베이스="dwarf", 사용자="brown".
dwarf=#
...
그리고 아래 Query를 실행해서 Table을 만든다. (Schema를 따로 지정하지 않았기 때문에 public Schema에 만들어 지게 된다)
...
dwarf=# CREATE TABLE star (
id integer NOT NULL,
name character varying(255),
class character varying(32),
age integer,
radius integer,
lum integer,
magnt integer,
CONSTRAINT star_pk PRIMARY KEY (id)
);
CREATE TABLE
dwarf=#
...
만들어진 결과는 '\dt'나 pg_tables를 조회하면 되는데, pg_tables를 조회할 때는 tableowner 조건을 넣어서 조회해야 한다.
...
dwarf=# \dt
릴레이션(relation) 목록
스키마 | 이름 | 종류 | 소유주
--------+------+--------+--------
public | star | 테이블 | brown
(1개 행)
...
dwarf=# SELECT * FROM pg_tables WHERE tableowner='brown';
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+-----------+------------+------------+------------+----------+-------------+-------------
public | star | brown | | t | f | f | f
(1개 행)
...
'Database > PostgreSQL' 카테고리의 다른 글
| PostgreSQL HA 구성 - 3. PGPool을 활용한 FailOver 구현 (7) | 2018.10.12 |
|---|---|
| PostgreSQL HA 구성 - 2. PGPool을 활용한 Load Balance 구현 (1) | 2018.10.05 |
| PostgreSQL HA 구성 -1. Streaming Replication (3) | 2018.09.27 |
| PostgreSQL 설정 변경 및 실행 (RHEL7 환경) (0) | 2018.09.13 |
| RHEL7에 PostgreSQL 설치 (1) | 2018.09.11 |