gpt4 book ai didi

PostgreSQL - 在数据库范围内授予 DEFAULT PRIVILEGES 并仅为特定模式撤销它们

转载 作者:行者123 更新时间:2023-11-29 13:39:37 30 4
gpt4 key购买 nike

我正在经历一种与 DEFAULT PRIVILEGES 相关的奇怪且(对我而言) 无法解释的行为。一旦在数据库范围内授予默认权限,似乎就不能仅针对特定模式撤销默认权限。我目前正在 CentOS 上使用 PostgreSQL 10.5 对此进行测试。

假设有 3 个用户:

admin    Owner of the database. Used to manipulate the STRUCTURE of the database (CREATE, DROP, TRUNCATE...)
manager Used for DATA manipulation (INSERT, UPDATE, DELETE)
reader Used to read DATA (basically SELECT)

这个想法是:

  1. admin 将是数据库和其中包含的所有对象的所有者
  2. manager 将用于跨所有 模式的数据操作,但 public(只有用户 admin 可以修改数据在 public 架构中)
  3. reader 将能够阅读所有内容。

为了使事情更简单,这将依赖于默认权限,以便新创建的对象(模式、表、 View 、函数等)都将具有正确的权限。

这是我第一次尝试类似的东西,而不是基于所有不同模式的多个用户的细粒度权限策略,显然这个设置应该非常简单。原来我错过了什么。

这是一个简单的测试脚本。用户 admindb 数据库的所有者,所有这些命令都是作为 admin 连接到它发出的:

-- 1. User manager inherits from user "reader" 
GRANT reader TO manager;

-- 2. Allow connections to the database to our users, but not PUBLIC
REVOKE ALL ON DATABASE db FROM PUBLIC;
GRANT CONNECT ON DATABASE db TO reader;

-- 3. Revoke default privileges from PUBLIC
ALTER DEFAULT PRIVILEGES REVOKE ALL ON SCHEMAS FROM PUBLIC;
ALTER DEFAULT PRIVILEGES REVOKE ALL ON TABLES FROM PUBLIC;
ALTER DEFAULT PRIVILEGES REVOKE ALL ON SEQUENCES FROM PUBLIC;
ALTER DEFAULT PRIVILEGES REVOKE ALL ON FUNCTIONS FROM PUBLIC;

-- 4. Grant default reading privileges to user "reader"
ALTER DEFAULT PRIVILEGES GRANT USAGE ON SCHEMAS TO reader;
ALTER DEFAULT PRIVILEGES GRANT SELECT ON TABLES TO reader;
ALTER DEFAULT PRIVILEGES GRANT SELECT ON SEQUENCES TO reader;
ALTER DEFAULT PRIVILEGES GRANT EXECUTE ON FUNCTIONS TO reader;

-- 5. Grant Defauly writing privileges to user "manager"
ALTER DEFAULT PRIVILEGES GRANT INSERT, UPDATE, DELETE ON TABLES TO manager;
ALTER DEFAULT PRIVILEGES GRANT USAGE ON SEQUENCES TO manager;

-- 6. Reinit "public" schema
DROP SCHEMA public;
CREATE SCHEMA public;

-- 7. HERE COMES THE WEIRD STUFF, the two following statements don't have any effect at all
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE INSERT, UPDATE, DELETE ON TABLES FROM manager;
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE USAGE ON SEQUENCES FROM manager;

这可以很容易地验证:

-- Execute as user "admin":
CREATE TABLE public.t (id serial PRIMARY KEY, dummy integer)

-- Execute as user "manager" (it should not be allowed, but it is!)
DELETE FROM public.t;

我知道我可以使用一些触发函数来规避这个问题,但问题的关键是这是否是正常的和预期的,某种错误还是我遗漏了什么?

最佳答案

我一直在考虑这个问题,我能想到的最优雅的解决方案依赖于 Event Trigger .

当然它并没有直接回答我的问题,意思是我还在想为什么不能那样使用默认权限,但这至少满足了set-and-forget的初始要求将提供默认权限。

创建一个函数来撤销不需要的权限并返回 event_trigger :

CREATE FUNCTION reset_privileges() RETURNS event_trigger AS $$
BEGIN
IF EXISTS (SELECT true FROM pg_event_trigger_ddl_commands() WHERE schema_name = 'public') THEN
REVOKE INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public FROM manager;
REVOKE USAGE ON ALL SEQUENCES IN SCHEMA public FROM manager;
END IF;
END;
$$ LANGUAGE plpgsql;

创建一个实际的 EVENT TRIGGER (这需要 super 用户权限!):

CREATE EVENT TRIGGER reset_public_schema_privileges
ON ddl_command_end WHEN TAG IN (
'CREATE TABLE',
'CREATE TABLE AS',
'CREATE VIEW',
'CREATE MATERIALIZED VIEW',
'CREATE FUNCTION'
) EXECUTE PROCEDURE reset_privileges();

该函数检查新创建的对象是否在 public 中模式并最终撤销用户的所有不需要的权限 manager .

它甚至懒得过滤那些对象,而是撤销了 ALL 的权限。 TABLE小号,VIEW s 和 FUNCTIONpublic 中模式。当然可以使用 object_identity 轻松定制它。 pg_event_trigger_ddl_commands 提供的字段以及函数内部更精细的逻辑。

关于PostgreSQL - 在数据库范围内授予 DEFAULT PRIVILEGES 并仅为特定模式撤销它们,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/57242673/

30 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com