如何给PostgreSQL数据库角色设置只读权限?

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

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;

huangapple
  • 本文由 发表于 2023年8月9日 15:12:41
  • 转载请务必保留本文链接:https://go.coder-hub.com/76865416.html
匿名

发表评论

匿名网友

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

确定