介绍 在这个实验里，重点会放在 PostgreSQL 的角色与权限管理上。你需要掌握的核心内容，是如何创建带有特定属性的角色，以及怎样管理数据库权限。 首先会创建一个名为 `reporting_role` 的角色，并为它设置 `NOLOGIN` 属性，避免它直接连接数据库。创建完成后，还需要确认这个角色是否已经成功建立。接下来的步骤会继续涉及权限授予与测试、切换用户验证访问效果，以及最后撤销该角色的相关权限。 创建角色 这一步主要学习如何在 PostgreSQL 中创建角色。角色本质上是用来管理数据库访问和权限的。 1. 连接到 PostgreSQL 服务器： 打开终端，使用 `psql` 命令行工具，并以 `postgres` 用户身份连接到 PostgreSQL 服务器。 ```bash sudo -u postgres psql ``` 连接成功后，终端里通常会出现类似下面的提示符： ```sql postgres=# ``` 2. 创建 `reporting_role` 角色： 接下来，使用 `CREATE ROLE` 命令创建 `reporting_role`。这里会显式指定 `NOLOGIN` 属性，也就是说，这个角色不能直接用来连接数据库。它更适合作为权限载体，后续再把权限授予其他角色或用户。 ```sql CREATE ROLE reporting_role NOLOGIN; ```nn 此命令将创建角色。你应该会看到以下输出：nn ```n CREATE ROLEn ```nn3. **验证角色创建：**nn 为了验证角色是否已创建，你可以使用 `\du` 命令（`\list users` 的缩写）。此命令会列出数据库中的所有角色。nn ```sqln \dun ```nn 你应该会在角色列表中看到 `reporting_role`。输出将类似于：nn ```n List of rolesn Role name | Attributes | Member ofn ----------------+------------------------------------------------------------+-----------n labex | Superuser | {}n postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}n reporting_role | Cannot login | {}n ```nn 退出 psql shell。nn ```sqln \qn ```n","need_verify":true,"has_solution":false},{"position":2,"title":"授予数据库权限","layout":"doc-workbench-split","text":"## 授予数据库权限nn在本步骤中，你将学习如何向角色授予数据库权限。我们将基于上一步创建的 `reporting_role`。nn1. **连接到 PostgreSQL 服务器：**nn 使用 `psql` 命令行工具以 `postgres` 用户身份连接到 PostgreSQL 服务器。nn ```bashn sudo -u postgres psqln ```nn2. **创建数据库：**nn 让我们创建一个名为 `reporting_db` 的数据库，`reporting_role` 将拥有访问权限。nn ```sqln CREATE DATABASE reporting_db;n ```nn 你应该会看到以下输出：nn ```n CREATE DATABASEn ```nn3. **连接到 `reporting_db` 数据库：**nn 连接到新创建的数据库：nn ```sqln \c reporting_dbn ```nn 提示符应该会变为 `reporting_db=#`。nn4. **创建表：**nn 让我们在 `reporting_db` 数据库中创建一个名为 `sales_data` 的简单表。nn ```sqln CREATE TABLE sales_data (n id SERIAL PRIMARY KEY,n product VARCHAR(255),n amount DECIMALn );n ```nn 你应该会看到以下输出：nn ```n CREATE TABLEn ```nn5. **授予 `reporting_role` 对 `sales_data` 表的 SELECT 权限：**nn 现在，我们将授予 `reporting_role` 对 `sales_data` 表的 `SELECT` 权限。这将允许任何属于 `reporting_role` 的角色或用户从 `sales_data` 表读取数据。nn ```sqln GRANT SELECT ON sales_data TO reporting_role;n ```nn 你应该会看到以下输出：nn ```n GRANTn ```nn 退出 psql shell。nn ```sqln \qn ```n","need_verify":true,"has_solution":false},{"position":3,"title":"创建用户并测试权限","layout":"doc-workbench-split","text":"## 创建用户并测试权限nn在本步骤中，你将创建一个用户并授予其 `reporting_role` 的成员资格，以测试权限。nn1. **连接到 PostgreSQL 服务器：**nn 使用 `psql` 命令行工具以 `postgres` 用户身份连接到 PostgreSQL 服务器。nn ```bashn sudo -u postgres psqln ```nn2. **连接到 `reporting_db` 数据库：**nn 连接到 `reporting_db` 数据库：nn ```sqln \c reporting_dbn ```nn3. **创建用户并授予 `reporting_role` 成员资格：**nn 让我们创建一个名为 `report_user` 的新用户。然后，我们将授予此用户 `reporting_role` 的成员资格。这意味着 `report_user` 将继承授予 `reporting_role` 的权限。nn ```sqln CREATE USER report_user;n GRANT reporting_role TO report_user;n ```nn 你应该会看到以下输出：nn ```n CREATE ROLEn GRANT ROLEn ```nn4. **配置 PostgreSQL 的无密码认证：**nn 默认情况下，PostgreSQL 使用 `peer` 认证，这在此实验中可能会导致连接问题。我们将将其更改为 `trust` 以简化 `report_user` 的访问。nn 首先，退出 `psql` shell：nn ```sqln \qn ```nn 现在，修改 `pg_hba.conf` 文件以更改认证方法。下面的 `sed` 命令会将原始文件备份为 `pg_hba.conf.bak`，并将本地连接的 `peer` 替换为 `trust`。nn ```bashn sudo sed -i.bak 's/^local\s\+all\s\+all\s\+peer/local all all trust/' /etc/postgresql/14/main/pg_hba.confn ```nn 重新加载 PostgreSQL 以应用更改：nn ```bashn sudo service postgresql reloadn ```nn5. **向 `sales_data` 表插入数据：**nn 以 `postgres` 用户身份重新连接到数据库。nn ```bashn sudo -u postgres psql -d reporting_dbn ```nn 为测试目的，向 `sales_data` 表插入一些示例数据。nn ```sqln INSERT INTO sales_data (product, amount) VALUES ('Laptop', 1200.00), ('Mouse', 25.00), ('Keyboard', 75.00);n ```nn 你应该会看到以下输出：nn ```n INSERT 0 3n ```nn 退出 psql shell。nn ```sqln \qn ```nn6. **以 `report_user` 身份连接到 `reporting_db` 数据库：**nn 打开一个新的终端窗口并使用以下命令：nn ```bashn psql -d reporting_db -U report_usern ```nn 你将以 `report_user` 身份连接到数据库，而不会出现密码提示。nn7. **测试权限：**nn 现在，尝试从 `sales_data` 表中选择数据：nn ```sqln SELECT * FROM sales_data;n ```nn 你应该会看到你插入的数据：nn ```n id | product | amountn ----+----------+--------n 1 | Laptop | 1200.00n 2 | Mouse | 25.00n 3 | Keyboard | 75.00n (3 rows)n ```nn 如果你看到此输出，则表示 `report_user` 拥有 `sales_data` 表的 `SELECT` 权限，该权限是通过 `reporting_role` 授予的。nn 退出 psql shell。nn ```sqln \qn ```n","nee在这一步，需要把 `reporting_role` 的 `SELECT` 权限撤销掉，再确认 `report_user` 是否已经无法继续访问 `sales_data` 表。 1. 以 `postgres` 用户身份连接到 PostgreSQL 服务器： 回到之前以 `postgres` 身份操作的终端，使用 `psql` 命令行工具连接 PostgreSQL 服务器。 ```bash sudo -u postgres psql ``` 2. 连接到 `reporting_db` 数据库： 执行下面的命令，切换到 `reporting_db` 数据库： ```sql \c reporting_db ``` 3. 从 `reporting_role` 撤销 `SELECT` 权限： 接下来，从 `reporting_role` 撤销它对 `sales_data` 表的 `SELECT` 权限。 ```sql REVOKE SELECT ON sales_data FROM reporting_role; ``` 正常情况下，你会看到这样的输出： ```text REVOKE ``` 然后退出 psql shell。 ```sql \q ``` 4. 以 `report_user` 身份连接到 `reporting_db` 数据库： 切换回你以 `report_user` 身份连接的终端。nn ```bashn psql -d reporting_db -U report_usern ```nn5. **验证 `report_user` 的访问被拒绝：**nn 作为 `report_user`，再次运行 `SELECT` 查询，以验证你是否不再拥有对 `sales_data` 表中数据的访问权限：nn ```sqln SELECT * FROM sales_data;n ```nn 你应该会看到类似以下的错误消息：nn ```n ERROR: permission denied for table sales_datan ```nn 这证实了 `report_user` 不再拥有 `sales_data` 表的 `SELECT` 权限，因为该权限已从 `reporting_role` 撤销。nn 退出 psql shell。nn ```sqln \qn ```n","need_verify":true,"has_solution":false},{"position":5,"title":"总结","layout":"doc-fullscreen","text":"## 总结nn在此实验中，你学习了如何在 PostgreSQL 中创建角色、授予权限、创建用户、将角色分配给用户以及撤销权限。这些是管理数据库安全和访问控制的基础概念。n","need_verify":false,"has_solution":false}