本文属于机器翻译版本。若本译文内容与英语原文存在差异，则一律以英文原文为准。
# 在 Aurora PostgreSQL 兼容中创建应用程序用户和角色
*Abhishek Verma，Amazon Web Services*
## Summary
迁移到 Amazon Aurora PostgreSQL 兼容版时，必须在 Aurora PostgreSQL 兼容数据库中创建源数据库上存在的数据库用户和角色。您可以使用两种不同的方法在 Aurora PostgreSQL 兼容中创建用户和角色：
+ 在目标数据库中使用与源数据库中类似的用户和角色。在这种方法中，从源数据库中提取用户和角色的数据定义语言 (DDLs)。然后，它们将被转换并应用于目标 Aurora PostgreSQL 兼容数据库。例如，博客文章[使用 SQL 将用户、角色和授权从 Oracle 映射到 PostgreSQL](https://aws.amazon.com/blogs/database/use-sql-to-map-users-roles-and-grants-from-oracle-to-postgresql)中介绍了如何使用从 Oracle 源数据库引擎中提取。
+ 使用在开发、管理以及在数据库中执行其他相关操作时常用的标准化用户和角色。这包括由相应用户执行的只读、读/写、开发、管理和部署操作。
此模式包含在标准化用户和角色方法所需 Aurora PostgreSQL 兼容中创建用户和角色所需授权。用户和角色创建步骤与向数据库用户授予最低权限的安全策略保持一致。下表列出了用户、其对应的角色及其在数据库上的详细信息。
|
|
| Users | 角色 | 用途 |
| --- |--- |--- |
| `APP_read` | `APP_RO` | 用于对架构 `APP` 的只读访问 |
| `APP_WRITE` | `APP_RW` | 用于对架构 `APP` 的写入和读取操作 |
| `APP_dev_user` | `APP_DEV` | 用于架构 `APP_DEV` 上的开发目的，对架构 `APP` 具有只读访问权限 |
| `Admin_User` | `rds_superuser` | 用于对数据库执行管理员操作 |
| `APP` | `APP_DEP` | 用于在 `APP` 架构下创建对象，以及在 `APP` 架构中部署对象 |
## 先决条件和限制
**先决条件**
+ 活跃 Amazon Web Services (AWS) account
+ PostgreSQL 数据库、Amazon Aurora PostgreSQL 兼容版数据库或适用于 PostgreSQL 的 Amazon Relational Database Service（Amazon RDS）数据库
**产品版本**
+ 所有 PostgreSQL 版本
## 架构
**源技术堆栈 **
+ 任何数据库
**目标技术堆栈 **
+ 兼容 Amazon Aurora PostgreSQL
**目标架构**
下图显示了 Aurora PostgreSQL 兼容数据库中的用户角色和架构架构。
**自动化和扩缩**
此模式包含用户、角色和架构创建脚本，您可以多次运行这些脚本，而不会对源数据库或目标数据库的现有用户产生任何影响。
## 工具
**Amazon Web Services**
+ [Amazon Aurora PostgreSQL 兼容版](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.AuroraPostgreSQL.html)是一个完全托管的、与 ACID 兼容的关系数据库引擎，可帮助您建立、运行和扩展 PostgreSQL 部署。
**其他服务**
+ [psql](https://www.postgresql.org/docs/current/app-psql.html) 是一个基于终端的前端工具，随每个 PostgreSQL 数据库安装一起安装。它有一个命令行界面，用于运行 SQL、PL-PGSQL 和操作系统命令。
+ [pgAdmin](https://www.pgadmin.org/) 是一种适用于 PostgreSQL 的开源管理工具。它提供了一个图形界面，可帮助您创建、维护和使用数据库对象。
## 操作说明
### 创建用户和角色
| Task | 说明 | 所需技能 |
| --- | --- | --- |
| 创建部署用户。 | 部署用户 `APP` 将用于在部署期间创建和修改数据库对象。使用以下脚本在架构 `APP` 中创建部署用户角色 `APP_DEP`。验证访问权限以确保此用户仅具有在所需架构 `APP` 中创建对象的权限。1. 连接到管理员用户，并创建架构。CREATE SCHEMA APP;
2. 创建 用户。CREATE USER APP WITH PASSWORD ;
3. 创建角色。CREATE ROLE APP_DEP ;
GRANT all on schema APP to APP_DEP ;
GRANT USAGE ON SCHEMA APP to APP_DEP ;
GRANT connect on database to APP_DEP ;
GRANT APP_DEP to APP;
4. 要测试权限，请连接到 `APP` 并创建表。set search_path to APP;
SET
CREATE TABLE test(id integer ) ;
CREATE TABLE
5. 检查权限。select schemaname , tablename , tableowner from pg_tables where tablename like 'test' ;
schemaname | tablename | tableowner
APP | test | APP
| 数据库管理员 |
| 创建只读用户。 | 只读用户 `APP_read` 将用于在架构 `APP` 中执行只读操作。使用以下脚本创建只读用户。验证访问权限以确保此用户仅具有读取架构 `APP` 中的对象的权限，并自动授予对架构 `APP` 中创建的任何新对象的读取访问权限。1. 创建用户 `APP_read`。create user APP_read ;
alter user APP_read with password 'your_password' ;
2. 创建角色。CREATE ROLE APP_ro ;
GRANT SELECT ON ALL TABLES IN SCHEMA APP TO APP_RO ;
GRANT USAGE ON SCHEMA APP TO APP_RO
GRANT CONNECT ON DATABASE testdb TO APP_RO ;
GRANT APP_RO TO APP_read;
3. 要测试权限，请使用 `APP_read` 用户登录。set search_path to APP ;
create table test1( id integer) ;
ERROR: permission denied for schema APP
LINE 1: create table test1( id integer) ;
insert into test values (34) ;
ERROR: permission denied for table test SQL state: 42501
select from test
no rows selected
| 数据库管理员 |
| 创建 read/write 用户。 | 该 read/write 用户`APP_WRITE`将用于对架构执行读取和写入操作`APP`。使用以下脚本创建 read/write 用户并授予其`APP_RW`角色。验证访问权限以确保此用户仅对架构 `APP` 中的对象具有读写权限，并自动授予对架构`APP`中创建的任何新对象的读取和写入访问权限。1. 创建 用户。CREATE USER APP_WRITE ;
alter user APP_WRITE with password 'your_password' ;
2. 创建角色。CREATE ROLE APP_RW;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA APP TO APP_RW ;
GRANT CONNECT ON DATABASE postgres to APP_RW ;
GRANT USAGE ON SCHEMA APP to APP_RW ;
ALTER DEFAULT PRIVILEGES IN SCHEMA APP
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO APP_RW ;
GRANT APP_RW to APP_WRITE
3. 要测试权限，请使用 `APP_WRITE` 用户登录。SET SEARCH_PATH to APP;
CREATE TABLE test1( id integer) ;
ERROR: permission denied for schema APP
LINE 1: create table test1( id integer) ;
SELECT * FROM test ;
id
----
12
INSERT INTO test values (31) ;
INSERT 0 1
| |
| 创建管理员用户。 | 管理员用户 `Admin_User` 将用于对数据库执行管理操作。这些操作的示例包括 `CREATE ROLE` 和 `CREATE DATABASE`。`Admin_User` 使用内置角色 `rds_superuser` 对数据库执行管理操作。使用以下脚本在数据库中创建并测试管理员用户 `Admin_User` 的权限。1. 创建用户并授予角色。create user Admin_User WITH PASSWORD ‘Your password’
ALTER user Admin_user CREATEDB;
ALTER user Admin_user CREATEROLE;
2. 要测试权限，请从 `Admin_User` 用户登录。SELECT * FROM APP.test ;
id
----
31
CREATE ROLE TEST ;
CREATE DATABASE test123 ;
| 数据库管理员 |
| 创建开发用户。 | 开发用户 `APP_dev_user` 将有权在其本地架构 `APP_DEV` 中创建对象，并有权在架构 `APP` 中读取访问权限。使用以下脚本在数据库中创建和测试用户 `APP_dev_user` 的权限。1. 创建 用户。CREATE USER APP1_dev_user with password ‘your password’;
2. 为 `App_dev_user` 创建 `APP_DEV` 架构。CREATE SCHEMA APP1_DEV ;
3. 创建 `APP_DEV`角色。CREATE ROLE APP1_DEV ;
GRANT APP1_RO to APP1_DEV ;
GRANT SELECT ON ALL TABLES IN SCHEMA APP1_DEV to APP1_dev_user
GRANT USAGE, CREATE ON SCHEMA APP1_DEV to APP1_DEV_USER
GRANT APP1_DEV to APP1_DEV_USER ;
4. 要测试权限，请从 `APP_dev_user` 登录。CREATE TABLE APP1_dev.test1( id integer ) ;
CREATE TABLE
INSERT into APP1_dev.test1 ( select * from APP1.test );
INSERT 0 1
CREATE TABLE APP1.test4 ( id int) ;
ERROR: permission denied for schema APP1
LINE 1: create table APP1.test4 ( id int) ;
| 数据库管理员 |
## 相关资源
**PostgreSQL 文档**
+ [CREATE ROLE](https://www.postgresql.org/docs/9.1/sql-createrole.html)
+ [CREATE USER](https://www.postgresql.org/docs/8.0/sql-createuser.html)
+ [预定义路由](https://www.postgresql.org/docs/14/predefined-roles.html)
## 附加信息
**PostgreSQL 14 增强功能**
PostgreSQL 14 提供了一组预定义的角色，这些角色允许访问某些常用的特权功能和信息。管理员（包括具有 `CREATE ROLE` 权限的角色）可以将这些角色或其环境中的其他角色授予用户，从而为他们提供对指定功能和信息的访问权限。
管理员可以使用 `GRANT` 命令授予用户访问这些角色的权限。例如，要向 `Admin_User` 授予 `pg_signal_backend` 角色，可以运行以下命令。
```
GRANT pg_signal_backend TO Admin_User;
```
`pg_signal_backend` 角色旨在允许管理员启用受信任的非超级用户角色向其他后端发送信号。有关更多信息，请参阅 [PostgreSQL 14 增强功能](https://www.postgresql.org/docs/14/predefined-roles.html)。
**微调访问**
在某些情况下，可能需要为用户提供更精细的访问（例如，基于表的访问或基于列的访问）。在这种情况下，可以创建其他角色来向用户授予这些权限。有关更多信息，请参阅 [PostgreSQL 授予](https://www.postgresql.org/docs/8.4/sql-grant.html)。