PostgreSQL 权限

概述

PostgreSQL通过角色和权限机制来控制用户对数据库对象的访问权限。

创建角色

-- 创建可登录角色
CREATE ROLE 用户名 WITH LOGIN PASSWORD '密码';

-- 创建具备建库权限的角色
CREATE ROLE 应用用户 WITH LOGIN PASSWORD 'password123' CREATEDB;

授予权限

-- 授予表的指定操作权限
GRANT SELECT, INSERT, UPDATE, DELETE ON 用户表 TO 应用用户;

-- 授予表的全部操作权限
GRANT ALL PRIVILEGES ON 用户表 TO 管理员;

-- 授予模式使用权限
GRANT USAGE ON SCHEMA public TO 应用用户;

-- 授予数据库连接权限
GRANT CONNECT ON DATABASE 我的数据库 TO 应用用户;

撤销权限

-- 撤销表的插入、更新权限
REVOKE INSERT, UPDATE ON 用户表 FROM 应用用户;
-- 撤销表的全部权限
REVOKE ALL PRIVILEGES ON 用户表 FROM 访客;

角色属性

-- 创建超级用户角色
CREATE ROLE 超级管理员 WITH LOGIN PASSWORD '密码' SUPERUSER;

-- 创建具备建库权限的角色
CREATE ROLE 数据库管理员 WITH LOGIN PASSWORD '密码' CREATEDB;

-- 创建具备建角色权限的角色
CREATE ROLE 角色管理员 WITH LOGIN PASSWORD '密码' CREATEROLE;

-- 创建具备复制权限的角色
CREATE ROLE 复制用户 WITH LOGIN PASSWORD '密码' REPLICATION;

组角色

-- 创建角色组
CREATE GROUP 开发人员组;

-- 添加组成员
GRANT 开发人员组 TO 爱丽丝;
GRANT 开发人员组 TO 鲍勃;

-- 给角色组授予权限
GRANT SELECT ON 产品表 TO 开发人员组;

行级安全性

-- 启用行级安全策略
ALTER TABLE 用户表 ENABLE ROW LEVEL SECURITY;

-- 创建行级访问策略
CREATE POLICY 用户访问策略 ON 用户表
    FOR SELECT
    USING (用户ID = current_setting('app.current_user')::INTEGER);

小结

权限管理核心内容：

• 角色管理：创建角色（CREATE ROLE）、授予权限（GRANT）、撤销权限（REVOKE）
• 权限类型：包含查询、插入、更新、删除等多种操作权限
• 角色属性：支持超级用户、建库、建角色、复制等特殊属性配置
• 组角色：可创建角色组统一管理批量用户权限
• 行级安全性：精细化控制单张表中不同行的访问权限