postgresql创建用户的方法及密码权限配置教程

在 PostgreSQL 中，所谓的“用户”本质上是一个具备登录属性的数据库角色。创建一个可用的业务账号，不仅仅是设定用户名和密码，更需要精确控制它能连接哪个数据库、访问哪个 Schema，以及对哪些表执行何种操作。下面将以 Navicat for PostgreSQL 作为图形化管理工具，配合可执行的 SQL 语句，演示如何创建一个标准的普通业务用户 app_reader 并完成其权限配置。请根据实际环境替换示例中的名称和密码。

Navicat 连接成功后的主界面，左侧为对象树，右侧为查询编辑器窗口

关于管理员连接准备

在执行任何创建或授权操作前，必须使用具有足够权限的管理员账号（如 postgres）登录数据库。打开 Navicat for PostgreSQL，新建或选择一个已有的服务器连接，填写主机地址、端口、初始数据库及管理员凭据。测试连接成功后，双击进入该连接。

在左侧对象树中，展开目标服务器节点，找到需要配置用户的目标数据库。右键点击该数据库，选择“新建查询”或直接打开查询编辑器窗口。确保后续的所有 SQL 命令都在正确的数据库上下文环境中执行，避免因连接错库导致权限授予无效。

关于创建带密码的登录用户

关于创建带密码的登录用户

使用 SQL 命令创建

最直接且跨版本兼容的方式是在查询窗口中执行 SQL。以下语句创建一个名为 app_reader 的用户，并设置登录权限和密码：

CREATE USER app_reader
WITH LOGIN
PASSWORD 'Replace-With-A-Strong-Password';

CREATE USER 是 CREATE ROLE ... LOGIN 的快捷方式。请务必将密码替换为高强度随机字符串，避免使用弱口令或在代码仓库中明文存储真实密码。

如果不确定用户是否已存在，可以先查询系统表：

SELECT rolname, rolcanlogin
FROM pg_roles
WHERE rolname = 'app_reader';

若用户已存在但需重置密码或启用登录，可使用 ALTER 语句：

ALTER ROLE app_reader
WITH LOGIN
PASSWORD 'Replace-With-A-Strong-Password';

通过 Navicat 界面创建

在 Navicat 的对象树中，通常可以在“角色”或“用户”节点下找到管理入口。右键点击“角色”，选择“新建角色”。在弹出的属性窗口中，输入角色名称，并在“定义”或“成员资格”标签页中勾选“可以登录”选项，随后在“密码”字段设置凭证。保存后，刷新列表即可看到新角色。由于不同版本的 Navicat 菜单布局可能略有差异，若找不到对应入口，建议直接使用上述 SQL 方式，更加稳定可靠。

关于允许连接指定数据库

创建角色后，该角色默认并不拥有访问所有数据库的权限。需要显式授予其对目标数据库的连接权：

GRANT CONNECT ON DATABASE appdb TO app_reader;

请将 appdb 替换为实际的数据库名称。注意，即使数据库允许 PUBLIC 组连接，也不意味着新用户自动拥有库内所有对象的访问权，后续仍需单独配置 Schema 和表级权限。

关于允许连接指定数据库

关于授予 Schema 和表的最小权限

连接到目标数据库后，首先需要允许用户使用特定的 Schema（通常为 public）：

GRANT USAGE ON SCHEMA public TO app_reader;

接下来，根据业务需求授予表权限。对于只读账号，仅授予 SELECT 权限：

GRANT SELECT
ON ALL TABLES IN SCHEMA public
TO app_reader;

如果账号需要写入数据，可按需追加 INSERT、UPDATE 或 DELETE 权限，尽量避免直接授予 ALL PRIVILEGES：

GRANT SELECT, INSERT, UPDATE, DELETE
ON ALL TABLES IN SCHEMA public
TO app_reader;

遵循最小权限原则，只赋予完成工作所必需的权限，能有效降低安全风险。

关于处理自增序列权限

如果表中包含使用 serial 或 identity 定义的自增主键，写入用户在插入数据时还需要访问对应的序列对象。若未授权，插入操作会报错。可根据需要授予序列权限：

GRANT USAGE, SELECT
ON ALL SEQUENCES IN SCHEMA public
TO app_reader;

遇到插入报错时，应首先检查是否是序列权限缺失，而非盲目提升用户等级。

关于让未来新表自动获得权限

使用 GRANT ON ALL TABLES 仅对执行时刻已存在的表生效。为了确保后续创建的新表也能被该用户访问，需要设置默认权限。请注意，这条命令必须由将来创建这些表的对象所有者（通常是建表用户或管理员）执行：

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO app_reader;

若新表也需要写入权限，则扩展为：

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_reader;

默认权限机制确保了权限管理的持续性，无需每次建表后手动重新授权。

关于让未来新表自动获得权限

关于在 Navicat 中核对权限

配置完成后，建议在 Navicat 中进行验证。刷新对象树，查看 app_reader 角色是否存在且属性正确。为了准确测试，可以使用该账号新建一个临时连接，或在查询窗口中切换用户身份（如果支持）。

执行以下语句确认当前身份和数据库：

SELECT current_user, current_database();

随后尝试执行预期允许的操作，例如只读用户执行查询：

SELECT *
FROM public.example_table
LIMIT 1;

若尝试执行未授权的操作（如只读用户执行 DELETE），数据库应返回权限拒绝错误，这证明最小权限配置已生效。

关于修改、回收和删除用户

随着业务变化，可能需要调整用户权限。修改密码使用：

ALTER ROLE app_reader PASSWORD 'New-Strong-Password';

临时禁止登录：

ALTER ROLE app_reader NOLOGIN;

回收特定权限：

REVOKE INSERT, UPDATE, DELETE
ON ALL TABLES IN SCHEMA public
FROM app_reader;

删除用户前，必须确保该角色不再拥有任何数据库对象，也没有其他角色依赖它：

DROP ROLE app_reader;

若删除失败，PostgreSQL 会提示存在依赖对象，此时需先转移对象所有权或回收权限，切勿在生产环境中随意使用级联删除。

在对象树中查看和管理已创建的角色