PostgreSQL - 数据库用户应该只被允许调用函数

Question

目前我正在为我的应用程序使用 PostgreSQL。由于我试图将每个包含事务(即插入、更新、删除)的 SQL 放入函数中，因此我偶然发现了这个问题:

数据库用户是否可以只允许调用函数和Select-Statements，而不能调用包含事务的SQL-Statements？ “调用函数”是指任何函数。不管它是否包含交易。

我已经尝试创建一个只能调用函数和 Select-Statements 的用户。但是在调用包含事务的函数时，我总是以错误告终。据我了解，如果 dbuser 调用使用插入、更新或删除语句的函数，则他需要写权限。

我错过了什么吗？这种情况真的不可能吗？在安全方面，这真的很棒，因为您基本上可以从一开始就防止 SQL 注入(inject)。

Answer 1

没有“SELECT 的特权”。您所需要的只是EXECUTE 函数的权限。相关功能可以用SECURITY DEFINER运行继承所有者的所有特权。为了先验地将可能的权限升级限制在最低限度，让守护进程角色拥有相关功能，只拥有必要的权限 - 而不是 super 用户!

食谱

作为 super 用户...

创建一个非 super 用户角色 myuser。

CREATE ROLE myuser PASSWORD ...;

创建组角色 mygroup 并让 myuser 成为其中的成员。

CREATE ROLE mygroup;
GRANT mygroup TO myuser;

您可能希望稍后添加更多用户，就像 myuser 一样。

不要完全不授予任何权限给myuser。
仅将这些授予 mygroup:

• GRANT CONNECT ON DATABASE mydb TO mygroup;
• GRANT USAGE ON SCHEMA public TO mygroup;
• GRANT EXCUTE ON FUNCTION foo() TO mygroup;

为 public 删除 所有 myuser 不应该拥有的权限。

REVOKE ALL ON ALL TABLES IN SCHEMA myschema FROM public;

可能还有更多。 I quote the manual:

PostgreSQL grants default privileges on some types of objects to PUBLIC. No privileges are granted to PUBLIC by default on tables, columns, schemas or tablespaces. For other types, the default privileges granted to PUBLIC are as follows: CONNECT and CREATE TEMP
TABLE for databases; EXECUTE privilege for functions; and USAGE privilege for languages. The object owner can, of course, REVOKE both default and expressly granted privileges. (For maximum security, issue the REVOKE in the same transaction that creates the object; then there is no window in which another user can use the object.) Also, these initial default privilege settings can be changed using the ALTER DEFAULT PRIVILEGES command.

创建一个守护进程以拥有相关功能。

CREATE ROLE mydaemon;

只授予 mydaemon 执行这些函数所需的权限(包括 EXECUTE ON FUNCTION 以允许调用另一个函数)。同样，您可以使用组角色来捆绑特权并将它们授予 mydaemon

GRANT bundle1 TO mydaemon;

此外，您还可以使用 DEFAULT PRIVILEGES自动将 future 对象的某些特权直接授予包或守护进程:

ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES    TO bundle1;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT USAGE  ON SEQUENCES TO bundle1;

这仅适用于执行它的角色。 Per the documentation:

If FOR ROLE is omitted, the current role is assumed.

同时覆盖模式中预先存在的对象(参见 rob's comment ):

GRANT SELECT ON ALL TABLES    IN SCHEMA public TO bundle1;
GRANT USAGE  ON ALL SEQUENCES IN SCHEMA public TO bundle1;

让mydaemon拥有相关功能。可能看起来像这样:

CREATE OR REPLACE FUNCTION foo()
  ...
SECURITY DEFINER SET search_path = myschema, pg_temp;

ALTER FUNCTION foo() OWNER TO mydaemon;
REVOKE EXECUTE ON FUNCTION foo() FROM public;
GRANT  EXECUTE ON FUNCTION foo() TO mydaemon;
GRANT  EXECUTE ON FUNCTION foo() TO mygroup;
-- possibly others ..

<罢工>###注意
由于 this bug在 pgAdmin 的当前版本 1.16.1 中必要的命令

REVOKE EXECUTE ON FUNCTION foo() FROM public;

在逆向工程 DDL 脚本中缺失。记得在重新创建时添加它。
此错误已在当前版本 pgAdmin 1.18.1 中修复。