基于角色的权限管理

1. 角色体系#

PostgreSQL 使用角色统一管理用户和组。

-- 创建登录角色（用户）
CREATE ROLE app_user LOGIN PASSWORD 'password';

-- 创建组角色（不能登录）
CREATE ROLE readonly NOLOGIN;
CREATE ROLE readwrite NOLOGIN;
CREATE ROLE admin NOLOGIN;

-- 授予权限给组角色
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO readwrite;
GRANT ALL PRIVILEGES ON SCHEMA public TO admin;

-- 将组角色授予用户
GRANT readonly TO app_read;
GRANT readwrite TO app_write;

2. 角色继承#

-- 默认角色继承
SET ROLE readonly;  -- 切换到 readonly 角色
SELECT current_user;  -- readonly
RESET ROLE;  -- 恢复

-- 禁止继承
ALTER ROLE app_user NOINHERIT;
-- 需要显式 SET ROLE 才能获得组角色权限

3. 默认权限#

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

ALTER DEFAULT PRIVILEGES FOR ROLE admin
    GRANT SELECT ON TABLES TO readonly;

4. 查看权限#

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

-- 查看角色成员
SELECT r.rolname, m.rolname AS member
FROM pg_roles r
JOIN pg_auth_members am ON r.oid = am.roleid
JOIN pg_roles m ON am.member = m.oid;

用户管理#

单行写法：创建用户 CREATE USER <用户名> [WITH] PASSWORD '<密码>'

-- 创建带密码的用户
CREATE USER app_user WITH PASSWORD 'StrongP@ss123';

单行写法：创建带登录权限的用户 CREATE ROLE <角色名> WITH LOGIN PASSWORD '<密码>'

-- 创建带登录权限的角色
CREATE ROLE app_role WITH LOGIN PASSWORD 'StrongP@ss123';

单行写法：修改用户密码 ALTER USER <用户名> [WITH] PASSWORD '<新密码>'

-- 修改用户密码
ALTER USER app_user WITH PASSWORD 'NewP@ss456';

单行写法：删除用户 DROP USER [IF EXISTS] <用户名>

-- 删除用户
DROP USER IF EXISTS app_user;

单行写法：查看所有用户 SELECT <列名> FROM pg_user

-- 查看所有用户列表
SELECT usename, usesuper FROM pg_user;

角色管理#

单行写法：创建角色 CREATE ROLE <角色名>

-- 创建角色
CREATE ROLE readonly;

单行写法：创建带属性的角色 CREATE ROLE <角色名> WITH <属性>

-- 创建带登录和创建数据库属性的角色
CREATE ROLE admin WITH LOGIN CREATEDB CREATEROLE;

单行写法：将角色分配给用户 GRANT <角色名> TO <用户名>

-- 分配角色给用户
GRANT readonly TO app_user;

单行写法：撤销用户角色 REVOKE <角色名> FROM <用户名>

-- 撤销用户的角色
REVOKE readonly FROM app_user;

单行写法：删除角色 DROP ROLE [IF EXISTS] <角色名>

-- 删除角色
DROP ROLE IF EXISTS readonly;

单行写法：查看所有角色 SELECT <列名> FROM pg_roles

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

权限管理#

单行写法：授予连接数据库权限 GRANT CONNECT ON DATABASE <库名> TO <角色名>

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

单行写法：授予使用模式权限 GRANT USAGE ON SCHEMA <模式名> TO <角色名>

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

单行写法：授予表查询权限 GRANT SELECT ON <表名> TO <角色名>

-- 授予表查询权限
GRANT SELECT ON users TO readonly;

单行写法：授予表所有权限 GRANT ALL ON <表名> TO <角色名>

-- 授予表所有权限
GRANT ALL ON users TO admin;

单行写法：授予模式所有表查询权限 GRANT SELECT ON ALL TABLES IN SCHEMA <模式名> TO <角色名>

-- 授予模式所有表查询权限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

单行写法：授予模式所有表所有权限 GRANT ALL ON ALL TABLES IN SCHEMA <模式名> TO <角色名>

-- 授予模式所有表所有权限
GRANT ALL ON ALL TABLES IN SCHEMA public TO admin;

单行写法：授予序列使用权限 GRANT USAGE ON SEQUENCE <序列名> TO <角色名>

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

单行写法：撤销表查询权限 REVOKE SELECT ON <表名> FROM <角色名>

-- 撤销表查询权限
REVOKE SELECT ON users FROM readonly;

单行写法：撤销表所有权限 REVOKE ALL ON <表名> FROM <角色名>

-- 撤销表所有权限
REVOKE ALL ON users FROM readonly;

单行写法：修改默认权限 ALTER DEFAULT PRIVILEGES IN SCHEMA <模式名> GRANT SELECT ON TABLES TO <角色名>

-- 设置未来创建表的默认权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

默认角色#

单行写法：设置用户默认角色 SET ROLE <角色名>

-- 切换当前会话角色
SET ROLE readonly;

单行写法：重置为原始角色 RESET ROLE

-- 重置为原始用户
RESET ROLE;

单行写法：设置默认搜索路径 ALTER ROLE <角色名> SET search_path TO <模式名>

-- 设置角色的默认搜索路径
ALTER ROLE app_user SET search_path TO myschema, public;

权限查看#

单行写法：查看表权限 \dp <表名>

-- 查看表的权限信息
\dp users;

单行写法：查看角色权限 SELECT <列名> FROM information_schema.role_table_grants WHERE <条件>

-- 查看角色表权限
SELECT grantee, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE table_name = 'users';

单行写法：查看用户权限 \du <用户名>

-- 查看用户角色和属性
\du app_user;

单行写法：查看数据库权限 SELECT <列名> FROM pg_database WHERE <条件>

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