ã¯ããã« ltreeãšã¯ ltreeå ltreeã®æäœ æŽ»çšæ³ 1. æ¿èªãããŒã®æ§ç¯ äºåæºå ããŒãã«äœæ ããŒã¿è¿œå 2. ããŒãã«ã«çްããã¢ã¯ã»ã¹å¶åŸ¡ãããã äºåæºå ltreeã®æå¹å ããŒãã«äœæ ããªã·ãŒäœæ è¡ã»ãã¥ãªãã£ããªã·ãŒã®æå¹å ããªã·ãŒã®èšå® ããŒã¿ã远å ãŠãŒã¶ãŒäœæ 詊ã ãŸãšã ã¯ããã« ããã«ã¡ã¯ïŒ ãšã³ãžãã¢2幎ç®ã®TKDSã§ãïŒ ä»åã¯ltreeã«ã€ããŠèª¿ã¹ããã®æŽ»çšæ³ãèããŠã¿ãŸããã ltreeã«ã€ããŠãltreeã®æŽ»çšæ³ã®2æ®µæ§æã§ãã ltreeãšã¯ éå±€ããªãŒæ§é ãæš¡ããæ§é ãæ ŒçŽããæ©èœãæäŸãã æ¡åŒµæ©èœ ã§ãã 詳ãã㯠ããã¥ã¡ã³ã ãã¿ãŠãã ããã ltreeå éå±€ããªãŒæ§é ã衚ãåã§ãã äŸïŒ`Company.Department.Team1 ãããåºåãã§å€§æåå°æåã¯åºå¥ããªãããã§ãã åããŒã¿ã¯ã©ãã«ãšåŒã³ãŸãïŒäžèšã§ã®CompanyãDepartmentãTeamïŒã ltreeã®æäœ ãã䜿ããããªæäœã ãïŒã€æããŸãã ltree @> ltree : 巊蟺ã®åŒæ°ãå³èŸºã®èŠªèŠçŽ ïŒãåãïŒãã©ãã äŸïŒ SELECT name FROM organization WHERE path @> 'Top.IT.Software'; ããŒãã«ã®pathãTop.IT.Softwareã®èŠªã«è©²åœããèŠçŽ ããã¹ãŠæ¢ããŸãã ltree <@ ltree : 巊蟺ã®åŒæ°ãå³èŸºã®åèŠçŽ ïŒãåãïŒãã©ãã äŸïŒ SELECT name FROM organization WHERE path <@ 'Top.IT'; ããŒãã«ã®pathãTop.ITãèŠªã«æã€èŠçŽ ãæ¢ããŸãã ãã®SELECTæã§ã¯ãTop.ITãèŠªã«æã€èŠçŽ ããã¹ãŠæ¢ããŸãã ~ :äžèŽãããã¹ã®æ€çŽ¢ å³èŸºã«æå®ãããã¹ã«äžèŽããltreeãæ¢ããŸãã SELECT name FROM organization WHERE path ~ ' Top.IT.* ' ; SELECT name FROM organization WHERE path ~ ' Top.I*.* ' ; æŽ»çšæ³ 2çš®é¡ã»ã©æŽ»çšäŸãèããŠã¿ãŸããã 1. æ¿èªãããŒã®æ§ç¯ ltreeã䜿ã£ãŠãæ¿èªãããŒã«äœ¿ããããŒãã«ãæ§ç¯ããŠã¿ãŸãã äºåæºå compose. yaml ces : db : image : postgres:16.4-bullseye container_name : db environment : POSTGRES_USER : postgres POSTGRES_DB : postgres POSTGRES_PASSWORD : postgres ports : - "127.0.0.1:5432:5432" volumes : - db_data:/var/lib/postgresql/data - ./init.sql:/docker-entrypoint-initdb.d/init.sql healthcheck : test : [ "CMD-SHELL" , "pg_isready -U postgres -d postgres" ] interval : 30s timeout : 10s retries : 5 start_period : 10s volumes : db_data : init. sql -- ltreeæ¡åŒµã®æå¹å CREATE EXTENSION IF NOT EXISTS ltree; ããŒãã«äœæ CREATE TABLE approval_flow ( id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, flow_path ltree UNIQUE , -- éå±€æ§é ã衚çŸããããã®ltreeã«ã©ã status text, -- åæ¿èªã¹ãããã®ã¹ããŒã¿ã¹ãæ ŒçŽ approver text -- æ¿èªè
ïŒãŠãŒã¶ãŒIDãååïŒ ); ããŒã¿è¿œå INSERT INTO approval_flow (flow_path, status, approver) VALUES ( ' 1.approver.userA ' , ' open ' , ' userA ' ), ( ' 2.approver.userB ' , ' pending ' , ' userB ' ), ( ' 2.approver.userC ' , ' pending ' , ' userC ' ), ( ' 3.approver.userD ' , ' closed ' , ' userD ' ); ãããŸã§ã§ããŒã¿æå
¥ãŸã§ã§ããŸããã ã§ã¯ã2ã®æ¿èªè
ã§pendingã®ã²ãšãæ€çŽ¢ããŠã¿ãŸãããã SELECT approver FROM approval_flow WHERE flow_path ~ ' 2.approver.* ' AND status = ' pending ' ; userCã approved ã«ããŠã¿ãŸãããã UPDATE approval_flow SET status = ' approved ' WHERE flow_path = ' 2.approver.userC ' ; 1ãŠãŒã¶ãŒæ€çŽ¢çµæããæ¶ããŠãã®ãããããŸãã éå±€æ§é ã衚çŸã§ããã®ã§ã1åã§æ¿èªãå¿
èŠãªäººã管çã§ããŠäŸ¿å©ã§ãã 2. ããŒãã«ã«çްããã¢ã¯ã»ã¹å¶åŸ¡ãããã æ¬¡ã«æŽ»çšäŸïŒã§ãã ããšããšèããŠãã®ã¯ãã®äœ¿ãæ¹ã§ããã ã¹ããŒã ããã现ãããã°ã«ãŒããåºåã£ãŠã¢ã¯ã»ã¹å¶åŸ¡ã§ããªãããªãšèããããšããããŸããã ãã®ãšãã«ã調ã¹ãŠã¿ã€ããã®ãéå±€æ§é ãæ±ãltreeã§ããã ãã®æŽ»çšæ¹æ³ã§ã¯ãltreeã§ã°ã«ãŒããäœãã Row Level Security (è¡ã»ãã¥ãªãã£ããªã·ãŒ) ã®æå¹åã§åç
§åäœãå¶éããŠã¢ã¯ã»ã¹å¶åŸ¡ãå®çŸããŸãã ããŒãã«ã®é¢é£ã®ã€ã¡ãŒãžã¯ä»¥äžã®å³ã§ãã åºæ¬çã«ãã¹ãŠã®ããŒãã«ãããã³ãIDã§çŽä»ããŠãäºæ
ã£ãŠããã³ãå€ã®ããŒã¿ãåç
§ããªãããã«ããŸããã ã§ã¯ãå®éã«ç°å¢ãæ§ç¯ããŠããŸãã äºåæºå 1ã®ãšããšåãã§ãã docker compose down -vãdocker compose up ã§ç°å¢äœãçŽããŠãããŸããã docker exec -it db psql -U postgres ã§ãã°ã€ã³ããŸãã ltreeã®æå¹å -- ltreeæ¡åŒµã®æå¹å CREATE EXTENSION IF NOT EXISTS ltree; ã€ãã§ã«test ã¹ããŒã ãäœã£ãŠåç
§ããããã«ããŠãããŸãã ltreeã®èšå®ã¯public ã¹ããŒã ã«ã€ã³ã¹ããŒã«ããããããªã®ã§ãpublicã远å ããŠãããŸãã publicãå«ããªããšltreeãåç
§ã§ãããltreeåããªããšã©ãŒã«ãªããŸãã CREATE SCHEMA test; set search_path to test, public ; ããŒãã«äœæ tenantããŒãã« CREATE TABLE test.tenants ( id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name TEXT NOT NULL ); usersããŒãã« CREATE TABLE test.users ( id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, username TEXT NOT NULL , email TEXT NOT NULL , tenant_id BIGINT NOT NULL , CONSTRAINT fk_tenant FOREIGN KEY (tenant_id) REFERENCES test.tenants(id) ); rolesããŒãã« ããŒã«ã®ååãèšå®ããããŒãã« CREATE TABLE test.roles ( id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name TEXT NOT NULL , tenant_id BIGINT NOT NULL , CONSTRAINT fk_tenant FOREIGN KEY (tenant_id) REFERENCES test.tenants(id), CONSTRAINT unique_role_name_per_tenant UNIQUE (name, tenant_id) ); role_permissionsããŒãã«ïŒããŒã«ã«çŽã¥ãæš©éãèšå®ããããŒãã« ltreeã§ã¢ã¯ã»ã¹å¶éãèšå®ããŸãã CREATE TABLE test.role_permissions ( id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, role_id BIGINT NOT NULL , access_ltree LTREE NOT NULL , tenant_id BIGINT NOT NULL , CONSTRAINT fk_role FOREIGN KEY (role_id) REFERENCES test.roles(id), CONSTRAINT fk_tenant FOREIGN KEY (tenant_id) REFERENCES test.tenants(id) ); user_rolesããŒãã«ïŒãŠãŒã¶ãŒãšããŒã«ã®çŽä»ãã管çããããŒãã« CREATE TABLE test.user_roles ( id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, user_id BIGINT NOT NULL , role_name TEXT NOT NULL , tenant_id BIGINT NOT NULL , CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES test.users(id), CONSTRAINT fk_tenant FOREIGN KEY (tenant_id) REFERENCES test.tenants(id) ); documentsããŒãã«ïŒã¢ã¯ã»ã¹å¶éããããŒã¿ãæå
¥ããããŒãã« CREATE TABLE test.documents ( id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, title TEXT NOT NULL , content TEXT NOT NULL , tenant_id BIGINT NOT NULL , access_ltree LTREE NOT NULL , CONSTRAINT fk_tenant FOREIGN KEY (tenant_id) REFERENCES test.tenants(id) ); ãã¹ãŠäœæãããããŒãã«äžèЧã確èªããŠã¿ãŸã \dt test.* ããªã·ãŒäœæ ã¢ã¯ã»ã¹å¶åŸ¡ãå®çŸããããã®ã«ãŒã«ãäœã£ãŠãããŸãã è¡ ã»ãã¥ãªãã£ããªã·ãŒ ã®æå¹å ALTER TABLE test.documents ENABLE ROW LEVEL SECURITY; ALTER TABLE test.documents FORCE ROW LEVEL SECURITY; æå¹ã«ãªã£ãŠããã確èªããŸãã SELECT relrowsecurity, relforcerowsecurity FROM pg_class WHERE relname = ' documents ' ; ããªã·ãŒã®èšå® 詳ãã㯠ããã¥ã¡ã³ã ãã¿ãŠãã ãã CREATE POLICY tenant_rbac_ltree_policy ON test.documents USING ( EXISTS ( SELECT 1 FROM test.user_roles ur JOIN test.roles r ON ur.role_name = r.name AND ur.tenant_id = r.tenant_id JOIN test.role_permissions rp ON r.id = rp.role_id WHERE ur.user_id = current_setting( ' session.authorization_user_id ' )::BIGINT AND rp.tenant_id = test.documents.tenant_id AND rp.access_ltree @> test.documents.access_ltree ) ); CREATE POLICY tenant_rbac_ltree_policy_insert_update ON test.documents WITH CHECK ( EXISTS ( SELECT 1 FROM test.user_roles ur JOIN test.roles r ON ur.role_name = r.name AND ur.tenant_id = r.tenant_id JOIN test.role_permissions rp ON r.id = rp.role_id WHERE ur.user_id = current_setting( ' session.authorization_user_id ' )::BIGINT AND rp.tenant_id = test.documents.tenant_id AND rp.access_ltree @> test.documents.access_ltree ) ); session.authorization_user_idãèšå®ããããšã§å®éã®ã¢ã¯ã»ã¹å¶åŸ¡ãå®çŸããŸãã ããŒã¿ã远å ããã³ãããŒã¿ã®æå
¥ INSERT INTO tenants (name) VALUES ( ' Tenant A ' ); INSERT INTO tenants (name) VALUES ( ' Tenant B ' ); ããŒã«ããŒã¿ã®æå
¥ -- Tenant Aã®ããŒã« INSERT INTO roles (name, tenant_id) VALUES ( ' Admin ' , 1 ); INSERT INTO roles (name, tenant_id) VALUES ( ' User ' , 1 ); -- Tenant Bã®ããŒã« INSERT INTO roles (name, tenant_id) VALUES ( ' Admin ' , 2 ); INSERT INTO roles (name, tenant_id) VALUES ( ' User ' , 2 ); ããŒã«æš©éã®æå
¥ -- Tenant Aã®AdminããŒã«ã®æš©é (Saleséšéå
šäœã«ã¢ã¯ã»ã¹å¯èœ) INSERT INTO role_permissions (role_id, access_ltree, tenant_id) VALUES ( 1 , ' TenantA.Sales ' , 1 ); -- Admin role for Tenant A (Saleséšéå
šäœ) -- Tenant Aã®UserããŒã«ã®æš©é (Saleséšéã®Team 2ã®ã¿ã¢ã¯ã»ã¹å¯èœ) INSERT INTO role_permissions (role_id, access_ltree, tenant_id) VALUES ( 2 , ' TenantA.Sales.Team2 ' , 1 ); -- User role for Tenant A (Team 2ã®ã¿) -- Tenant Bã®AdminããŒã«ã®æš©é (Marketingéšéå
šäœã«ã¢ã¯ã»ã¹å¯èœ) INSERT INTO role_permissions (role_id, access_ltree, tenant_id) VALUES ( 3 , ' TenantB.Marketing ' , 2 ); -- Admin role for Tenant B (Marketingéšéå
šäœ) -- Tenant Bã®UserããŒã«ã®æš©é (Marketingéšéã®Team 2ã®ã¿ã¢ã¯ã»ã¹å¯èœ) INSERT INTO role_permissions (role_id, access_ltree, tenant_id) VALUES ( 4 , ' TenantB.Marketing.Team2 ' , 2 ); -- User role for Tenant B (Team 2ã®ã¿) ãŠãŒã¶ãŒããŒã¿ã®æå
¥ -- Tenant Aã®ãŠãŒã¶ãŒ INSERT INTO users (username, email, tenant_id) VALUES ( ' user_a1 ' , ' user_a1@example.com ' , 1 ); INSERT INTO users (username, email, tenant_id) VALUES ( ' user_a2 ' , ' user_a2@example.com ' , 1 ); -- Tenant Bã®ãŠãŒã¶ãŒ INSERT INTO users (username, email, tenant_id) VALUES ( ' user_b1 ' , ' user_b1@example.com ' , 2 ); INSERT INTO users (username, email, tenant_id) VALUES ( ' user_b2 ' , ' user_b2@example.com ' , 2 ); ãŠãŒã¶ãŒãšããŒã«ã®çŽä»ã -- Tenant Aã®ãŠãŒã¶ãŒã«ããŒã«ãå²ãåœãŠ INSERT INTO user_roles (user_id, role_name, tenant_id) VALUES ( 1 , ' Admin ' , 1 ); -- user_a1 is Admin in Tenant A INSERT INTO user_roles (user_id, role_name, tenant_id) VALUES ( 2 , ' User ' , 1 ); -- user_a2 is User in Tenant A -- Tenant Bã®ãŠãŒã¶ãŒã«ããŒã«ãå²ãåœãŠ INSERT INTO user_roles (user_id, role_name, tenant_id) VALUES ( 3 , ' Admin ' , 2 ); -- user_b1 is Admin in Tenant B INSERT INTO user_roles (user_id, role_name, tenant_id) VALUES ( 4 , ' User ' , 2 ); -- user_b2 is User in Tenant B ããã¥ã¡ã³ãããŒã¿ã®æå
¥ -- Tenant Aã®ããã¥ã¡ã³ã (äŒç€Ÿ=Tenant A, éšé=Sales, ããŒã =Team 1) INSERT INTO documents (title, content, tenant_id, access_ltree) VALUES ( ' Document A1 ' , ' Content of Document A1 ' , 1 , ' TenantA.Sales.Team1 ' ); INSERT INTO documents (title, content, tenant_id, access_ltree) VALUES ( ' Document A2 ' , ' Content of Document A2 ' , 1 , ' TenantA.Sales.Team2 ' ); -- Tenant Bã®ããã¥ã¡ã³ã (äŒç€Ÿ=Tenant B, éšé=Marketing, ããŒã =Team 1) INSERT INTO documents (title, content, tenant_id, access_ltree) VALUES ( ' Document B1 ' , ' Content of Document B1 ' , 2 , ' TenantB.Marketing.Team1 ' ); INSERT INTO documents (title, content, tenant_id, access_ltree) VALUES ( ' Document B2 ' , ' Content of Document B2 ' , 2 , ' TenantB.Marketing.Team2 ' ); ãŠãŒã¶ãŒäœæ postgresãŠãŒã¶ãŒã®ãŸãŸã ãšãã¹ãŠã®ã¬ã³ãŒããã¿ããŠããŸãã®ã§ãå¥ã®ãŠãŒã¶ãŒãäœæããŸãã CREATE USER access_user WITH PASSWORD ' password ' ; -- ã¹ããŒããžã®ã¢ã¯ã»ã¹æš©éïŒUSAGEïŒãä»äž GRANT USAGE ON SCHEMA test TO access_user; -- ããŒãã«ã«å¯ŸããCRUDæäœæš©éãä»äž GRANT INSERT, SELECT, UPDATE, DELETE ON ALL TABLES IN SCHEMA test TO access_user; è©Šã æåã«å
šããŒã¿ãpostgresãŠãŒã¶ãŒã§ã¿ãŠãããŸãããã åèšäºã« access _ltreeãèšå®ãããŠããã®ãããããŸãã ãã®æš©ééãã«ã¢ã¯ã»ã¹å¶éã§ããŠããã確èªããŠãããŸãã ãã°ã€ã³ããŸãã docker exec -it db psql -U access_user -d postgres ã©ã®æš©éã§ã¢ã¯ã»ã¹ãããã¯ã session.authorization_user_id ã§æ±ºããããŸãã ãŸãã¯èšå®ãªãã§ãã£ãŠã¿ãŸãã æš©éããªãã®ã§ã¿ããŸããã æ¬¡ã¯user_id=1ã§ãã£ãŠã¿ãŸãã æš©éã¯TenantAã®Saleséšéå
šäœã«ã¢ã¯ã»ã¹å¯èœã§ãã æ¬¡ã¯user_id=2ã§ãã£ãŠã¿ãŸãã æš©éã¯TenantAã®Saleséšéã®Team 2ã®ã¿ã¢ã¯ã»ã¹å¯èœã§ãã æ¬¡ã¯user_id=3ã§ãã£ãŠã¿ãŸãã æš©éã¯TenantBã®Marketingéšéå
šäœã«ã¢ã¯ã»ã¹å¯èœã§ãã æ¬¡ã¯user_id=4ã§ãã£ãŠã¿ãŸãã æš©éã¯TenantBã®Marketingéšéã®Team 2ã®ã¿ã¢ã¯ã»ã¹å¯èœã§ãã æ¬¡ã¯user_id=5ã§ãã£ãŠã¿ãŸãã çŽä»ãããããŠãŒã¶ãŒã¯ããŸããã ãã£ããæäœæš©éãçµãããŠããã®ã確èªã§ããŸããïŒ ãŸãšã ltreeã«ã€ããŠç޹ä»ãšæŽ»çšæ¹æ³ãèããŠã¿ãŸããã éå±€æ§é ãç°¡åã«æ±ããŠããã¿ãŒã³ãããã§æ€çŽ¢ãã§ããã®ã§éåžžã«äŸ¿å©ã§ãã èšäºãã¿ãŠãã ãã£ãããããã²æŽ»çšæ¹æ³ãèããŠã¿ãŠãã ããïŒïŒç€Ÿå
ã®ããã¯ãã£ããæããŠããã ãããšãããããã§ãïŒ ãããŸã§èªãã§ããã ãããããšãããããŸããïŒ