PostgreSQL：授予用户对PostgreSQL数据库的所有权限

Question

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

我尝试了：

grant ALL on database MY_DB to group MY_GROUP;

但它似乎没有给予任何许可。

然后我尝试：

grant all privileges on schema MY_SCHEMA to group MY_GROUP;

它似乎允许我创建对象，但不允许我在属于其他用户的模式上查询\delete对象

我可以继续通过给予用户USAGE权限MY_SCHEMA但它会抱怨没有在表上的权限...

所以我想我的问题是：有没有简单的方法可以将所有权限授予DB上的用户？

我正在开发PostgreSQL 8.1.23。

Answer 1

在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作业中。

Answer 2

GRANT ALL PRIVILEGES ON DATABASE "my_db" to my_user;

Answer 3

所有命令都必须在连接到正确的数据库集群时执行。确保它。

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

显然，角色需要访问数据库。默认情况下，这是授予公共的。其他：

GRANT CONNECT ON DATABASE my_db TO my_user;

Postgres 14添加了预定义的非登录角色pg\u read\u all\u data
他们对所有表、视图和序列都具有选择权限、更新权限、删除权限。加上模式上的用法。我们可以授予这些角色的成员资格：

GRANT pg_read_all_data TO my_user;
GRANT pg_write_all_data TO my_user;

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

pg\u read\u all\u数据

读取所有数据（表、视图、序列），就好像拥有对这些对象的选择权限，以及对所有模式的使用权限，即使没有明确的权限。此角色未设置角色属性BYPASSRLS。如果正在使用RLS，管理员可能希望在授予此角色的角色上设置绕过RLS。

pg_write_all_data代码

写入所有数据（表、视图、序列），就好像对这些对象具有INSERT、UPDATE和DELETE权限，以及对所有模式的USAGE权限，即使没有显式拥有它。此角色没有设置角色属性BYPASSRLS。如果正在使用RLS，管理员可能希望在GRANT编辑此角色的角色上设置BYPASSRLS。

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

角色需要（至少）架构上的使用权限。同样，如果这是授予公共机构的，那么您就可以参保了。其他：

GRANT USAGE ON SCHEMA public TO my_user;

或在所有自定义架构上授予用法：

sql prettyprint-override">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的“授予向导”来使用GUI。

这包括现有对象的权限。若要覆盖将来的对象，请设置默认权限。请参见：

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

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

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

但其余的很少需要。更多细节：

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

考虑升级到当前版本。