英文:
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.clientAS PERMISSIVEFOR ALLTO user_roleUSING (email = CURRENT_USER);
我尝试使用不同的角色向这个表中插入记录：
CREATE ROLE manage_account_user WITHCREATEROLELOGINPASSWORD 'MyPassword';GRANT SELECT, INSERTON TABLE parking.clientTO 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.clientAS PERMISSIVEFOR ALLTO user_roleUSING (email = CURRENT_USER);
I am trying to insert records on this table using a different role:
CREATE ROLE manage_account_user WITHCREATEROLELOGINPASSWORD 'MyPassword';GRANT SELECT,INSERTON TABLE parking.clientTO 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.clientFOR INSERT TO inserterWITH 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.clientFOR INSERT TO inserterWITH CHECK (TRUE);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论