如何使用Doctrine和Symfony动态设置PostgreSQL模式

Question

我正在尝试使用Symfony 2.6和PostgreSQL模式（命名空间）创建一个多租户应用程序。我想知道如何更改预持久化事件上的某些实体架构？

我知道可以将架构设置为注释@Table(schema="schema")但这是静态解决方案，我需要更动态的东西！

使用PostgreSQL的目的是利用模式功能，例如：

CREATE TABLE tenant_1.users (
    # table schema
);
CREATE TABLE tenant_2.users (
    # table schema
);

因此，如果我只想要来自tenant_2的用户，我的查询将类似于SELECT * FROM tenant_2.users;

这样，我的数据将被分离，我将只有一个数据库需要连接和维护。

Answer 1

$schema = sprintf('tenant_%d', $id);
$em->getConnection()->exec('SET search_path TO ' . $schema);

Answer 2

您可能还希望使用 PostgreSQL 的行级别安全性 - 这样您实际上可以防止租户访问数据，而不仅仅是通过为架构路径添加前缀来隐藏它。

看看这个：https://www.tangramvision.com/blog/hands-on-with-postgresql-authorization-part-2-row-level-security。我刚刚使用该页面上的信息设置了一个工作租户分离，我对此感到非常兴奋。

就我而言，我的租户称为组织，某些（不是全部）表具有永久绑定行的organisation_id。

以下是我在架构更新期间运行的脚本版本，该脚本查找具有列organisation_id的所有表，并使用仅显示组织拥有的行的策略启用行级别安全性（如果设置了组织角色）：

CREATE ROLE "org";
-- Find all tables with organisation_id and enable the row level security
DO $$ DECLARE
    r RECORD;
BEGIN
    FOR r IN (
        SELECT
            t.table_name, t.table_schema, c.column_name
        FROM
            information_schema.tables t
        INNER JOIN
            information_schema.columns c ON
                c.table_name = t.table_name
                AND c.table_schema = t.table_schema
                AND c.column_name = 'organisation_id'
        WHERE
            t.table_type = 'BASE TABLE'
            AND t.table_schema != 'information_schema'
            AND t.table_schema NOT LIKE 'pg_%'
    ) LOOP
        EXECUTE 'ALTER TABLE ' || quote_ident(r.table_schema) || '.' || quote_ident(r.table_name) || ' ENABLE ROW LEVEL SECURITY';
        EXECUTE 'DROP POLICY IF EXISTS org_separation ON ' || quote_ident(r.table_schema) || '.' || quote_ident(r.table_name);
        EXECUTE 'CREATE POLICY org_separation ON ' || quote_ident(r.table_schema) || '.' || quote_ident(r.table_name) || 'FOR ALL to org USING (organisation_id = substr(current_user, 5)::int)';
    END LOOP;
END $$;
-- Grant usage on all tables in all schemas to the org role
DO $do$
DECLARE
    sch text;
BEGIN
    FOR sch IN (
        SELECT
            schema_name
        FROM
            information_schema.schemata
        WHERE
            schema_name != 'information_schema'
            AND schema_name NOT LIKE 'pg_%'
    ) LOOP
        EXECUTE format($$ GRANT USAGE ON SCHEMA %I TO org $$, sch);
        EXECUTE format($$ GRANT SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA %I TO org $$, sch);
        EXECUTE format($$ GRANT SELECT, UPDATE, INSERT, DELETE ON ALL TABLES IN SCHEMA %I TO org $$, sch);
        EXECUTE format($$ ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT SELECT, UPDATE ON SEQUENCES TO org $$, sch);
        EXECUTE format($$ ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT INSERT, SELECT, UPDATE, DELETE ON TABLES TO org $$, sch);
    END LOOP;
END;
$do$;

第二步，当我创建一个新组织时，我也会为其创建一个角色：

CREATE ROLE "org:86" LOGIN;
GRANT org TO "org:86";

第三步，在应限定为特定组织的每个请求开始时，我调用SET ROLE "org:86";以启用限制。

围绕我们处理所有这些工作还有很多事情要做，但上面的代码应该足够完整，可以帮助人们入门。

祝你好运！