PostgreSQL - 只允许 DB 用户调用函数

PostgreSQL - DB user should only be allowed to call functions

提问by lefthy

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:

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

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.

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

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.

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

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.

我错过了什么吗？这种场景真的不可能吗？在安全方面，这真的很棒，因为您一开始就几乎可以防止 SQL 注入。

回答by Erwin Brandstetter

There is no "privilege on SELECT". All you need is the privilege to EXECUTEfunctions. Relevant function can run with SECURITY DEFINERto 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!

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

Recipe

食谱

As superuser ...

作为超级用户...

Create a non-superuser role myuser.

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

CREATE ROLE myuser PASSWORD ...;

Create a group role mygroupand make myusermember in it.

创建一个组角色mygroup并使其成为myuser成员。

CREATE ROLE mygroup;
GRANT mygroup TO myuser;

You may want to add more users just like myuserlater.

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

Do not grant any privileges at allto myuser.
Only grant these to mygroup:

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

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

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

Remove allprivileges for publicthat myusershouldn't have.

删除所有权限public是myuser不应该有。

REVOKE ALL ON ALL TABLES IN SCHEMA myschema FROM public;

There may be more. 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: CONNECTand CREATE TEMP TABLEfor databases; EXECUTEprivilege for functions; and USAGEprivilege for languages. The object owner can, of course, REVOKEboth default and expressly granted privileges. (For maximum security, issue the REVOKEin 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 PRIVILEGEScommand.

PostgreSQL 将某些类型对象的默认权限授予 PUBLIC. 默认情况下，不会向 PUBLIC 授予对表、列、模式或表空间的任何权限。对于其他类型，是授予PUBLIC的默认权限如下：CONNECT和CREATE TEMP TABLE为数据库; EXECUTE功能特权；和USAGE语言特权。当然，对象所有者可以REVOKE默认和明确授予的权限。（为了获得最大的安全性，请REVOKE在创建对象的同一事务中发出；然后没有其他用户可以使用该对象的窗口。）此外，可以使用ALTER DEFAULT PRIVILEGES命令更改这些初始默认权限设置。

Create a daemon roleto ownrelevant functions.

创建守护进程角色以拥有相关功能。

CREATE ROLE mydaemon;

Grant only privileges necessary to execute these functions to mydaemon, (including EXECUTE ON FUNCTIONto allow another function to be called). Again, you can use group roles to bundle privileges and grant them to mydaemon

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

GRANT bundle1 TO mydaemon;

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

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

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:

这仅适用于为其执行的角色。根据文档：

If FOR ROLEis omitted, the current role is assumed.

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

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

还要涵盖模式中预先存在的对象（请参阅rob 的评论）：

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

Make mydaemonown relevant functions. Could look like this:

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

###Note
Due to this bugin the current version 1.16.1 of pgAdminthe necessary command

###Note
由于当前版本 1.16.1 的pgAdmin 中的此错误，需要的命令

REVOKE EXECUTE ON FUNCTION foo() FROM public;

REVOKE EXECUTE ON FUNCTION foo() FROM public;

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.

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