授予PostgreSQL中特定数据库的权限

问题描述

我从MySQL转移到PostgreSQL，并且用户权限打了墙。我习惯于使用以下命令为数据库的所有表分配用户所有权限：

 ＃MySQL 
将mydatabase的所有权限授予'myuser'@'localhost'，由'mypassword'标识; 
  

我觉得PostgreSQL 9.x解决方案涉及到赋予模式特权，我需要努力确定什么SQL发出，证明是过度的。我知道更多的小时的研究将产生一个答案，但我认为从MySQL迁移到PostgreSQL可以受益于至少一个网页提供了一个简单和完整的配方。这是我需要为用户发出的唯一命令。我不想为每个新表发出一个命令。

我不知道在PostgreSQL中不同的处理方式，所以我会列出一些我通常不得不在过去处理的情况。假设我们只是想修改已创建的单个数据库的特权。

我已经看到了所有数据库的所有权限的答案，但这不是我想要的。请，我正在寻找一个简单的食谱，虽然我不介意解释。

我不想授予所有用户和所有数据库的权限，似乎是常规的快捷方式，因为当任何一个用户被攻破时，该方法危及所有数据库。我托管多个数据库客户端并为每个客户端分配不同的登录信息。

看起来我还需要 USAGE 权限以获得 serial 列的增加值，但是我必须以某种顺序授予它。

b b 角色是可以访问数据库集群中所有数据库的全局对象。

集群拥有多个数据库，其中包含许多模式。不同DB中的模式（即使具有相同的名称）是不相关的。授予模式的权限只适用于当前DB（授予时的当前DB）中的特定模式。

每个数据库都以模式<$ c开头$ c> public 。这是一个惯例，许多设置从它开始。除此之外，模式 public 只是一个像任何其他模式。

以开始于单个模式 public ，有效地完全忽略模式层。我正在为每个数据库定期使用几十个模式。

模式有点（但不是完全）像文件系统中的目录。

使用多个模式，一定要了解 search_path 设置：

默认权限

所有这些默认值都可以更改：

群组角色

，最好是 GRANT 权限，然后创建该角色的特定用户成员（ GRANT 组角色为用户角色）。这种方式更容易处理和撤销某些任务所需的权限。

组角色只是另一个角色，无需登录。添加登录以将其转换为用户角色。更多：

配方

说，我们有一个新数据库 mydb ，组 mygrp 和用户 myusr ...

当连接到超级用户（ postgres 例如）：

  REVOKE ALL ON DATABASE mydb FROM public ; - 关闭一般公众
 GRANT CONNECT ON DATABASE mydb TO mygrp; - 因为我们从公共撤销
 
授予使用在SCHEMA公共TO mygrp; 
  

为所有表分配所有权限

 授予所有在SCHEMA中的所有表格public to mygrp; 
授予所有在SCHEMA中的所有序列public TO mygrp; - 不要忘记那些
  

要设置未来对象的默认权限，请运行 every角色：

在此架构中创建对象：

  ALTER默认权限角色myusr IN SCHEMA public 
 GRANT所有的表到mygrp; 
 
 ALTER DEFAULT PRIVILEGES FOR ROLE myusr IN SCHEMA public 
授予所有序列给mygrp; 
 
  - 更多角色？ 
  

现在，将组授予用户：

  GRANT mygrp TO myusr; 
  

相关答案：

替代（非标准）设置

来自MySQL，因为你想保留对数据库分开的权限，你可能喜欢这个非标准设置 db_user_namespace 。

仔细阅读手册。我不使用此设置。它不会使上述操作失效。

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
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.

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.

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 USAGE privilege to get the increasing values of a serial column, but I have to grant it on some sort of sequence. My problem got more complex.

解决方案

Basic concept in Postgres

Roles are global objects that can access all databases in a db cluster - given the required privileges.

A cluster holds 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).

Every database starts with a schema public by default. That's a convention, and many settings start with it. Other than that, the schema public is just a schema like any other.

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.

Once you make use of multiple schemas, be sure to understand search_path setting:

• How does the search_path influence identifier resolution and the "current schema"

Default privileges

Per documentation on GRANT:

All of these defaults can be changed with ALTER DEFAULT PRIVILEGES:

• Grant all on a specific schema in the db to a group role in PostgreSQL

Group role

Like @Craig commented, it's best to GRANT privileges to a group role and then make a specific user member of that role (GRANT the group role to the user role). this way it is simpler to deal out and revoke bundles of privileges needed for certain tasks.

A group role is just another role without login. Add a login to transform it into a user role. More:

• Why did PostgreSQL merge users and groups into roles?

Recipe

Say, we have a new database mydb, a group mygrp, and a user myusr ...

While connected to the database in question as superuser (postgres for instance):

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 tables like you wrote (I might be more restrictive):

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 role that 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:

• PostgreSQL - DB user should only be allowed to call functions

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:

Read the manual carefully. I don't use this setting. It does not void the above.

这篇关于授予PostgreSQL中特定数据库的权限的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！