英文:
How to give readonly permission to postgres database role?
问题
你好，我有一个PostgreSQL数据库，我已经创建了一个用户，如下所示：
CREATE USER 用户名 WITH PASSWORD '你的密码';
GRANT CONNECT ON DATABASE 数据库名 TO 用户名;
GRANT USAGE ON SCHEMA 模式名 TO 用户名;
GRANT SELECT ON ALL TABLES IN SCHEMA 模式名 TO 用户名;
现在我正在使用这个用户来执行存储过程。我已经执行了以下语句：
GRANT EXECUTE ON ALL PROCEDURES IN SCHEMA 我的模式名 TO 我的用户;
在我的存储过程中，我有select、insert、update和delete语句。每当我执行存储过程，如果存储过程包含任何insert、delete、update语句，就会抛出错误：
42501: 拒绝对表表名的权限
如果我的存储过程只包含select语句，那么就没有问题，只有在insert、update和delete时才会出现问题。所以我的问题是，我创建了一个用户，只用于执行存储过程，我不想给任何表格授予update、delete和insert访问权限。那么我应该怎么做才能在存储过程中执行insert和delete？有人可以帮助吗？
英文:
Hi I have postgres database and I have created user like below
CREATE USER username WITH PASSWORD 'your_password';
GRANT CONNECT ON DATABASE database_name TO username;
GRANT USAGE ON SCHEMA schema_name TO username;
GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO username;
Now I am using this user to execute Stored procedures. I have executed below statement
GRANT EXECUTE ON ALL PROCEDURES IN SCHEMA myschemaname TO myuser;
In my stored procedures I have select, insert, update and delete statements.
whenever I execute Stored procedures and if stored procedure contains any insert,delete update statements its throwing error
42501: permission denied for table tablename
If i have only select inside the SP then no issues only problem with insert,update and delete. So my question is I have created user for the purpose of executing stored procedure only and i do not want to give update,delete and insert access to any table. So what should I do to execute inset,delete inside SP? Can someone help
答案1
得分: 1
使用SECURITY DEFINER创建存储过程，该选项指定函数将以拥有它的用户的权限执行。详细信息请参考此处。确保存储过程的所有者具备对存储过程体内所有资源的所需权限。
CREATE OR REPLACE PROCEDURE procedure_name()
AS
$$
BEGIN
-- 更新操作;
-- 删除操作;
-- 插入操作;
END;
$$ LANGUAGE PLPGSQL SECURITY DEFINER;
英文:
Create the procedures with SECURITY DEFINER which specifies that the function is to be executed with the privileges of the user that owns it.
Check here.
Make sure that the owner of the procedures have required privileges of all operations on resources within the proc body.
CREATE OR REPLACE PROCEDURE procedure_name()
AS
$$
BEGIN
-- UPDATE;
-- DELETE;
-- INSERT
END;
$$ LANGUAGE PLPGSQL SECURITY DEFINER;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论