用户权限

PostgreSQL提供了完善的用户和权限管理系统，可以精确控制用户对数据库对象的访问。本章将详细介绍用户管理、角色管理和权限控制。

用户与角色

在PostgreSQL中，用户和角色本质上相同，区别在于用户默认可以登录，而角色默认不能登录。

创建用户

sql

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

-- 创建用户并设置属性
CREATE USER admin_user 
WITH 
    PASSWORD 'admin_password'
    SUPERUSER              -- 超级用户
    CREATEDB               -- 可以创建数据库
    CREATEROLE             -- 可以创建角色
    VALID UNTIL '2025-12-31';  -- 密码有效期

-- 创建普通用户
CREATE USER readonly_user WITH PASSWORD 'readonly_password';

-- 创建带连接限制的用户
CREATE USER limited_user 
WITH 
    PASSWORD 'limited_password'
    CONNECTION LIMIT 5;    -- 最大连接数限制

创建角色

sql

-- 创建角色（默认不能登录）
CREATE ROLE app_role;

-- 创建可以登录的角色（等同于CREATE USER）
CREATE ROLE login_role WITH LOGIN PASSWORD 'role_password';

-- 创建角色并设置属性
CREATE ROLE admin_role 
WITH 
    SUPERUSER
    CREATEDB
    CREATEROLE
    NOINHERIT;             -- 不自动继承父角色权限

用户属性

sql

-- 查看用户属性
\du

-- 使用SQL查询
SELECT 
    usename AS 用户名,
    usesuper AS 是否超级用户,
    usecreatedb AS 可创建数据库,
    usecreaterole AS 可创建角色,
    useconnlimit AS 连接限制,
    valuntil AS 密码有效期
FROM pg_user;

-- 修改用户属性
ALTER USER app_user WITH SUPERUSER;
ALTER USER app_user WITH NOSUPERUSER;
ALTER USER app_user WITH CREATEDB;
ALTER USER app_user WITH CONNECTION LIMIT 10;

-- 设置密码
ALTER USER app_user WITH PASSWORD 'new_password';
ALTER USER app_user WITH PASSWORD 'new_password' VALID UNTIL '2025-06-30';

-- 重命名用户
ALTER USER app_user RENAME TO new_app_user;

删除用户

sql

-- 删除用户
DROP USER readonly_user;

-- 如果存在则删除
DROP USER IF EXISTS readonly_user;

-- 删除角色
DROP ROLE app_role;

-- 注意：删除用户前需要先转移或删除其拥有的对象
-- 查看用户拥有的对象
SELECT 
    n.nspname AS schema,
    c.relname AS object_name,
    c.relkind AS object_type
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE c.relowner = (SELECT oid FROM pg_roles WHERE rolname = 'app_user');

权限类型

PostgreSQL权限分为对象权限和系统权限。

对象权限

权限	适用对象	说明
SELECT	表、视图、序列	查询数据
INSERT	表	插入数据
UPDATE	表、序列	更新数据
DELETE	表	删除数据
TRUNCATE	表	清空表
REFERENCES	表	创建外键引用
TRIGGER	表	创建触发器
CREATE	数据库、Schema	创建对象
CONNECT	数据库	连接数据库
TEMPORARY	数据库	创建临时表
EXECUTE	函数、过程	执行函数/过程
USAGE	Schema、序列、类型	使用对象
ALL	所有对象	所有权限

系统权限（用户属性）

sql

-- SUPERUSER：超级用户，拥有所有权限
-- CREATEDB：创建数据库
-- CREATEROLE：创建/管理角色
-- REPLICATION：流复制
-- BYPASSRLS：绕过行级安全策略

授权（GRANT）

表权限

sql

-- 创建示例表
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    price NUMERIC(10, 2)
);

-- 授予所有权限
GRANT ALL PRIVILEGES ON TABLE products TO app_user;

-- 授予特定权限
GRANT SELECT ON TABLE products TO readonly_user;
GRANT SELECT, INSERT, UPDATE ON TABLE products TO app_user;

-- 授予列级权限
GRANT SELECT (id, name) ON TABLE products TO readonly_user;
GRANT UPDATE (price) ON TABLE products TO app_user;

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

-- 授予将来创建表的权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT ON TABLES TO readonly_user;

数据库权限

sql

-- 授予连接权限
GRANT CONNECT ON DATABASE mydb TO app_user;

-- 授予创建临时表权限
GRANT TEMPORARY ON DATABASE mydb TO app_user;

-- 授予在数据库中创建Schema的权限
GRANT CREATE ON DATABASE mydb TO app_user;

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

Schema权限

sql

-- 创建Schema
CREATE SCHEMA app_schema;

-- 授予在Schema中创建对象的权限
GRANT CREATE ON SCHEMA app_schema TO app_user;

-- 授予使用Schema的权限
GRANT USAGE ON SCHEMA app_schema TO app_user;

-- 授予所有Schema权限
GRANT ALL PRIVILEGES ON SCHEMA app_schema TO app_user;

-- 授予所有Schema的权限
GRANT USAGE, CREATE ON ALL SCHEMAS IN DATABASE mydb TO app_user;

序列权限

sql

-- 创建序列
CREATE SEQUENCE order_seq;

-- 授予序列使用权限
GRANT USAGE ON SEQUENCE order_seq TO app_user;

-- 授予序列更新权限
GRANT USAGE, SELECT, UPDATE ON SEQUENCE order_seq TO app_user;

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

-- 授予所有序列
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO app_user;

函数权限

sql

-- 创建函数
CREATE OR REPLACE FUNCTION get_product_count()
RETURNS INTEGER
AS $$
BEGIN
    RETURN (SELECT COUNT(*) FROM products);
END;
$$ LANGUAGE plpgsql;

-- 授予执行权限
GRANT EXECUTE ON FUNCTION get_product_count() TO app_user;

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

-- 授予将来创建函数的执行权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT EXECUTE ON FUNCTIONS TO app_user;

撤销权限（REVOKE）

sql

-- 撤销表权限
REVOKE SELECT ON TABLE products FROM readonly_user;
REVOKE ALL PRIVILEGES ON TABLE products FROM app_user;

-- 撤销列级权限
REVOKE SELECT (price) ON TABLE products FROM readonly_user;

-- 撤销数据库权限
REVOKE CONNECT ON DATABASE mydb FROM app_user;

-- 撤销Schema权限
REVOKE CREATE ON SCHEMA app_schema FROM app_user;

-- 撤销序列权限
REVOKE USAGE ON SEQUENCE order_seq FROM app_user;

-- 撤销函数权限
REVOKE EXECUTE ON FUNCTION get_product_count() FROM app_user;

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

角色管理

角色继承

sql

-- 创建角色层次结构
CREATE ROLE read_only;           -- 只读角色
CREATE ROLE read_write;          -- 读写角色
CREATE ROLE app_admin;           -- 应用管理员角色

-- 授予角色权限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO read_write;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO app_admin;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO app_admin;

-- 角色继承
GRANT read_only TO read_write;   -- read_write继承read_only的权限
GRANT read_write TO app_admin;   -- app_admin继承read_write的权限

-- 将角色授予用户
GRANT read_only TO readonly_user;
GRANT read_write TO app_user;
GRANT app_admin TO admin_user;

-- 检查角色继承是否启用
SHOW role_inheritance;  -- 默认为on

角色成员管理

sql

-- 将用户添加到角色
GRANT read_write TO app_user;

-- 将用户从角色中移除
REVOKE read_write FROM app_user;

-- 查看角色成员
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
ORDER BY r.rolname, m.rolname;

-- 使用\du查看
\du

角色切换

sql

-- 设置当前会话的角色
SET ROLE read_only;

-- 查看当前角色
SELECT CURRENT_ROLE;

-- 恢复原始角色
RESET ROLE;

-- 在会话中使用其他角色的权限
-- 前提是用户是该角色的成员

默认权限

sql

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

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

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

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT EXECUTE ON FUNCTIONS TO app_user;

-- 查看默认权限
SELECT 
    n.nspname AS schema,
    pg_get_userbyid(d.defaclrole) AS owner,
    d.defaclobjtype AS object_type,
    pg_catalog.array_to_string(d.defaclacl, E'\n') AS privileges
FROM pg_catalog.pg_default_acl d
JOIN pg_catalog.pg_namespace n ON d.defaclnamespace = n.oid
ORDER BY n.nspname, d.defaclobjtype;

行级安全（RLS）

PostgreSQL支持行级安全策略，可以控制用户访问特定行。

启用行级安全

sql

-- 创建示例表
CREATE TABLE employee_data (
    id SERIAL PRIMARY KEY,
    emp_name VARCHAR(50),
    department VARCHAR(50),
    salary NUMERIC(10, 2),
    created_by VARCHAR(50) DEFAULT CURRENT_USER
);

INSERT INTO employee_data (emp_name, department, salary, created_by) VALUES
    ('张三', '技术部', 20000, 'admin'),
    ('李四', '技术部', 18000, 'admin'),
    ('王五', '销售部', 15000, 'admin'),
    ('赵六', '销售部', 12000, 'manager');

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

-- 创建策略：用户只能看到自己创建的数据
CREATE POLICY user_data_policy ON employee_data
    FOR ALL
    TO PUBLIC
    USING (created_by = CURRENT_USER);

-- 创建策略：管理员可以看到所有数据
CREATE POLICY admin_all_policy ON employee_data
    FOR ALL
    TO admin_user
    USING (true)
    WITH CHECK (true);

-- 创建策略：部门经理只能看到本部门数据
CREATE POLICY dept_policy ON employee_data
    FOR SELECT
    TO manager_user
    USING (department = current_setting('app.user_department'));

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

策略类型

sql

-- SELECT策略
CREATE POLICY select_policy ON employee_data
    FOR SELECT
    USING (department = '技术部');

-- INSERT策略
CREATE POLICY insert_policy ON employee_data
    FOR INSERT
    WITH CHECK (created_by = CURRENT_USER);

-- UPDATE策略
CREATE POLICY update_policy ON employee_data
    FOR UPDATE
    USING (created_by = CURRENT_USER)  -- 可见的行
    WITH CHECK (created_by = CURRENT_USER);  -- 可更新的行

-- DELETE策略
CREATE POLICY delete_policy ON employee_data
    FOR DELETE
    USING (created_by = CURRENT_USER);

-- ALL策略（适用于所有操作）
CREATE POLICY all_policy ON employee_data
    FOR ALL
    USING (created_by = CURRENT_USER)
    WITH CHECK (created_by = CURRENT_USER);

管理策略

sql

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

-- 删除策略
DROP POLICY user_data_policy ON employee_data;

-- 强制策略（表所有者也受限制）
ALTER TABLE employee_data FORCE ROW LEVEL SECURITY;

-- 取消强制策略
ALTER TABLE employee_data NO FORCE ROW LEVEL SECURITY;

权限查询

查看用户权限

sql

-- 查看所有用户
\du

-- 查看表的权限
\z products
-- 或
\dp products

-- 使用SQL查询表权限
SELECT 
    grantee,
    table_schema,
    table_name,
    privilege_type
FROM information_schema.table_privileges
WHERE table_name = 'products'
ORDER BY grantee;

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

-- 查看Schema权限
SELECT 
    n.nspname AS schema,
    pg_catalog.array_to_string(n.nspacl, E'\n') AS acl
FROM pg_catalog.pg_namespace n
WHERE n.nspname = 'public';

-- 查看数据库权限
SELECT 
    datname,
    pg_catalog.array_to_string(datacl, E'\n') AS acl
FROM pg_database
WHERE datname = current_database();

检查用户是否有特定权限

sql

-- 检查表权限
SELECT has_table_privilege('app_user', 'products', 'SELECT');
SELECT has_table_privilege('app_user', 'products', 'INSERT');
SELECT has_table_privilege('app_user', 'products', 'UPDATE');

-- 检查列权限
SELECT has_column_privilege('app_user', 'products', 'price', 'SELECT');

-- 检查Schema权限
SELECT has_schema_privilege('app_user', 'public', 'USAGE');

-- 检查数据库权限
SELECT has_database_privilege('app_user', 'mydb', 'CONNECT');

-- 检查函数权限
SELECT has_function_privilege('app_user', 'get_product_count()', 'EXECUTE');

权限最佳实践

最小权限原则

sql

-- 只授予必要的权限
-- 不好的做法
GRANT ALL PRIVILEGES ON DATABASE mydb TO app_user;

-- 好的做法
GRANT CONNECT ON DATABASE mydb TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO app_user;

使用角色管理权限

sql

-- 创建角色并授予权限
CREATE ROLE app_readonly;
GRANT CONNECT ON DATABASE mydb TO app_readonly;
GRANT USAGE ON SCHEMA public TO app_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;

CREATE ROLE app_readwrite;
GRANT app_readonly TO app_readwrite;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_readwrite;

-- 将角色授予用户
GRANT app_readonly TO readonly_user;
GRANT app_readwrite TO app_user;

定期审计权限

sql

-- 查看所有用户的权限
SELECT 
    grantee AS 用户,
    table_schema AS schema,
    table_name AS 表,
    string_agg(privilege_type, ', ') AS 权限
FROM information_schema.table_privileges
WHERE grantee NOT IN ('postgres', 'PUBLIC')
GROUP BY grantee, table_schema, table_name
ORDER BY grantee, table_schema, table_name;

-- 查看超级用户
SELECT usename FROM pg_user WHERE usesuper = true;

-- 查看可以创建数据库的用户
SELECT usename FROM pg_user WHERE usecreatedb = true;

小结

本章介绍了PostgreSQL用户权限管理的主要内容：

内容	说明
用户管理	CREATE USER、ALTER USER、DROP USER
角色管理	CREATE ROLE、GRANT ROLE、REVOKE ROLE
对象权限	SELECT、INSERT、UPDATE、DELETE等
系统权限	SUPERUSER、CREATEDB、CREATEROLE等
授权	GRANT命令
撤销权限	REVOKE命令
行级安全	行级访问控制策略

用户权限 ​

用户与角色 ​

创建用户 ​

创建角色 ​

用户属性 ​

删除用户 ​

权限类型 ​

对象权限 ​

系统权限（用户属性） ​

授权（GRANT） ​

表权限 ​

数据库权限 ​

Schema权限 ​

序列权限 ​

函数权限 ​

撤销权限（REVOKE） ​

角色管理 ​

角色继承 ​

角色成员管理 ​

角色切换 ​

默认权限 ​

行级安全（RLS） ​

启用行级安全 ​

策略类型 ​

管理策略 ​

权限查询 ​

查看用户权限 ​

检查用户是否有特定权限 ​

权限最佳实践 ​

最小权限原则 ​

使用角色管理权限 ​

定期审计权限 ​

小结 ​