点击上方蓝字，关注我们

想学会更多实用技巧，欢迎加入青学会MOP技术社区（实名社区）。

加入方法：公众号后台回复关键字“加入”获取小助手微信，添加后登记入会。

同时欢迎大家在评论区留言互动交流！社区会不定期举行相关的抽奖、公开分享活动。

如果你有想了解的知识点希望我们发文可以后台私信。

最近联合几个 Oracle ACE技术专家 开通了一个付费微信群，都是具有10多年金融、医疗、制造业10年以上的一线专家，坑位费399/人，无限期，目前群内近155人。加群后会有一些福利（分享各类技术文档，干货资源，问题解答等等），更有特邀嘉宾会定期在群内直播，解读AWR，快问快答等！有问题我们尽量都解答，毕竟399不能都解决所有问题。有兴趣联系微：ywu0613

内部知识库正在筹建中,不止有oracle!

在PostgreSQL的世界里，用户、角色和权限管理就像是一个精心设计的安全系统，它能帮助我们控制谁可以访问数据库，以及他们可以做什么。今天，我们就通过一些简单的实验，带你快速了解PostgreSQL的用户、角色和权限管理。别担心，我会用最通俗易懂的语言，让你轻松掌握这些知识。

角色创建与删除

在PostgreSQL中，角色是一个非常重要的概念。角色可以是用户，也可以是用户组。通过角色，我们可以灵活地管理权限。

创建角色

创建一个角色非常简单，只需要一条命令：

CREATE ROLE db_role1;  -- 创建一个名为db_role1的角色

复制

如果你想让这个角色能够登录数据库，可以加上LOGIN
权限：

CREATE ROLE db_role1 LOGIN;  -- 创建一个可以登录的用户角色

复制

删除角色

如果某个角色不再需要了，可以用以下命令删除：

DROP ROLE db_role1;  -- 删除角色db_role1

复制

为了安全起见，PostgreSQL还提供了一个更安全的删除方式：

DROP ROLE IF EXISTS db_role1;  -- 如果角色存在，则删除

复制

查看所有角色

如果你想查看数据库中所有的角色，可以用以下命令：

SELECT rolename FROM pg_roles;  -- 查询所有角色名称

复制

或者在命令行中直接输入：

\du  -- 在命令行下查看所有角色

复制

角色的权限

角色的权限决定了角色可以做什么。PostgreSQL提供了多种权限选项，比如登录权限、超级用户权限、创建数据库权限等。

创建具有特定权限的角色

CREATE ROLE db_role1 LOGIN;  -- 创建具有登录权限的角色
CREATE ROLE db_role2 SUPERUSER;  -- 创建具有超级用户权限的角色
CREATE ROLE db_role3 CREATEDB;  -- 创建具有创建数据库权限的角色
CREATE ROLE db_role4 CREATEROLE;  -- 创建具有创建角色权限的角色

复制

修改角色权限

如果需要修改角色的权限，可以使用ALTER ROLE
命令。比如：

ALTER ROLE db_role1 NOLOGIN NOCREATEDB;  -- 取消登录和创建数据库权限

复制

用户与用户组

在PostgreSQL中，用户其实也是一种角色，只是用户通常具有LOGIN
权限，而角色不一定有。

创建用户

创建用户的方式有两种：

CREATE USER db_user1 PASSWORD '123';  -- 创建用户
CREATE ROLE db_user1 PASSWORD '123' LOGIN;  -- 这两种方式是等价的

复制

还可以为用户设置密码有效期：

CREATE USER rep WITH PASSWORD '123' VALID UNTIL '2025-12-31';  -- 密码有效期到2025年底

复制

删除用户

删除用户也很简单：

DROP USER db_user1;  -- 删除用户

复制

修改用户密码

如果需要修改用户的密码，可以用以下命令：

ALTER USER db_user1 PASSWORD '123456';  -- 修改用户密码

复制

用户组

用户组在PostgreSQL中也是通过角色来实现的。一个包含其他角色的“角色”就是一个用户组。

创建用户组并添加用户

CREATE ROLE test_group;  -- 创建一个用户组
CREATE USER db_user2 PASSWORD '123';  -- 创建一个用户
GRANT test_group TO db_user2;  -- 将用户db_user2添加到用户组test_group

复制

切换角色

在某些情况下，我们可能需要切换到某个角色来执行操作：

SET ROLE test_group;  -- 切换到用户组test_group
RESET ROLE;  -- 切换回初始角色

复制

设置访问权限

在PostgreSQL中，权限管理是通过GRANT
和REVOKE
命令来实现的。我们可以为表、序列、函数等对象设置权限。

表级权限

赋予权限

GRANT SELECT, INSERT ON my_table TO db_user1;  -- 赋予用户db_user1查询和插入权限
GRANT ALL PRIVILEGES ON my_table TO db_user1;  -- 赋予用户所有权限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO db_user1;  -- 赋予用户查询public模式下所有表的权限

复制

列级权限

我们还可以为表的特定列设置权限：

GRANT SELECT (name, age), UPDATE (age) ON my_table TO db_user1;  -- 赋予查询name和age列，以及更新age列的权限

复制

序列权限

GRANT USAGE, SELECT, UPDATE ON my_sequence TO db_user1;  -- 赋予用户序列的权限

复制

数据库权限

GRANT CONNECT ON DATABASE mydb TO db_user1;  -- 赋予用户连接数据库的权限
GRANT CREATE ON DATABASE mydb TO db_user1;  -- 赋予用户在数据库中创建对象的权限

复制

五、查看和管理权限

查看权限

查看用户或角色的权限可以通过以下命令：

\z  -- 查看所有对象的权限
\dp+  -- 查看表的详细权限

复制

如果需要查询特定用户或角色的权限，可以使用以下SQL语句：

SELECT * FROM information_schema.role_table_grants WHERE grantee = 'db_user1';

复制

解读权限说明

权限的显示结果可能看起来有些复杂，但其实很容易理解。比如：

postgres=arwdDxt/postgres

复制

• postgres
  ：表示权限被赋予的用户或角色。
• arwdDxt
  ：表示具体的权限：
• a
  ：INSERT
  （插入）
• r
  ：SELECT
  （查询）
• w
  ：UPDATE
  （更新）
• d
  ：DELETE
  （删除）
• D
  ：TRUNCATE
  （清空表）
• x
  ：REFERENCES
  （引用）
• t
  ：TRIGGER
  （触发器）
• /postgres
  ：表示这个权限是由postgres
  用户赋予的。

撤销权限

如果需要撤销权限，可以使用REVOKE
命令：

REVOKE SELECT ON my_table FROM db_user1;  -- 撤销用户db_user1的查询权限

复制

案例

场景：创建用户组并管理权限

假设我们有一个团队，需要管理一个数据库teamdb
。我们希望创建一个用户组team_group
，并为团队成员member1
和member2
分配权限。

创建用户组和用户

CREATE ROLE team_group;  -- 创建用户组
CREATE USER member1 PASSWORD 'password1';  -- 创建用户member1
CREATE USER member2 PASSWORD 'password2';  -- 创建用户member2
GRANT team_group TO member1, member2;  -- 将用户添加到用户组

复制

赋予用户组权限

GRANT CONNECT ON DATABASE teamdb TO team_group;  -- 允许用户组连接数据库
GRANT USAGE ON SCHEMA public TO team_group;  -- 允许访问public模式
GRANT SELECT, INSERT, UPDATE ON ALLTABLES IN SCHEMA public TO team_group;  -- 赋予表的权限

复制

撤销权限

如果某个用户不再需要某些权限，可以撤销：

REVOKE UPDATE ON my_table FROM team_group;  -- 撤销用户组的更新权限

复制

通过今天的学习，我们学习了PostgreSQL中用户、角色和权限管理的基础知识。你可以通过创建角色和用户，为他们分配合适的权限，从而实现对数据库的精细管理。希望这些内容能帮助你更好地理解和使用PostgreSQL，让数据库管理变得轻松又安全！