PostgreSQL 스키마 개념 및 활용

1.객체 구조

 - 전역 객체와 지역 객체로 나눔

 

• 전역 객체

              - 하나의 DB포트로 운영되는 한 서버를 뜻함 : 데이터베이스 정보, 테이블 스페이스, 그룹 롤, 로그인 롤

              - 이들은 각 데이터베이스 단위 지역 객체들에서 공통적으로 사용된다. 즉, 한 테이블의 소유주나 테이블

                스페이스를 지정할 때, 이 전역 객체의 값을 사용해야 한다. 달리 표현하면, 데이터베이스 단위로 똑 같

                은 이름의 물리적으로 다른 테이블스페이스를 사용할 수 없음을 의미한다.

• 지역 객체(데이터베이스)

             - 데이터베이스라는 객체 안에는 그 데이터베이스 단위로 각각 지역 객체들이 있는데, 이것은 스키마라는

               객체로 묶여진다.(Catalogs,Extensions,Schemas)

 

2. 스키마

    - PPAS에서 스키마는 DB 소속 객체(DB 접속 계정은 인스턴스 소속 객체)

    - Object 가 포함되어 있다.

    - Super User 를 제외한 모든 Loing role 과 Group role 은 Schema 접근권한이 있어야 한다.

    - Public Schema 접근은 모두 가능

    - Schema 접근 권한

        Usage : Schema 접근권한

        All : Schema접근권한 + Create Objec

• 스키마 생성

              [root@localhost ~]#su - enterprisedb

              -bash-4.1$./psql mydb

              mydb=# create schema topsql

              CREATE SCHEMA

 

3.로그인 롤

 - 전역 객체로서 모든 데이타베이스 접속 가능

 - 최초 DB접속 시 Login role 은 OS 계정과 동일함(Enterprisedb,Postgres) = sys(oracle)

 - Login role 은 전역 객체이지만 Schema 접근권한이 없다면 아무것도 할 수 없음

 - Public Schema 접근은 가능

 - Schema 가 여러개 있다면 Schema.object 로 구분하여 접근 할 수 있음

• 로그인 롤 생성

              [root@localhost ~]#su - enterprisedb

              -bash-4.1$./psql mydb

               mydb=#create role ap_admin login password 'dev1234!';

               CREATE ROLE

 

4.Login Role 생성 및 권한

• Schema 접근권한

             [root@localhost ~]#su - enterprisedb

             -bash-4.1$./psql mydb

             mydb=#create table topsql test(a int);

             CREATE TABLE

             mydb=#insert into topsql.test values(1);

             INSERT 0 1

             mydb=#grant usage on schema topsql to ap_admin;

• Object(table) 권한

            mydb=#grant select on topsql.test to ap_admin;

•  Schema,Ojbect 접근 확인

           mydb=# \c mydb ap_admin

           mydb=#./psql mydb

             password for user ap_admin;

           You are now connected to database "mydb"....

           mydb=>select * from topsql.tet

           a

         ----

           1

         (1 row)

• Schema 접근권한(ALL)

            [root@localhost ~]#su - enterprisedb

            -bash-4.1$./psql mydb

            mydb=#create table topsql.test (a int);

            CREATE TABLE

            mydb=#insert into topsql.test values(1);

            INSERT 0 1

            mydb=#grant ALL on schema topsql to ap_admin;

• Schema,Object 접근확인

           mydb=# \c mydb ap_admin

           mydb=#./psql mydb

           pssword for user ap_admin;

         You are now connected to database "mydb"....

        mydb=>create table topsql.create_table(a int);

       ERROR: permission denied for schema topsql ---> Grant usage

         mydb=>create table topsql.create_table(a int); --->Grant all

          CREATE TABLE

 

5.Group Role

  - 전역 객체로서 모든 데이타베이스 접속 가능

  - Group role 은 전역 객체이지만 Schema 접근권한이 없다면 아무것도 할 수 없다.

  - Schema 를 직접 조작하지는 않음

  - Group role 도 패스워드를 정의할 수 있음

  - Group role 에 패스워드를 정의하고 Login 권한을 주면 Login role 이 Group role의 이름으로 자동 생성된다.

  - Group role 에 패스워드만 정의하고 loing 권한을 주지 않는다면 Login role

  - Group role 에 패스워드를 주지않고 login 권한을 주면 Group role 은 drop 되고 Login role 만 생성됨

  - 이동한 Group role, Login role의 Schema,object 권한은 동일하게 유지됨

• Grop Role에 궈한주기

            [root@localhost ~]#su - enterprisedb

            -bash-4.1$./psql mydb

            mydb=#create role ap_group;

            CREATE ROLE

            mydb=#grant ALL on schema topsql to ap_admin;

            GRANT

            mydb=#grant select on topsql.test to ap_group;

            mydb=#grant ap_group to ap_admin;

• Schema,Object 접근 확인

            mydb=# \c mydb ap_admin

            mydb=#./psql mydb

            pssword for user ap_admin;

         You are now connected to database "mydb"....

         mydb=>select * from topsql.test;

           a

         ---

           1

         (1 row)

 

6.Search_path

  - SEARCH_PATH: 스키마를 생략하고 Object를 Search 할 수 있는 방법

  - Database의 기본 Search_path 변경

    alter database mydb set search_path to topsql,"$user",public;

  - 신규 Loing role 를 생성하고 search_path를 등록하면 편하게 사용할 수 있음.

• Search_path 확인

            [root@localhost ~]#su - enterprisedb

            -bash-4.1$./psql mydb

            mydb=#show search_path;

                  search_path

           --------------------

             "$user",public          ---> $user : 현재 고그인 한 role 과 같은 이름의 schema 를 의미

            mydb=#set search_path to topsql, "$user",public;

            SET

            mydb=#show search_path;

                 search_path

          --------------------

           topsql,"$user",public  --->Search 순서 : topsql -->$user -->public

 

작성자 : 황 석