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-Statement,而他不能调用包含事务的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.
我已经尝试创建一个只能调用函数和Select-Statement的用户。但是,当调用包含事务的函数时,我总是以错误告终。据我了解,如果dbuser调用使用插入,更新或删除语句的函数,则需要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
函数的特权。相关功能可以与 一起运行安全定义
继承所有者的所有特权。为了将可能的特权升级限制到最小的先验条件,请使守护进程角色仅具有必要的特权-而不是超级用户,以拥有相关功能!
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
。< br>
仅将这些授予 mygroup
:
-
将数据库mydb上的GRANT连接到mygroup;
-
在SCHEMA上对GRANT的使用公开给mygroup;
-
执行功能foo()到mygroup;
GRANT CONNECT ON DATABASE mydb TO mygroup;
GRANT USAGE ON SCHEMA public TO mygroup;
GRANT EXECUTE ON FUNCTION foo() TO mygroup;
删除 public
的 all 特权,而 myuser
不应
REVOKE ALL ON ALL TABLES IN SCHEMA myschema FROM public;
可能还有更多。 我引用该手册:
PostgreSQL将某些类型的对象的默认特权授予
PUBLIC
。默认情况下,不对表,
列,模式或表空间授予PUBLIC特权。对于其他类型,授予PUBLIC的默认
特权如下:CONNECT
和CREATE TEMP
用于数据库;
TABLEEXECUTE
的功能特权;和USAGE
语言特权。当然,对象所有者可以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
andCREATE TEMP TABLE
for databases;EXECUTE
privilege for functions; andUSAGE
privilege for languages. The object owner can, of course,REVOKE
both default and expressly granted privileges. (For maximum security, issue theREVOKE
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 theALTER 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;
此外,您还可以使用 默认特权
,以便将来自动授予某些特权
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 RAR
,则假定当前角色。
还要涵盖模式中的预先存在的对象(请参见 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 ..
### Note
由于
###Note
Due to this bug in the current version 1.16.1 of pgAdmin the necessary command
REVOKE EXECUTE ON FUNCTION foo() FROM public;
。
此错误已在当前版本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屋!