pgsql-用户和角色

作为一个“最先进”的数据库，PostgreSQL 的用户与权限系统（Role & Privilege）是它安全性的护城河目前我们一直用超级管理员postgres裸奔，这在生产环境里简直是“自杀”行

作为一个“最先进”的数据库，PostgreSQL 的**用户与权限系统（Role & Privilege）**是它安全性的护城河

目前我们一直用超级管理员postgres裸奔，这在生产环境里简直是“自杀”行为。

**场景：**你现在是 DBA，你要给前端开发人员创建一个账号，只能看数据，不能删库。

-- 1. 创建一个组（不准登录）
CREATE ROLE managers; 

-- 2. 创建一个用户（准许登录）
CREATE USER fengfeng WITH PASSWORD 'fengfeng';

-- 3. 让“用户”加入“组”
GRANT managers TO fengfeng;

查看我有哪些角色 \du

SELECT
    rolname AS 角色名,
    rolcanlogin AS 能否登录,
    rolsuper AS 是否超级管理员,
    rolcreatedb AS 能否建库
FROM pg_roles
WHERE rolname NOT LIKE 'pg_%';  -- 过滤掉所有以 pg_ 开头的系统角色

现在这个用户，可以登录，可以看到有哪些库，有哪些表，但是不能创建库，创建表，查询表数据

示例1：给用户设置某一个库的只读权限

-- 1. 创建用户
CREATE USER reader_user WITH PASSWORD 'fengfeng';

-- 2. 基础进门权
GRANT CONNECT ON DATABASE db TO reader_user;  -- 可以进入db这个库
GRANT USAGE ON SCHEMA public TO reader_user; -- 可以进入public这个模式

-- 3. 核心只读权（现在的表 + 序列）
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reader_user;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO reader_user;

-- 4. 自动化（未来的表）
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT ON TABLES TO reader_user;

示例2：给用户设置一个库的增删改查权限

CREATE USER feng_app WITH PASSWORD 'fengfeng';

GRANT CONNECT ON DATABASE fengfeng_db TO feng_app;
GRANT USAGE ON SCHEMA public TO feng_app;

-- 1. 针对表：赋予全部增删改查权限
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO feng_app;

-- 2. 针对序列：必须给权限！否则无法执行自增 ID 的插入
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA public TO feng_app;

-- 后续新创建的表也拥有权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO feng_app;

ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO feng_app;