本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。
# 在 Aurora PostgreSQL 相容中建立應用程式使用者和角色
*Abhishek Verma，Amazon Web Services*
## 總結
當您遷移至 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 相容中建立使用者和角色所需的授予。使用者和角色建立步驟符合授予最低權限給資料庫使用者的安全政策。下表列出使用者、其對應的角色，以及其在資料庫上的詳細資訊。
|
|
| 使用者 | 角色 | 用途 |
| --- |--- |--- |
| `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) 帳戶
+ PostgreSQL 資料庫、Amazon Aurora PostgreSQL 相容版本資料庫，或 PostgreSQL 資料庫的 Amazon Relational Database Service (Amazon RDS)
**產品版本**
+ PostgreSQL 的所有版本
## Architecture
**來源技術堆疊**
+ 任何資料庫
**目標技術堆疊**
+ Amazon Aurora PostgreSQL 相容
**目標架構**
下圖顯示 Aurora PostgreSQL 相容資料庫中的使用者角色和結構描述架構。
**自動化和擴展**
此模式包含使用者、角色和結構描述建立指令碼，您可以執行多次，而不會影響來源或目標資料庫的現有使用者。
## 工具
**AWS 服務**
+ [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 的開放原始碼管理工具。它提供圖形界面，可協助您建立、維護和使用資料庫物件。
## 史詩
### 建立使用者和角色
| 任務 | Description | 所需的技能 |
| --- | --- | --- |
| 建立部署使用者。 | 部署使用者`APP`將用於在部署期間建立和修改資料庫物件。使用下列指令碼在結構描述 `APP_DEP`中建立部署使用者角色`APP`。驗證存取權，以確保此使用者只有在所需結構描述 中建立物件的權限`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
| DBA |
| 建立唯讀使用者。 | 唯讀使用者`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
| DBA |
| 建立讀取/寫入使用者。 | 讀取/寫入使用者`APP_WRITE`將用於對結構描述 執行讀取和寫入操作`APP`。使用下列指令碼來建立讀取/寫入使用者，並授予該`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 ;
| DBA |
| 建立開發使用者。 | 開發使用者`APP_dev_user`將有權在其本機結構描述中建立物件，`APP_DEV`並在結構描述 中建立讀取存取權`APP`。使用下列指令碼來建立和測試`APP_dev_user`資料庫中使用者的權限。1. 建立使用者。CREATE USER APP1_dev_user with password ‘your password’;
2. 建立 `APP_DEV` 的結構描述`App_dev_user`。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) ;
| DBA |
## 相關資源
**PostgreSQL 文件**
+ [建立角色](https://www.postgresql.org/docs/9.1/sql-createrole.html)
+ [建立使用者](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`命令授予使用者對這些角色的存取權。例如，若要將`pg_signal_backend`角色授予 `Admin_User`，您可以執行下列命令。
```
GRANT pg_signal_backend TO Admin_User;
```
此`pg_signal_backend`角色旨在允許管理員啟用信任的非超級使用者角色，將訊號傳送至其他後端。如需詳細資訊，請參閱 [PostgreSQL 14 增強](https://www.postgresql.org/docs/14/predefined-roles.html)功能。
**微調存取權**
在某些情況下，可能需要為使用者提供更精細的存取 （例如，資料表型存取或資料欄型存取）。在這種情況下，可以建立其他角色，將這些權限授予使用者。如需詳細資訊，請參閱 [PostgreSQL Grants](https://www.postgresql.org/docs/8.4/sql-grant.html)。