PostgreSQL - 只允许 DB 用户调用函数 [英] PostgreSQL - DB user should only be allowed to call functions

问题描述

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

Currently I'm using PostgreSQL for my application. Since I am trying to put every SQL that contains a transaction (i.e. insert, update, delete) in a function, I stumbled upon this problem:

是否有可能只允许数据库用户调用函数和 Select-Statements 而不能调用包含事务的 SQL-Statements?调用函数"是指任何函数.不管它是否包含交易.

Is it possible that a database user may only be allowed to call functions and Select-Statements while he can not call SQL-Statements which contains a transaction? By "call functions" I mean any function. Regardless if it contains a transaction or not.

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

I already tried to create a user which can only call functions and Select-Statements. But I always end up with an error, when calling functions which contains transactions. For what I understand a dbuser needs write permissions if a he calls a function which uses an insert, update or delete statement.

我错过了什么吗?这种场景真的不可能吗?在安全方面，这真的很棒，因为您首先可以防止 SQL 注入.

Am I missing something? Is this scenario really not possible? Security-wise this would be really great because you pretty much prevent SQL-injection in the first place.

推荐答案

没有SELECT 权限".您所需要的只是EXECUTE 函数的权限.相关函数可以使用 SECURITY DEFINER 继承所有者的所有权限.要将可能的权限提升限制在最低限度，请让守护进程角色拥有仅具有必要权限的相关功能 - 而不是超级用户！

There is no "privilege on SELECT". All you need is the privilege to EXECUTE functions. Relevant function can run with SECURITY DEFINER to inherit all privileges of the owner. To restrict possible privilege escalation to a minimum a priori, make a daemon role own relevant functions with only the necessary privileges - not a superuser!

作为超级用户...

创建一个非超级用户角色 myuser.

Create a non-superuser role myuser.

CREATE ROLE myuser PASSWORD ...;

创建一个组角色 mygroup 并使 myuser 成为其中的成员.

Create a group role mygroup and make myuser member in it.

CREATE ROLE mygroup;
GRANT mygroup TO myuser;

您可能想稍后添加更多用户，就像 myuser 一样.

You may want to add more users just like myuser later.

不要根本不授予任何权限给myuser.
仅将这些授予 mygroup:

• 将数据库 mydb 上的连接授予 mygroup；
• 将 SCHEMA 的使用权授予 mygroup；
• 将函数 foo() 上的执行授予 mygroup;

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

Remove all privileges for public that myuser shouldn't have.

REVOKE ALL ON ALL TABLES IN SCHEMA myschema FROM public;

可能还有更多.我引用手册:

PostgreSQL 授予对某些类型对象的默认权限公共.默认情况下，没有向 PUBLIC 授予对表的权限，列、模式或表空间.对于其他类型，默认授予 PUBLIC 的权限如下:CONNECT 和 CREATE TEMPTABLE 用于数据库；EXECUTE 函数权限；和 用法语言的特权.对象所有者当然可以REVOKE默认和明确授予的权限.(为了最大的安全性，发出REVOKE 在创建对象的同一事务中；然后在那里不是另一个用户可以使用该对象的窗口.)此外，这些可以使用 ALTER DEFAULT PRIVILEGES 命令更改初始默认权限设置.

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 only privileges necessary to execute these functions to mydaemon, (including EXECUTE ON FUNCTION to allow another function to be called). Again, you can use group roles to bundle privileges and grant them to mydaemon

GRANT bundle1 TO mydaemon;

此外，您可以使用 DEFAULT PRIVILEGES 自动将未来对象的某些权限直接授予包或守护程序:

In addition you can use DEFAULT PRIVILEGES to automatically grant certain privileges for future objects to a bundle or the daemon directly:

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

这仅适用于执行它的角色.根据文档:

This applies only to the role it is executed for. Per the documentation:

如果省略FOR ROLE，则假定当前角色.

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

还要涵盖架构中预先存在的对象(参见 rob 的评论):

To also cover pre-existing objects in the schema (see rob's comment):

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

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

Make mydaemon own relevant functions. Could look like this:

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 ..

<罢工>###注意
由于 这个错误，在 pgAdmin 必要的命令

REVOKE EXECUTE ON FUNCTION foo() FROM public;

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

is missing in the reverse engineered DDL script. Remember to add it, when recreating.
This bug is fixed in the current version pgAdmin 1.18.1.

这篇关于PostgreSQL - 只允许 DB 用户调用函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！