Posts

一、为什么表权限还不够 普通权限控制的是： 1 某个用户能不能访问某张表 例如： 1 GRANT SELECT ON sales.orders TO report_user; 这表示 report_user 可以查询 sales.orders。 但很多企业场景需要更细的控制： 1 2 3 4 5 销售只能看自己区域的订单 医生只能看本科室病人的记录 租户只能访问自己的数据 分公司只能看本公司的财务数据 客服只能看被分配给自己的工单 这些不是“能不能访问表”的问题，而是： 1 能访问表里的哪些行 这就需要 PostgreSQL 的 RLS。 二、RLS 是什么 RLS 是 Row Level Security，行级安全。 表级权限控制： 1 能不能 SELECT orders 表 RLS 控制： 1 SELECT orders 时能看到哪些行 例如 orders 表中有字段： 1 2 3 tenant_id department_id owner_user RLS 可以根据当前会话、当前用户或应用设置的上下文，只返回符合条件的行。 ...

一、企业权限管理的目标 企业级权限管理不是简单地“让用户能连上数据库”，而是要回答几个问题： 1 2 3 4 5 6 7 8 谁可以登录？ 谁可以连接哪个数据库？ 谁可以访问哪个 schema？ 谁可以读写哪些表？ 谁可以修改结构？ 谁可以管理权限？ 谁执行过高危操作？ 账号离职或下线后如何安全清理？ 权限治理的目标是： 满足业务访问需要 遵守最小权限原则 降低误操作和泄露风险 方便审计 方便账号生命周期管理 避免权限长期失控 二、最小权限原则 最小权限原则是企业权限管理的核心。 含义是： 1 一个用户或程序只拥有完成工作所必需的最小权限。 不推荐： 1 2 GRANT ALL PRIVILEGES ON DATABASE sales_db TO app_user; ALTER ROLE app_user SUPERUSER; 推荐： 1 2 3 4 GRANT CONNECT ON DATABASE sales_db TO app_user; GRANT USAGE ON SCHEMA sales TO app_user; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA sales TO app_user; GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA sales TO app_user; 如果应用不需要删数据，就不要给 DELETE。 ...

一、为什么要单独设计只读用户和应用用户 生产环境中，不建议所有程序和人员都使用同一个数据库账号。 常见错误做法： 1 应用、报表、开发、DBA 都使用 postgres 或同一个 app_user 这样会带来几个问题： 无法区分是谁执行了操作 报表查询账号可能误删或误改数据 应用账号权限过大，一旦泄露风险很高 权限审计困难 无法做到最小权限 更合理的设计是： 1 2 3 4 5 6 7 readonly_user：只读查询 app_user：应用读写 etl_user：数据导入导出 backup_user：备份 monitor_user：监控 dba_user：日常管理 postgres：超级管理，不用于业务连接 本篇重点整理只读用户和应用读写用户的标准创建方法。 二、准备示例环境 假设数据库名为： 1 sales_db 业务 schema 为： 1 sales 创建 schema： 1 CREATE SCHEMA IF NOT EXISTS sales; 示例表： 1 2 3 4 5 6 7 8 9 CREATE TABLE sales.orders ( id bigserial PRIMARY KEY, order_no text NOT NULL, customer_id bigint NOT NULL, amount numeric(12,2) NOT NULL, status text NOT NULL, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now() ); 注意 bigserial 会创建序列，因此应用插入数据时除了表权限，还可能需要序列权限。 ...

一、GRANT 和 REVOKE 解决什么问题 PostgreSQL 中，角色属性和对象权限是两类不同的权限。 角色属性例如： 1 2 3 4 5 6 LOGIN SUPERUSER CREATEDB CREATEROLE CONNECTION LIMIT VALID UNTIL 这些通常通过 CREATE ROLE 或 ALTER ROLE 管理。 对象权限例如： 1 2 3 4 5 6 7 8 CONNECT USAGE CREATE SELECT INSERT UPDATE DELETE EXECUTE 这些通常通过 GRANT 和 REVOKE 管理。 简单说： 1 2 GRANT：授予权限 REVOKE：回收权限 二、数据库级权限 数据库级常见权限有： 1 2 3 CONNECT：允许连接数据库 CREATE：允许在数据库中创建 schema TEMPORARY / TEMP：允许创建临时表 允许用户连接数据库： ...

一、先看 PostgreSQL 的层级 PostgreSQL 的对象层级可以理解为： 1 2 3 4 5 6 7 8 PostgreSQL 实例 └── database └── schema ├── table ├── view ├── sequence ├── function └── type 也就是说，PostgreSQL 的 database 下面还有一层 schema。表、视图、函数等对象并不是直接属于 database，而是属于 database 里的某个 schema。 例如： 1 2 3 4 5 6 7 8 9 10 11 12 sales_db ├── public │ ├── users │ └── orders ├── sales │ ├── customers │ └── orders ├── finance │ ├── invoices │ └── payments └── audit └── operation_logs 这里 public.orders 和 sales.orders 可以同时存在，因为它们位于不同 schema，完整名称不同。 ...

一、为什么 PostgreSQL 权限容易混乱 很多人刚接触 PostgreSQL 权限时，会下意识拿 MySQL 来类比： 1 2 MySQL：用户 + 数据库 + 表权限 PostgreSQL：角色 + 数据库 + schema + 对象权限 + 默认权限 + RLS PostgreSQL 的权限体系更细，灵活性也更高，但如果没有先建立层级概念，就很容易出现下面这些问题： 用户明明有表的 SELECT 权限，却仍然提示 permission denied for schema 创建了只读用户，但用户还能在 public schema 里建表 给了 GRANT SELECT ON ALL TABLES，新建表却无法查询 不知道 CREATE USER 和 CREATE ROLE 到底有什么区别 不知道 public schema 和 PUBLIC 角色是不是同一个东西 删除用户时提示仍然有对象依赖，无法 DROP ROLE 要理解这些问题，必须先把 PostgreSQL 的权限模型分层看清楚。 二、PostgreSQL 的对象层级 PostgreSQL 常见层级如下： 1 2 3 4 5 6 7 8 PostgreSQL 实例 └── database └── schema ├── table ├── view ├── sequence ├── function └── type 一个 PostgreSQL 实例里可以有多个数据库。每个数据库内部又可以有多个 schema。表、视图、序列、函数等对象通常位于某个 schema 下面。 ...

一、为什么 PostgreSQL 有这么多索引类型 很多人刚接触 PostgreSQL 索引时，会自然地用 MySQL B+Tree 的思路去理解所有索引：索引就是按字段值排序，然后通过树结构快速定位行。 这个理解对 B-tree 索引基本成立，但不能覆盖 PostgreSQL 的全部索引体系。PostgreSQL 支持多种索引访问方法，常见的有： B-tree：默认索引，适合等值、范围、排序。 Hash：适合等值查询，实际使用频率低于 B-tree。 GIN：通用倒排索引，适合数组、JSONB、全文搜索。 GiST：通用搜索树，适合范围、空间、相似、最近邻。 SP-GiST：空间分区搜索树，适合某些非平衡分布的数据结构。 BRIN：块区域索引，适合超大表且数据物理顺序相关的场景。 本文重点解释最常被问到、也最容易混淆的四类： B-tree GIN GiST BRIN 尤其会重点讲清楚： GIN 的 posting list 和 posting tree 是什么。 GIN 找到 TID 后如何回表。 多个高频 key 的 posting tree 是否会占用很大空间。 GiST 和 BRIN 都像“摘要索引”，但区别在哪里。 千万级、亿级表上应该如何选择索引。 二、先理解 PostgreSQL 的行位置：TID / CTID 在理解 GIN、GiST、BRIN 之前，要先理解 PostgreSQL 索引里常见的一个概念：TID。 TID 可以理解为一行数据在 heap 表里的物理位置，形式类似： 1 (block number, offset number) 也就是： ...

1. 本文范围与参考 本文基于 MySQL 8.0（以 8.0.35 为讨论基线）InnoDB 的 MVCC（多版本并发控制） 与 一致性读 语义，聚焦： ReadView 各字段含义及与 全局事务 id 的关系； 聚簇索引行 与 undo 版本链； 本事务修改 与 他事务版本 在可见性判断上的不同路径； RR（REPEATABLE READ） 与 RC（READ COMMITTED） 下 ReadView 生命周期 的差异； 事务 id 尚未分配（常为 0） 时，不会以 0 进入活跃事务 id 列表；分配后才以正式 id 参与活跃事务管理。 官方文档入口： InnoDB Multi-Versioning Consistent Nonlocking Reads Transaction Isolation Levels 以下对源码字段的命名以教学常用归纳为主，与 storage/innodb 中结构体字段名可能略有出入，语义与手册一致。 2. 为什么需要 MVCC InnoDB 在不加锁的 SELECT（一致性非锁定读）下，需要回答： 当前会话应该看到该行哪一个已提交/未提交版本？ 若直接读最新聚簇记录，会与并发事务的未提交写冲突；若全部加锁，并发度下降。MVCC 通过保留历史版本（undo）与可见性判断（ReadView），使读操作在多数场景下不阻塞写，写不阻塞读（在可接受的隔离级别语义下）。 3. 一行数据与 undo 版本链 3.1 聚簇索引记录上的关键信息（概念） 对聚簇索引上一行（简化）： ...

本文由《MySQL 内部两阶段提交机制深度解析》与《MySQL 内部两阶段提交（2PC）深度解析》合并整理，去重后按「背景 → 流程 → 恢复 → 性能 → 参数 → 扩展 → 实战」组织。 一、背景：为什么需要内部两阶段提交 MySQL 的存储架构分为两层： Server 层：负责 SQL 解析、优化、执行，以及 binlog 的写入。 引擎层（InnoDB）：负责数据页的持久化与 redo log 的写入。 两层各自维护一套日志。事务提交时必须让两套日志在语义上一致；若简单「先写一个、再写另一个」而不协调，崩溃后极易出现主从不一致。MySQL 在引擎内部采用内部两阶段提交（Internal 2PC）（常以 XID 关联 redo 与 binlog），而不是分布式事务里的外部 2PC 协调者模型。 1.1 两个日志的分工 日志 层级 作用 常见格式/形态 redo log InnoDB 崩溃恢复，保证已提交变更可重做 物理日志（页修改） binlog MySQL Server 主从复制、备份与按时间点恢复（PITR） 逻辑日志（语句）或 ROW 为什么不能只保留一种日志？ redo log 与 InnoDB 紧耦合，其它存储引擎未必具备。 binlog 属于 Server 层，与引擎解耦，复制与备份都依赖它。 历史演进上 binlog 更早出现，redo 后补；二者长期并存。 1.2 若无 2PC：两种顺序都会导致主从不一致 先 redo、后 binlog，中间崩溃： ...

Orchestrator：MySQL 高可用复制拓扑管理与故障切换实战指南 一、引言 在 GTID 时代，MHA 显得有些落伍了，因此大家会优先选择了由 GitHub 团队开源的 MySQL 高可用组件 Orchestrator。 Orchestrator 是 MySQL 复制拓扑 HA，管理和可视化工具，目前 Booking、Github、Google/Vitess 等公司在使用。 项目地址：https://github.com/openark/orchestrator 二、Orchestrator 的特点 组件自身高可用：Orchestrator 节点之间通过 Raft 协议实现数据一致性即高可用性。 拓扑发现：主动搜寻拓扑并进行映射，读取基本的 MySQL 信息，例如复制状态和配置。 拓扑重组：可以通过 binlog file:position, GTID, Pseudo GTID, Binlog Servers 复制规则，对 MySQL 拓扑进行重组。 故障恢复：根据从拓扑本身获得的信息，它可以识别各种故障情况。可以手动或自动进行故障恢复，故障恢复前后都有对应的 Hook，比较灵活。 多种操作方式：提供了三种操作方式，API 接口调用、命令行操作、Web 页面调用。 其具有如下主要功能： 可以自动发现 MySQL 集群，通过 MySQL 节点信息获取相关 slave 的信息，进而获取整个集群拓扑结构并将信息存储在后端数据库中。 基于复制集管理规则，提供自由重构复制集拓扑结构的功能，如将某个 slave 节点移动到其他 master 下。 自动探测故障并采取相应恢复措施，实现 MySQL 服务高可用的目的，减少人工介入。 提供 Web UI，API 以及命令行等入口操作，方便用户监控和操作集群。 三、三大核心特性 Orchestrator 还有三个重要特性： 3.1 Discover（发现） Orchestrator 主动搜寻 MySQL 拓扑并进行映射。它能读取基本的 MySQL 信息，例如复制状态和配置。即使遇到故障，也可以为 MySQL 环境的拓扑提供流畅的可视化效果，包括复制问题。 ...