Postgres 15 中public schema的相关更改

PostgreSQL 15 大大加强 了对“公共”模式的使用限制。

在这里，一个标准登录用户（不是超级用户）尝试创建一个表，如下所示：

user=> CREATE TABLE mydata (id integer);

ERROR:  permission denied for schema public
LINE 1: CREATE TABLE mydata (id integer);

不！为什么我不能将表写入公共？

对于开发人员和实验人员来说，PostgreSQL 长期以来的乐趣之一是 PostgreSQL 为“公共”模式提供的免费且简单的安全策略。

• “public”是默认的search_path，所以你总能在里面找到东西；和，
• 任何用户都可以在“公共”中创建新对象；所以，
• “就当众扔吧！” 一直是一个简单的协作技巧。

然而，对于任何使用数据库超过 15 分钟的人来说，“松散的公众”的含义是非常清楚的：

• “public”最终变成了一堆临时和长期废弃的桌子，
• “public”是一个安静的安全漏洞，因为它倾向于保持默认状态 search_path，有时位于其他模式之前。

开始实验

建立一个新的数据库和试验应该很有趣！

最简单的事情就是给每个人“postgres”超级用户密码。无限的特权和完全的透明度！

然而，这种方法仅限于实验的早期阶段。当您进入生产阶段时，您将需要更多东西。

分离用户数据

最终您将拥有生产数据，并且需要限制对该数据的访问，但保留临时和实验数据和用户。您如何轻松地将临时数据从生产数据中分割出来？

PostgreSQL很容易将用户数据分开，因为默认search_path以“用户模式”开头。看到那个$user条目 search_path？这是一个用您的用户名填充的动态变量。

postgres=> show search_path;

   search_path
-----------------
 "$user", public

• 要试用它，首先创建一个基本登录用户。

  -- as 'postgres'
  CREATE USER luser1;
  

• 然后，为该用户创建一个“用户模式”。

  -- as 'postgres'
  CREATE SCHEMA luser1 WITH AUTHORIZATION luser1;
  

• 现在，当在不指定架构luser1的情况下创建表时，该表将在其. 默认情况下，第一个模式扩展为他们的用户名，现在有一个与他们的用户名匹配的模式，因此就是创建表的地方！search_path$user

  -- as 'luser1'
  CREATE TABLE my_test_data (id integer);
  

              List of relations
     Schema |     Name     | Type  | Owner
    --------+--------------+-------+-------
    luser1  | my_test_data | table | luser1
  

因此，如果您只是为您创建的每个新登录用户创建一个用户模式，您可以巧妙地将用户数据与其他数据分开，因为每个用户最终都会在默认情况下在其用户模式中创建新表和对象。

共享用户数据

甚至在 PostgreSQL 14 中，为了在public模式中的用户拥有的表之间共享数据，有必要将GRANT SELECT这些表设置为您想要访问它们的角色。

使用“用户模式”设置，同样的原则适用，除了您还必须 GRANT USAGE在您的用户模式上设置您想要访问表的任何角色。

-- as 'luser1'
GRANT USAGE ON SCHEMA luser1 TO luser2;
GRANT SELECT ON my_test_data TO luser2;

与角色共享

USAGE用户可以授予整个用户类别，而不是单独授予SELECT可能想要使用其数据的每个用户。如果数据库管理员设置了一个包含所有相关用户的角色：

-- as 'postgres'
CREATE USER luser1 LOGIN;
CREATE USER luser2 LOGIN;
CREATE ROLE lusers;
GRANT lusers TO luser1, luser2;

-- as 'luser1'
GRANT USAGE ON SCHEMA luser1 TO lusers;
GRANT SELECT ON my_test_data TO lusers;

现在，随着新帐户被添加到lusers角色中，他们将自动能够访问luser1已共享的表。

结论

• publicPostgreSQL 15 从架构中删除了全局写入权限！
• 但是，通过使用用户模式和基于角色的访问，为用户提供相同级别的协作非常容易。

原文标题：Be Ready! Public schema changes in Postgres 15

原文作者：Paul Ramsey

原文链接：https://www.crunchydata.com/blog/be-ready-public-schema-changes-in-postgres-15