SaaSͷ Ϛϧνςφϯτ࣮ݱํ๏ @neko-neko

ࠓ೔࿩͢͜ͱ • SaaSͷιϑτ΢ΣΞఏڙํ๏ • Ϛϧνςφϯτͷ࣮ݱํ๏

ࠓ೔࿩͢͜ͱ • SaaSͷιϑτ΢ΣΞఏڙํ๏ • Ϛϧνςφϯτͷ࣮ݱํ๏

SaaSʹ͓͚Διϑτ΢ΣΞ ఏڙํ๏͸2ͭ

SaaSͷιϑτ΢ΣΞఏڙํ๏ • γϯάϧςφϯτ • Ϛϧνςφϯτ • ΞϓϦέʔγϣϯڞ༗/DB෼ׂܕ • ΞϓϦέʔγϣϯ/DBڞ༗ܕ

SaaSͷιϑτ΢ΣΞఏڙํ๏ • γϯάϧςφϯτ • Ϛϧνςφϯτ • ΞϓϦέʔγϣϯڞ༗/DB෼ׂܕ • ΞϓϦέʔγϣϯ/DBڞ༗ܕ

γϯάϧςφϯτͬͯʁ

ΞϓϦέʔγϣϯΛݸผͷ؀ڥ Ͱར༻͢ΔϞσϧ

• ސ٬ຖʹސ٬ઐ༻ͷαʔϏεͱDBΛ༻ҙ͢Δ • ઐ༗؀ڥΛ༻ҙ͢ΔͷͰߴίετ • ϥϯχϯάίετɺӡ༻ίετͰࢮΜͰ͠·͏

SaaSͷιϑτ΢ΣΞఏڙํ๏ • γϯάϧςφϯτ • Ϛϧνςφϯτ • ΞϓϦέʔγϣϯڞ༗/DB෼ׂܕ • ΞϓϦέʔγϣϯ/DBڞ༗ܕ

Ϛϧνςφϯτͬͯʁ

ΞϓϦέʔγϣϯΛಉ͡؀ڥͰڞ༗ར༻͢Δ Ϟσϧ

• ෳ਺ͷސ٬͕1ͭͷ؀ڥΛڞ༻͢Δ • γϯάϧςφϯτͱൺֱͯ͠ϥϯχϯάίε τ΋ӡ༻ίετ΋άοͱ཈͑ΒΕΔ • ੈͷSaaS͸΄ͱΜͲ͜Ε

ϚϧνςφϯτΞʔΩςΫνϟ Ҿ༻ݩ: https://www.ibm.com/developerworks/jp/cloud/library/cl-multitenantsaas/index.html

SaaSͷιϑτ΢ΣΞఏڙํ๏ • γϯάϧςφϯτ • Ϛϧνςφϯτ • ΞϓϦέʔγϣϯڞ༗/DB෼ׂܕ • ΞϓϦέʔγϣϯ/DBڞ༗ܕ

ΞϓϦέʔγϣϯڞ༗ɺDB෼ׂܕ

• ϝϦοτ • ؅ཧָ͕ • ༰қʹ৘ใ࿙͍͑Λ๷͙ͨΊͷϑΟϧλ৚݅Λ૊ΊΔ • σϝϦοτ • ࿦ཧతʹDBΛ෼ׂ͍ͯ͠Δέʔε(databaseΛސ٬ຖʹ༻ҙ͢Δํ๏΍εΩʔϚΛސ٬ ຖʹ༻ҙ͢Δํ๏) • ސ٬਺ͷ૿ՃͰςʔϒϧ਺͕૿͑ΔͨΊDBϚΠάϨʔγϣϯ࣮ߦ͕࣌ؒ૿͑Δ • ςφϯτຖʹεΩʔϚ͕ҟͳΔλΠϛϯά͕௕͍࣌ؒى͖ಘΔ • ສ͕ҰϚΠάϨʔγϣϯ͕ࣦഊͨ࣌͠ͷϦΧόϦ͕खؒ

SaaSͷιϑτ΢ΣΞఏڙํ๏ • γϯάϧςφϯτ • Ϛϧνςφϯτ • ΞϓϦέʔγϣϯڞ༗/DB෼ׂܕ • ΞϓϦέʔγϣϯ/DBڞ༗ܕ

ΞϓϦέʔγϣϯɺDBڞ༗ܕ

• ϝϦοτ • γϯϓϧΏ͑ʹ؅ཧָ͕ • γεςϜશମͷςʔϒϧ૯਺΋཈͑ΒΕΔ • σϝϦοτ • DB෼ׂܕͱൺ΂ͯ৘ใ࿙͍͑Λ๷͙ͨΊͷϑΟϧλΛ૊ΉͷΛߟྀ͠ͳ͘ ͯ͸͍͚ͳ͍ • ୯७ʹݸʑͷॲཧʹରͯ͠ϑΟϧλ৚݅Λ૊ΈࠐΉͱɺ࿙Ε͕ى͖ͨ࣌ ʹॏେͳηΩϡϦςΟϗʔϧͱͳΓಘΔ • ސ٬਺ͷ૿ՃͰγεςϜશମͷϨΠςϯγ͕ѱԽ͢Δ

͔͜͜Β͕ࠓ೔ͷຊ୊

ࠓ೔࿩͢͜ͱ • SaaSͷιϑτ΢ΣΞఏڙํ๏ • Ϛϧνςφϯτͷ࣮ݱํ๏

ΞϓϦέʔγϣϯڞ༗/ DB෼ׂܕΛ࣮ݱ͢Δ৔߹

ΞϓϦέʔγϣϯڞ༗ɺDB෼ׂܕ • Ҋ1: ςφϯτຖʹεΩʔϚ͔databaseΛ෼ ׂ͢Δ • Ҋ2: ϝΠϯςʔϒϧΛ༻ҙ͠ɺςφϯτຖ ʹViewΛ༻ҙ͢Δ

ΞϓϦέʔγϣϯڞ༗ɺDB෼ׂܕ • Ҋ1: ςφϯτຖʹεΩʔϚ͔databaseΛ෼ ׂ͢Δ • Ҋ2: ϝΠϯςʔϒϧΛ༻ҙ͠ɺςφϯτຖ ʹViewΛ༻ҙ͢Δ

ςφϯτຖʹεΩʔϚ͔DB෼ׂ CREATE SCHEMA tenant1; CREATE TABLE tenant1.blogs ( id bigserial NOT NULL PRIMARY KEY, title text NOT NULL, created_at timestamp with time zone NOT NULL, updated_at timestamp with time zone NOT NULL, );

ςφϯτຖʹεΩʔϚ͔DB෼ׂ CREATE SCHEMA tenant1; CREATE TABLE tenant1.blogs ( id bigserial NOT NULL PRIMARY KEY, title text NOT NULL, created_at timestamp with time zone NOT NULL, updated_at timestamp with time zone NOT NULL, ); ςφϯτຖʹεΩʔϚΛ༻ҙͯ͠ ςʔϒϧΛ࡞੒

ΞϓϦέʔγϣϯڞ༗ɺDB෼ׂܕ • Ҋ1: ςφϯτຖʹεΩʔϚ͔databaseΛ෼ ׂ͢Δ • Ҋ2: ϝΠϯςʔϒϧΛ༻ҙ͠ɺςφϯτຖ ʹViewΛ༻ҙ͢Δ

ςφϯτຖʹViewΛ༻ҙ͢Δ CREATE TABLE public.blogs ( id bigserial NOT NULL PRIMARY KEY, tenant_id bigint NOT NULL, title text NOT NULL, created_at timestamp with time zone NOT NULL, updated_at timestamp with time zone NOT NULL, ); CREATE SCHEMA tenant1; CREATE VIEW tenant1.blogs AS SELECT * FROM public.blogs WHERE tenant_id::text = current_setting('application_name');

ςφϯτຖʹViewΛ༻ҙ͢Δ CREATE TABLE public.blogs ( id bigserial NOT NULL PRIMARY KEY, tenant_id bigint NOT NULL, title text NOT NULL, created_at timestamp with time zone NOT NULL, updated_at timestamp with time zone NOT NULL, ); CREATE SCHEMA tenant1; CREATE VIEW tenant1.blogs AS SELECT * FROM public.blogs WHERE tenant_id::text = current_setting('application_name'); ςφϯτຖʹεΩʔϚΛ༻ҙ͢Δͷ͸มΘΒͳ ͍͕ɺ7JFXΛ࢖ͬͯՄࢹੑͷ੍ݶΛߦ͏ɻ ςφϯτͷૢ࡞͸7JFXͷΈΛ௨ͯ͡$36%͢Δ͜ ͱͰ҆શʹσʔλΛѻ͑Δ

ΞϓϦέʔγϣϯ/DBڞ༗ܕ Λ࣮ݱ͢Δ৔߹

ΞϓϦέʔγϣϯ/DBڞ༗ܕ • Ҋ1: ۪௚ʹςφϯτͷϦιʔε͔ϦΫΤετ ຖʹνΣοΫ • Ҋ2: ςφϯτຖͷViewΛ༻ҙ͢Δ • Ҋ3: RLS(Row Level Security)Λ࢖͏

ΞϓϦέʔγϣϯ/DBڞ༗ܕ • Ҋ1: ۪௚ʹςφϯτͷϦιʔε͔ϦΫΤετ ຖʹνΣοΫ • Ҋ2: ςφϯτຖͷViewΛ༻ҙ͢Δ • Ҋ3: RLS(Row Level Security)Λ࢖͏

ςφϯτຖʹཁٻϦΫΤετ͕ ਖ਼͍͠είʔϓ͔֬ೝ id := req.params(“id”) if !model.validScope(id) { return permissionDenied } // something to do…

• ͦͩ͘Δ͍ • ݱ࣮͸͜Μͳγϯϓϧʹ͸ऩ·Βͳ͍ • ສ͕Ұ࿙Ε͕͋ͬͨ৔߹ɺॏେͳηΩϡϦςΟ ϗʔϧʹͳΔ • ਓ͸ϛε͢Δ΋ͷɻݕग़࿙Ε͕ݕ஌ͮ͠Β͍(ςε τέʔε͔Β΋࿙ΕΔՄೳੑ͕͋Δ)

ΞϓϦέʔγϣϯ/DBڞ༗ܕ • Ҋ1: ۪௚ʹςφϯτͷϦιʔε͔ϦΫΤετ ຖʹνΣοΫ • Ҋ2: ςφϯτຖͷViewΛ༻ҙ͢Δ • Ҋ3: RLS(Row Level Security)Λ࢖͏

ςφϯτຖʹViewΛ༻ҙ͢Δ CREATE TABLE public.blogs ( id bigserial NOT NULL PRIMARY KEY, tenant_id bigint NOT NULL, title text NOT NULL, created_at timestamp with time zone NOT NULL, updated_at timestamp with time zone NOT NULL, ); CREATE VIEW public.tenant1_blogs AS SELECT * FROM public.blogs WHERE tenant_id::text = current_setting('application_name');

• UPDATE/DELETE࣌͸Viewʹରͯ͠ߦ͏ • INSERT͸௚઀େݩͷςʔϒϧʹରͯ͠ߦ͏ • ސ٬ຖʹViewΛ༻ҙ͢Δ͜ͱʹ͸ͳΔ͕ɺ࣮ ૷͸ָͪΜ

ΞϓϦέʔγϣϯ/DBڞ༗ܕ • Ҋ1: ۪௚ʹςφϯτͷϦιʔε͔ϦΫΤετ ຖʹνΣοΫ • Ҋ2: ςφϯτຖͷViewΛ༻ҙ͢Δ • Ҋ3: RLS(Row Level Security)Λ࢖͏

RLSͬͯʁ

Row Level Security • ߦ୯ҐηΩϡϦςΟ • ߦຖʹϨίʔυͷࢀর/ૠೖ/ߋ৽/࡟আΛ੍ݶ͢Δ͜ͱ͕ग़དྷΔ • ϙϦγʔʹϚον͠ͳ͍৔߹
 ࢀর: ֘౰ͷϨίʔυ͕ฦ٫͞Εͳ͍
 ͦΕҎ֎: ࣮ߦग़དྷͳ͍ • ϙϦγʔ͸SUPERUSER΋͘͠͸BYPASSRLS͕Ξλον͞Εͯ ͍Δϩʔϧͷ৔߹ແࢹ͞ΕΔ

ࢼͯ͠ΈΔ CREATE TABLE public.blogs ( id bigserial NOT NULL PRIMARY KEY, tenant_id bigint NOT NULL, title text NOT NULL ); CREATE POLICY blogs_policy ON public.blogs FOR ALL TO app_user USING (tenant_id::text = current_setting(‘application_name', FALSE)); ALTER TABLE public.blogs ENABLE ROW LEVEL SECURITY; INSERT INTO public.blogs (tenant_id, title) VALUES (1, ‘TEST1’); INSERT INTO public.blogs (tenant_id, title) VALUES (1, ‘TEST2’); INSERT INTO public.blogs (tenant_id, title) VALUES (2, ‘TEST3’); INSERT INTO public.blogs (tenant_id, title) VALUES (2, ‘TEST4’); INSERT INTO public.blogs (tenant_id, title) VALUES (3, ‘TEST5’);

ಈ࡞ΛݟΔ(SELECT) -- superuserͷ৔߹ select * from blogs; id | tenant_id | title ----+-----------+------- 1 | 1 | TEST1 2 | 1 | TEST2 3 | 2 | TEST3 4 | 2 | TEST4 5 | 3 | TEST5 -- normal userͷ৔߹ select * from blogs; id | tenant_id | title ----+-----------+------- (0 rows) -- normal userͷ৔߹(application_name: 1) SET application_name = '1'; select * from blogs; id | tenant_id | title ----+-----------+------- 1 | 1 | TEST1 2 | 1 | TEST2 (2 rows)

ಈ࡞ΛݟΔ(INSERT) -- superuserͷ৔߹ ໰୊ແ͘ೖΔ -- normal userͷ৔߹ INSERT INTO public.blogs (tenant_id, title) VALUES (1, 'TEST1'); ERROR: new row violates row-level security policy for table "blogs" -- normal userͷ৔߹(application_name: 1) SET application_name = ‘1'; INSERT INTO public.blogs (tenant_id, title) VALUES (1, 'TEST1'); INSERT 0 1 -- normal userͷ৔߹(application_name: 2) INSERT INTO public.blogs (tenant_id, title) VALUES (1, 'TEST1'); ERROR: new row violates row-level security policy for table "blogs"

ಈ࡞ΛݟΔ(UPDATE) -- superuserͷ৔߹ UPDATE blogs SET title='UPDATE' WHERE id = 1; select * from blogs where id = 1; id | tenant_id | title ----+-----------+-------- 1 | 1 | UPDATE -- normal userͷ৔߹ UPDATE blogs SET title='UPDATE' WHERE id = 1; UPDATE 0 <- Ξοϓσʔτ͞Ε͍ͯͳ͍ -- normal userͷ৔߹(application_name: 1) SET application_name = '1'; UPDATE blogs SET title=‘UPDATE' WHERE id = 1; UPDATE 1 <- Ξοϓσʔτ͞Ε͍ͯΔ

ಈ࡞ΛݟΔ(DELETE) -- superuserͷ৔߹ ໰୊ͳ͘࡟আग़དྷΔ -- normal userͷ৔߹ DELETE FROM blogs WHERE id = 1; DELETE 0 <- DELETE͞Ε͍ͯͳ͍ -- normal userͷ৔߹(application_name: 1) SET application_name = ‘1’; DELETE FROM blogs WHERE id = 1; DELETE 1 <- DELETE͞Ε͍ͯΔ

RLS with go

લఏ • ֤ςʔϒϧʹtenant_idΛ࣋ͨͤΔӡ༻ʹ͢Δ • ސ٬ຖʹϩʔϧΛ࡞੒͢Δӡ༻ʹ͸͠ͳ͍ • 1ͭͷεΩʔϚͰαʔϏεͷςʔϒϧ؅ཧΛ ߦ͏

Table CREATE TABLE public.blogs ( id bigserial NOT NULL PRIMARY KEY, tenant_id bigint NOT NULL, title text NOT NULL, created_at timestamp with time zone NOT NULL, updated_at timestamp with time zone NOT NULL, ); CREATE TABLE public.posts ( id bigserial NOT NULL PRIMARY KEY, tenant_id bigint NOT NULL, title text NOT NULL, content text NOT NULL, created_at timestamp with time zone NOT NULL, updated_at timestamp with time zone NOT NULL, ); -- ֎෦Ωʔͷఆٛ͸লུ -- tenant has many groups͕͋ΔલఏͰάϧʔϓͷॴଐϝϯόʔΛ؅ཧ͢Δςʔϒϧ CREATE TABLE public.group_users ( id bigserial NOT NULL PRIMARY KEY, tenant_id bigint NOT NULL, group_id bigint NOT NULL, user_id bigint NOT NULL, created_at timestamp with time zone NOT NULL, updated_at timestamp with time zone NOT NULL, );

Policy — ϙϦγʔͷఆٛ CREATE POLICY blogs_policy ON public.blogs FOR ALL TO app_user USING (tenant_id::text = current_setting(‘application_name', FALSE)); CREATE POLICY posts_policy ON public.posts FOR ALL TO app_user USING (tenant_id::text = current_setting('application_name', FALSE)); CREATE POLICY group_users_policy ON public.group_users FOR ALL TO app_user USING (tenant_id::text = current_setting('application_name', FALSE)); WITH CEHCK ( group_id = (SELECT id FROM groups WHERE id = group_id) AND user_id = (SELECT id FROM users WHERE id = user_id) ); —- ϙϦγʔͷ༗ޮԽ ALTER TABLE public.blogs ENABLE ROW LEVEL SECURITY; ALTER TABLE public.posts ENABLE ROW LEVEL SECURITY; ALTER TABLE public.group_users ENABLE ROW LEVEL SECURITY;

Go

Middleware(Τϥʔൈ͖) func ExampleMultitenantMiddleware(db *sql.DB) func(next http.Handler) http.Handler { return func(next http.Handler) http.Handler { fn := func(w http.ResponseWriter, r *http.Request) { ctx := r.Context() // context͔ΒݱࡏͷςφϯτIDΛऔಘͯ͠ // ίωΫγϣϯϓʔϧͷ઀ଓΛઐ༗͢Δ tenantID := context.CurrentTenantID(ctx) conn, _ := db.Conn(ctx) defer conn.Close() // ηογϣϯ͕ੜ͖͍ͯΔؒ༗ޮʹͳΔม਺ΛDB΁ηοτ // Ҏ߱ͷhandler͸ઐ༗தͷίωΫγϣϯΛ࢖ͬͯΫΤϦΛൃߦ͢Δ conn.ExecContext(ctx, fmt.Sprintf(“SET application_name=%s;”, tenantID)) next.ServeHTTP(w, r.WithContext(context.WithCurrentConnection(ctx, conn))) } return http.HandleFunc(fn) } }

·ͱΊ • RLSΛ࢖͏͜ͱͰDBͷ؅ཧ͕ͦ͜·Ͱ൥ࡶʹͳΒͣ Ϛϧνςφϯτͷ࣮ݱ͕ग़དྷΔ • ϓϩμΫτॳظϑΣʔζͰ͋Ε͹ɺ͜ΕͰे෼྇͛ Δ • Ϩίʔυ਺͕૿͍͑ͯͬͨΒ֤ςʔϒϧͷ tenant_idΛ࢖ͬͯγϟʔσΟϯάΛߦ͍ෛՙରࡦ