为用户授予对所有表的访问权限

Question

我对Postgres还不熟悉，正在尝试迁移我们的MySQL数据库。在MySQL中，我可以授予低权限用户SELECT、UPDATE、INSERT和DELETE权限，并使这些授权适用于指定数据库中的所有表。在Postgres中，我可能遗漏了一些东西，因为看起来我必须逐个表授予这些权限。对于每个数据库和每个数据库中的数百张表而言，这似乎是一个艰巨的任务，难以启动。此外，一旦数据库投入运行，添加表的频率足够高，以至于我不想每次都授予权限，除非绝对必要。

如何最好地完成这个任务呢？

- PlaidFan

Answer 1

首先，您必须能够连接到数据库以便运行查询。这可以通过...实现。

REVOKE CONNECT ON DATABASE your_database FROM PUBLIC;

GRANT CONNECT
ON DATABASE database_name 
TO user_name;

REVOKE是必要的因为

关键字PUBLIC表示权限将授予所有角色，包括以后可能创建的角色。PUBLIC可以被视为一个隐式定义的组，始终包括所有角色。任何特定角色将直接被授予的权限总和，当前成员角色被授予的权限以及PUBLIC被授予的权限。

如果您真的想限制用户只能执行DML语句，那么还有一些工作要做：

REVOKE ALL
ON ALL TABLES IN SCHEMA public 
FROM PUBLIC;

GRANT SELECT, INSERT, UPDATE, DELETE
ON ALL TABLES IN SCHEMA public 
TO user_name;

这些假设您只有一个架构（默认情况下命名为“public”）。

正如Jack Douglas指出的那样，上述仅提供了对已经存在的表的权限。要实现对未来表的同样权限，您必须定义默认权限。

ALTER DEFAULT PRIVILEGES 
    FOR ROLE some_role   -- Alternatively "FOR USER"
    IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO user_name;

在这里，some_role是创建表的角色，而user_name则是获得权限的人。定义这个时，你必须以some_role或其成员身份登录。

最后，你还需要对序列执行相同的操作（感谢PlaidFan指出）- 这里需要的是USAGE权限。

- dezso

---

谢谢，FOR some_role 是我之前在后续创建的表中遗漏的关键部分。但是我并不需要以 some_role 的身份登录，如果我以默认管理员 postgres 用户执行查询，它也能正常工作。 - JustAMartin

我为我的用户执行了所有这些命令，但是我仍然没有被允许在数据库中的表上使用SELECT。只有在我再次撤销了所有权限并重新授权后，它才起作用。 - rubo77

请注意，如果用户不是数据库的所有者，则更新（刷新）物化视图需要额外的权限。 - KuN

REVOKE CONNECT FROM PUBLIC会从超级用户中移除CONNECT权限吗？ - Meraj al Maksud

@MerajalMaksud 它不行 - dezso

Answer 2

假设你想给予他们所有的特权 - 这样做：

grant all privileges on database dbname to dbuser;

其中dbname是您的数据库名称，dbuser是用户的名称。

- alxkls

---

这将在数据库上添加以下权限：CREATE, CONNECT, TEMPORARY。对表没有任何权限。 - dezso

对于所有表格来说，一个类似的命令是 GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO dbuser;。 - Dolan Antenucci

我也觉得这很有用：GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO dbuser; - Paolo

mysql bg? 读一下评论！ - WEBjuju

Answer 3

授予数据库中所有表的所有权限可以通过以下方式实现：

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA <schema_name> TO <username>;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA <schema_name> TO <username>;

- Ganbayar Gansukh

---

如果有多个模式怎么办？ - asds_asds

@asds_asds 你需要逐个执行所有所需的模式。 - Ganbayar Gansukh

Answer 4

可能是因为我在这里做错了什么，因为我对PostgreSQL非常陌生。但这只解决了问题的第一部分 - 设置所有现有表的权限。

为了正确设置我的用户对新创建的表的权限，我必须为该用户设置默认权限。

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON tables TO user_name;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, USAGE ON sequences TO user_name;

- Will

Answer 5

--Create User

CREATE USER my_user_test WITH LOGIN NOSUPERUSER NOCREATEDB  NOCREATEROLE    INHERIT NOREPLICATION   CONNECTION LIMIT -1 PASSWORD 'xxxxxxx';

-- Grant connect to my data base

GRANT CONNECT ON DATABASE my_db_test TO my_user_test;

-- Grant usage the schema

GRANT USAGE ON SCHEMA my_sch_test TO my_user_test ;

-- Grant all table for SELECT, INSERT, UPDATE, DELETE

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA my_sch_test TO my_user_test;

- Jorge Valenzuela

---

如果我需要允许使用多个模式，而这些模式又包含多个表，该怎么办？ - asds_asds