Multi Schema Single Database บน PostgreSQL

Article Index

วิธีการ

วิธีการจัดการเรื่องนี้ใน PostgreSQL ประกอบด้วย คือ role, search_path และ schema

            ใน PostgreSQL นั้น role ใช้กำหนดผู้ใช้งาน (user) ของฐานข้อมูล การใช้งาน PostgreSQL จะต้องระบุ role ที่เข้าใช้งาน ซึ่ง PostgreSQL ก็ควบคุมขอบเขตการใช้งานของ role นั้นเฉพาะ objects ที่มีสิทธิ์เท่านั้น

            เมื่อผู้ใช้งาน role ใดเข้าใช้งาน PostgreSQL จะกำหนดเส้นทางลำดับของ schema เพื่อใช้ค้นหา objects ต่างๆ ด้วย search_path โดยมี schema แรกใน search_path เป็น default schema

            และสุดท้าย schema เป็นการแบ่ง objects ต่างๆในฐานข้อมูลออกเป็นกลุ่ม เพื่อเป็นเครื่องมือในการควบคุมการใช้งาน

            เราสามารถตั้งค่า role และ search_path ในการเข้าใช้งานได้ด้วยคำสั่ง set role และ set search_path ตามลำดับ

เราสามารถตรวจสอบค่าปัจจุบันของค่าทั้งสองด้วยคำสั่ง select current_role, show search_path เมื่อมีการสร้าง objects ใหม่ในฐานข้อมูล ค่า owner ของ objects นั้น จะถูกตั้งด้วยค่าของ current_role โดยสร้างใน schema แรกที่อยู่ใน search_path ซึ่งเป็น default schema หากมีการค้นหา objects ใดๆ จะค้นหาตามลำดับ schema ที่กำหนดใน search_path

                ลองมาดูตัวอย่างในการ implement scenario ที่กล่าวข้างต้น

 

Postgres=# select current_role;                                             (1)                                        

 current_user

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

 postgres

(1 row)

postgres=# select current_user;                                

 current_user

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

 postgres

(1 row)

 

Postgres=# create role microx;                                              (2)                                  

CREATE ROLE

postgres=# create role affix;                                                 (3)                                  

CREATE ROLE

postgres=# create database inventory;                                   (4)                              

CREATE DATABASE

postgres=# grant all on database inventory to public;               (5)           

GRANT

 

            เริ่มต้น ตรวจสอบว่าทำงานด้วย user หรือ role ใด ด้วยคำสั่ง select current_user หรือ select current_role ในคำสั่ง (1)  ซึ่ง PostgreSQL ใช้ user หรือ role ในความหมายเดียวกัน ซึ่ง จากผล เราพบว่า ณ จุดนั้นทำงานด้วย user postgres

                จากนั้น สร้าง role ใหม่ที่ชื่อ microx และ affix ด้วยคำสั่ง create role ในคำสั่งที่ (2) และ (3) จากนั้นสร้างฐานข้อมูลที่ชื่อ inventory ด้วยคำสั่ง create database ใน คำสั่งที่ (4) ต่อมา อนุญาตให้ inventory database ใช้ได้ทั่วไปด้วยคำสั่ง grant ในคำสั่งที่ (5)

            มาถึงตอนนี้เรามี role และ database ที่ถูก grant ให้พร้อมใช้แล้ว

postgres=# show search_path;                                                (1)                                                       

  search_path

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

 "$user",public

(1 row)

postgres=> \c inventory;                                                          (2)                                                       

WARNING: Console code page (437) differs from Windows code page (1252)

         8-bit characters might not work correctly. See psql reference

         page "Notes for Windows users" for details.

You are now connected to database "inventory" as user "postgres".

inventory=# set role to microx;                                                  (3)                                                

SET

inventory=> create schema microx;                                              (4)                                                                               

CREATE SCHEMA

inventory=> create table stock (code varchar(10), qty int);            (5)              

CREATE TABLE

inventory=> insert into stock values('aaa',10);                               (6)                            

INSERT 0 1

inventory=>

inventory=> set role to affix;                                                        (7)                                            

SET

inventory=> create schema affix;                                                   (8)                                             

CREATE SCHEMA

inventory=> create table stock (code varchar(10), qty int);                (9)             

CREATE TABLE

inventory=> insert into stock values('bbb',20);                                 (10)                           

INSERT 0 1

 

ต่อมา ใช้คำสั่ง show search_path ตรวจสอบเส้นทางการใช้งานของแต่ละ role ในคำสั่ง (1) เราจะพบว่าค่าที่ได้คือ "$user",public มีความหมายว่า คำสั่ง sql ใดๆที่เรียกใช้งาน จะค้นหาใน “$user” schema ก่อน หากหาไม่เจอจะค้นใน public schema ต่อไป นั่นหมายความ user postgres จะค้นใน postrges, public schema ตามลำดับ

            คำสั่ง (2) เราใช้คำสั่ง \c เพื่อเชื่อมต่อกับฐานข้อมูล inventory จากนั้น ใช้คำสั่ง set role เพื่อตั้งค่า role ที่ใช้ในการติดต่อกับฐานข้อมูล ในคำสั่งที่ (3) ผลจากคำสั่ง set role ใน (3) ค่า search_path จะเปลี่ยนไปเป็น microx, public เมื่อสั่งคำสั่ง (4) create schema microx จะทำให้ schema ใหม่ที่ได้มี microx เป็นเจ้าของ (owner)

เมื่อสั่งคำสั่ง (5) create table stock ตารางที่สร้างมี microx เป็นเจ้าของ ตาม role ของผู้สั่งคำสั่ง และสร้างใน microx schema ตามค่าของ search_path และคำสั่งที่ (6) เป็นการสร้างข้อมูลลงในตาราง stock

คำสั่ง (7) – (10) เป็นใช้ affix role การสร้างตาราง stock พร้อมข้อมูลลงใน affix schema แบบเดียวกันคำสั่ง (3) – (6)

inventory=> set role to microx;                                                        (1)                                                 

SET

inventory=> select * from stock;                                                       (2)                                             

 code | qty

------+-----

 aaa  |  10

(1 row)

 

inventory=> set role to affix;                                                             (3)                                                 

SET

inventory=> select * from stock;                                                        (4)                                                

 code | qty

------+----

 bbb  |  20

(1 row)

 

            คำสั่ง (1) – (4) เป็นการตรวจสอบผล ด้วยการเปลี่ยน role ก่อนสั่งคำสั่ง select การตั้งค่า role จะทำให้ search_path เปลี่ยนไปตาม role ซึ่งมีผลต่อคำสั่ง select ที่สั่ง

            สุดท้าย กลับไปที่ปัญหาเริ่มต้นของเรา หากเราต้องการให้ web application สามารถเรียกใช้ฐานข้อมูลตาม schema ของผู้ใช้งาน จะทำได้อย่างไร

            วิธีทำคือ สร้างตาราง login_users ใน public schema เพื่อเก็บว่าผู้ใช้งานที่ login เข้าใช้ web application แต่ละคนสังกัดบริษัทไหน ใช้ schema อะไร เมื่อผู้ใช้งาน login เข้ามาครั้งแรก ให้ระบบตรวจสอบว่า ผู้ใช้งานนั้นสังกัดบริษัทไหน เมื่อทราบแล้ว ก็ set role และ search_path ให้ตรงตามที่ต้องการ 


Print