PostgreSQL：在PostgreSQL数据库上为用户授予所有权限

Question

我希望能够在不使其成为管理员的情况下，将所有数据库权限授予用户。 我这样做的原因是目前DEV和PROD是同一集群上的不同数据库，因此我不希望用户能够更改生产对象，但必须能够更改DEV上的对象。 我尝试过：

grant ALL on database MY_DB to group MY_GROUP;

但似乎没有给任何权限。

然后我尝试了：

grant all privileges on schema MY_SCHEMA to group MY_GROUP;

看起来它似乎给了我在该模式下创建对象的权限，但不能查询/删除属于其他用户的该模式下的对象。

我可以通过在 MY_SCHEMA 上给用户授予 USAGE 权限来继续进行，但然后它会抱怨没有在表上拥有权限...

所以我想问的是：有没有一种简单的方法可以将所有权限授予用户在数据库上？

我正在使用 PostgreSQL 8.1.23。

- Diego

Answer 1

所有命令必须在连接到正确的数据库集群时执行。请确保这一点。

角色是数据库集群的对象。同一集群中的所有数据库共享定义的角色集。权限是按数据库/模式/表等授予/撤销的。

显然，角色需要访问数据库。默认情况下，这是授予给PUBLIC的。否则：

GRANT CONNECT ON DATABASE my_db TO my_user;

Postgres 14或更高版本的基本权限

Postgres 14增加了预定义的非登录角色pg_read_all_data / pg_write_all_data。
它们具有对所有表、视图和序列的SELECT / INSERT、UPDATE、DELETE权限，以及模式上的USAGE权限。我们可以授予这些角色的成员资格：GRANT。

GRANT pg_read_all_data TO my_user;
GRANT pg_write_all_data TO my_user;

这涵盖了所有基本的DML命令（但不包括DDL，也不包括一些特殊命令，如TRUNCATE或函数的EXECUTE权限！）。

手册：

pg_read_all_data

读取所有数据（表、视图、序列），就像对这些对象具有SELECT权限和所有模式的USAGE权限一样，即使没有明确声明。该角色没有设置BYPASSRLS角色属性。如果使用了RLS，则管理员可能希望在授予此角色的角色上设置BYPASSRLS。

pg_write_all_data

写入所有数据（表、视图、序列），就像对这些对象具有INSERT、UPDATE和DELETE权限和所有模式的USAGE权限一样，即使没有明确声明。该角色没有设置BYPASSRLS角色属性。如果使用了RLS，则管理员可能希望在授予此角色的角色上设置BYPASSRLS。

不使用预定义角色的所有权限（任何Postgres版本）

命令必须在连接到正确的数据库时执行。请确保。

该角色需要对模式具有（至少）USAGE权限。同样，如果授予了PUBLIC，则已经涵盖。否则：

GRANT USAGE ON SCHEMA public TO my_user;

或者在所有自定义模式上授予 USAGE 权限：

DO
$$
BEGIN
   -- RAISE NOTICE '%', (  -- use instead of EXECUTE to see generated commands
   EXECUTE (
   SELECT string_agg(format('GRANT USAGE ON SCHEMA %I TO my_user', nspname), '; ')
   FROM   pg_namespace
   WHERE  nspname <> 'information_schema' -- exclude information schema and ...
   AND    nspname NOT LIKE 'pg\_%'        -- ... system schemas
   );
END
$$;

然后，需要所有表的所有权限（需要Postgres 9.0或更高版本）。
并且不要忘记序列（如果有）：

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO my_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO my_user;

或者，您可以使用 pgAdmin 4 的 "Grant Wizard" 来使用 GUI。

这涵盖了现有对象的权限。为了覆盖未来的对象，请设置 DEFAULT PRIVILEGES。请参见：

• 在 PostgreSQL 中为特定数据库授予权限
• 如何管理用户在数据库与模式上的 DEFAULT PRIVILEGES？

还有一些其他对象，GRANT 的手册 包含完整列表。截至 Postgres 14：

{数据库对象（表、列、视图、外部表、序列、数据库、外部数据包装器、外部服务器、函数、过程、过程语言、模式或表空间）的权限}

但是很少需要其余的细节:

• 在PostgreSQL中为特定数据库授予权限
• 如何将所有视图的权限授予任意用户

考虑升级到当前版本。

- Erwin Brandstetter

---

首先，我使用了“sudo -i -u postgres”。 - Abhishek Patil

@Abhishek：使用psql交互式终端或连接到问题数据库的其他客户端程序之一。在那里，您可以在交互式会话中使用SQL命令。您不能在Linux shell中键入SQL命令。 - Erwin Brandstetter

请注意：在运行这些命令之前，您需要先在psql中切换到数据库：“\c my_db”。 - Martín De la Fuente

哦，这个答案非常完美，只需要澄清一下新创建的表等不会获得这些新权限。 为此，它们应该使用 ALTER DEFAULT PRIVILEGES 指定... - Ivan Baldo

@Ivan：我添加了一点内容以使其更清晰。 - Erwin Brandstetter

有没有简短易懂的说明，适用于那些只想创建一个名为“test”的新数据库，并在其中授予名为“tester”的用户所有权限的人 - 我以为这很简单，但是一个小时后...我不太确定了。 - PandaWood

Answer 2

GRANT ALL PRIVILEGES ON DATABASE "my_db" to my_user;

- Unkas

---

将所有权限授予ON DATABASE听起来很强大，但实际作用有限。这只是一个开始。它不会授予任何包含对象的权限。 - Erwin Brandstetter

@ErwinBrandstetter，那么有没有回答OP的问题的方法...在给定数据库中同时执行所有特权和所有包含对象的简单方法？ - PandaWood

Answer 3

在PostgreSQL 9.0+中，您应该执行以下操作：

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA MY_SCHEMA TO MY_GROUP;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA MY_SCHEMA TO MY_GROUP;

如果您希望将此功能用于新创建的关系，请设置默认权限：

ALTER DEFAULT PRIVILEGES IN SCHEMA MY_SCHEMA
  GRANT ALL PRIVILEGES ON TABLES TO MY_GROUP;
ALTER DEFAULT PRIVILEGES IN SCHEMA MY_SCHEMA
  GRANT ALL PRIVILEGES ON SEQUENCES TO MY_GROUP;

然而，考虑到您使用的是8.1版本，您必须自己编写代码：

CREATE FUNCTION grant_all_in_schema (schname name, grant_to name) RETURNS integer AS $$
DECLARE
  rel RECORD;
BEGIN
  FOR rel IN
    SELECT c.relname
    FROM pg_class c
    JOIN pg_namespace s ON c.namespace = s.oid
    WHERE s.nspname = schname
  LOOP
    EXECUTE 'GRANT ALL PRIVILEGES ON ' || quote_ident(schname) || '.' || rel.relname || ' TO ' || quote_ident(grant_to);
  END LOOP;
  RETURN 1;
END; $$ LANGUAGE plpgsql STRICT;
REVOKE ALL ON FUNCTION grant_all_in_schema(name, name) FROM PUBLIC;

这将设置所有关系的权限：表、视图、索引、序列等。如果您想要限制权限，请过滤 pg_class.relkind。有关详细信息，请参见pg_class文档。

您应该以超级用户身份运行此函数，并根据应用程序的要求定期运行。一种选择是将其打包到每天或每小时执行的cron作业中。

- Patrick

---

嗨，Patrick，“ALL TABLES”在8.1上不可用（http://www.postgresql.org/docs/8.1/static/sql-grant.html）。我知道我可以循环遍历表并逐个授予权限，但这正是我想避免的。 但还是谢谢你的帮助。 - Diego

@Diego：添加了8.1的解决方案。 - Patrick

谢谢 Patrick。最终我使用了和你相似的方法，但没有使用“GRANT ALL”。由于某种原因，它似乎没有起作用。例如，我执行了以下命令：grant ALL on schema test to userA;但之后userA仍然无法从schema test中读取表的数据。 - Diego

你应该在模式上授予 USAGE 权限。然后在该模式中的所有关系（表、视图、序列、索引等）上，你必须分别授予 SELECT、INSERT、UPDATE、DELETE、TRUNCATE 权限。模式是命名空间，关系是数据所在的位置。 - Patrick

提到 ALTER DEFAULT 时加一 - Gradyn Wursten

Answer 4

我在 PostgreSQL 9.4.15 数据库中进行了以下操作，以添加一个名为 'eSumit' 的角色，并为该角色提供所有权限:

CREATE ROLE eSumit;

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO eSumit;

GRANT ALL PRIVILEGES ON DATABASE "postgres" to eSumit;

ALTER USER eSumit WITH SUPERUSER;

我也通过以下方式检查了pg_table的条目:

select * from pg_roles;

数据库查询快照：

- Sumit Arora

---

我使用的是Postgres 10.3版本，当我在数据库名称周围使用引号时，它会抛出语法错误。 - sajid

Answer 5

在PostgreSQL 12及以上版本中，可以将一个数据库表的所有权限授予一个角色/用户/账户。

语法如下：

GRANT ALL ON table_name TO role_name;

如果你想将权限授予数据库中的所有表格，则语法如下：

GRANT ALL ON ALL TABLES TO role_name;

如果想要将其授权给数据库模式中的所有表格，则语法将是：

grant all on schema_name.* to username;

GRANT ALL ON ALL TABLES IN SCHEMA schema_name TO role_name;

注意：在为用户授予数据库权限之前，您需要选择数据库。

资源：PostgreSQL GRANT

就这些了。

希望这可以帮到您

- Promise Preston

---

在模式schema_name中授予role_name对所有表的使用权限，语句为GRANT USAGE ON ALL TABLES IN SCHEMA schema_name TO role_name。如果没有指定模式名称，将会出现错误，错误信息为ERROR: 42601: syntax error at or near "TO"。 - phil pirozhkov

我可以轻松地对Postgres运行命令，但是我找不到任何方法来"选择数据库" - 也就是说，让那些没有指定数据库名称的命令正常工作。 - PandaWood

Answer 6

赋予用户在PostgreSQL数据库中的所有权限:

命令:

grant all privileges on database [database_name] to [database_user_name];

范例：

grant all privileges on database studentdb to shaifullah;

OR

GRANT ALL PRIVILEGES ON DATABASE studentdb TO shaifullah;

- MD. SHIFULLAH

Answer 7

授予用户对模式（schema）schema_name 的使用权限。

- Yaditya

Answer 8

GRANT ALL ON SCHEMA schema_name TO user_name;

- Ryabchenko Alexander