行安全策略问题在插入行时

huangapple go评论62阅读模式
英文:

Row security policy issue when inserting rows

问题

我已经创建了一个表来存储客户记录:

CREATE TABLE parking.client (
    k_client integer NOT NULL,
    id varchar(12) NOT NULL,
    name varchar(50) NOT NULL,
    last_name varchar(50) NOT NULL,
    email varchar(200) NOT NULL,
    CONSTRAINT client_pk PRIMARY KEY (k_client)
);

然后我为这个表定义了一个策略:

ALTER TABLE parking.client ENABLE ROW LEVEL SECURITY;

CREATE POLICY client_pl ON parking.client
    AS PERMISSIVE
    FOR ALL
    TO user_role
    USING (email = CURRENT_USER);

我尝试使用不同的角色向这个表中插入记录:

CREATE ROLE manage_account_user WITH 
    CREATEROLE
    LOGIN
    PASSWORD 'MyPassword';

GRANT SELECT, INSERT
   ON TABLE parking.client
   TO manage_account_user;

但无论何时尝试进行插入操作,我都会收到以下错误消息:

new row violates row-level security policy for table "client"

为什么我会受到行级安全限制,而我只为角色 user_role 定义了策略?

英文:

I have created a table to store client records on it:

CREATE TABLE parking.client (
	k_client integer NOT NULL,
    id varchar(12) NOT NULL,
	name varchar(50) NOT NULL,
	last_name varchar(50) NOT NULL,
	email varchar(200) NOT NULL,
    CONSTRAINT client_pk PRIMARY KEY (k_client)
);

Then I defined a policy for this table:

ALTER TABLE parking.client ENABLE ROW LEVEL SECURITY;

CREATE POLICY client_pl ON parking.client
	AS PERMISSIVE
	FOR ALL
	TO user_role
	USING (email = CURRENT_USER);

I am trying to insert records on this table using a different role:

CREATE ROLE manage_account_user WITH 
	CREATEROLE
	LOGIN
	 PASSWORD 'MyPassword';

GRANT SELECT,INSERT
   ON TABLE parking.client
   TO manage_account_user;

But whenever I try an INSERT I get:

new row violates row-level security policy for table "client"

Why am I getting a row-level security restriction if I have only defined the policy for the role user_role?

答案1

得分: 1

启用了行级安全性后,除非通过策略明确允许,否则一切都被禁止。因此,您需要添加一个允许角色执行INSERT操作的策略。

如果您想要为角色inserter创建一个允许其插入任何内容的策略,您可以使用以下代码:

CREATE POLICY inserter_can_insert ON parking.client
   FOR INSERT TO inserter
   WITH CHECK (TRUE);
英文:

With row level security enabled, everything is forbidden unless it is explicitly allowed by a policy. So you'd have to add a policy that allows the role to INSERT data.

If you want a policy for the role inserter that allows it to insert anything, you could use

CREATE POLICY inserter_can_insert ON parking.client
   FOR INSERT TO inserter
   WITH CHECK (TRUE);

huangapple
  • 本文由 发表于 2023年6月2日 11:19:04
  • 转载请务必保留本文链接:https://go.coder-hub.com/76386921.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定