用户与权限管理

-- 创建用户
CREATE USER alice WITH PASSWORD 'alice123';
-- 等价于 CREATE ROLE alice WITH LOGIN PASSWORD 'alice123';

-- 创建角色（不用于登录，用于权限分组）
CREATE ROLE sales_team;
CREATE ROLE dev_team;

CREATE USER bob WITH PASSWORD 'bob456';
CREATE USER charlie WITH PASSWORD 'charlie789';

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

-- 修改属性
ALTER USER alice WITH SUPERUSER;  -- 设为超级用户
ALTER USER alice WITH NOSUPERUSER;  -- 取消超级用户
ALTER USER alice WITH CREATEDB;  -- 允许创建数据库
ALTER USER alice WITH CREATEROLE;  -- 允许创建角色

-- 禁用用户
ALTER USER alice WITH NOLOGIN;

-- 重命名
ALTER USER alice RENAME TO alice_new;

-- 删除前确认没有依赖的数据库对象
REASSIGN OWNED BY alice TO postgres;  -- 转移对象所有权
DROP OWNED BY alice;  -- 删除用户所有的对象
DROP USER alice;

-- 创建角色层级
CREATE ROLE readonly NOINHERIT;  -- NOINHERIT：不自动继承权限
CREATE ROLE readwrite;
CREATE ROLE admin;

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

-- 继承关系（admin 继承 readwrite 和 readonly 的权限）
GRANT readonly TO readwrite;
GRANT readwrite TO admin;

-- 授予用户
GRANT readwrite TO bob;

-- 设置角色
SET ROLE readonly;
-- 现在拥有 readonly 的权限
SELECT * FROM orders;  -- 可以查询
INSERT INTO orders ... VALUES ...;  -- ❌ 不可插入

-- 恢复原始用户
RESET ROLE;

-- 授予对特定表的查询权限
GRANT SELECT ON orders TO readonly;
GRANT SELECT, INSERT, UPDATE ON orders TO readwrite;
GRANT ALL ON orders TO admin;

-- 授予 schema 的使用权限
GRANT USAGE ON SCHEMA public TO readonly;
GRANT CREATE ON SCHEMA public TO readwrite;

-- 当前用户将来创建的所有表，readwrite 组都有 SELECT, INSERT 权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite;

-- 当前用户将来创建的所有函数，readonly 组都有执行权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT EXECUTE ON FUNCTIONS TO readonly;

-- 敏感列仅对特定角色开放
GRANT SELECT (name, email, department) ON employees TO readonly;
-- 但工资列隐藏
REVOKE SELECT (salary) ON employees FROM readonly;

-- 启用行级安全
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders FORCE ROW LEVEL SECURITY;  -- 对表所有者同样生效

-- 创建策略：每个用户只能看自己的订单
CREATE POLICY user_orders ON orders
    FOR SELECT
    USING (customer_id = current_setting('myapp.user_id')::INT);

-- 创建策略：用户只能修改自己的订单
CREATE POLICY user_update_orders ON orders
    FOR UPDATE
    USING (customer_id = current_setting('myapp.user_id')::INT)
    WITH CHECK (customer_id = current_setting('myapp.user_id')::INT);

-- 数据表带可见性控制
ALTER TABLE documents ADD COLUMN visibility VARCHAR(20) DEFAULT 'private';
ALTER TABLE documents ADD COLUMN owner_id INT;
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- 公开文档：所有人可见
CREATE POLICY public_docs ON documents
    FOR SELECT
    USING (visibility = 'public');

-- 私有文档：仅自己可见
CREATE POLICY own_docs ON documents
    FOR SELECT
    USING (owner_id = current_setting('app.user_id')::INT);

-- 已提交文档：仅审核组可见
CREATE POLICY reviewed_docs ON documents
    FOR SELECT
    USING (visibility = 'reviewed' AND
           current_role IN ('reviewer', 'admin'));

-- 创建只读账号用于报表查询
CREATE ROLE report_user LOGIN PASSWORD 'report123';
GRANT CONNECT ON DATABASE mydb TO report_user;
GRANT USAGE ON SCHEMA public TO report_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO report_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO report_user;

-- 开启登录审计
ALTER USER alice SET log_login_connections = on;  -- 需要超级用户设置

-- 查看 pg_hba.conf 设置后生效
-- 在配置文件中增加：
-- log_connections = on
-- log_disconnections = on

-- 查看用户的权限
SELECT * FROM information_schema.role_table_grants
WHERE grantee = 'readwrite';

-- 查看当前用户的权限
SELECT * FROM information_schema。applicable_roles;

-- 查看数据库的权限概要
SELECT 
    grantee,
    table_schema,
    table_name,
    string_agg(privilege_type, ', ' ORDER BY privilege_type) AS privileges
FROM information_schema。role_table_grants
WHERE table_schema = 'public'
GROUP BY grantee, table_schema, table_name
ORDER BY grantee, table_name;