SQL 为 PostgreSQL 中的特定数据库授予权限
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24918367/
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
Grant privileges for a particular database in PostgreSQL
提问by Joe Lapp
I'm moving from MySQL to PostgreSQL and have hit a wall with user privileges. I am used to assigning a user all privileges to all tables of a database with the following command:
我正在从 MySQL 迁移到 PostgreSQL，并且遇到了用户权限问题。我习惯于使用以下命令为用户分配对数据库所有表的所有权限：
# MySQL
grant all privileges on mydatabase.* to 'myuser'@'localhost' identified by 'mypassword';
It appears to me that the PostgreSQL 9.x solution involves assigning privileges to a "schema", but the effort required of me to figure out exactly what SQL to issue is proving excessive. I know that a few more hours of research will yield an answer, but I think everyone moving from MySQL to PostgreSQL could benefit from having at least one page on the web that provides a simple and complete recipe. This is the only command I have ever needed to issue for users. I'd rather not have to issue a command for every new table.
在我看来，PostgreSQL 9.x 解决方案涉及为“模式”分配权限，但事实证明，我需要努力弄清楚要发出什么 SQL 是多余的。我知道再多花几个小时的研究就会得到答案，但我认为从 MySQL 迁移到 PostgreSQL 的每个人都可以从网络上至少有一个页面提供简单而完整的方法中受益。这是我需要为用户发出的唯一命令。我宁愿不必为每个新表发出命令。
I don't know what scenarios have to be handled differently in PostgreSQL, so I'll list some of the scenarios that I have typically had to handle in the past. Assume that we only mean to modify privileges to a single database that has already been created.
我不知道在 PostgreSQL 中必须以不同的方式处理哪些场景，因此我将列出一些我过去通常不得不处理的场景。假设我们只想修改一个已经创建的单个数据库的权限。
(1a) Not all of the tables have been created yet, or (1b) the tables have already been created.
(2a) The user has not yet been created, or (2b) the user has already been created.
(3a) Privileges have not yet been assigned to the user, or (3b) privileges were previously assigned to the user.
(4a) The user only needs to insert, update, select, and delete rows, or (4b) the user also needs to be able to create and delete tables.
(1a) 尚未创建所有表，或 (1b) 已创建表。
(2a) 用户尚未创建，或 (2b) 用户已创建。
(3a) 权限尚未分配给用户，或 (3b) 权限之前已分配给用户。
(4a) 用户只需要插入、更新、选择和删除行，或者 (4b) 用户还需要能够创建和删除表。
I have seen answers that grant all privileges to all databases, but that's not what I want here. Please, I am looking for a simple recipe, although I wouldn't mind an explanation as well.
我已经看到将所有权限授予所有数据库的答案，但这不是我想要的。拜托，我正在寻找一个简单的食谱，尽管我也不介意解释。
I don't want to grant rights to all users and all databases, as seems to be the conventional shortcut, because that approach compromises all databases when any one user is compromised. I host multiple database clients and assign each client a different login.
我不想向所有用户和所有数据库授予权限，这似乎是传统的快捷方式，因为当任何一个用户受到损害时，这种方法都会损害所有数据库。我托管多个数据库客户端并为每个客户端分配不同的登录名。
It looks like I also need the USAGEprivilege to get the increasing values of a serialcolumn, but I have to grant it on some sort of sequence. My problem got more complex.
看起来我还需要USAGE获得增加一serial列值的特权，但我必须按某种顺序授予它。我的问题变得更复杂了。
回答by Erwin Brandstetter
Basic concept in Postgres
Postgres 的基本概念
Roles are global objects that can access all databases in a db cluster - given the required privileges.
角色是全局对象，可以访问数据库集群中的所有数据库 - 给定所需的权限。
A clusterholds many databases, which hold many schemas. Schemas (even with the same name) in different DBs are unrelated. Granting privileges for a schema only applies to this particular schema in the current DB (the current DB at the time of granting).
一个集群拥有许多数据库，这些数据库拥有许多模式。不同数据库中的模式（即使名称相同）是不相关的。授予架构的权限仅适用于当前 DB（授予时的当前 DB）中的此特定架构。
Every database starts with a schema publicby default. That's a convention, and many settings start with it. Other than that, the schema publicis just a schema like any other.
public默认情况下，每个数据库都以架构开头。这是一个约定，许多设置都是从它开始的。除此之外，模式public只是一个像任何其他模式一样的模式。
Coming from MySQL, you may want to start with a single schema public, effectively ignoring the schema layer completely. I am using dozens of schema per database regularly.
Schemas are a bit (but not completely) like directories in the file system.
来自 MySQL，您可能希望从单个 schema 开始public，有效地完全忽略 schema 层。我定期为每个数据库使用数十个模式。
模式有点（但不完全）像文件系统中的目录。
Once you make use of multiple schemas, be sure to understand search_pathsetting:
一旦你使用了多个模式，一定要了解search_path设置：
Default privileges
默认权限
PostgreSQL grants default privileges on some types of objects to
PUBLIC. No privileges are granted toPUBLICby default on tables, columns, schemas or tablespaces. For other types, the default privileges granted toPUBLICare as follows:CONNECTandCREATE TEMP TABLEfor databases;EXECUTEprivilege for functions; andUSAGEprivilege for languages.
PostgreSQL 将某些类型的对象的默认权限授予
PUBLIC.PUBLIC默认情况下不授予对表、列、模式或表空间的权限。对于其他类型，授予的默认权限PUBLIC如下：CONNECT和CREATE TEMP TABLE对于数据库；EXECUTE功能特权；和USAGE语言特权。
All of these defaults can be changed with ALTER DEFAULT PRIVILEGES:
所有这些默认值都可以更改为ALTER DEFAULT PRIVILEGES：
Group role
小组角色
Like @Craig commented, it's best to GRANTprivileges to a group role and then make a specific user member of that role (GRANTthe group role to the user role). this way it is simpler to deal out and revoke bundles of privileges needed for certain tasks.
就像@Craig 评论的那样，最好GRANT授予组角色的权限，然后让特定用户成为该角色的成员（GRANT组角色到用户角色）。通过这种方式，可以更轻松地处理和撤销某些任务所需的特权包。
A group role is just another role without login. Add a login to transform it into a user role. More:
组角色只是另一个没有登录的角色。添加登录名以将其转换为用户角色。更多的：
Recipe
食谱
Say, we have a new database mydb, a group mygrp, and a user myusr...
比如说，我们有一个新的数据库mydb、一个组mygrp和一个用户myusr......
While connected to the database in question as superuser (postgresfor instance):
以超级用户身份连接到相关数据库时（postgres例如）：
REVOKE ALL ON DATABASE mydb FROM public; -- shut out the general public
GRANT CONNECT ON DATABASE mydb TO mygrp; -- since we revoked from public
GRANT USAGE ON SCHEMA public TO mygrp;
To assign a user all privileges to all tableslike you wrote (I might be more restrictive):
a user all privileges to all tables像你写的那样分配（我可能会更严格）：
GRANT ALL ON ALL TABLES IN SCHEMA public TO mygrp;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO mygrp; -- don't forget those
To set default privileges for future objects, run for every rolethat creates objects in this schema:
要为未来的对象设置默认权限，请针对在此架构中创建对象的每个角色运行：
ALTER DEFAULT PRIVILEGES FOR ROLE myusr IN SCHEMA public
GRANT ALL ON TABLES TO mygrp;
ALTER DEFAULT PRIVILEGES FOR ROLE myusr IN SCHEMA public
GRANT ALL ON SEQUENCES TO mygrp;
-- more roles?
Now, grant the group to the user:
现在，将组授予用户：
GRANT mygrp TO myusr;
Related answer:
相关回答：
Alternative (non-standard) setting
替代（非标准）设置
Coming from MySQL, and since you want to keep privileges on databases separated, you might like this non-standard setting db_user_namespace. Per documentation:
来自 MySQL，并且由于您希望将数据库的权限分开，您可能会喜欢这个非标准设置db_user_namespace。根据文档：
This parameter enables per-database user names. It is off by default.
此参数启用每个数据库的用户名。默认情况下它是关闭的。
Read the manual carefully. I don't use this setting. It does not void the above.
仔细阅读说明书。我不使用这个设置。它不会使上述内容无效。
回答by Mike Sherrill 'Cat Recall'
Maybe you could give me an example that grants a specific user select/insert/update/delete on all tables -- those existing and not yet created -- of a specific database?
也许你可以给我一个例子，授予特定用户选择/插入/更新/删除所有表 - 那些现有的和尚未创建的 - 特定数据库？
What you call a database in MySQL more closely resembles a PostgreSQL schema than a PostgreSQL database.
您在 MySQL 中所说的数据库更类似于 PostgreSQL 模式，而不是 PostgreSQL 数据库。
Connect to database "test" as a superuser. Here that's
以超级用户身份连接到数据库“test”。这是
$ psql -U postgres test
Change the default privilegesfor the existing user "tester".
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT INSERT, SELECT, UPDATE, DELETE ON TABLES
TO tester;
Changing default privileges has no effect on existing tables. That's by design. For existing tables, use standard GRANT and REVOKE syntax.
更改默认权限对现有表没有影响。那是设计使然。对于现有表，使用标准的 GRANT 和 REVOKE 语法。
You can't assign privileges for a user that doesn't exist.
您不能为不存在的用户分配权限。
回答by Red
I don't want to grant rights to all users and all databases, as seems to be the conventional shortcut, because that approach compromises all databases when any one user is compromised. I host multiple database clients and assign each client a different login.
我不想向所有用户和所有数据库授予权限，这似乎是传统的快捷方式，因为当任何一个用户受到损害时，这种方法都会损害所有数据库。我托管多个数据库客户端并为每个客户端分配不同的登录名。
OK. When you assign tables to the correct role, the privileges granted will be role-specific and not to all users! Then you can decide who to give rolesto.
好的。当您将表分配给正确的角色时，授予的权限将是特定于角色的，而不是所有用户！然后你可以决定给谁roles。
- Create a
rolefor each database. A role can hold many users. - Then assign a
client-usernameto the correct role. - Also assign
your-usernameto each role if needed.
role为每个数据库创建一个。一个角色可以容纳多个用户。- 然后将 a 分配
client-username给正确的角色。 your-username如果需要，还可以分配给每个角色。
(1a) Not all of the tables have been created yet, or (1b) the tables have already been created.
(1a) 尚未创建所有表，或 (1b) 已创建表。
OK. You can create tables later.
When you are ready, assign tables to the correct client role.
好的。您可以稍后创建表。
准备好后，将表分配给正确的客户端role。
CREATE TABLE tablename();
CREATE ROLE rolename;
ALTER TABLE tablename OWNER TO rolename;
(2a) The user has not yet been created, or (2b) the user has already been created.
(2a) 用户尚未创建，或 (2b) 用户已创建。
OK. Create usernames when you are ready.If your client needs more than one username simply create a second client-username.
好的。准备好后创建用户名。如果您的客户需要多个用户名，只需创建第二个client-username.
CREATE USER username1;
CREATE USER username2;
(3a) Privileges have not yet been assigned to the user, or (3b) privileges were previously assigned to the user.
(3a) 权限尚未分配给用户，或 (3b) 权限之前已分配给用户。
OK. When you are ready to give privileges, create the user and assign the correct role to her.
Use GRANT-TO command to assign roles to users.
好的。当您准备好授予权限时，创建用户并为其分配正确的角色。
使用 GRANT-TO 命令为用户分配角色。
GRANT rolename TO username1;
GRANT rolename TO username2;
(4a) The user only needs to insert, update, select, and delete rows, or (4b) the user also needs to be able to create and delete tables.
(4a) 用户只需要插入、更新、选择和删除行，或者 (4b) 用户还需要能够创建和删除表。
OK. You run these commands to add permissions to your users.
好的。您可以运行这些命令为您的用户添加权限。
GRANT SELECT, UPDATE, INSERT, DELETE ON dbname TO role-or-user-name;
ALTER USER username1 CREATEDB;
回答by Str.
You can forget about the schema if you only use PUBLIC. Then you do something like this: (see doc here)
如果您只使用 PUBLIC，您可以忘记架构。然后你做这样的事情：（请参阅此处的文档）
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[, ...] | ALL [ PRIVILEGES ] }
ON { [ TABLE ] table_name [, ...]
| ALL TABLES IN SCHEMA schema_name [, ...] }
TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
