SQL 将所有权限授予数据库上的用户
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22483555/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Give all the permissions to a user on a DB
提问by Diego
I would like to give an 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.
我想授予用户对数据库的所有权限，而无需使其成为管理员。我想要这样做的原因是，目前 DEV 和 PROD 是同一集群上的不同数据库，所以我不希望用户能够更改生产对象，但它必须能够更改 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 ...
我可以通过在 MY_SCHEMA 上向用户授予 USAGE 权限来继续，但是它会抱怨没有对表的权限......
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.
我正在研究 PostgreSQL 8.1.23。
回答by Erwin Brandstetter
The user needs access to the database, obviously:
用户需要访问数据库，显然：
GRANT CONNECT ON DATABASE my_db TO my_user;
And (at least) the USAGEprivilege on the schema:
以及（至少）架构USAGE上的特权：
GRANT USAGE ON SCHEMA public TO my_user;
Or grant USAGEon allcustom schemas:
或授予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
$$;
Then, all permissions for all tables(requires Postgres 9.0or later).
And don't forget sequences(if any):
然后，所有表的所有权限（需要 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;
For older versionsyou could use the "Grant Wizard" of pgAdmin III (the default GUI).
对于旧版本，您可以使用 pgAdmin III（默认 GUI）的“授权向导”。
There are some other objects, the manual for GRANThas the complete list as of Postgres 12:
还有一些其他对象，手册中GRANT有 Postgres 12 的完整列表：
privileges on a database object (table, column, view, foreign table, sequence, database, foreign-data wrapper, foreign server, function, procedure, procedural language, schema, or tablespace)
对数据库对象（表、列、视图、外部表、序列、数据库、外部数据包装器、外部服务器、函数、过程、过程语言、模式或表空间）的特权
But the rest is rarely needed. More details:
但其余的很少需要。更多细节：
- How to manage DEFAULT PRIVILEGES for USERs on a DATABASE vs SCHEMA?
- Grant privileges for a particular database in PostgreSQL
- How to grant all privileges on views to arbitrary user
Consider upgrading to a current version.
考虑升级到当前版本。
回答by Unkas
GRANT ALL PRIVILEGES ON DATABASE "my_db" to my_user;
回答by Patrick
In PostgreSQL 9.0+ you would do the following:
在 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;
If you want to enable this for newly created relations too, then set the default permissions:
如果您也想为新创建的关系启用此功能，请设置默认权限：
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;
However, seeing that you use 8.1 you have to code it yourself:
但是，看到您使用 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;
This will set the privileges on all relations: tables, views, indexes, sequences, etc. If you want to restrict that, filter on pg_class.relkind. See the pg_class docsfor details.
这将设置所有关系的权限：表、视图、索引、序列等。如果你想限制它，过滤pg_class.relkind。有关详细信息，请参阅pg_class 文档。
You should run this function as superuser and as regular as your application requires. An option would be to package this in a cron job that executes every day or every hour.
您应该以超级用户身份运行此功能，并按照您的应用程序要求定期运行。一种选择是将其打包在每天或每小时执行的 cron 作业中。
回答by Sumit Arora
I did the following to add a role 'eSumit' on PostgreSQL 9.4.15 database and provide all permission to this role :
我执行以下操作以在 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;
Also checked the pg_table enteries via :
还通过以下方式检查了 pg_table 条目：
select * from pg_roles;
从 pg_roles 中选择 *；
Database queries snapshot :
数据库查询快照：
