Database/PostgreSQL

PostgreSQL 사용자 추가 및 DB/ Table 생성

갈색왜성 2018. 9. 18. 16:47
반응형



지난 포스팅 'PostgreSQL 설정 변경 및 실행 (RHEL7 환경)' 에 이어 사용자 추가와 DB 생성을 해보려 한다.


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개 행)
...