介绍
在本实验中，你将探索 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的访问。首先，退出
psqlshell：\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 中创建角色、授予权限、创建用户、将角色分配给用户以及撤销权限。这些是管理数据库安全和访问控制的基础概念。
