Multi Schema Single Database บน PostgreSQL

Article Index

Multi Schema Single Database

Multi Schema Single Database นั้น เป็นการสร้าง database ขึ้นมาและแบ่ง database ออกเป็นส่วนๆ ที่เรียกว่า Schema แต่ละ schema มีการแบ่งแยกผู้ใช้งานกันชัดเจน เป็นการใช้งานรูปแบบหนึ่งที่มีต้องรู้จักไว้ แต่มักถูกละเลยไปเนื่องจากไม่ค่อยการแสดงวิธีการใช้งานที่เด่นชัด เราลองมาดูวิธีการใช้งานการออกแบบฐานข้อมูลรูปแบบนี้กัน

ตัวอย่างปัญหา/การใช้งาน

สมมุติว่าเราพัฒนา web application ระบบสินค้าคลังขึ้นมา ซึ่ง framework ที่ใช้ ต่อกับฐานข้อมูลเดียวตลอดการใช้งาน

            ในการออกแบบทั่วไป มักสร้างฐานข้อมูลสำหรับจัดเก็บข้อมูลของแต่ละระบบ จากนั้นติดตั้ง application ให้ใช้งานกับฐานข้อมูลนั้น เมื่อ application นั้นถูกนำไปติดตั้งใช้งานสำหรับบริษัทต่างๆ ก็เพียงสร้างฐานข้อมูลขึ้นมาใหม่ เพื่อจัดเก็บข้อมูลสำหรับแต่ละบริษัทนั้น

เราสามารถจัดทำ application เดียวที่ทำงานกับฐานข้อมูลเดียว ให้บริการกับหลายบริษัทพร้อมกัน ได้หรือไม่?

ความต้องการใหม่คือ เราต้องการติดตั้ง web application เดียวแต่ใช้ได้หลายบริษัท โดยไม่ต้องการติดตั้ง web application หลายระบบแยกตามบริษัท ซึ่งสิ้นเปลืองทรัพยากรเกินความจำเป็น

            ทั้งนี้ application เดิมที่เคยออกแบบให้จัดเก็บข้อมูลของบริษัทเดียว สามารถนำมาใช้งานได้โดยไม่ต้องปรับแก้โปรแกรมใหม่ให้จัดเก็บและทำงานกับข้อมูลหลายบริษัทในตารางเดียวกัน

ในการใช้งาน เมื่อมีผู้ใช้งาน login เข้ามา  application จะตรวจสอบว่าผู้ใช้งานนั้นสังกัดบริษัทไหน จากนั้น จะเรียกใช้ข้อมูลของบริษัทนั้น

หลักการ

ใน PostgreSQL เราสามารถที่จะแบ่งกลุ่มของ tables มาอยู่ด้วยกันเสมือนเป็น namespace เดียวกันในการอ้างอิง โดยใช้ schema ซึ่งผู้ดูแลระบบสามารถสร้าง path ในการสืบค้นข้อมูลใน tables ตาม schema ได้ คล้ายคลึงการสร้าง path ในการค้นหา files บน directories ในระบบปฏิบัติการ

            หลักสำคัญของเรื่องนี้มี 2 เรื่อง เรื่องแรก ผู้ใช้งานแต่ละคนต้องมี schema ใช้งานเป็นของตนเอง รู้ว่าจะค้นหา schema นั้นได้อย่างไร เรื่องต่อมา ผู้ใช้งานจะต้องมีสิทธิ์ในการใช้งาน objects ต่างๆ ไม่ว่าจะเป็น tables, view, functions และอื่นๆ ใน schema นั้น ซึ่งผู้ใช้งานนั้นอาจเป็นเจ้าของ objects เหล่านั้น หรือได้รับสิทธิ์การใช้งาน (grant) จากเจ้าของ objects เหล่านั้น


วิธีการ

วิธีการจัดการเรื่องนี้ใน 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