如何创建仅支持CRUD操作、无法删库表的PostgreSQL角色

问题根源 #

你之前的配置未针对数据库本身的DROP权限做明确限制，且未精细化管控用户的数据库级权限，导致用户仍能执行删库操作。

正确的权限配置命令 #

替换占位符your_password和your_database_name后执行：

-- 创建普通业务用户（默认无超级权限）
CREATE USER developer WITH PASSWORD 'your_password';

-- 创建独立业务角色，便于权限批量管理
CREATE ROLE developer_role;

-- 授予schema访问权限
GRANT USAGE ON SCHEMA public TO developer_role;

-- 授予现有表的CRUD权限
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO developer_role;

-- 配置未来新建表的默认CRUD权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO developer_role;

-- 将角色权限赋予用户
GRANT developer_role TO developer;

-- 关键操作：收回用户对目标数据库的所有权限，仅保留必要的连接和临时表权限
REVOKE ALL ON DATABASE your_database_name FROM developer;
GRANT CONNECT, TEMP ON DATABASE your_database_name TO developer;

-- 收回用户对schema的创建/删除权限
REVOKE CREATE, DROP ON SCHEMA public FROM developer;
REVOKE CREATE, DROP ON ALL SCHEMAS IN DATABASE your_database_name FROM developer;

-- 清理用户可能继承的额外高权限
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM developer;
REVOKE ALL PRIVILEGES ON ALL SCHEMAS FROM developer;

验证方法 #

切换到developer用户后执行以下测试：

• 执行DROP DATABASE your_database_name; 应返回权限不足错误
• 执行DROP TABLE public.your_test_table; 应返回权限不足错误
• 执行SELECT/INSERT/UPDATE/DELETE 操作应正常执行

内容的提问来源于stack exchange，提问作者vramanu