PostgreSQL 用户权限管理 #

┌─────────────────────────────────────────────────────────────┐
│                    角色类型                                  │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  角色（Role）                                               │
│  ├── 可以拥有数据库对象                                    │
│  ├── 可以拥有权限                                          │
│  └── 可以包含其他角色                                      │
│                                                             │
│  用户（可以登录的角色）                                      │
│  ├── 具有 LOGIN 属性                                       │
│  └── 可以连接数据库                                        │
│                                                             │
│  组角色（不能登录）                                          │
│  ├── 没有 LOGIN 属性                                       │
│  └── 用于权限分组                                          │
│                                                             │
└─────────────────────────────────────────────────────────────┘

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

-- 常用选项：
-- LOGIN / NOLOGIN          - 是否可以登录
-- SUPERUSER / NOSUPERUSER  - 是否为超级用户
-- CREATEDB / NOCREATEDB    - 是否可以创建数据库
-- CREATEROLE / NOCREATEROLE - 是否可以创建角色
-- INHERIT / NOINHERIT      - 是否继承所属角色的权限
-- PASSWORD 'password'      - 设置密码
-- VALID UNTIL 'timestamp'  - 密码有效期
-- IN ROLE role_name        - 加入指定角色
-- ROLE role_name           - 包含指定角色

-- 创建可登录的用户
CREATE USER app_user WITH PASSWORD 'secure_password';

-- 等价于
CREATE ROLE app_user WITH LOGIN PASSWORD 'secure_password';

-- 创建带更多属性的用户
CREATE USER admin_user WITH
    PASSWORD 'admin_password'
    CREATEDB
    CREATEROLE
    VALID UNTIL '2026-12-31';

-- 创建超级用户（需要超级用户权限）
CREATE USER super_user WITH SUPERUSER PASSWORD 'super_password';

-- 创建只读用户
CREATE USER readonly_user WITH PASSWORD 'readonly_password';

-- 创建组角色（不能登录）
CREATE ROLE read_only;
CREATE ROLE read_write;
CREATE ROLE admin;

-- 创建用户并加入组
CREATE USER analyst WITH PASSWORD 'analyst_password' IN ROLE read_only;
CREATE USER developer WITH PASSWORD 'dev_password' IN ROLE read_write;

-- 修改密码
ALTER USER app_user WITH PASSWORD 'new_password';

-- 添加属性
ALTER USER app_user WITH CREATEDB;

-- 移除属性
ALTER USER app_user WITH NOCREATEDB;

-- 设置密码有效期
ALTER USER app_user WITH VALID UNTIL '2026-06-30';

-- 重命名角色
ALTER USER app_user RENAME TO application_user;

-- 修改所有者
ALTER TABLE employees OWNER TO app_user;

-- 删除角色（必须先转移或删除其拥有的对象）
DROP ROLE app_user;

-- 如果存在则删除
DROP ROLE IF EXISTS app_user;

-- 查看角色拥有的对象
SELECT 
    n.nspname AS schema,
    c.relname AS object,
    c.relkind AS type
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relowner = (SELECT oid FROM pg_roles WHERE rolname = 'app_user');

-- 转移对象所有权
REASSIGN OWNED BY app_user TO postgres;

-- 删除角色拥有的对象
DROP OWNED BY app_user;

┌─────────────────┬─────────────────────────────────────────────┐
│ 权限            │ 说明                                        │
├─────────────────┼─────────────────────────────────────────────┤
│ SELECT          │ 查询数据                                    │
│ INSERT          │ 插入数据                                    │
│ UPDATE          │ 更新数据                                    │
│ DELETE          │ 删除数据                                    │
│ TRUNCATE        │ 清空表                                      │
│ REFERENCES      │ 创建外键引用                                │
│ TRIGGER         │ 创建触发器                                  │
│ CREATE          │ 创建对象（数据库/模式/表）                  │
│ CONNECT         │ 连接数据库                                  │
│ TEMPORARY       │ 创建临时表                                  │
│ EXECUTE         │ 执行函数/过程                               │
│ USAGE           │ 使用模式/序列/类型                          │
│ ALL             │ 所有权限                                    │
└─────────────────┴─────────────────────────────────────────────┘

-- 授予表权限
GRANT SELECT, INSERT, UPDATE, DELETE ON table_name TO role_name;
GRANT ALL PRIVILEGES ON table_name TO role_name;

-- 授予列权限
GRANT SELECT (column1, column2) ON table_name TO role_name;
GRANT UPDATE (column1) ON table_name TO role_name;

-- 授予序列权限
GRANT USAGE, SELECT ON SEQUENCE sequence_name TO role_name;

-- 授予数据库权限
GRANT CONNECT, TEMPORARY ON DATABASE database_name TO role_name;
GRANT ALL PRIVILEGES ON DATABASE database_name TO role_name;

-- 授予模式权限
GRANT USAGE, CREATE ON SCHEMA schema_name TO role_name;
GRANT ALL PRIVILEGES ON SCHEMA schema_name TO role_name;

-- 授予函数权限
GRANT EXECUTE ON FUNCTION function_name TO role_name;

-- 授予所有表的权限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO read_write;

-- 授予所有序列的权限
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO read_write;

-- 将用户加入角色
GRANT read_only TO analyst;
GRANT read_write TO developer;
GRANT admin TO admin_user;

-- 授予多个角色
GRANT read_only, read_write TO power_user;

-- 授予 WITH ADMIN OPTION（可以再将此角色授予他人）
GRANT admin TO admin_user WITH ADMIN OPTION;

-- 撤销表权限
REVOKE SELECT, INSERT ON table_name FROM role_name;
REVOKE ALL PRIVILEGES ON table_name FROM role_name;

-- 撤销列权限
REVOKE SELECT (column1) ON table_name FROM role_name;

-- 撤销数据库权限
REVOKE CONNECT ON DATABASE database_name FROM role_name;

-- 撤销模式权限
REVOKE USAGE ON SCHEMA schema_name FROM role_name;

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

-- 撤销角色成员关系
REVOKE read_only FROM analyst;

-- 撤销 WITH ADMIN OPTION
REVOKE ADMIN OPTION FOR admin FROM admin_user;

-- 设置默认权限（新创建的对象自动获得权限）
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO read_only;

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

ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT USAGE ON SEQUENCES TO read_write;

-- 查看默认权限
SELECT 
    pg_get_userbyid(defaclrole) AS role,
    n.nspname AS schema,
    defaclobjtype AS object_type,
    defaclacl AS privileges
FROM pg_default_acl d
JOIN pg_namespace n ON n.oid = d.defaclnamespace;

-- 创建带 INHERIT 属性的角色
CREATE ROLE child_role INHERIT;
CREATE ROLE parent_role;

GRANT parent_role TO child_role;

-- child_role 自动继承 parent_role 的权限

-- 创建不继承的角色
CREATE ROLE no_inherit_role NOINHERIT;
GRANT parent_role TO no_inherit_role;

-- no_inherit_role 不会自动继承权限
-- 需要使用 SET ROLE 显式切换
SET ROLE parent_role;

-- 切换到指定角色
SET ROLE read_only;

-- 执行操作
SELECT * FROM employees;

-- 恢复原角色
RESET ROLE;

-- 查看当前角色
SELECT current_role;
SELECT current_user;
SELECT session_user;

-- 查看所有角色
SELECT rolname, rolsuper, rolcreatedb, rolcreaterole, rolcanlogin
FROM pg_roles;

-- 使用 psql 命令
\du
\du+ role_name

-- 查看表权限
SELECT 
    grantee,
    table_schema,
    table_name,
    privilege_type
FROM information_schema.table_privileges
WHERE table_name = 'employees';

-- 查看列权限
SELECT 
    grantee,
    table_name,
    column_name,
    privilege_type
FROM information_schema.column_privileges
WHERE table_name = 'employees';

-- 查看数据库权限
SELECT 
    datname,
    datacl
FROM pg_database;

-- 查看模式权限
SELECT 
    nspname,
    nspacl
FROM pg_namespace;

-- 使用 psql 命令
\dp table_name
\l+  -- 数据库权限
\dn+ -- 模式权限

-- 创建只读角色
CREATE ROLE read_only;
GRANT CONNECT ON DATABASE mydb TO read_only;
GRANT USAGE ON SCHEMA public TO read_only;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO read_only;

-- 创建读写角色
CREATE ROLE read_write;
GRANT read_only TO read_write;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO read_write;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO read_write;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT INSERT, UPDATE, DELETE ON TABLES TO read_write;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT USAGE ON SEQUENCES TO read_write;

-- 创建用户并分配角色
CREATE USER app_readonly WITH PASSWORD 'xxx' IN ROLE read_only;
CREATE USER app_readwrite WITH PASSWORD 'xxx' IN ROLE read_write;

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

-- 创建策略：用户只能看到自己部门的数据
CREATE POLICY department_policy ON employees
    USING (department_id = current_user_department());

-- 创建策略函数
CREATE FUNCTION current_user_department() RETURNS INTEGER AS $$
    SELECT department_id FROM users WHERE name = current_user;
$$ LANGUAGE SQL SECURITY DEFINER;

-- 管理员可以看到所有数据
CREATE POLICY admin_all_access ON employees
    USING (current_user IN (SELECT name FROM users WHERE is_admin = TRUE));

-- 禁用行级安全
ALTER TABLE employees DISABLE ROW LEVEL SECURITY;

-- 删除策略
DROP POLICY department_policy ON employees;

-- 使用 scram-sha-256 加密（推荐）
-- 在 postgresql.conf 中设置：
-- password_encryption = 'scram-sha-256'

-- 设置密码
ALTER USER app_user WITH PASSWORD 'secure_password';

-- 设置密码有效期
ALTER USER app_user WITH VALID UNTIL '2026-12-31';

-- 强制用户下次登录修改密码
ALTER USER app_user WITH PASSWORD 'temp_password' VALID UNTIL 'now';

运维阶段
├── 用户权限管理（本文）
├── 备份与恢复
└── 性能优化