postgresql - 如何插入具有行级安全性的表？

Question

我有下表:

            Table "api_v1.person"
    Column     |  Type  |      Modifiers
---------------+--------+-------------------------------------------------------
 person_id     | bigint | not null default...
 name          | text   | not null
 date_of_birth | date   |
 api_user      | text   | not null default "current_user"()

具有以下策略:

POLICY "api_user_only" FOR ALL
  USING ((api_user = ("current_user"())::text))
  WITH CHECK ((api_user = ("current_user"())::text))

我的理解是，策略的 FOR ALL 部分意味着它涵盖插入，而 WITH CHECK 确保插入到 api_user 的值与当前值相同用户，例如角色名称。 USING 子句应该只影响 SELECTS 或返回的其他数据。但是，当我尝试插入时，我得到以下结果:

demo=> INSERT INTO api_v1.person (name, api_user) VALUES ('Greg', current_user);
ERROR:  query would be affected by row-level security policy for table "person"

我该如何插入？

我正在运行 PostgreSQL 9.6.8。

这里是重现所需的 SQL:

BEGIN;

CREATE SCHEMA api_v1;

CREATE TABLE api_v1.person (
    person_id BIGSERIAL PRIMARY KEY,
    "name" TEXT NOT NULL,
    date_of_birth DATE,
    api_user TEXT NOT NULL DEFAULT current_user
);

ALTER TABLE api_v1.person ENABLE ROW LEVEL SECURITY;

CREATE POLICY
api_user_only
ON
    api_v1.person
USING
    (api_user = CURRENT_USER)
WITH CHECK
    (api_user = CURRENT_USER)
;

CREATE ROLE test_role;

GRANT USAGE ON SCHEMA api_v1 TO test_role;
GRANT ALL ON api_v1.person TO test_role;
GRANT USAGE ON SEQUENCE api_v1.person_person_id_seq TO test_role;

COMMIT;

SET ROLE test_role;

INSERT INTO api_v1.person ("name") VALUES ('Greg');

Answer 1

postgresql.conf 中有一个设置，row_security。如果将其设置为关闭，则任何受行级安全策略影响的查询都会失败并出现错误:错误:查询将受到表“table_name”的行级安全策略的影响。但是，来自 super 用户、表所有者(如果您不强制 RLS)和具有 bypassrls 的角色的查询将起作用。

row_security 设置需要设置为 on，然后需要重新启动 PostgreSQL，以便针对具有行级安全策略的表处理常规用户语句。

来自源代码:

/*
 * We should apply RLS.  However, the user may turn off the row_security
 * GUC to get a forced error instead.
 */
if (!row_security && !noError)
    ereport(ERROR,
            (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
             errmsg("query would be affected by row-level security policy for table \"%s\"",
                    get_rel_name(relid)),
             amowner ? errhint("To disable the policy for the table's owner, use ALTER TABLE NO FORCE ROW LEVEL SECURITY.") : 0));