PostgreSQL:赋予一个用户对PostgreSQL数据库的所有权限

我想给一个用户在数据库上的所有权限，而不使它成为管理员。我想这样做的原因是，目前DEV和PROD是同一集群上的不同db，所以我不希望用户能够更改生产对象，但必须能够更改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工作。

当前回答

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

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

显然，角色需要访问数据库。默认情况下，它被授予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 pg_read_all_data TO my_user;
GRANT pg_write_all_data TO my_user;

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

pg_read_all_data Read all data (tables, views, sequences), as if having SELECT rights on those objects, and USAGE rights on all schemas, even without having it explicitly. This role does not have the role attribute BYPASSRLS set. If RLS is being used, an administrator may wish to set BYPASSRLS on roles which this role is GRANTed to. pg_write_all_data Write all data (tables, views, sequences), as if having INSERT, UPDATE, and DELETE rights on those objects, and USAGE rights on all schemas, even without having it explicitly. This role does not have the role attribute BYPASSRLS set. If RLS is being used, an administrator may wish to set BYPASSRLS on roles which this role is GRANTed to.

不使用预定义角色的所有权限(任何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中为特定的数据库授予权限如何管理默认特权的用户在数据库vs SCHEMA?

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

数据库对象(表、列、视图、外部表、序列、数据库、外部数据包、外部服务器、函数、过程、过程语言、模式或表空间)上的特权

但剩下的就很少需要了。更多的细节:

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

考虑升级到当前版本。

2014-03-18 16:56:17

其他回答

GRANT ALL ON SCHEMA schema_name TO user_name;

2021-07-25 08:28:15

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

2014-03-18 15:36:47

GRANT USAGE ON SCHEMA schema_name

2020-09-17 11:44:16