英文:
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);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论