英文:
How to give readonly permission to postgres database role?
问题
你好,我有一个PostgreSQL数据库,并创建了以下用户:
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;
现在我正在使用这个用户来执行存储过程。我执行了以下语句:
GRANT EXECUTE ON ALL PROCEDURES IN SCHEMA myschemaname TO myuser;
在我的存储过程中,我有select、insert、update和delete语句。
每当我执行存储过程时,如果存储过程包含任何insert、delete或update语句,就会抛出错误:
42501: permission denied for table tablename
如果存储过程中只有select语句,那么没有问题,只有在insert、update和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
-- UPDATE;
-- DELETE;
-- INSERT
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;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论