在本实验中，你将探索 PostgreSQL 的角色和权限管理。主要目标是学习如何创建具有特定属性的角色以及管理数据库权限。
你将首先创建一个名为 reporting_role 的角色，并赋予 NOLOGIN 属性，以防止其直接连接到数据库。然后，你将验证角色的创建。后续步骤将涵盖授予和测试数据库权限、切换用户以验证访问以及撤销角色的权限。
在本步骤中，你将学习如何在 PostgreSQL 中创建角色。角色用于管理数据库访问和权限。
连接到 PostgreSQL 服务器：
打开终端，使用 psql 命令行工具以 postgres 用户身份连接到 PostgreSQL 服务器。
sudo -u postgres psql你应该会看到类似以下的提示符：
postgres=#创建 reporting_role 角色：
现在，让我们使用 CREATE ROLE 命令创建 reporting_role。我们将指定 NOLOGIN 属性，这意味着该角色不能用于直接连接数据库。它将仅用于向其他角色或用户授予权限。
CREATE ROLE reporting_role NOLOGIN;此命令将创建角色。你应该会看到以下输出：
CREATE ROLE验证角色创建：
为了验证角色是否已创建，你可以使用 \du 命令（\list users 的缩写）。此命令会列出数据库中的所有角色。
\du你应该会在角色列表中看到 reporting_role。输出将类似于：
List of roles
Role name | Attributes | Member of
----------------+------------------------------------------------------------+-----------
labex | Superuser | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
reporting_role | Cannot login | {}退出 psql shell。
\q在本步骤中，你将学习如何向角色授予数据库权限。我们将基于上一步创建的 reporting_role。
连接到 PostgreSQL 服务器：
使用 psql 命令行工具以 postgres 用户身份连接到 PostgreSQL 服务器。
sudo -u postgres psql创建数据库：
让我们创建一个名为 reporting_db 的数据库，reporting_role 将拥有访问权限。
CREATE DATABASE reporting_db;你应该会看到以下输出：
CREATE DATABASE连接到 reporting_db 数据库：
连接到新创建的数据库：
\c reporting_db提示符应该会变为 reporting_db=#。
创建表：
让我们在 reporting_db 数据库中创建一个名为 sales_data 的简单表。
CREATE TABLE sales_data (
id SERIAL PRIMARY KEY,
product VARCHAR(255),
amount DECIMAL
);你应该会看到以下输出：
CREATE TABLE授予 reporting_role 对 sales_data 表的 SELECT 权限：
现在，我们将授予 reporting_role 对 sales_data 表的 SELECT 权限。这将允许任何属于 reporting_role 的角色或用户从 sales_data 表读取数据。
GRANT SELECT ON sales_data TO reporting_role;你应该会看到以下输出：
GRANT退出 psql shell。
\q在本步骤中，你将创建一个用户并授予其 reporting_role 的成员资格，以测试权限。
连接到 PostgreSQL 服务器：
使用 psql 命令行工具以 postgres 用户身份连接到 PostgreSQL 服务器。
sudo -u postgres psql连接到 reporting_db 数据库：
连接到 reporting_db 数据库：
\c reporting_db创建用户并授予 reporting_role 成员资格：
让我们创建一个名为 report_user 的新用户。然后，我们将授予此用户 reporting_role 的成员资格。这意味着 report_user 将继承授予 reporting_role 的权限。
CREATE USER report_user;
GRANT reporting_role TO report_user;你应该会看到以下输出：
CREATE ROLE
GRANT ROLE配置 PostgreSQL 的无密码认证：
默认情况下，PostgreSQL 使用 peer 认证，这在此实验中可能会导致连接问题。我们将将其更改为 trust 以简化 report_user 的访问。
首先，退出 psql shell：
\q现在，修改 pg_hba.conf 文件以更改认证方法。下面的 sed 命令会将原始文件备份为 pg_hba.conf.bak，并将本地连接的 peer 替换为 trust。
sudo sed -i.bak 's/^local\s\+all\s\+all\s\+peer/local all all trust/' /etc/postgresql/14/main/pg_hba.conf重新加载 PostgreSQL 以应用更改：
sudo service postgresql reload向 sales_data 表插入数据：
以 postgres 用户身份重新连接到数据库。
sudo -u postgres psql -d reporting_db为测试目的，向 sales_data 表插入一些示例数据。
INSERT INTO sales_data (product, amount) VALUES ('Laptop', 1200.00), ('Mouse', 25.00), ('Keyboard', 75.00);你应该会看到以下输出：
INSERT 0 3退出 psql shell。
\q以 report_user 身份连接到 reporting_db 数据库：
打开一个新的终端窗口并使用以下命令：
psql -d reporting_db -U report_user你将以 report_user 身份连接到数据库，而不会出现密码提示。
测试权限：
现在，尝试从 sales_data 表中选择数据：
SELECT * FROM sales_data;你应该会看到你插入的数据：
id | product | amount
----+----------+--------
1 | Laptop | 1200.00
2 | Mouse | 25.00
3 | Keyboard | 75.00
(3 rows)如果你看到此输出，则表示 report_user 拥有 sales_data 表的 SELECT 权限，该权限是通过 reporting_role 授予的。
退出 psql shell。
\q在本步骤中，你将从 reporting_role 撤销 SELECT 权限，并验证 report_user 是否不再拥有对 sales_data 表的访问权限。
以 postgres 用户身份连接到 PostgreSQL 服务器：
切换回你以 postgres 用户身份连接的终端。使用 psql 命令行工具以 postgres 用户身份连接到 PostgreSQL 服务器。
sudo -u postgres psql连接到 reporting_db 数据库：
连接到 reporting_db 数据库：
\c reporting_db从 reporting_role 撤销 SELECT 权限：
现在，我们将从 reporting_role 撤销对 sales_data 表的 SELECT 权限。
REVOKE SELECT ON sales_data FROM reporting_role;你应该会看到以下输出：
REVOKE退出 psql shell。
\q以 report_user 身份连接到 reporting_db 数据库：
切换回你以 report_user 身份连接的终端。
psql -d reporting_db -U report_user验证 report_user 的访问被拒绝：
作为 report_user，再次运行 SELECT 查询，以验证你是否不再拥有对 sales_data 表中数据的访问权限：
SELECT * FROM sales_data;你应该会看到类似以下的错误消息：
ERROR: permission denied for table sales_data这证实了 report_user 不再拥有 sales_data 表的 SELECT 权限，因为该权限已从 reporting_role 撤销。
退出 psql shell。
\q在此实验中，你学习了如何在 PostgreSQL 中创建角色、授予权限、创建用户、将角色分配给用户以及撤销权限。这些是管理数据库安全和访问控制的基础概念。
