本文介绍了PostgreSQL:向用户授予PostgreSQL数据库上的所有权限的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我想授予用户对数据库的所有权限,而无需使其成为管理员.之所以要这样做,是因为目前DEV和PROD是同一集群上的不同DB,所以我不希望用户能够更改生产对象,但用户必须能够更改DEV上的对象./p>

我尝试过:

 将数据库MY_DB上的ALL授予组MY_GROUP; 

但是似乎没有任何许可.

然后我尝试:

 授予模式MY_SCHEMA的所有特权以将MY_GROUP分组; 

似乎允许我创建对象,但不能查询\删除该模式中属于其他用户的对象

我可以继续在MY_SCHEMA上向用户授予USAGE权限,但随后它会抱怨没有对该表的权限...

所以我想我的问题是:是否有任何简单的方法可以将所有权限授予数据库上的用户?

我正在使用PostgreSQL 8.1.23.

解决方案

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

用户显然需要访问数据库:

  GRANT将数据库my_db连接到my_user; 

(至少)对 schema USAGE 特权:

  SCHEMA上的GRANT用法公开给my_user; 

或在所有自定义架构上授予使用情况:

  DO$$开始-RAISE NOTICE'%',((-代替EXECUTE来查看生成的命令执行 (SELECT string_agg(format('GRANT USAGE ON SCHEMA%I TO my_user',nspname),';')从pg_namespacenspname<>所在的位置'information_schema'-排除信息模式和...AND nspname不喜欢'pg \ _%'-...系统架构);结尾$$; 

然后,获取所有的所有权限(需要Postgres 9.0 或更高版本).
并且不要忘记序列(如果有的话):

 将SCHEMA中所有表上的所有特权都授予my_user;将SCHEMA中所有序列上的所有特权授予public给my_user; 

对于旧版本,您可以使用授权向导"pgAdmin III(然后是默认的GUI).

还有其他一些对象, GRANT手册 具有完整列表.从Postgres 12开始:

但是其余的几乎不需要.详细信息:

考虑升级到当前版本.

I would like to give a user all the permissions on a database without making it an admin.The reason why I want to do that is that at the moment DEV and PROD are different DBs on the same cluster so I don't want a user to be able to change production objects but it must be able to change objects on DEV.

I tried:

grant ALL on database MY_DB to group MY_GROUP;

but it doesn't seem to give any permission.

Then I tried:

grant all privileges on schema MY_SCHEMA to group MY_GROUP;

and it seems to give me permission to create objects but not to query\delete objects on that schema that belong to other users

I could go on by giving USAGE permission to the user on MY_SCHEMA but then it would complain about not having permissions on the table ...

So I guess my question is: is there any easy way of giving all the permissions to a user on a DB?

I'm working on PostgreSQL 8.1.23.

解决方案

All commands must be executed while connected to the right database in the right database cluster. Make sure of it.

The user needs access to the database, obviously:

GRANT CONNECT ON DATABASE my_db TO my_user;

And (at least) the USAGE privilege on the schema:

GRANT USAGE ON SCHEMA public TO my_user;

Or grant USAGE on all custom schemas:

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
$$;

Then, all permissions for all tables (requires Postgres 9.0 or later).
And don't forget sequences (if any):

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

For older versions you could use the "Grant Wizard" of pgAdmin III (then default GUI).

There are some other objects, the manual for GRANT has the complete list. As of Postgres 12:

But the rest is rarely needed. More details:

Consider upgrading to a current version.

这篇关于PostgreSQL:向用户授予PostgreSQL数据库上的所有权限的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

05-23 06:10