PostgreSQL-用户和权限（一）

运维小路

发布于 2026-01-26 12:25:49

5900

文章被收录于专栏：运维小路运维小路

作者介绍：简历上没有一个精通的运维工程师，下面的思维导图也是预计更新的内容和当前进度(不定时更新)。

数据库是一个系统（应用）最重要的资产之一，所以我们的数据库将从以下几个数据库来进行介绍。

MySQL

PostgreSQL（本章节）

MongoDB

Redis

Etcd

我们在部署PostgreSQL的时候，采用的是psql免密码直接连接数据库，在上个小节也介绍了PostgreSQL的增删改查（CRUD），但是真实应用我们都应该遵循最小权限原则，所以本小节就来介绍下他的用户及权限。

核心概念

在 PostgreSQL 中，用户和角色在概念上是统一的。从 8.1 版本开始，CREATE USER 只是 CREATE ROLE 的一个别名，唯一的区别是 CREATE USER 默认带有 LOGIN 权限，而 CREATE ROLE 默认没有。

角色：是一个实体，可以拥有数据库对象（如表、视图）和被授予权限。角色可以是一个用户，也可以是一个组。
用户：本质上就是一个具有 LOGIN 权限的角色。
组角色：一个可以被其他角色“加入”的角色，用于批量管理权限。现在通常直接用角色继承来实现。

权限层次结构

PostgreSQL 的权限是分层的，理解这个层次至关重要：

实例级别 (Cluster Level) -> 数据库级别 (Database Level) -> 模式级别 (Schema Level) -> 对象级别 (Table/View/Function etc.)。其中模式是PostgreSQL特有的概念，其他概念和MySQL是一样的。

1. 角色/用户管理

创建用户（可登录的角色）

CREATE USER username WITH PASSWORD 'your_strong_password';
-- 或者等价的
CREATE ROLE username WITH LOGIN PASSWORD 'your_strong_password';

创建组角色（不可登录）

CREATE ROLE group_name NOLOGIN;

修改用户/角色

-- 修改密码
ALTER USER username WITH PASSWORD 'new_password';

-- 重命名
ALTER USER old_username RENAME TO new_username;

-- 给角色添加/删除属性
ALTER ROLE role_name WITH CREATEDB; -- 授予创建数据库权限
ALTER ROLE role_name WITH SUPERUSER; -- 授予超级用户权限（慎用！）
ALTER ROLE role_name WITH NOLOGIN; -- 禁止登录

除了被授予超级用户权限，则其他情况其实都无法被正确登录，因为PostgreSQL连接的时候需要显式或者隐式指定数据库。

显式指定：通过 -d 参数（如 psql -U 用户名 -d 数据库名）。
隐式指定：如果不指定，PostgreSQL 会按以下顺序尝试连接：与用户名同名的数据库（如用户 myuser 会先尝试连接 myuser 库）。若同名库不存在，尝试连接默认的 postgres 库（安装时创建的管理员库）。

bash-4.2$ psql -U username -h127.0.0.1
Password for user username: 
psql: error: connection to server at "127.0.0.1", port 5432 failed: 
FATAL:  database "username" does not exist

删除用户/角色

DROP ROLE role_name;
-- 或者
DROP USER username;

注意：如果角色拥有数据库对象，则不能直接删除，需要先转移所有权或使用 DROP OWNED BY role_name; 和 REASSIGN OWNED BY role_name TO new_owner;。

2. 权限管理

数据库权限

连接到一个数据库（如 postgres）后，可以授予其他数据库的权限。

-- 允许角色连接到数据库
GRANT CONNECT ON DATABASE database_name TO role_name;

-- 允许角色在数据库中创建模式，模式没讲解，先忽略吧。
GRANT CREATE ON DATABASE database_name TO role_name;

授予连接（CONNECT）权限以后，我们才能登录到psql里面，可以查看对应库所有表权限，但是却不能对表进行操作。

mydb=> \dt
         List of relations
 Schema | Name  | Type  |  Owner   
--------+-------+-------+----------
 public | users | table | postgres
(1 row)

mydb=> select * from users;
ERROR:  permission denied for table users
mydb=>

表/视图权限

这是最常用的权限控制。

--允许访问 public 模式（前提）。
GRANT USAGE ON SCHEMA public TO users; 

-- 授予 public 模式下所有现有表的 SELECT 权限。
GRANT SELECT ON ALL TABLES IN SCHEMA public TO users;

-- 授予 public 模式下所有现有表 增删改查的权限 
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO user ;

-- 授予 public 模式下所有表的所有权限
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO myuser;

-- 假设 users 表在 public 模式（默认模式）
GRANT ALL PRIVILEGES ON TABLE public.users TO myuser;

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2025-10-31，如有侵权请联系 cloudcommunity@tencent.com 删除