📚 目录

  1. 概述
  2. 权限的分类
  3. 授予权限的语法
  4. 撤销权限的语法
  5. 查看权限
  6. 角色与权限
  7. 参考资料

🛠️ 概述

在 PostgreSQL 中，权限（privileges）是用于控制用户对数据库资源（如表、视图、列等）的访问和操作的一种机制。通过权限管理，数据库管理员可以确保只有被授权的用户可以访问或修改数据。

PostgreSQL 提供了多种权限类型，可以细粒度地控制用户对不同资源的访问和操作。同时，权限管理还与数据库中的角色（roles）密切相关，角色用于表示用户或用户组，并且可以授予某些权限。

📝 权限的分类

PostgreSQL 中的权限可以分为以下几类：

  1. 表权限：
    • SELECT：允许查询表中的数据。
    • INSERT：允许向表中插入数据。
    • UPDATE：允许更新表中的数据。
    • DELETE：允许删除表中的数据。
    • TRUNCATE：允许截断（删除所有行）表。
    • REFERENCES：允许在表中创建外键。
    • TRIGGER：允许在表上创建触发器。
  2. 数据库权限：
    • CONNECT：允许连接到数据库。
    • CREATE：允许在数据库中创建对象，如表和视图。
    • TEMPORARY：允许创建临时表。
  3. 模式（Schema）权限：
    • USAGE：允许访问模式中的对象（如表、视图等）。
    • CREATE：允许在模式中创建对象。
  4. 序列权限：
    • USAGE：允许使用序列（通常用于 nextval() 函数）。
    • SELECT：允许查询序列的当前值（currval()）。
    • UPDATE：允许修改序列的下一个值。
  5. 函数权限：
    • EXECUTE：允许执行函数。

📝 授予权限的语法

要授予权限，可以使用 GRANT 命令。GRANT 语句可以授予单个或多个权限给一个或多个用户。

语法：

GRANT privilege_type ON object TO role;
  • privilege_type：要授予的权限类型（如 SELECT、INSERT 等）。
  • object：权限所关联的数据库对象（如表、视图等）。
  • role：要授予权限的角色或用户。

示例：

GRANT SELECT, INSERT ON employees TO user1;

这将授予 user1 用户对 employees 表的 SELECT 和 INSERT 权限。

授予所有权限：

GRANT ALL PRIVILEGES ON employees TO user2;

这将授予 user2 用户对 employees 表的所有权限，包括查询、插入、更新、删除等。

📝 撤销权限的语法

撤销权限可以使用 REVOKE 命令。REVOKE 命令会撤销先前通过 GRANT 命令授予的权限。

语法：

REVOKE privilege_type ON object FROM role;
  • privilege_type：要撤销的权限类型。
  • object：权限所关联的数据库对象。
  • role：要撤销权限的角色或用户。

示例：

REVOKE SELECT ON employees FROM user1;

这将撤销 user1 用户对 employees 表的 SELECT 权限。

撤销所有权限：

REVOKE ALL PRIVILEGES ON employees FROM user1;

这将撤销 user1 用户对 employees 表的所有权限。

📝 查看权限

要查看当前数据库对象的权限，可以使用 pg_catalog 中的视图，如 pg_roles 和 pg_tables。

示例：查看表的权限

SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name = 'employees';

这将返回 employees 表上所有用户的权限。

🛠️ 角色与权限

在 PostgreSQL 中，权限是通过角色（roles）管理的。角色可以是单个用户，也可以是用户组。角色可以拥有权限，或者将权限授予其他角色。

  1. 创建角色：
CREATE ROLE admin_role;
  1. 授予角色权限：
GRANT SELECT, INSERT ON employees TO admin_role;
  1. 为角色赋予另一个角色的权限：
GRANT admin_role TO user1;

这将把 admin_role 的所有权限授予 user1。

  1. 检查角色的权限：
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE grantee = 'user1';

这将显示 user1 用户的所有权限。

📘 参考资料