在PostgreSQL中授予使用和权限对未来创建的模式进行操作

Question

我现在正在集成的应用程序将创建新的模式（每个客户都有自己的模式，例如schema1，schema2，schema3等）。 为了授予对新创建的模式和特定表的使用和只读访问权限，我执行以下命令：

GRANT USAGE ON SCHEMA schema1 TO read_only_user;
GRANT SELECT ON schema1.talbe1 TO read_only_user;
GRANT SELECT ON schema1.table2 TO read_only_user;

GRANT USAGE ON SCHEMA schema2 TO read_only_user;
GRANT SELECT ON schema2.talbe1 TO read_only_user;
GRANT SELECT ON schema2.table2 TO read_only_user;

(......and so on.....)

我想知道是否可以在PostgreSQL中授予未来创建的模式使用和权限。只能找到一些关于更改未来创建表的默认权限的方法，但没有关于更改未来创建模式的。

- edyywang

Answer 1

在模式中没有默认权限。但是，由于您正在使用每个用户都拥有自己的模式的模型，因此可以自动化整个过程，包括创建用户并设置密码（如果需要）：

CREATE FUNCTION new_user_schema (user text, pwd text) RETURNS void AS $$
DECLARE
  usr name;
  sch name;
BEGIN
  -- Create the user
  usr := quote_identifier(user);
  EXECUTE format('CREATE ROLE %I LOGIN PASSWORD %L', usr, quote_literal(pwd));

  -- Create the schema named after the user and set default privileges
  sch := quote_identifier('sch_' || user);
  EXECUTE format('CREATE SCHEMA %I', sch);
  EXECUTE format('ALTER SCHEMA %I OWNER TO %L', sch, usr);
  EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I
                    GRANT SELECT ON TABLES TO %L', sch, usr);
END; $$ LANGUAGE plpgsql STRICT;

您可以使用一个简单的命令创建用户、创建模式并设置默认权限：

SELECT new_user_schema('new_user', 'secret');

- Patrick

---

感谢您的回答。非常感激。 - edyywang

谢谢您的回答，我很感激。然而，我的问题在于当新的数据库模式被创建时，我无法控制，并且我授权的只读用户不是模式所有者（我也无法控制原始模式所有者）。因此，我想我需要一个自动化的流程来检测模式的创建并向只读用户授予使用/选择权限。 - edyywang

那么谁将执行GRANT命令呢？既然显然不是模式所有者，那么它必须是超级用户角色。那么模式本身是如何创建的呢？ - Patrick

有一个超级用户角色来创建新模式，但当超级用户创建新模式时，我无法在他们的代码中进行控制。因此，我认为我需要创建一些自动检测或一些触发器来检测新模式的创建，然后将使用/选择权限授予另一个只读用户。挑战在于我不能在超级用户创建新模式时更改代码:( 或者是否有一种方法可以在超级用户帐户中添加一些代码，以便每当超级用户创建新模式时，它将在创建新模式后执行GRANT命令。非常感谢。 - edyywang