logo标签列表

在PostgreSQL中为特定数据库授予权限

sqldatabasepostgresqlprivileges
40

我正在从MySQL迁移到PostgreSQL，在用户权限方面遇到了麻烦。我习惯使用以下命令将所有表的所有权限分配给一个用户：

我正在从 MySQL 迁移到 PostgreSQL，但在用户权限方面遇到了问题。我习惯使用以下命令将某个用户赋予该数据库中所有表的所有权限：

# MySQL
grant all privileges on mydatabase.* to 'myuser'@'localhost' identified by 'mypassword';
我认为 PostgreSQL 9.x 的解决方案涉及将权限分配给“模式”，但是我需要花费大量精力才能弄清楚要发出什么 SQL。我知道再研究几个小时就会得到答案，但我认为每个从 MySQL 到 PostgreSQL 转移的人都可以受益于至少在网上提供一个简单完整的教程。这是我曾经需要为用户发出的唯一命令。我不想为每个新表发出命令。
我不知道在 PostgreSQL 中有哪些情况需要以不同方式处理，因此我列出了我过去通常必须处理的一些情况。假设我们只想修改已创建的单个数据库的权限。
(1a) 并非所有表都已创建，或者 (1b) 表已经被创建。
(2a) 用户尚未创建，或者 (2b) 用户已经被创建。
(3a) 权限尚未分配给用户，或者 (3b) 先前已向用户分配了权限。
(4a) 用户仅需要插入、更新、选择和删除行，或者 (4b) 用户还需要能够创建和删除表。
我看到了授予所有数据库的所有权限的答案，但那不是我想要的。请注意，我正在寻找一个简单的教程，尽管我不介意也有一些解释。
我不想授予所有用户和所有数据库的权限，因为这种方法会在任何一个用户受到损害时危及所有数据库。我托管多个数据库客户端，并为每个客户分配不同的登录名。
看起来我还需要 USAGE 权限才能获得序列列的递增值，但是我必须在某种序列上授予它。我的问题变得更加复杂了。
我怀疑PostgresQL用户权限的复杂性可能会成为更广泛采用的重要障碍。 - Joe Lapp
以下页面似乎授予用户所有数据库的所有表所需的权限，而我只想授予单个数据库的权限。http://dba.stackexchange.com/questions/36870/permission-denied-in-postgres - Joe Lapp
4个回答
89

Postgres的基本概念

角色(Roles)是全局对象，可以访问数据库集群中的所有数据库 - 假设具备所需权限。

集群(Cluster)包含多个数据库(databases)，每个数据库又包含多个模式(schemas)。不同数据库中的相同名称模式不相关。授予权限仅适用于当前数据库中的特定模式(授予权限时的当前数据库)。

默认情况下，每个数据库都从一个名为public的模式开始。这是一种惯例，并且许多设置都以此开头。除此之外，public模式就像任何其他模式一样。

如果来自MySQL，则可以从单个模式public开始，完全忽略模式层。我经常在每个数据库中使用几十个模式。
模式有点(但不完全)类似于文件系统中的目录。

一旦使用多个模式，请确保了解search_path设置：

默认权限

根据GRANT文档：

PostgreSQL会将某些类型对象的默认权限授予PUBLIC。默认情况下，未向表、列、模式或表空间授予PUBLIC任何权限。对于其他类型，授予给PUBLIC的默认权限如下：CONNECT和CREATE TEMP TABLE适用于数据库；EXECUTE适用于函数；以及USAGE适用于语言。

所有这些默认值都可以通过ALTER DEFAULT PRIVILEGES更改：

用户组角色(Group role)

正如 @Craig 评论所说，最好将权限授予一个组角色，然后使特定用户成为该角色的成员（GRANT 组角色给用户角色）。这样更容易分配和撤销某些任务所需的权限包。

组角色只是没有登录的另一个角色。添加登录以将其转换为用户角色。更多信息：

预定义角色

更新：Postgres 14 或更高版本添加了新的预定义角色（正式称为“默认角色”）pg_read_all_data 和 pg_write_all_data，以简化下面的一些操作。请参见：

步骤

假设我们有一个新的数据库 mydb，一个组 mygrp 和一个用户 myusr...

在作为超级用户（例如 postgres）连接到所讨论的数据库时：

REVOKE ALL ON DATABASE mydb FROM public;  -- shut out the general public
GRANT CONNECT ON DATABASE mydb TO mygrp;  -- since we revoked from public

GRANT USAGE ON SCHEMA public TO mygrp;

如果您要指定一个用户对所有表拥有完全权限，可以按照您所写的方式进行操作（我可能会更加严格）：

GRANT ALL ON ALL TABLES IN SCHEMA public TO mygrp;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO mygrp; -- don't forget those

要为将来的对象设置默认权限，请对在此模式中创建对象的每个角色运行以下命令：

ALTER DEFAULT PRIVILEGES FOR ROLE myusr IN SCHEMA public
GRANT ALL ON TABLES TO mygrp;

ALTER DEFAULT PRIVILEGES FOR ROLE myusr IN SCHEMA public
GRANT ALL ON SEQUENCES TO mygrp;

-- more roles?

现在，将该组授权给用户：
GRANT mygrp TO myusr;

相关答案：

替代（非标准）设置

如果你来自MySQL，并且希望保持对数据库的权限隔离，那么你可能会喜欢这个非标准设置db_user_namespace。 根据文档:

此参数启用基于数据库的用户名。默认情况下关闭。

仔细阅读手册。我不使用此设置。它不会影响上述内容。

7
虽然没有必要单独回答，但我想补充一点，在一般情况下你不应该向个别用户授予权限，而应该创建角色（组）并授予它们访问表的权限。在创建用户时将用户添加到相应的角色中。这样做可以减少未来管理上的很多痛点。 - Craig Ringer
2
@CraigRinger：没错。这里有相关的答案和演示代码：https://dev59.com/TWUo5IYBdhLWcg3wpg7N#15868268 - Erwin Brandstetter
1
@ErwinBrandstetter，您的修改接近澄清我另一个问题。为什么在您的“ALTER DEFAULT PRIVILEGES”查询中提到“myusr”？在我看来，“GRANT mygrp TO myusr”应该足以授予myusr mygrp的权限。 - Joe Lapp
1
只要只有一个用户，您根本不需要使用“ALTER DEFAULT PRIVILEGES”。如果有多个用户，并且每个用户都可以创建对象，并且您希望其他用户共享新对象的权限，则自动将特权授予组角色。 - Erwin Brandstetter
2
只是想澄清一下。在数据库中创建资源的用户默认拥有该资源的所有权限。ALTER DEFAULT PRIVILEGES 的目的是让其他用户也能访问该资源。在这个例子中，通过为由 myusr 创建的所有资源授予 mygrp 的权限来实现这一点。AWS 文档给了我明确的解释：“定义将应用于未来由指定用户创建的对象的默认访问权限集”（我强调）。http://docs.aws.amazon.com/redshift/latest/dg/r_ALTER_DEFAULT_PRIVILEGES.html - Joe Lapp
显示剩余12条评论
3

您能否为我提供一个示例，以授予特定用户对特定数据库中所有表(包括已存在和未创建的表)的选择/插入/更新/删除权限？

在MySQL中，您所谓的数据库更类似于PostgreSQL模式而不是PostgreSQL数据库。

以超级用户身份连接到名为“test”的数据库。在这里：

$ psql -U postgres test

为现有用户"tester" 更改默认权限。
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT INSERT, SELECT, UPDATE, DELETE ON TABLES
    TO tester;

更改默认权限对现有表没有影响。这是设计上的考虑。对于现有表，请使用标准的GRANT和REVOKE语法。

无法为不存在的用户分配权限。

我认为这是我的一个瓶颈之一：假设MySQL和PostgreSQL数据库是类似的实体。您的解释与Erwin的解释相结合，纠正了这种误解。谢谢！ - Joe Lapp
1
我不想授予所有用户和所有数据库权限，因为这种方法会在任何一个用户受到威胁时危及所有数据库。我托管多个数据库客户端并为每个客户分配不同的登录名。
好的。当您将表分配给正确的角色时，授予的特权将是特定于角色而不是所有用户的！然后您可以决定谁要赋予角色。
1. 为每个数据库创建一个角色。一个角色可以容纳多个用户。 2. 然后将client-username分配给正确的角色。 3. 如果需要，还可以将your-username分配给每个角色。


(1a) 并非所有的表都已经创建，或者 (1b) 表已经被创建。

好的。您可以稍后创建表。
当您准备好时，请将表分配给正确的客户端 role。

CREATE TABLE tablename();
CREATE ROLE rolename;
ALTER TABLE tablename OWNER TO rolename;


（2a）用户尚未创建，或者（2b）用户已经创建。

好的。准备就绪后创建用户名即可。 如果您的客户需要多个用户名，请创建第二个client-username。

CREATE USER username1;
CREATE USER username2;


(3a) 用户尚未被分配特权，或者 (3b) 特权先前已经被分配给了用户。

好的。当您准备授予特权时，请创建该用户并将正确的角色分配给她。
使用 GRANT-TO 命令将角色分配给用户。

GRANT rolename TO username1;
GRANT rolename TO username2;


(4a) 用户只需要插入、更新、选择和删除行，或者 (4b) 用户还需要能够创建和删除表。

好的。您可以运行这些命令来为用户添加权限。

GRANT SELECT, UPDATE, INSERT, DELETE ON dbname TO role-or-user-name;
ALTER USER username1 CREATEDB;
1
如果您只使用PUBLIC，可以忘记模式。然后，您可以执行以下操作：(在此处查看文档)
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] table_name [, ...]
         | ALL TABLES IN SCHEMA schema_name [, ...] }
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
谢谢，但如果我使用PUBLIC，那么我不是授予所有用户对所有数据库的特定权限吗？我想授予单个用户对单个数据库中所有表格的权限，即使这些表格尚未创建。 - Joe Lapp
我说的是公共模式，而不是公共角色。 - Str.
非常抱歉，我想我没有理解你的回答。也许你可以给我一个例子，在特定数据库中针对所有已经存在或未创建的表格，授予某个特定用户选择/插入/更新/删除的权限？谢谢！ - Joe Lapp
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接