一、什么是SQLAdmin

SQLAdmin（通常指pgAdmin）是PostgreSQL官方推荐的开源Web管理工具，提供图形化界面来管理PostgreSQL数据库服务器。与命令行操作相比，SQLAdmin让用户权限管理变得更加直观可视化，特别适合不熟悉SQL语法的运维人员。

在CentOS服务器上配置SQLAdmin后，可以通过浏览器访问其Web界面，在”Login/Group Roles”节点下管理用户和角色。与直接使用psql命令相比，SQLAdmin的优势在于：可以直接右键菜单创建用户、分配权限，无需记忆复杂的SQL语法；可以直观查看每个用户的当前权限；可以批量编辑多个用户的权限设置。

二、CentOS上SQLAdmin安装与配置

2.1 安装PostgreSQL

在管理用户权限之前，需要先确保CentOS上已正确安装PostgreSQL数据库：

# CentOS 7/8 安装PostgreSQL
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo yum install -y postgresql15-server postgresql15-contrib

# 初始化数据库
sudo /usr/pgsql-15/bin/postgresql-15-setup initdb
sudo systemctl enable postgresql-15
sudo systemctl start postgresql-15

# 安装pgAdmin（SQLAdmin的桌面版）
sudo rpm -i https://ftp.postgresql.org/pub/pgadmin/pgadmin4/yum/pgadmin4-redhat-repo-2-1.noarch.rpm
sudo yum install -y pgadmin4-web

# 配置pgAdmin Web
sudo /usr/pgadmin4/bin/setup-web.sh
# 按提示设置管理员邮箱和密码

2.2 配置防火墙允许SQLAdmin访问

# 开放PostgreSQL默认端口5432
sudo firewall-cmd --permanent --add-service=postgresql
sudo firewall-cmd --permanent --add-service=http
sudo firewall-cmd --permanent --add-service=https
sudo firewall-cmd --reload

# 验证PostgreSQL正在运行
sudo systemctl status postgresql-15
sudo ss -tulnp | grep 5432

三、SQLAdmin用户权限体系解析

3.1 PostgreSQL权限模型

PostgreSQL采用基于角色的权限管理模型（Role-Based Access Control）。用户（User）和组（Group）都是角色（Role），区别在于用户可以登录，而组通常用于批量管理权限。

核心权限类型：

权限类型	缩写	说明
SELECT	SELECT	读取数据
INSERT	INSERT	插入新数据
UPDATE	UPDATE	修改现有数据
DELETE	DELETE	删除数据
TRUNCATE	TRUNCATE	清空表数据
REFERENCES	REF	引用外键约束
TRIGGER	TRIGGER	创建触发器
CREATE	CREATE	创建数据库对象
TEMPORARY	TEMP	创建临时表
EXECUTE	EXEC	执行函数/存储过程
USAGE	USAGE	使用schema/序列/类型

权限层级：

服务器级别 → 数据库级别 → Schema级别 → 表/视图/序列级别

3.2 对象所有者与默认权限

在PostgreSQL中，每个数据库对象都有一个所有者（Owner）。默认情况下，只有对象所有者和管理员（superuser）可以操作该对象。其他用户要访问这些对象，必须被明确授予相应权限。

四、通过SQLAdmin创建与管理用户

4.1 创建新用户

打开SQLAdmin，连接到目标PostgreSQL服务器：

1. 在左侧树形菜单中找到「Login/Group Roles」→「Create」→「Login/Group Role…」
2. 在「General」标签中填写角色名称（如 app_user）
3. 在「Definition」标签中设置密码和有效期
4. 在「Privileges」标签中配置角色属性
5. 点击「Save」保存

通过SQL命令创建：

-- 创建可登录的普通用户
CREATE ROLE app_user WITH LOGIN PASSWORD 'StrongPassword123!';

-- 创建带过期时间的用户
CREATE ROLE temp_user WITH LOGIN PASSWORD 'TempPass123' VALID UNTIL '2026-12-31';

-- 创建超级用户（谨慎使用）
CREATE ROLE db_admin WITH LOGIN PASSWORD 'AdminPass123!' SUPERUSER CREATEDB CREATEROLE REPLICATION;

-- 修改用户密码
ALTER ROLE app_user WITH PASSWORD 'NewPassword456!';

SQLAdmin中的角色属性选项：

属性	SQL关键词	作用
Superuser	SUPERUSER	绕过所有权限检查
Can create database	CREATEDB	可以创建新数据库
Can create role	CREATEROLE	可以创建和管理角色
Replication	REPLICATION	可以进行流复制
Inherit	INHERIT	自动继承组成员权限
Login	LOGIN	可以连接数据库（用户专属）

4.2 创建角色组

角色组用于批量管理权限。将多个用户添加到同一个组，所有组成员自动继承组的权限：

-- 创建只读角色组
CREATE ROLE readonly_group;

-- 创建读写角色组
CREATE ROLE readwrite_group;

-- 创建管理员角色组
CREATE ROLE admin_group;

-- 将用户添加到组
GRANT readonly_group TO app_user;
GRANT readwrite_group TO app_admin;
GRANT admin_group TO db_admin;

-- 从组中移除用户
REVOKE readonly_group FROM app_user;

五、分配与撤销权限

5.1 GRANT分配权限

授予表级别权限：

-- 授予SELECT权限（只读）
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_group;

-- 授予读写权限
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO readwrite_group;

-- 授予特定表的权限
GRANT SELECT, INSERT ON orders TO app_user;
GRANT UPDATE ON customers TO app_user;

-- 授予序列权限（允许插入自增字段）
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO readwrite_group;

授予Schema级别权限：

-- 授予schema的使用权限
GRANT USAGE ON SCHEMA public TO readonly_group;

-- 授予schema的创建权限
GRANT CREATE ON SCHEMA public TO readwrite_group;

授予数据库级别权限：

-- 授予连接数据库的权限
GRANT CONNECT ON DATABASE myapp_db TO app_user;

-- 授予创建数据库的权限（仅管理员）
GRANT CREATEDB ON DATABASE myapp_db TO db_admin;

5.2 REVOKE撤销权限

-- 撤销用户的特定表权限
REVOKE UPDATE ON customers FROM app_user;

-- 撤销组的所有表权限
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM app_user;

-- 撤销数据库连接权限
REVOKE CONNECT ON DATABASE myapp_db FROM temp_user;

5.3 默认权限设置（ALTER DEFAULT PRIVILEGES）

设置默认权限后，该用户未来创建的所有对象会自动应用指定权限：

-- 为readonly_group设置默认只读权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO readonly_group;

-- 为readwrite_group设置默认读写权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite_group;

-- 为readwrite_group设置默认序列权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT USAGE, SELECT ON SEQUENCES TO readwrite_group;

-- 撤销默认权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public
REVOKE SELECT ON TABLES FROM readonly_group;

六、高级权限管理

6.1 行级安全策略（Row-Level Security）

PostgreSQL支持行级安全策略，可以限制用户只能看到和修改符合特定条件的行：

-- 启用行级安全
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- 创建策略：用户只能看自己的订单
CREATE POLICY user_orders_policy ON orders
    FOR SELECT
    USING (user_id = current_user);

-- 创建策略：用户只能修改自己的订单
CREATE POLICY user_orders_update ON orders
    FOR UPDATE
    USING (user_id = current_user)
    WITH CHECK (user_id = current_user);

-- 查看表的所有策略
SELECT tablename, policyname, permissive, roles, cmd, qual, with_check
FROM pg_policies
WHERE tablename = 'orders';

6.2 列级权限

可以限制用户只能访问特定列：

-- 只授予用户访问特定列的权限（隐藏敏感字段）
GRANT SELECT (id, name, email, phone) ON customers TO app_user;
-- 用户无法访问password_hash和credit_card列

-- 授予更新特定列的权限
GRANT UPDATE (status, notes) ON orders TO app_user;

6.3 权限继承与NO INHERIT

默认情况下，用户会继承角色组的权限。如果需要临时禁用继承：

-- 创建不带继承权限的用户
CREATE ROLE temp_role WITH LOGIN NOINHERIT;

-- 临时激活组权限
SET ROLE readonly_group;
SELECT * FROM orders;  -- 可以访问
RESET ROLE;

-- 再次验证
SELECT * FROM orders;  -- 无法访问（回到原始权限）

七、安全最佳实践

7.1 最小权限原则

始终授予用户完成工作所需的最小权限。避免使用超级用户账户进行日常操作。

权限检查清单：

-- 查看用户的当前权限
SELECT 
    grantee,
    table_schema,
    table_name,
    privilege_type
FROM information_schema.table_privileges
WHERE grantee = 'app_user'
ORDER BY table_schema, table_name;

-- 查看用户的角色成员关系
SELECT 
    r1.rolname AS user_name,
    r2.rolname AS member_of
FROM pg_auth_members m
JOIN pg_authid r1 ON m.member = r1.oid
JOIN pg_authid r2 ON m.roleid = r2.oid;

7.2 使用预定义角色

PostgreSQL提供了一组预定义角色，可以直接使用：

预定义角色	说明	适用场景
pg_read_all_settings	读取所有配置	审计人员
pg_read_all_stats	读取所有统计信息	监控工具
pg_stat_scan_tables	执行监控函数	运维工具
pg_signal_backend	发送信号到后端	运维人员
pg_execute_server_program	执行服务器程序	特定工具

-- 授予监控工具只读统计信息权限
GRANT pg_read_all_stats TO monitoring_user;

-- 授予运维人员信号发送权限
GRANT pg_signal_backend TO ops_user;

7.3 密码安全与审计

-- 设置密码复杂度要求（通过pg_profile插件）
-- 或者使用PostgreSQL的check_password_function

-- 创建密码验证函数
CREATE OR REPLACE FUNCTION check_password(pwd text)
RETURNS boolean AS $$
BEGIN
    -- 至少8位，包含字母和数字
    IF length(pwd) < 8 THEN RETURN false; END IF;
    IF pwd !~ '[A-Za-z]' THEN RETURN false; END IF;
    IF pwd !~ '[0-9]' THEN RETURN false; END IF;
    RETURN true;
END;
$$ LANGUAGE plpgsql STRICT;

-- 查看登录历史（需要pg_audit扩展）
-- 安装pg_audit: CREATE EXTENSION pgaudit;
-- 配置:ALTER SYSTEM SET pgaudit.log = 'read, write';

八、常见问题与排查

8.1 用户无法连接数据库

错误：FATAL: database "xxx" is not accessible by this user

排查步骤：

-- 1. 确认用户存在
SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin
FROM pg_authid
WHERE rolname = 'app_user';

-- 2. 确认数据库连接权限
SELECT datname, datacl FROM pg_database;

-- 3. 重新授予连接权限
GRANT CONNECT ON DATABASE myapp_db TO app_user;

8.2 用户无法访问特定表

错误：ERROR: permission denied for table xxx

排查步骤：

-- 1. 查看表的所有者和当前权限
\d+ table_name
-- 或
SELECT grantee, privilege_type, table_name
FROM information_schema.table_privileges
WHERE table_name = 'xxx';

-- 2. 重新授予必要权限
GRANT SELECT ON xxx TO app_user;

-- 3. 如果是schema权限问题
GRANT USAGE ON SCHEMA public TO app_user;

8.3 权限设置后不生效

原因：可能是连接未刷新或默认权限未应用

解决方法：

-- 1. 重新加载权限
ALTER DEFAULT PRIVILEGES FOR USER db_owner IN SCHEMA public GRANT SELECT ON TABLES TO readonly_group;

-- 2. 对于已存在的对象，手动授予
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_group;

-- 3. 新建会话让权限生效
-- 退出并重新连接SQLAdmin

九、总结

在CentOS服务器上通过SQLAdmin管理PostgreSQL用户权限，是保障数据库安全的重要工作。本指南涵盖了从用户创建、权限分配、撤销，到行级安全、列级权限等高级功能，再到密码安全和审计的最佳实践。

核心要点回顾：

• 角色组管理：善用角色组批量管理权限，减少重复配置
• 最小权限：始终授予最小必要权限，避免过度授权
• 默认权限：设置ALTER DEFAULT PRIVILEGES，避免新建对象后忘记授权
• 行级安全：对于多用户共享表的场景，启用RLS实现数据隔离
• 定期审计：定期检查用户权限配置，及时撤销不必要的权限

通过SQLAdmin图形化界面结合SQL命令，可以高效地完成PostgreSQL的用户权限管理工作。建议在正式环境操作前，先在测试环境充分验证权限配置的正确性。

本文详细介绍了在CentOS上通过SQLAdmin管理PostgreSQL用户权限的方法，涵盖创建用户、分配权限、安全策略等核心内容，希望能帮助您更好地管理数据库安全。