当前位置：首页 > 虚拟主机 > 正文

pg数据库创建用户时，如何正确授权并设置密码有效期？

admin
虚拟主机
2025-12-20
4

在PostgreSQL（简称PG）数据库中创建用户是数据库管理的基础操作之一，用户（在PG中通常称为“角色”）决定了谁能访问数据库以及拥有哪些权限，本文将详细介绍PG数据库中创建用户的完整流程，包括语法、权限配置、注意事项及实际操作示例，帮助用户全面掌握这一技能。

创建用户的基本语法

在PG中,创建用户主要通过CREATE USER或CREATE ROLE命令完成，两者的核心区别在于：CREATE USER默认创建的角色没有登录权限（即LOGIN属性为NOLOGIN），而CREATE ROLE默认不包含登录权限，但可通过LOGIN选项赋予登录能力，现代PG版本更推荐使用CREATE ROLE，并通过选项灵活控制权限。

基本语法如下：

CREATE ROLE user_name [ WITH option [ ... ] ];

option可以是以下常见选项：

LOGIN：允许角色登录数据库。
SUPERUSER：超级用户，拥有所有权限（需谨慎使用）。
CREATEDB：允许创建数据库。
CREATEROLE：允许创建角色。
CREATEDB：允许创建数据库。
CREATEROLE：允许创建角色。
REPLICATION：允许流复制和逻辑复制。
CONNECTION LIMIT conn_limit：限制并发连接数。
PASSWORD 'password'：设置登录密码。
VALID UNTIL 'timestamp'：密码过期时间。
IN ROLE role_name [, ...]：将新角色加入现有角色组。
IN GROUP role_name [, ...]：同IN ROLE。
ADMIN role_name [, ...]：赋予其他角色的管理权限。
USER role_name [, ...]：赋予其他角色的成员权限。

创建用户的实际操作步骤

连接到PostgreSQL

首先需要使用具有足够权限的用户（如postgres超级用户）连接到PG数据库，可通过以下命令连接：

psql U postgres d postgres

U指定用户名，d指定数据库。

创建普通用户

假设创建一个名为app_user的用户，允许登录并设置密码：

CREATE ROLE app_user WITH LOGIN PASSWORD 'secure_password123' CREATEDB;

LOGIN：允许用户登录。
PASSWORD：设置密码（生产环境中需使用强密码）。
CREATEDB：允许用户创建数据库。

创建只读用户

若需创建一个仅具有查询权限的用户,可先创建角色，再授权：

创建只读角色 CREATE ROLE read_user WITH LOGIN PASSWORD 'read_only_pass'; 授予public模式的查询权限 GRANT USAGE ON SCHEMA public TO read_user; GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_user;

USAGE ON SCHEMA：允许访问模式中的对象。
SELECT ON ALL TABLES：允许查询所有表。

创建超级用户（谨慎使用）

超级用户拥有最高权限,通常仅用于管理员：

CREATE ROLE super_admin WITH LOGIN SUPERUSER PASSWORD 'admin_pass';

创建具有连接限制的用户

限制用户最大并发连接数为5：

CREATE ROLE limited_user WITH LOGIN PASSWORD 'limited_pass' CONNECTION LIMIT 5;

用户权限管理

创建用户后,需通过GRANT和REVOKE命令管理权限，常见权限包括：

pg数据库创建用户时，如何正确授权并设置密码有效期？第1张

SELECT：查询表。
INSERT：插入数据。
UPDATE：更新数据。
DELETE：删除数据。
USAGE：访问模式或序列。
ALL PRIVILEGES：所有权限。

示例：为用户app_user授予特定表的权限：

GRANT SELECT, INSERT ON TABLE orders TO app_user;

撤销权限：

REVOKE DELETE ON TABLE orders FROM app_user;

用户管理常用命令

查看所有用户：
或使用du命令（在psql中）。
修改用户密码：

ALTER ROLE app_user WITH PASSWORD 'new_password';
删除用户：

DROP ROLE IF EXISTS app_user;
锁定/解锁用户：
ALTER ROLE app_user NOLOGIN; 锁定 ALTER ROLE app_user LOGIN; 解锁

注意事项

密码安全：避免使用弱密码，建议定期更换。
最小权限原则：仅授予用户必要的权限，避免过度授权。
超级用户限制：超级用户可绕过所有权限检查，需严格控制使用场景。
默认权限：新创建的表默认不会继承旧表的权限，需显式授权。

常见用户创建场景示例

以下表格归纳了不同场景下的用户创建命令：

场景	命令示例
普通应用用户	CREATE ROLE app_user WITH LOGIN PASSWORD 'app_pass' CREATEDB;
只读用户	CREATE ROLE read_user WITH LOGIN PASSWORD 'read_pass'; GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_user;
备份用户	CREATE ROLE backup_user WITH LOGIN REPLICATION PASSWORD 'backup_pass';
临时用户（密码过期）	CREATE ROLE temp_user WITH LOGIN PASSWORD 'temp_pass' VALID UNTIL '20251231';