跳到主要内容

PostgreSQL 用户权限管理

PostgreSQL 拥有一套完善的用户权限管理系统，本章将详细介绍如何创建用户、管理角色、分配权限，确保数据库的安全性。

用户与角色概述​

用户和角色的关系​

在 PostgreSQL 中，用户（User）和角色（Role）本质上是相同的。区别在于：

  • 用户：默认可以登录（LOGIN 权限）
  • 角色：默认不能登录，通常用于权限分组

权限层级​

PostgreSQL 的权限从高到低分为：

  1. 超级用户：拥有所有权限
  2. 数据库所有者：对特定数据库的所有权限
  3. 表所有者：对特定表的所有权限
  4. 普通用户：被授予的特定权限

创建用户和角色​

创建用户​

-- 创建基本用户
CREATE USER john WITH PASSWORD 'secret123';

-- 创建用户并设置属性
CREATE USER dev_user WITH 
    PASSWORD 'dev_password'
    LOGIN
    NOSUPERUSER
    NOCREATEDB
    NOCREATEROLE;

-- 创建带有效期的用户
CREATE USER temp_user WITH 
    PASSWORD 'temp_password'
    VALID UNTIL '2024-12-31';

创建角色​

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

-- 创建角色并设置权限
CREATE ROLE developer WITH 
    NOLOGIN
    NOSUPERUSER
    NOCREATEDB;

-- 创建可以创建其他角色的角色
CREATE ROLE admin WITH 
    NOLOGIN
    CREATEROLE
    CREATEDB;

用户和角色的关键属性​

属性说明示例
SUPERUSER超级用户权限SUPERUSER / NOSUPERUSER
CREATEDB创建数据库权限CREATEDB / NOCREATEDB
CREATEROLE创建角色权限CREATEROLE / NOCREATEROLE
LOGIN登录权限LOGIN / NOLOGIN
PASSWORD设置密码PASSWORD 'secret'
VALID UNTIL密码有效期VALID UNTIL '2024-12-31'
CONNECTION LIMIT连接数限制CONNECTION LIMIT 10

修改用户属性​

-- 修改密码
ALTER USER john WITH PASSWORD 'new_secret';

-- 授予超级用户权限
ALTER USER john WITH SUPERUSER;

-- 移除超级用户权限
ALTER USER john WITH NOSUPERUSER;

-- 授予创建数据库权限
ALTER USER john WITH CREATEDB;

-- 设置连接数限制
ALTER USER john WITH CONNECTION LIMIT 5;

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

-- 重命名用户
ALTER USER john RENAME TO john_doe;

-- 禁用用户（设置无法使用的密码）
ALTER USER john WITH PASSWORD NULL;

角色成员关系​

授予角色成员资格​

-- 创建角色组
CREATE ROLE sales_team;
CREATE ROLE marketing_team;

-- 将用户添加到角色组
GRANT sales_team TO john;
GRANT marketing_team TO jane;

-- 一个用户可以是多个角色的成员
GRANT sales_team TO jane;

角色继承​

PostgreSQL 默认启用角色继承，用户会自动继承所属角色的权限：

-- 查看当前角色继承设置
SHOW role_inheritance;

-- 创建角色并设置继承属性
CREATE ROLE manager WITH NOINHERIT;

-- 用户继承角色时不会自动继承权限
-- 需要手动 SET ROLE
GRANT manager TO john;

-- 切换到角色
SET ROLE manager;

-- 查看当前角色
SELECT current_role;

管理角色成员资格​

-- 查看角色成员
SELECT 
    r.rolname AS role,
    m.rolname AS member,
    a.rolname AS admin
FROM pg_roles r
JOIN pg_auth_members am ON r.oid = am.roleid
JOIN pg_roles m ON am.member = m.oid
LEFT JOIN pg_roles a ON am.grantor = a.oid
WHERE r.rolname = 'sales_team';

-- 移除角色成员资格
REVOKE sales_team FROM john;

权限类型​

数据库权限​

权限说明
CONNECT连接数据库
CREATE创建 schema
TEMPORARY创建临时表
ALL所有权限
-- 授予数据库连接权限
GRANT CONNECT ON DATABASE mydb TO john;

-- 授予创建 schema 权限
GRANT CREATE ON DATABASE mydb TO john;

-- 授予所有数据库权限
GRANT ALL PRIVILEGES ON DATABASE mydb TO john;

Schema 权限​

权限说明
CREATE在 schema 中创建对象
USAGE使用 schema 中的对象
-- 授予 schema 使用权限
GRANT USAGE ON SCHEMA public TO john;

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

-- 授予所有 schema 权限
GRANT ALL PRIVILEGES ON SCHEMA public TO john;

表权限​

权限说明
SELECT查询数据
INSERT插入数据
UPDATE更新数据
DELETE删除数据
TRUNCATE清空表
REFERENCES创建外键引用
TRIGGER创建触发器
-- 授予查询权限
GRANT SELECT ON TABLE users TO john;

-- 授予查询和插入权限
GRANT SELECT, INSERT ON TABLE users TO john;

-- 授予所有表权限
GRANT ALL PRIVILEGES ON TABLE users TO john;

-- 授予列级权限
GRANT SELECT (name, email) ON TABLE users TO john;
GRANT UPDATE (email) ON TABLE users TO john;

序列权限​

权限说明
USAGE使用序列（currval, nextval）
SELECT读取序列当前值
UPDATE设置序列值
-- 授予序列使用权限
GRANT USAGE ON SEQUENCE users_id_seq TO john;

-- 授予所有序列权限
GRANT ALL PRIVILEGES ON SEQUENCE users_id_seq TO john;

函数权限​

-- 授予函数执行权限
GRANT EXECUTE ON FUNCTION calculate_total(NUMERIC, NUMERIC) TO john;

-- 授予所有函数执行权限
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO john;

批量授权​

授权所有表​

-- 授予 schema 中所有表的查询权限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;

-- 授予 schema 中所有表的所有权限
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO developer;

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

默认权限​

使用 ALTER DEFAULT PRIVILEGES 可以设置新创建对象的默认权限：

-- 设置新表的默认权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT ON TABLES TO read_only;

-- 设置新函数的默认权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT EXECUTE ON FUNCTIONS TO developer;

-- 查看默认权限
\ddp

权限管理实践​

创建只读用户​

-- 1. 创建只读角色
CREATE ROLE read_only WITH NOLOGIN;

-- 2. 授予数据库连接权限
GRANT CONNECT ON DATABASE mydb TO read_only;

-- 3. 授予 schema 使用权限
GRANT USAGE ON SCHEMA public TO read_only;

-- 4. 授予所有表的查询权限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;

-- 5. 设置新表的默认权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT ON TABLES TO read_only;

-- 6. 创建用户并授予角色
CREATE USER reader1 WITH PASSWORD 'reader_pass';
GRANT read_only TO reader1;

创建读写用户​

-- 1. 创建读写角色
CREATE ROLE read_write WITH NOLOGIN;

-- 2. 授予数据库连接和 schema 权限
GRANT CONNECT ON DATABASE mydb TO read_write;
GRANT USAGE, CREATE ON SCHEMA public TO read_write;

-- 3. 授予表的增删改查权限
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO read_write;

-- 4. 授予序列使用权限
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO read_write;

-- 5. 设置默认权限
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;

-- 6. 创建用户
CREATE USER app_user WITH PASSWORD 'app_pass';
GRANT read_write TO app_user;

创建管理员用户​

-- 创建数据库管理员
CREATE ROLE db_admin WITH NOLOGIN CREATEROLE CREATEDB;

-- 授予所有权限
GRANT ALL PRIVILEGES ON DATABASE mydb TO db_admin;
GRANT ALL PRIVILEGES ON SCHEMA public TO db_admin;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO db_admin;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO db_admin;

-- 设置默认权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT ALL PRIVILEGES ON TABLES TO db_admin;

-- 创建管理员用户
CREATE USER admin_user WITH PASSWORD 'admin_pass';
GRANT db_admin TO admin_user;

查看权限​

查看用户信息​

-- 查看所有用户/角色
\du
-- 或
SELECT rolname, rolsuper, rolcreatedb, rolcreaterole, rollogin 
FROM pg_roles;

-- 查看特定用户信息
\du john

查看权限​

-- 查看表权限
\dp users
-- 或
SELECT 
    grantee,
    privilege_type
FROM information_schema.table_privileges 
WHERE table_name = 'users';

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

-- 查看数据库权限
SELECT 
    datname,
    datacl
FROM pg_database 
WHERE datname = 'mydb';

-- 查看 schema 权限
SELECT 
    nspname,
    nspacl
FROM pg_namespace 
WHERE nspname = 'public';

撤销权限​

-- 撤销表权限
REVOKE SELECT ON TABLE users FROM john;

-- 撤销多个权限
REVOKE SELECT, INSERT ON TABLE users FROM john;

-- 撤销所有表权限
REVOKE ALL PRIVILEGES ON TABLE users FROM john;

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

-- 撤销数据库权限
REVOKE ALL PRIVILEGES ON DATABASE mydb FROM john;

-- 撤销角色成员资格
REVOKE sales_team FROM john;

删除用户和角色​

-- 删除用户（必须先撤销所有权限）
DROP USER john;

-- 删除角色（必须先撤销所有权限和成员）
DROP ROLE read_only;

-- 强制删除（先重新分配所有权）
REASSIGN OWNED BY john TO postgres;
DROP OWNED BY john;
DROP USER john;

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

行级安全策略​

PostgreSQL 支持行级安全（Row-Level Security），可以控制用户只能访问特定行：

-- 创建测试表
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER,
    product_name VARCHAR(100),
    amount NUMERIC(10, 2)
);

-- 插入测试数据
INSERT INTO orders (customer_id, product_name, amount) VALUES
(1, 'Product A', 100),
(1, 'Product B', 200),
(2, 'Product C', 150),
(3, 'Product D', 300);

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

-- 创建策略：用户只能看到自己客户的订单
CREATE POLICY customer_policy ON orders
    USING (customer_id = current_setting('app.customer_id')::INTEGER);

-- 创建策略：允许所有用户查询
CREATE POLICY select_all ON orders
    FOR SELECT
    USING (true);

-- 创建策略：用户只能插入自己的订单
CREATE POLICY insert_own ON orders
    FOR INSERT
    WITH CHECK (customer_id = current_setting('app.customer_id')::INTEGER);

-- 查看策略
\d orders
SELECT * FROM pg_policies WHERE tablename = 'orders';

-- 删除策略
DROP POLICY customer_policy ON orders;

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

最佳实践​

权限设计原则​

  1. 最小权限原则：只授予必要的权限
  2. 使用角色分组：通过角色管理权限，而非直接授予用户
  3. 定期审计：检查和清理不必要的权限
  4. 避免使用超级用户：日常操作使用普通用户

权限管理模板​

-- 创建标准角色组
CREATE ROLE app_readonly WITH NOLOGIN;
CREATE ROLE app_readwrite WITH NOLOGIN;
CREATE ROLE app_admin WITH NOLOGIN NOINHERIT;

-- 设置权限...

-- 创建用户时使用角色
CREATE USER app_reader WITH PASSWORD 'xxx';
GRANT app_readonly TO app_reader;

CREATE USER app_writer WITH PASSWORD 'xxx';
GRANT app_readwrite TO app_writer;

小结​

本章我们学习了 PostgreSQL 用户权限管理：

  1. 用户与角色：理解用户和角色的关系
  2. 创建用户角色：CREATE USER、CREATE ROLE
  3. 权限类型：数据库、Schema、表、序列、函数权限
  4. 授权和撤销：GRANT、REVOKE 语句
  5. 角色成员关系：角色继承和成员管理
  6. 行级安全：细粒度的数据访问控制
  7. 最佳实践：权限设计原则

练习​

  1. 创建一个只读用户，只能查询特定表
  2. 创建一个角色组，包含多个用户
  3. 实现行级安全策略，用户只能访问自己的数据
  4. 审计并清理不必要的权限

参考资源​