用户权限管理 #

-- 创建用户
CREATE USER readonly WITH PASSWORD 'password123';

-- 创建管理员用户
CREATE USER admin WITH PASSWORD 'admin123';

-- 创建带选项的用户
CREATE USER app_user 
    WITH PASSWORD 'app123'
    LOGIN
    CREATEDB;

-- 查看所有用户
SELECT * FROM pg_catalog.pg_user;
SHOW USERS;

-- 修改密码
ALTER USER readonly WITH PASSWORD 'newpassword';

-- 重命名用户
ALTER USER readonly RENAME TO read_user;

-- 禁用登录
ALTER USER readonly NOLOGIN;

-- 启用登录
ALTER USER readonly LOGIN;

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

-- 授予创建用户权限
ALTER USER admin CREATEROLE;

-- 删除用户
DROP USER readonly;

-- 如果用户拥有对象，需要先转移或删除
DROP USER IF EXISTS readonly;

-- 创建角色
CREATE ROLE readonly_role;

-- 创建带权限的角色
CREATE ROLE app_role WITH LOGIN PASSWORD 'role123';

-- 角色与用户的区别:
-- - 用户: 可以登录
-- - 角色: 不能登录 (除非指定 LOGIN)

-- 查看所有角色
SHOW ROLES;
SELECT * FROM pg_catalog.pg_roles;

-- 将角色授予用户
GRANT readonly_role TO readonly_user;

-- 将角色授予另一个角色
GRANT readonly_role TO app_role;

-- 撤销角色
REVOKE readonly_role FROM readonly_user;

-- 查看用户角色
SELECT 
    r.rolname AS role,
    m.rolname AS member
FROM pg_catalog.pg_roles r
JOIN pg_catalog.pg_auth_members am ON r.oid = am.roleid
JOIN pg_catalog.pg_roles m ON am.member = m.oid;

预定义角色
┌─────────────────────────────────────────────────────────────┐
│                                                             │
│   admin: 完全管理权限                                       │
│                                                             │
│   root: 超级用户，拥有所有权限                              │
│                                                             │
│   public: 所有用户默认属于此角色                            │
│                                                             │
│   常用权限组合:                                             │
│   ├── readonly: 只读权限                                   │
│   ├── readwrite: 读写权限                                  │
│   └── admin: 管理权限                                      │
│                                                             │
└─────────────────────────────────────────────────────────────┘

-- 数据库级别权限
GRANT ALL ON DATABASE mydb TO app_user;
GRANT CONNECT ON DATABASE mydb TO readonly_user;

-- Schema级别权限
GRANT ALL ON SCHEMA public TO app_user;
GRANT USAGE ON SCHEMA public TO readonly_user;

-- 表级别权限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE users TO app_user;

-- 列级别权限
GRANT SELECT (id, name) ON users TO readonly_user;

-- 序列权限
GRANT USAGE, SELECT ON SEQUENCE user_id_seq TO app_user;

-- 授予数据库权限
GRANT ALL ON DATABASE mydb TO admin_user;
GRANT CONNECT ON DATABASE mydb TO readonly_user;

-- 授予表权限
GRANT SELECT ON users TO readonly_user;
GRANT SELECT, INSERT, UPDATE ON users TO app_user;
GRANT ALL ON users TO admin_user;

-- 授予多个表权限
GRANT SELECT ON users, orders, products TO readonly_user;

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

-- 授予所有权限
GRANT ALL ON ALL TABLES IN SCHEMA public TO admin_user;

-- 允许用户授权给其他人
GRANT SELECT ON users TO app_user WITH GRANT OPTION;

-- 撤销表权限
REVOKE SELECT ON users FROM readonly_user;
REVOKE ALL ON users FROM app_user;

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

-- 撤销Schema下所有表权限
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM readonly_user;

-- 撤销授权选项
REVOKE GRANT OPTION FOR SELECT ON users FROM app_user;

-- 查看表权限
SHOW GRANTS ON TABLE users;

-- 查看数据库权限
SHOW GRANTS ON DATABASE mydb;

-- 查看用户权限
SELECT 
    grantee,
    table_schema,
    table_name,
    privilege_type
FROM information_schema.table_privileges
WHERE grantee = 'readonly_user';

-- 查看所有权限
SELECT * FROM information_schema.table_privileges;

最小权限原则
┌─────────────────────────────────────────────────────────────┐
│                                                             │
│   1. 只授予必要的权限                                       │
│      ├── 应用用户: 只授予业务需要的权限                    │
│      ├── 只读用户: 只授予 SELECT                           │
│      └── 管理员: 授予管理权限                              │
│                                                             │
│   2. 使用角色管理权限                                       │
│      ├── 创建角色并授权                                    │
│      ├── 将角色授予用户                                    │
│      └── 便于权限管理                                      │
│                                                             │
│   3. 定期审计权限                                           │
│      ├── 检查用户权限                                      │
│      ├── 清理不必要的权限                                  │
│      └── 及时回收离职用户权限                              │
│                                                             │
│   4. 避免直接授权给用户                                     │
│      ├── 通过角色授权                                      │
│      └── 简化管理                                          │
│                                                             │
└─────────────────────────────────────────────────────────────┘

-- 创建只读角色
CREATE ROLE readonly_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO readonly_role;

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

-- 创建管理员角色
CREATE ROLE admin_role;
GRANT ALL ON DATABASE mydb TO admin_role;
GRANT ALL ON ALL TABLES IN SCHEMA public TO admin_role;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO admin_role;

-- 将角色授予用户
GRANT readonly_role TO readonly_user;
GRANT readwrite_role TO app_user;
GRANT admin_role TO admin_user;